MYSQL entry 5: MYSQL Character Set

MYSQL entry 5: MYSQL Character Set link: MYSQL Entry 1: basic operations http://www.bkjia.com/database/201212/173868.htmlMYSQL Entry 2: Search Using Regular Expressions http://www.bkjia.com/database/201212/173869.htmlMYSQL Entry 3: Full Text Search http://www.bkjia.com/database/201212/173873.htmlMYSQL Entry 4: MYSQL Data Types http://www.bkjia.com/database/201212/175536.html MySQL Character Set Support has two aspects: Character set and Collation ). Www.2cto.com provides support for character sets in four layers: server, database, table, and connection ). I. MySQL Default Character Set MySQL specifies the character set to a database, a table, a column, and the character set to use. However, traditional programs do not use such complex configurations when creating databases and data tables. They use the default configuration. So where does the default configuration come from? (1) when MySQL is compiled, a default character set is specified, which is latin1; (2) when MySQL is installed, you can. ini) specifies a default character set. If not specified, this value inherits from the value specified during compilation. (3) When mysqld is started, you can specify a default character set in the command line parameter. If not specified, this value inherits from the configuration in the configuration file. In this case, character_set_server is set to the default character set. (4) when creating a new database, unless explicitly specified, the character set of this database is set to character_set_server by default; (5) When a database is selected, character_set_database is set to the default Character Set of this database; (6) When a table is created in this database, the default Character Set of the table is set to character_set_database, which is the default Character Set of this database; (7) When setting a column in a table, unless explicitly specified The default character set in this column is the default Character Set of the table. www.2cto.com simply summarizes that if no modification is made, all the column spaces in all the databases are stored in latin1, however, if we install MySQL, we usually choose multi-language support, that is, the installer will automatically set default_character_set to UTF-8 in the configuration file, which ensures that by default, all columns of all tables in all databases are stored in UTF-8. 2. View Character Set 1. View MySQL database server and database character set mysql> show variables like '% char % '; + character + | Variable_name | Value | + character + | character_set_client | latin1 | -- client character set | character_set_connection | latin1 | character_set_database | latin1 | -- database character set | bytes | binary | character_set_results | latin1 | character_set_server | latin1 | -- server character set | character_set_system | utf8 | character_sets_dir | D: \ APMServ5.2.6 \ MySQL5.1 \ share \ charsets \ | + ------------------------ + tables + 2. View MySQL Data table Character Set mysql> show table status from test like '% test % '; + --------------- + -------- + --------- + ------------ + signature + | Name | Engine | Version | Row_format | Collation | + --------------- + -------- + --------- + ------------ + + signature + | test_char | MyISAM | 10 | Fixed | Protocol | test_inn | InnoDB | 10 | Compact | latin1_swedish_ci | test_inn2 | InnoDB | 10 | Compact | utf8_general_ci | test_priority | MyISAM | 10 | Dynamic | latin1_swedish_ci | test_trans | InnoDB | 10 | Compact | latin1_swedish_ci | test_view | NULL | + ----------------- + -------- + accept + www.2cto.com 3. view the MySQL DATA column (column) character Set of mysql> show full columns from v9_picture; + --------------- + bytes + -------------- + | Field | Type | Collation | + ------------- + bytes + ---------------- + | id | mediumint (8) unsigned | NULL | catid | smallint (5) unsigned | NULL | typeid | smallint (5) unsigned | NULL | title | char (80) | gbk_chinese_ci | style | char (24) | gbk_chinese_ci | thumb | char (100) | gbk_chinese_ci | keywords | char (40) | gbk_chinese_ci | description | char (255) | gbk_chinese_ci | posids | tinyint (1) unsigned | NULL | url | char (100) | gbk_chinese_ci | listorder | tinyint (3) unsigned | NULL | status | tinyint (2) unsigned | NULL | sysadd | tinyint (1) unsigned | NULL | islink | tinyint (1) unsigned | NULL | username | char (20) | gbk_chinese_ci | inputtime | int (10) unsigned | NULL | updatetime | int (10) unsigned | NULL | + ------------- + ----------------------- + -------------- + 3. set and modify character set 1. create a character set for a database specified by the database mysql> create database mydb character set gb2312; mysql> use mydb mysql> show variables like 'character _ set_database '; + character + -------- + | Variable_name | Value | + ------------------------ + -------- + | character_set_database | gb2312 | + -------------------- + -------- + 2. Modify the character set of the database (1) modify mysql> alter database mydb character set utf8 through SQL; mysql> show variables like 'character _ set_database '; + ------------------------ + ------- + | Variable_name | Value | + ------------------------ + ------- + | character_set_database | utf8 | + ------------------------ + ------- + (2) modify the file $ MYSQL_HOME \ data \ mydb \ db through the configuration file. opt content default-character-set = utf8 default-collation = utf8_general_ci default-character-set = latin1 default-collation = latin1_swedish_ci takes effect after restarting MYSQL 3. Modifying character set mysql through SQL> set character_set_client = utf8; mysql> set character_set_connection = utf8; mysql> set character_set_database = utf8; mysql> set character = utf8; mysql> set character_set_server = utf8; mysql> set character_set_system = utf8; mysql> set collation_connection = utf8; mysql> set collation_database = utf8; mysql> set collation_server = utf8;