MySQL Character set settings

1.1mysql Character Set Knowledge:

Overview: A character set is a set of text symbols and their encoding, comparison rules. The MySQL database character set includes both the character set (character) and the proofing Rules (collation) concepts. Where the character set is used to define how the MySQL database is stored, and proofing rules are a way to define how strings are compared, and the character set and collation rules are one-to-many relationships

The command to view the MySQL available character set is show character set;

The 1.2mysql database uses a common character set description:

Common • Character sets	Length	Description
GBK	2	Not an international standard, but more supported systems
UTF-8	3	Combined in English and Chinese, this character set is recommended
Utf8mb4	4	UTF-8 Uniconde
Lantin1	1	The character set used by MySQL default installation

1.3 Choosing the right Character set

1) to meet the needs of the application support language, processing a variety of text, support different languages of the country and region, should choose the Unicode character set, namely Utf-8.

2) processing data volume is large, and to support Chinese, performance requirements are high, optional gbk (fixed length character set, occupies two bytes) on a large number of operations processing, comparison sorting, faster, higher.

3) Mobile Internet service, we recommend using the UTF8MB4 character set.

Settings for the 2.1mysql character set

1) MySQL settings for the character set by default

Show variables like ' character_set% ';

+--------------------------+-------------------------------------------+

| 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 | /application/mysql-5.6.36/share/charsets/|

2) Linux Server side

[Email protected] mysql]# cat/etc/sysconfig/i18n

Lang= "ZH_CN. UTF-8

3) Temporarily modify the MySQL client character set

mysql> set names UTF8;

Query OK, 0 rows Affected (0.00 sec)

Permanent modification:

[Client]

Default-character-set=utf8

4) Modify the MySQL server-side character set

Add Default-character-set=utf8 under Mysqld

Mysql5.5 and later versions

[Mysqld]

Character-set-server=utf8

5) To modify the library table character Set method:

Modify Library: Alter DATABASE databasename Character set ******

Modify table: ALTER TABLE tablename Character set ******

Changes to the library:

Example: show create Database oldboy\g;

1. Row ***************************

Database:oldboy

Create database:create Database ' Oldboy '/*!40100 DEFAULT CHARACTER SET GBK */

1 row in Set (0.00 sec)

Oldboy database Character set is currently GBK, modify it to UTF8, the command is as follows;

ALTER DATABASE Oldboy character set UTF8 collate utf8_general_ci;

Changes to the table:

ALTER TABLE student character set UTF8 collate utf8_general_ci;

2.2 Modification of the character set in the production environment

Description: How the production environment does not adjust the settings of the character set, and after the run found to meet the needs of the adjustment, but do not want to lose data, you can modify the character set, directly through the ALTER DATABASE databasename character set ****** and ALTER TABLE tablename character set ****** command to modify, only the modified inserted data is valid, the data inserted before the modification will still retain the previous character set, in order to implement the previous data can also modify the character set, you must export its data, Re-import again after adjustment.

Simulation process:

1) Export table structure

Mysqldump-uroot-p--default-character-set=utf8-d Oldboy>oldboy.sql

Description:--default-character-set=utf8: What character set to connect-D: Export only table structure, do not export data

Manually modify the character set in the Oldboy.sql table to be the new character set.

2) Ensure that the database is no longer updating data and exporting data.

Mysqldump-uroot-p--quick--no-create-info--extended-insert--DEFAULT-CHARACTER-SET=GBK oldboy>data.sql

Parameter description:

--quick: The function is to retrieve rows from the table one row at a time, instead of retrieving all rows and caching them in memory before output, to dump larger tables.

--extended-insert: To make the dump file smaller, reload the file to speed up the insertion.

--no-create-info: The Dump table CREATE TABLE statement is not recreated.

--DEFAULT-CHARACTER-SET=GBK: Preserves the character set of the original exported data so that the exported file does not appear garbled.

3) Open Data.sql, change set names GBK to set names UTF8

4) Create a new database with the new character set

Show CREATE DATABASE Oldboy default CharSet UTF8;

5) Create the table structure and execute the oldboy.sql.

Mysql-uroot-p Oldboy<oldboy.sql

6) Re-import data u, execute data.sql.

Mysql-uroot-p Oldboy<data.sql

MySQL Character set settings