Mysql character set adjustment Summary

Character Set is a set of symbols and encoding rules. Both the oracle database and mysql database have character set selection problems. Character sets are important for databases, because most of the data stored in the database is various texts. character sets have an important impact on database storage, processing performance, and data migration.

If the character set is not correctly selected during the database creation stage, you may need to replace the character set later. Character Set replacement is a costly operation, and there are also some risks, therefore, we recommend that you select the appropriate character set as required at the beginning of the application to avoid unnecessary adjustments in the future.

When mysql is compiled and installed, the method for specifying the character set is as follows:

./Configure -- with-charset = utf8

Mysql character set has four default settings: Server-level, database-level, table-level, and field-level. They are set in different places, and their functions are also different.
1. Set the server character set, which is determined when the mysql service is started.
You can set it in my. cnf:

[Mysql]

### Default Character Set: utf8

Default-character-set = utf8

[Mysqld]
### Default Character Set: utf8

Default-character-set = utf8

### Set utf8 encoding when connecting to the mysql database to run the mysql database as utf8)

Init_connect = 'set NAMES utf8'

You can also specify the following in the startup options:
Mysqld -- default-character-set = utf8

If no particular server character set is specified, latin1 (alias for the ISO-8859-1) is used by default as the server character set. In the preceding three settings, only the character set is specified and verification is not performed. We can use the show variables like 'Char % 'command to query the characters on the current server.
Set and proofread 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 default-character-set = utf8 is added, an error is returned when MYSQL is started. You can use character_set_server = utf8 to replace default-character-set = utf8, and then start it normally. This is because of the problem of identifying different MYSQL versions.

2. Database-level

Specify character set when creating a database

Mysql> create database my_db default charset utf8 COLLATE utf8_general_ci;

# Note the following sentence "COLLATE utf8_general_ci", which roughly means sorting by utf8 encoding format

If the database encoding is specified, the default character set of all data tables created in this database will be utf8.

Modify the MYSQL database encoding. If the MYSQL database encoding is incorrect, run the following command in MYSQL:

Alter database my_db default character set utf8;

The preceding command sets the MYSQL my_db database encoding to utf8.

3. Table-level

Specify character set when creating a table

Mysql> create table my_table (name varchar (20) not null default '') type = myisam default charset utf8;

# This statement creates a table and specifies the default character set as utf8.

Modify the MYSQL table encoding:

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 (110) after stu_id;

Add a field address after stu_id

Alter table test add id int unsigned not Null auto_increment primary key;

Modify the field encoding:

Alter table 'test' CHANGE 'name' VARCHAR (45) character set utf8 COLLATE utf8_bin NOT NULL

The above command is to change the name field encoding in the MYSQL database test table to utf8

The following code inserts a Chinese character in a command line:

Set names utf8; sometimes this sentence is critical!

Insert into charset values ('wang da ');

Note: The alter method cannot update the character set of an existing record. It only takes effect for the newly created table and record. To adjust the character set of an existing record, you must first export the data and re-import the data to completely modify the encoding.

How to adjust the exported characters:

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 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 and change set names latin1 to set names utf8.

: % S/latin1/utf8/g

Full text replacement

3. Use a new character set to create a new database

Create database mydata default charset utf8;

4. Create a table and run createtab. SQL.

Mysql-uroot-pmysql mydata <creattab. SQL

5. Import Data

Mysql-uroot-pmysql mydata <data. SQL

Note that the target character set must be greater than or equal to the source character set. Otherwise, some unsupported Chinese characters will be lost.

Appendix: Methods for upgrading old data

Take the original character set latin1 as an example to upgrade it to the utf8 character set. Original table: old_table (default charset = latin1), new table: new_table (default charset = utf8 ).

Step 1: Export old data

Mysqldump -- default-character-set = latin1-hlocalhost-uroot-B my_db -- tables old_table> old. SQL

Step 2: Convert the Encoding

Iconv-t utf8-f latin1-c old. SQL> new. SQL

Here, we assume that the original data is latin1 encoding by default.

Step 3: Import

Modify old. SQL and add an SQL statement: "SET NAMES utf8;" to save.

Mysql-hlocalhost-uroot my_db <new. SQL

Success!

Mysql collate rules:

* _ Bin: binary case sensitive collation, that is, case sensitive.
* _ Cs: case sensitive collation, case sensitive
* _ Ci: case insensitive collation, case insensitive

This article from the "Drop water stone sunjie" blog, please be sure to keep this source http://xjsunjie.blog.51cto.com/999372/1355013