MySQL Character set selection

One How to choose the right character set
For MySQL database, the character set is important, because the database stores most of the data is a variety of text, the character set on the database storage, processing performance will be affected.

The main consideration is a few factors
1. Meet the needs of the application support language, the application handles a variety of text, published to different languages of the country or region, you can choose the Unicode character set, MySQL can choose UTF-8
2. If the import of existing data is involved in the application, it is necessary to consider the compatibility of the database character set with the existing data.
Assuming that the data is GBK text, if you choose a different database character set, you may cause some text to not import correctly.
3. If the database needs to support the general is Chinese, the data is large, the performance requirements are very high, you can choose the double-byte fixed length encoding Chinese character set, such as GBK.
Because compared to UTF-8, GBK is small, each Chinese character only occupies 2 bytes, and UTF-8 Chinese character coding needs 3 bytes, which can reduce disk I/O, database cache, network transmission time, thereby improving performance.
If it is an English character, only a small number of Chinese characters, then choose UTF-8 better.
4. If the database requires a large number of character operations, such as comparisons, sorting, it may be better to choose a fixed-length character set, because the fixed-length character set is faster than the longer.

Second, the MySQL supported character set
MySQL server can support a variety of character sets, on the same server, the same database, the same table can specify a different character set.
See commands for all available character sets show character set;
For example, MySQL5.7 supports 41 types of characters that can be used
Mysql> show Character set;

MySQL's character set includes character set and proofing rules
1. Character set is used to define how MySQL stores strings
2. Proofing rules are used to define how strings are compared.
3. Character sets and proofing rules are one-to-many relationships
4. Each character set corresponds to at least one proofing rule
Example: View GBK's proofreading
Show collation like ' gbk% ';

Proofing rule naming conventions, which start with a character set name, usually include a language name, and are _ci (case insensitive), _cs (case sensitive), or _bin (two yuan)

Iv. settings for the MySQL character set
The 1.MySQL character set and proofing rules have 4 levels of default settings, server-level, database-level, table-level, and field-level.
2. Server character set and proofing rules
Default server character set using Latin1

You can set the server charset in My.cnf, and it will take effect when the reboot is set.
[Mysqld]
Character-set-server=utf8

3. View the character set of the current server

Mysql> Show variables like ' character_set_server ';
+----------------------+-------+
| variable_name | Value |
+----------------------+-------+
| Character_set_server | UTF8 |
+----------------------+-------+
1 row in Set (0.00 sec)

4. Review the proofing rules for the current server
Mysql> Show variables like ' collation_server ';
+------------------+-----------------+
| variable_name | Value |
+------------------+-----------------+
| Collation_server | Utf8_general_ci |
+------------------+-----------------+
1 row in Set (0.00 sec)

Note: To modify the character set after creating the database, you can use the ALTER DB_NAME character set
However, the data in the database can not be stored in accordance with the new character set, so the database character set can not modify the contents of the data directly.

5. Connecting character sets and proofing rules
Client and server interoperability, MySQL provides 3 different parameters: Character_set_client,character_set_connection and Character_set_result.
Character sets representing clients, connections, and returned results, respectively
Usually this 3 character set should be the same, in order to ensure that the data written by the user can be correctly read out
Can be set in MY.CNF
[MySQL]
Default-character-set=utf8
When the server starts, all connections are connected by default using the UTF8 character set, without the need to perform set names in the program.

Five, the character set modification steps
ALTER DATABASE db_name Character set = UTF8;
ALTER TABLE table_name Character set = UTF8;

MySQL Character set selection