MySQL Character set tuning summary--http://xjsunjie.blog.51cto.com/999372/1355013
The character set is a set of symbols and encoding rules, whether in the Oracle database or in the MySQL database, there is a character set selection problem. For the database, the character set is more important, because the database stores most of the data is a variety of text, character sets for the database storage, processing performance and data migration have a significant impact.
If the character set is not correctly selected at the time of database creation, then it may be necessary to replace the character set at a later stage, while the replacement of the character set is a costly operation and there is a certain risk, so we recommend that the appropriate character set be chosen correctly according to the requirements at the beginning of the application, avoiding unnecessary adjustments at a later stage.
When MySQL compiles the installation, specify the method of the character set:
./configure--with-charset=utf8
The MySQL character set has 4 levels of default settings: Server-level, database-level, table-level, and field-level. They are set up in different places, and the effects are not the same.
1, the server character set setting, when the MySQL service starts to determine.
Can be set in MY.CNF:
[MySQL]
# # # Default character set is UTF8
Default-character-set=utf8
[Mysqld]
# # # Default character set is UTF8
Default-character-set=utf8
# # # (set the MySQL database to use UTF8 encoding to let MySQL database run for UTF8)
init_connect= ' SET NAMES UTF8 '
Or specify in the startup options:
Mysqld--default-character-set=utf8
If you do not specify a specific server character set, the default is to use Latin1 (iso-8859-1 alias) as the server character set. The above three types of settings only specify the character set, not to do proofreading, we can use show variables like ' char% '; command query the current server's characters
Set and proofing rules.
Mysql>show variables like ' char% ';
+--------------------------+----------------------------+
| variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | UTF8 |
| character_set_connection | UTF8 |
| Character_set_database | UTF8 |
| Character_set_filesystem | binary |
| Character_set_results | UTF8 |
| Character_set_server | UTF8 |
| Character_set_system | UTF8 |
| Character_sets_dir | /usr/share/mysql/charsets/|
+--------------------------+----------------------------+
Note: If you add Default-character-set=utf8, MySQL starts an error. Can use Character_set_server=utf8 to replace Default-character-set=utf8, can start normally. This is because MySQL has different versions of the problem identified.
2. Database level
Specifying a character set when creating a database
Mysql>create DATABASE my_db default charset UTF8 COLLATE utf8_general_ci;
#注意后面这句话 "COLLATE utf8_general_ci", roughly meaning to sort by UTF8 encoded format when sorting
If you specify a database encoding, the default character set for all data tables created under this database will be UTF8.
To modify the MySQL database encoding, if the MySQL database encoding is incorrect, you can execute the following command in MySQL:
ALTER DATABASE my_db DEFAULT CHARACTER SET UTF8;
The above command is to set the MySQL my_db database encoding to UTF8
3. Table level
Specifying a character set when creating a table
Mysql>create table my_table (name varchar () NOT NULL default ') Type=myisam default CharSet UTF8;
#这句话就是创建一个表, specifies that the default character set is UTF8
To modify the encoding of a MySQL table:
ALTER TABLE my_table DEFAULT CHARACTER SET UTF8;
The above command is to change the encoding of a table my_table to UTF8
4. Field level
ALTER TABLE test Add column address varchar (a) after stu_id;
Add a field address after stu_id
ALTER TABLE test add ID int unsigned not Null auto_increment primary key;
To modify the encoding of a field:
ALTER TABLE ' test ' change ' name ' of ' name ' VARCHAR (CHARACTER SET UTF8 COLLATE utf8_bin not NULL
The above command is to change the field encoding of name in the MySQL Database test table to UTF8
The following code is inserted when inserting Chinese characters at the command line:
Set names UTF8; sometimes this is a very important sentence!
INSERT into charset values (' Talmai King Geshur ');
NOTE: Alter modifies a method that cannot update a character set that already has a record, and only takes effect on newly created tables and records. The adjustment of the existing recorded character set requires that the data be exported and then re-imported to fully modify the encoding after proper adjustment.
To export an imported character adjustment method:
Export table Structure
Mysqldump-uroot-pmysql--default-character-set=latin1-d my_db> Createtab.sql
Manually modify the character set in the CREATETAB.SQL table structure definition to be the new character set
1. Export All records
Mysqldump-uroot-pmysql--quick--no-create-info--extended-insert--default-character-set=latin1--host=localhost my _db> Data.sql
2. Open Data.sql, change set names latin1 to set names UTF8
:%s/latin1/utf8/g
Full text replacement
3. Create a new database with a new character set
Create DATABASE MyData default CharSet UTF8;
4, create the table, execute Createtab.sql
Mysql-uroot-pmysql Mydata<creattab.sql
5. Import data
Mysql-uroot-pmysql Mydata<data.sql
Note that if the target character set is greater than or equal to the source character set, some unsupported kanji data will be lost.
Attached: Old data upgrade method
As an example of the original character set, Latin1 is promoted to be the UTF8 character set. Original table: old_table (Default charset=latin1), new table: new_table (Default Charset=utf8).
First step: Export old data
Mysqldump--default-character-set=latin1-hlocalhost-uroot-b my_db--tables old_table > Old.sql
Step two: Convert the encoding
Iconv-t utf8-f latin1-c old.sql > New.sql
Here, it is assumed that the original data is latin1 encoded by default.
Step Three: Import
Modify Old.sql, add an SQL statement: "SET NAMES UTF8;", save.
Mysql-hlocalhost-uroot my_db < New.sql
Done!
Mysql COLLATE Rules:
*_bin: Indicates a binary case sensitive collation, which means
*_cs:case sensitive collation, case-sensitive
*_ci:case insensitive collation, case insensitive
MySQL Character Set tuning summary
