浅谈MySQL字符集
2024-08-27 02:49:13
Preface
MySQL use character set & collation to organize the different charater.It provides a flexible way in setting individual character set on a database,a table even on a single column of table.Each character set has a series of collations with one default collation.We can generally see the character set in MySQL as the combination of code page & character encoding.
In the early version of MySQL(eg. version 5.5) ,latin1 is the default character set which does not support Chinese characters.There're some other commonly used character set such as GBK,UTF-8.MySQL 5.7 chooses UTF-8 as default character set nowadays in order to support more characters of different languages.
Messy code is a general issue about character set.It always occur in our MySQL databases if we do not use character rules appropriately.Worse,it leads to data loss in some cases what is really a big trouble we should avoid.
Introduce
MySQL provides a lot of parameters to specify character set in various dimentionality.We should know clearly about the principle and function of each parameter to avert underlying messy code issue.Let's see details about it.
Procedure
Check the character set supported by MySQL(version 5.7).
(root@localhost mysql3306.sock)[(none)]>show character set;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | |
| dec8 | DEC West European | dec8_swedish_ci | |
| cp850 | DOS West European | cp850_general_ci | |
| hp8 | HP West European | hp8_english_ci | |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | |
| latin1 | cp1252 West European | latin1_swedish_ci | |
| latin2 | ISO - Central European | latin2_general_ci | |
| swe7 | 7bit Swedish | swe7_swedish_ci | |
| ascii | US ASCII | ascii_general_ci | |
| ujis | EUC-JP Japanese | ujis_japanese_ci | |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | |
| hebrew | ISO - Hebrew | hebrew_general_ci | |
| tis620 | TIS620 Thai | tis620_thai_ci | |
| euckr | EUC-KR Korean | euckr_korean_ci | |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | |
| greek | ISO - Greek | greek_general_ci | |
| cp1250 | Windows Central European | cp1250_general_ci | |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | |
| latin5 | ISO - Turkish | latin5_turkish_ci | |
| armscii8 | ARMSCII- Armenian | armscii8_general_ci | |
| utf8 | UTF- Unicode | utf8_general_ci | |
| ucs2 | UCS- Unicode | ucs2_general_ci | |
| cp866 | DOS Russian | cp866_general_ci | |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | |
| macce | Mac Central European | macce_general_ci | |
| macroman | Mac West European | macroman_general_ci | |
| cp852 | DOS Central European | cp852_general_ci | |
| latin7 | ISO - Baltic | latin7_general_ci | |
| utf8mb4 | UTF- Unicode | utf8mb4_general_ci | |
| cp1251 | Windows Cyrillic | cp1251_general_ci | |
| utf16 | UTF- Unicode | utf16_general_ci | |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | |
| cp1256 | Windows Arabic | cp1256_general_ci | |
| cp1257 | Windows Baltic | cp1257_general_ci | |
| utf32 | UTF- Unicode | utf32_general_ci | |
| binary | Binary pseudo charset | binary | |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | |
+----------+---------------------------------+---------------------+--------+
rows in set (0.00 sec) //There're 41 results of the supported character set.
//Each character set has a default collation.
//Maxlen is the max bytes of corresponding character set(eg. utf8mb4 supports 4 bytes).
Check character set parameters of current MySQL server.
(root@localhost mysql3306.sock)[(none)]>show variables like 'character%';
+--------------------------+----------------------------------------------------------------+
| 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/local/mysql-5.7.-linux-glibc2.-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
rows in set (0.00 sec) //character_set_client:It's used by client when connect to servers for requesting data.
//character_set_connection:It's used for those literals not have a character set introducer for conversion.
//character_set_database:It's used by default database.The value of "character_set_server" will be inherited if it is not specified.
//character_set_filesystem:It's used to interpret string literals refer to file names.
//character_set_results:It's used to return query results to the client.
//character_set_server:It's the default character set of server.
//character_set_system:It's used by server for storing identifiers,the value is utf8 forever.
//character_sets_dir:It's the directory where contains the xml files of installed character set.
The relationship of above character set parameters shows below.
Check collation parameters of current MySQL server.
(root@localhost mysql3306.sock)[(none)]>show variables like 'collation%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
rows in set (0.01 sec) //collation_connection:The collation of connection character set.
//collation_database:The collation of default database.It will inherite the value of "collation_server" if not specified.
//collation_server:The default collation of server.
//"ci" means Case Insensitive.
Case of change character set from utf8 to latin1 with "set names ...;".
(root@localhost mysql3306.sock)[(none)]>\s
--------------
mysql Ver 14.14 Distrib 5.7., for linux-glibc2. (x86_64) using EditLine wrapper Connection id:
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.-log MySQL Community Server (GPL)
Protocol version:
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql3306.sock
Uptime: hours min sec Threads: Questions: Slow queries: Opens: Flush tables: Open tables: Queries per second avg: 0.003
-------------- (root@localhost mysql3306.sock)[(none)]>set names latin1;
Query OK, rows affected (0.00 sec) (root@localhost mysql3306.sock)[(none)]>\s
--------------
mysql Ver 14.14 Distrib 5.7., for linux-glibc2. (x86_64) using EditLine wrapper Connection id:
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.-log MySQL Community Server (GPL)
Protocol version:
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /tmp/mysql3306.sock
Uptime: hours min sec Threads: Questions: Slow queries: Opens: Flush tables: Open tables: Queries per second avg: 0.003
-------------- (root@localhost mysql3306.sock)[(none)]>select @@character_set_client;
+------------------------+
| @@character_set_client |
+------------------------+
| latin1 |
+------------------------+
row in set (0.00 sec) (root@localhost mysql3306.sock)[(none)]>select @@character_set_connection;
+----------------------------+
| @@character_set_connection |
+----------------------------+
| latin1 |
+----------------------------+
row in set (0.00 sec) (root@localhost mysql3306.sock)[(none)]>select @@character_set_results;
+-------------------------+
| @@character_set_results |
+-------------------------+
| latin1 |
+-------------------------+
row in set (0.00 sec) (root@localhost mysql3306.sock)[(none)]>show variables like '%collation%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-------------------+
rows in set (0.00 sec) //The influence of command "set names latin1" to character set is to change "character_set_client","character_set_connection","character_set_results" into latin1.
//The influence of command "set names latin1" to collation is to change "collation_connection" into latin1.
Case of change character set from utf8 to latin1 with "set character set ...;".
(root@localhost mysql3306.sock)[(none)]>\s
--------------
mysql Ver 14.14 Distrib 5.7., for linux-glibc2. (x86_64) using EditLine wrapper Connection id:
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.-log MySQL Community Server (GPL)
Protocol version:
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: latin1
Conn. characterset: utf8
UNIX socket: /tmp/mysql3306.sock
Uptime: hours min sec Threads: Questions: Slow queries: Opens: Flush tables: Open tables: Queries per second avg: 0.004
-------------- (root@localhost mysql3306.sock)[(none)]>select @@character_set_client;
+------------------------+
| @@character_set_client |
+------------------------+
| latin1 |
+------------------------+
row in set (0.00 sec) (root@localhost mysql3306.sock)[(none)]>select @@character_set_connection;
+----------------------------+
| @@character_set_connection |
+----------------------------+
| utf8 |
+----------------------------+
row in set (0.00 sec) (root@localhost mysql3306.sock)[(none)]>select @@character_set_results;
+-------------------------+
| @@character_set_results |
+-------------------------+
| latin1 |
+-------------------------+
row in set (0.00 sec) (root@localhost mysql3306.sock)[(none)]>show variables like '%collation%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
rows in set (0.00 sec) //The two variables about "connection" was not altered this time.They were still utf8 relevant.
Case of change character set of server & database from utf8 to latin1.
(root@localhost mysql3306.sock)[(none)]>\s
--------------
mysql Ver 14.14 Distrib 5.7., for linux-glibc2. (x86_64) using EditLine wrapper Connection id:
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.-log MySQL Community Server (GPL)
Protocol version:
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql3306.sock
Uptime: hours min sec Threads: Questions: Slow queries: Opens: Flush tables: Open tables: Queries per second avg: 0.005
-------------- (root@localhost mysql3306.sock)[(none)]>set character_set_server=latin1;
Query OK, rows affected (0.00 sec) (root@localhost mysql3306.sock)[(none)]>set character_set_database=latin1;
Query OK, rows affected, warning (0.00 sec) (root@localhost mysql3306.sock)[(none)]>show warnings;
+---------+------+-------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------------------------------+
| Warning | | Updating 'character_set_database' is deprecated. It will be made read-only in a future release. |
+---------+------+-------------------------------------------------------------------------------------------------+
row in set (0.00 sec) (root@localhost mysql3306.sock)[(none)]>\s
--------------
mysql Ver 14.14 Distrib 5.7., for linux-glibc2. (x86_64) using EditLine wrapper Connection id:
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.-log MySQL Community Server (GPL)
Protocol version:
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql3306.sock
Uptime: hours min sec Threads: Questions: Slow queries: Opens: Flush tables: Open tables: Queries per second avg: 0.005
-------------- //It shows that change "character_set_server" online is not supported in future release because of safety concern.
//Change character set of database may bring about risk of data loss if your client program using the supersetwhile database using subset.(eg. client->utf8mb4,database->utf8,will lost emoji data.)
Example of messy code.
(root@localhost mysql3306.sock)[zlm]>create table test_charset(
-> s1 char() character set latin1 not null,
-> s2 char() char set gbk,
-> s3 varchar() charset utf8,
-> s4 varchar()) character set=utf8mb4 engine=innodb;
Query OK, rows affected (0.01 sec) (root@localhost mysql3306.sock)[zlm]>show create table test_charset;
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_charset | CREATE TABLE `test_charset` (
`s1` char() CHARACTER SET latin1 NOT NULL,
`s2` char() CHARACTER SET gbk DEFAULT NULL,
`s3` varchar() CHARACTER SET utf8 DEFAULT NULL,
`s4` varchar() DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
row in set (0.00 sec) (root@localhost mysql3306.sock)[zlm]>insert into test_charset values('ASCII','国标','万国','表情');
Query OK, row affected (0.00 sec) (root@localhost mysql3306.sock)[zlm]>select * from test_charset;
+-------+--------+--------+--------+
| s1 | s2 | s3 | s4 |
+-------+--------+--------+--------+
| ASCII | 国标 | 万国 | 表情 |
+-------+--------+--------+--------+
row in set (0.00 sec) (root@localhost mysql3306.sock)[zlm]>\s
--------------
mysql Ver 14.14 Distrib 5.7., for linux-glibc2. (x86_64) using EditLine wrapper Connection id:
Current database: zlm
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.-log MySQL Community Server (GPL)
Protocol version:
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql3306.sock
Uptime: hours min sec Threads: Questions: Slow queries: Opens: Flush tables: Open tables: Queries per second avg: 0.006
-------------- (root@localhost mysql3306.sock)[zlm]>set names latin1;
Query OK, rows affected (0.00 sec) (root@localhost mysql3306.sock)[zlm]>\s
--------------
mysql Ver 14.14 Distrib 5.7., for linux-glibc2. (x86_64) using EditLine wrapper Connection id:
Current database: zlm
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.-log MySQL Community Server (GPL)
Protocol version:
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /tmp/mysql3306.sock
Uptime: hours min sec Threads: Questions: Slow queries: Opens: Flush tables: Open tables: Queries per second avg: 0.008
-------------- (root@localhost mysql3306.sock)[zlm]> (root@localhost mysql3306.sock)[zlm]>select * from test_charset;
+-------+------+------+------+
| s1 | s2 | s3 | s4 |
+-------+------+------+------+
| ASCII | ?? | ?? | ?? |
+-------+------+------+------+
row in set (0.00 sec) (root@localhost mysql3306.sock)[zlm]>set names gbk;
Query OK, rows affected (0.00 sec) (root@localhost mysql3306.sock)[zlm]>\s
--------------
mysql Ver 14.14 Distrib 5.7., for linux-glibc2. (x86_64) using EditLine wrapper Connection id:
Current database: zlm
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.-log MySQL Community Server (GPL)
Protocol version:
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: gbk
Conn. characterset: gbk
UNIX socket: /tmp/mysql3306.sock
Uptime: hours min sec Threads: Questions: Slow queries: Opens: Flush tables: Open tables: Queries per second avg: 0.006
-------------- (root@localhost mysql3306.sock)[zlm]>select * from test_charset;
+-------+------+------+------+
| s1 | s2 | s3 | s4 |
+-------+------+------+------+
| ASCII | ¹螠 | β¹ | ± |
+-------+------+------+------+
row in set (0.00 sec) (root@localhost mysql3306.sock)[zlm]>set names utf8mb4;
Query OK, rows affected (0.00 sec) (root@localhost mysql3306.sock)[zlm]>\s
--------------
mysql Ver 14.14 Distrib 5.7., for linux-glibc2. (x86_64) using EditLine wrapper Connection id:
Current database: zlm
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.-log MySQL Community Server (GPL)
Protocol version:
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /tmp/mysql3306.sock
Uptime: hours min sec Threads: Questions: Slow queries: Opens: Flush tables: Open tables: Queries per second avg: 0.006
-------------- (root@localhost mysql3306.sock)[zlm]>select * from test_charset;
+-------+--------+--------+--------+
| s1 | s2 | s3 | s4 |
+-------+--------+--------+--------+
| ASCII | 国标 | 万国 | 表情 |
+-------+--------+--------+--------+
row in set (0.00 sec) //MySQL support define character set on database,table even on a single column.
//Messy code will occur when "character_set_result" is subset of the value of character set which has been stored only if it turns back to the value equal or bigger than the stored value.
Example of losting data.
(root@localhost mysql3306.sock)[(none)]>\s
--------------
mysql Ver 14.14 Distrib 5.7., for linux-glibc2. (x86_64) using EditLine wrapper Connection id:
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.-log MySQL Community Server (GPL)
Protocol version:
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql3306.sock
Uptime: min sec Threads: Questions: Slow queries: Opens: Flush tables: Open tables: Queries per second avg: 0.219
-------------- (root@localhost mysql3306.sock)[(none)]>set @@character_set_server=latin1;
Query OK, rows affected (0.00 sec) (root@localhost mysql3306.sock)[(none)]>set @@character_set_database=latin1;
Query OK, rows affected, warning (0.01 sec) (root@localhost mysql3306.sock)[(none)]>set @@character_set_connection=latin1;
Query OK, rows affected (0.00 sec) (root@localhost mysql3306.sock)[(none)]>\s
--------------
mysql Ver 14.14 Distrib 5.7., for linux-glibc2. (x86_64) using EditLine wrapper Connection id:
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.-log MySQL Community Server (GPL)
Protocol version:
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: latin1
UNIX socket: /tmp/mysql3306.sock
Uptime: min sec Threads: Questions: Slow queries: Opens: Flush tables: Open tables: Queries per second avg: 0.213
-------------- (root@localhost mysql3306.sock)[(none)]>insert into test_charset values('ASCII','国标','万国','表情');
ERROR (3D000): No database selected
(root@localhost mysql3306.sock)[(none)]>use zlm
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A Database changed
(root@localhost mysql3306.sock)[zlm]>insert into test_charset values('ASCII','国标','万国','表情');
Query OK, row affected, warnings (0.01 sec) (root@localhost mysql3306.sock)[zlm]>show warnings;
+---------+------+-----------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------+
| Warning | | Invalid utf8 character string: '\xE5\x9B\xBD\xE6\xA0\x87' |
| Warning | | Invalid utf8 character string: '\xE4\xB8\x87\xE5\x9B\xBD' |
| Warning | | Invalid utf8 character string: '\xE8\xA1\xA8\xE6\x83\x85' |
+---------+------+-----------------------------------------------------------+
rows in set (0.00 sec) (root@localhost mysql3306.sock)[zlm]>select @@character_set_results;
+-------------------------+
| @@character_set_results |
+-------------------------+
| utf8 |
+-------------------------+
row in set (0.00 sec) (root@localhost mysql3306.sock)[zlm]>select * from test_charset;
+-------+--------+--------+--------+
| s1 | s2 | s3 | s4 |
+-------+--------+--------+--------+
| ASCII | 国标 | 万国 | 表情 |
| ASCII | ?? | ?? | ?? |
+-------+--------+--------+--------+
rows in set (0.00 sec) (root@localhost mysql3306.sock)[zlm]>set @@character_set_results=latin1;
Query OK, rows affected (0.00 sec) (root@localhost mysql3306.sock)[zlm]>select @@character_set_results;
+-------------------------+
| @@character_set_results |
+-------------------------+
| latin1 |
+-------------------------+
row in set (0.00 sec) (root@localhost mysql3306.sock)[zlm]>select * from test_charset;
+-------+------+------+------+
| s1 | s2 | s3 | s4 |
+-------+------+------+------+
| ASCII | ?? | ?? | ?? |
| ASCII | ?? | ?? | ?? |
+-------+------+------+------+
rows in set (0.00 sec) //The data of first row has been correctly resored in database.
//The data of second row has lost the Chinese character data.
//The value of "character_set_result" only influence the screen output.
//There's a data loss risk while value of character set of client is superset of the one of database.
Summary
- MySQL character set is flexible and various,be more careful when modify data.
- The parameter "default_character_set" only affect original mysql client not for the other client tools.
- Make sure your character set of client is a subset but superset of the value of database when modifying data.Meanwhile it needs to be small than character set of connection to avoid data loss.
- It's recommended to set character set of database to a big set such as utf8 even utf8mb4 to be compatible with most characters of various languages.
最新文章
- redux-undo
- 跟我学Windows Azure 四 Cloud Service中的WebRole与WorkRole，及他们之间的通信
- android 使用两个surfaceview 在摄像机画面上绘图
- phpeclipse xdebug 配置配置 -摘自网络
- 关于常用meta的总结
- Redis的五种数据类型及方法
- Python的下划线_
- [编织消息框架][JAVA核心技术]动态代理应用12-总结
- 二叉搜索树的第K大节点
- LR与SVM的异同
- vue-router-transiton
- rocketmq中的NettyRemotingClient类的简单分析
- Codeforces 1082 G - Petya and Graph
- php自定义session存储路径
- 搭建 FTP 文件服务vsftpd
- 要点Java20 java.util.Collections
- java线程-synchronized实现可见性代码
- SpringBoot学习（一）
- 轻松编写 C++ 单元测试
- 云存储上传控件(cloud2)-Xproer.CloudUploader