首页 / MYSQL / MySQL 字符集问题
MySQL 字符集问题
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL 字符集问题，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含27303字，纯文字阅读大概需要40分钟。
内容图文
MySQL的字符集和排序：
MySQL数据库的字符集包含两个基本概念：“字符集”和“排序”
“字符集”的英文是 character set，简称 charset。
“排序”的英文是 collate（动词），collation（名词）。
常见的字符集有：ascii、big5、bgk、latin1、utf8。
MySQL 5.3.3 中，加入了几个新的字符集：utf16、utf32、utf8mb4。
MySQL 中的 utf8 最多支持 3 字节的文字（实际上 utf8 是 utf8mb3 的别名），utf8mb4 最多支持 4 字节的文字。
“字符集”是数据库存储字符时用的编码格式，“排序”是使用 ORDER 或 WHERE a>100 等语句时，比较字符大小的方法。
utf8mb4_general_ci、utf8mb4_unicode_ci、utf8mb4_bin。
bin 指二进制比较，general 指比较字符串的编码值（实际比较的是 unicode 值）。
unicode 在 general 的基础上，增加了字母组合的处理，如德文中 ? 和 ss 是相同的。
下段摘自https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html
For any Unicode character set, operations performed using the xxx_general_ci collation are faster than those for the xxx_unicode_ci collation. For example, comparisons for the utf8_general_ci collation are faster, but slightly less correct, than comparisons for utf8_unicode_ci. The reason is that utf8_unicode_ci supports mappings such as expansions; that is, when one character compares as equal to combinations of other characters. For example, ? is equal to ss in German and some other languages. utf8_unicode_ci also supports contractions and ignorable characters. utf8_general_ci is a legacy collation that does not support expansions, contractions, or ignorable characters. It can make only one-to-one comparisons between characters.
查看数据库支持的字符集（MySQL 8.0）：
mysql> show charset;
或
mysql> show character set; +----------+---------------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+---------------------------------+---------------------+--------+ | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | binary | Binary pseudo charset | binary | 1 | | cp1250 | Windows Central European | cp1250_general_ci | 1 | | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 | | cp1256 | Windows Arabic | cp1256_general_ci | 1 | | cp1257 | Windows Baltic | cp1257_general_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | cp852 | DOS Central European | cp852_general_ci | 1 | | cp866 | DOS Russian | cp866_general_ci | 1 | | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | | euckr | EUC-KR Korean | euckr_korean_ci | 2 | | gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 | | greek | ISO 8859-7 Greek | greek_general_ci | 1 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 | | latin1 | cp1252 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | macce | Mac Central European | macce_general_ci | 1 | | macroman | Mac West European | macroman_general_ci | 1 | | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | | swe7 | 7bit Swedish | swe7_swedish_ci | 1 | | tis620 | TIS620 Thai | tis620_thai_ci | 1 | | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 | +----------+---------------------------------+---------------------+--------+ 41 rows in set (0.00 sec)
查看某个字符集支持的排序（MySQL 8.0）：
mysql> show collation; +----------------------------+----------+-----+---------+----------+---------+---------------+ | Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute | +----------------------------+----------+-----+---------+----------+---------+---------------+ | armscii8_bin | armscii8 | 64 | | Yes | 1 | PAD SPACE | ...... ...... ...... ...... ...... ...... ...... .......... | utf8_vietnamese_ci | utf8 | 215 | | Yes | 8 | PAD SPACE | +----------------------------+----------+-----+---------+----------+---------+---------------+ 272 rows in set (0.00 sec) latin1 支持的排序： mysql> show collation like 'latin1%'; +-------------------+---------+----+---------+----------+---------+---------------+ | Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute | +-------------------+---------+----+---------+----------+---------+---------------+ | latin1_bin | latin1 | 47 | | Yes | 1 | PAD SPACE | | latin1_danish_ci | latin1 | 15 | | Yes | 1 | PAD SPACE | | latin1_general_ci | latin1 | 48 | | Yes | 1 | PAD SPACE | | latin1_general_cs | latin1 | 49 | | Yes | 1 | PAD SPACE | | latin1_german1_ci | latin1 | 5 | | Yes | 1 | PAD SPACE | | latin1_german2_ci | latin1 | 31 | | Yes | 2 | PAD SPACE | | latin1_spanish_ci | latin1 | 94 | | Yes | 1 | PAD SPACE | | latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 | PAD SPACE | +-------------------+---------+----+---------+----------+---------+---------------+ 8 rows in set (0.00 sec) utf8mb4 支持的排序： mysql> show collation like 'utf8mb4%'; +----------------------------+---------+-----+---------+----------+---------+---------------+ | Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute | +----------------------------+---------+-----+---------+----------+---------+---------------+ | utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD | | utf8mb4_0900_as_ci | utf8mb4 | 305 | | Yes | 0 | NO PAD | | utf8mb4_0900_as_cs | utf8mb4 | 278 | | Yes | 0 | NO PAD | | utf8mb4_0900_bin | utf8mb4 | 309 | | Yes | 1 | NO PAD | | utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | PAD SPACE | | utf8mb4_croatian_ci | utf8mb4 | 245 | | Yes | 8 | PAD SPACE | | utf8mb4_cs_0900_ai_ci | utf8mb4 | 266 | | Yes | 0 | NO PAD | | utf8mb4_cs_0900_as_cs | utf8mb4 | 289 | | Yes | 0 | NO PAD | | utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 | PAD SPACE | | utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 | PAD SPACE | | utf8mb4_da_0900_ai_ci | utf8mb4 | 267 | | Yes | 0 | NO PAD | | utf8mb4_da_0900_as_cs | utf8mb4 | 290 | | Yes | 0 | NO PAD | | utf8mb4_de_pb_0900_ai_ci | utf8mb4 | 256 | | Yes | 0 | NO PAD | | utf8mb4_de_pb_0900_as_cs | utf8mb4 | 279 | | Yes | 0 | NO PAD | | utf8mb4_eo_0900_ai_ci | utf8mb4 | 273 | | Yes | 0 | NO PAD | | utf8mb4_eo_0900_as_cs | utf8mb4 | 296 | | Yes | 0 | NO PAD | | utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 | PAD SPACE | | utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 | PAD SPACE | | utf8mb4_es_0900_ai_ci | utf8mb4 | 263 | | Yes | 0 | NO PAD | | utf8mb4_es_0900_as_cs | utf8mb4 | 286 | | Yes | 0 | NO PAD | | utf8mb4_es_trad_0900_ai_ci | utf8mb4 | 270 | | Yes | 0 | NO PAD | | utf8mb4_es_trad_0900_as_cs | utf8mb4 | 293 | | Yes | 0 | NO PAD | | utf8mb4_et_0900_ai_ci | utf8mb4 | 262 | | Yes | 0 | NO PAD | | utf8mb4_et_0900_as_cs | utf8mb4 | 285 | | Yes | 0 | NO PAD | | utf8mb4_general_ci | utf8mb4 | 45 | | Yes | 1 | PAD SPACE | | utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 | PAD SPACE | | utf8mb4_hr_0900_ai_ci | utf8mb4 | 275 | | Yes | 0 | NO PAD | | utf8mb4_hr_0900_as_cs | utf8mb4 | 298 | | Yes | 0 | NO PAD | | utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 | PAD SPACE | | utf8mb4_hu_0900_ai_ci | utf8mb4 | 274 | | Yes | 0 | NO PAD | | utf8mb4_hu_0900_as_cs | utf8mb4 | 297 | | Yes | 0 | NO PAD | | utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 | PAD SPACE | | utf8mb4_is_0900_ai_ci | utf8mb4 | 257 | | Yes | 0 | NO PAD | | utf8mb4_is_0900_as_cs | utf8mb4 | 280 | | Yes | 0 | NO PAD | | utf8mb4_ja_0900_as_cs | utf8mb4 | 303 | | Yes | 0 | NO PAD | | utf8mb4_ja_0900_as_cs_ks | utf8mb4 | 304 | | Yes | 24 | NO PAD | | utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 | PAD SPACE | | utf8mb4_la_0900_ai_ci | utf8mb4 | 271 | | Yes | 0 | NO PAD | | utf8mb4_la_0900_as_cs | utf8mb4 | 294 | | Yes | 0 | NO PAD | | utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 | PAD SPACE | | utf8mb4_lt_0900_ai_ci | utf8mb4 | 268 | | Yes | 0 | NO PAD | | utf8mb4_lt_0900_as_cs | utf8mb4 | 291 | | Yes | 0 | NO PAD | | utf8mb4_lv_0900_ai_ci | utf8mb4 | 258 | | Yes | 0 | NO PAD | | utf8mb4_lv_0900_as_cs | utf8mb4 | 281 | | Yes | 0 | NO PAD | | utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 | PAD SPACE | | utf8mb4_pl_0900_ai_ci | utf8mb4 | 261 | | Yes | 0 | NO PAD | | utf8mb4_pl_0900_as_cs | utf8mb4 | 284 | | Yes | 0 | NO PAD | | utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 | PAD SPACE | | utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 | PAD SPACE | | utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 | PAD SPACE | | utf8mb4_ro_0900_ai_ci | utf8mb4 | 259 | | Yes | 0 | NO PAD | | utf8mb4_ro_0900_as_cs | utf8mb4 | 282 | | Yes | 0 | NO PAD | | utf8mb4_ru_0900_ai_ci | utf8mb4 | 306 | | Yes | 0 | NO PAD | | utf8mb4_ru_0900_as_cs | utf8mb4 | 307 | | Yes | 0 | NO PAD | | utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 | PAD SPACE | | utf8mb4_sk_0900_ai_ci | utf8mb4 | 269 | | Yes | 0 | NO PAD | | utf8mb4_sk_0900_as_cs | utf8mb4 | 292 | | Yes | 0 | NO PAD | | utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 | PAD SPACE | | utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 | PAD SPACE | | utf8mb4_sl_0900_ai_ci | utf8mb4 | 260 | | Yes | 0 | NO PAD | | utf8mb4_sl_0900_as_cs | utf8mb4 | 283 | | Yes | 0 | NO PAD | | utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 | PAD SPACE | | utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 | PAD SPACE | | utf8mb4_sv_0900_ai_ci | utf8mb4 | 264 | | Yes | 0 | NO PAD | | utf8mb4_sv_0900_as_cs | utf8mb4 | 287 | | Yes | 0 | NO PAD | | utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 | PAD SPACE | | utf8mb4_tr_0900_ai_ci | utf8mb4 | 265 | | Yes | 0 | NO PAD | | utf8mb4_tr_0900_as_cs | utf8mb4 | 288 | | Yes | 0 | NO PAD | | utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 | PAD SPACE | | utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 | PAD SPACE | | utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 | PAD SPACE | | utf8mb4_vietnamese_ci | utf8mb4 | 247 | | Yes | 8 | PAD SPACE | | utf8mb4_vi_0900_ai_ci | utf8mb4 | 277 | | Yes | 0 | NO PAD | | utf8mb4_vi_0900_as_cs | utf8mb4 | 300 | | Yes | 0 | NO PAD | | utf8mb4_zh_0900_as_cs | utf8mb4 | 308 | | Yes | 0 | NO PAD | +----------------------------+---------+-----+---------+----------+---------+---------------+ 75 rows in set (0.00 sec)
上表中可以看出，latin1 的默认排序是 latin1_swedish_ci，utf8mb4 的默认排序是 utf8mb4_0900_ai_ci。
对于字符集的设置，有几个级别：
- 数据库程序实例级别（指一个运行着的数据库进程）
- 数据库级别
- 数据表级别
- 数据列级别
- SQL 语句级别
优先级依次递增
对于数据库实例级别的设置，有两个方法：
1、修改 my.cnf 文件：
character_set_server=utf8mb4 ; 如果没设置 character_set_server，MySQL 8.0 之前默认是 latin1，MySQL 8.0 及之后默认是 utf8mb4 collation=utf8mb4_0900_ai_ci ; 如果没设置 collation，则使用字符集的默认排序 ; latin1 的默认排序是 latin1_swedish_ci，utf8mb4 的默认排序是 utf8mb4_0900_ai_ci
2、启动服务时设定
mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_0900_ai_ci
这两个我都没试过，因为新建数据库和新建数据表时，最好显式提供 character set 和 collation，数据库实例级别的设置对我们没有任何影响。
对于数据库级别的设置（MySQL 5.7）：
下例：MySQL 5.7 默认字符集是 latin1，新建数据库时，不指定字符集，便使用默认字符集 latin1。
mysql> create database mytest; Query OK, 1 row affected mysql> show create database mytest; +----------+-------------------------------------------------------------------+ | Database | Create Database | +----------+-------------------------------------------------------------------+ | mytest | CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+-------------------------------------------------------------------+ 1 row in set mysql> drop database mytest; Query OK, 0 rows affected
下例：新建数据库时，指定字符集为 utf8mb4。
mysql> create database mytest default character set utf8mb4; Query OK, 1 row affected mysql> show create database mytest; +----------+--------------------------------------------------------------------+ | Database | Create Database | +----------+--------------------------------------------------------------------+ | mytest | CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ | +----------+--------------------------------------------------------------------+ 1 row in set mysql> drop database mytest; Query OK, 0 rows affected
下例：新建数据库时，指定字符集为 utf8mb4，指定排序为 utf8mb4_general_ci（MySQL 8.0 之前，没有 utf8mb4_0900_ai_ci）。
数据库 DDL 不显示默认排序规则。
mysql> create database mytest default character set utf8mb4 collate utf8mb4_general_ci; Query OK, 1 row affected mysql> show create database mytest; +----------+--------------------------------------------------------------------+ | Database | Create Database | +----------+--------------------------------------------------------------------+ | mytest | CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ | +----------+--------------------------------------------------------------------+ 1 row in set mysql> drop database mytest; Query OK, 0 rows affected
下例：新建数据库时，指定字符集为 utf8mb4，指定排序为 utf8mb4_bin。
mysql> create database mytest default character set utf8mb4 collate utf8mb4_bin; Query OK, 1 row affected mysql> show create database mytest; +----------+----------------------------------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------------------------------+ | mytest | CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ | +----------+----------------------------------------------------------------------------------------+ 1 row in set mysql> drop database mytest; Query OK, 0 rows affected
下例：修改已有数据库的字符集和排序。
mysql> alter database mytest default character set utf8mb4; Query OK, 1 row affected mysql> alter database mytest collate utf8mb4_bin; Query OK, 1 row affected mysql> alter database mytest default character set utf8mb4 collate utf8mb4_bin; Query OK, 1 row affected
数据表级别
TODO
数据列级别
TODO
SQL 语句级别
TODO
数据库中字符集相关的几个变量：
MySQL [(none)]> show variables like '%char%'; mysql> show variables like '%char%';
+--------------------------+-----------------------------------------------+ +--------------------------+-----------------------------------------------+
| Variable_name | Value | | Variable_name | Value |
+--------------------------+-----------------------------------------------+ +--------------------------+-----------------------------------------------+
| character_set_client | utf8 | | character_set_client | gbk |
| character_set_connection | utf8 | | character_set_connection | gbk |
| character_set_database | latin1 | | character_set_database | latin1 |
| character_set_filesystem | binary | | character_set_filesystem | binary |
| character_set_results | utf8 | | character_set_results | gbk |
| character_set_server | latin1 | | character_set_server | latin1 |
| character_set_system | utf8 | | character_set_system | utf8 |
| character_sets_dir | d:\wamp\bin\mysql\mysql5.7.24\share\charsets\ | | character_sets_dir | d:\wamp\bin\mysql\mysql5.7.24\share\charsets\ |
+--------------------------+-----------------------------------------------+ +--------------------------+-----------------------------------------------+
8 rows in set, 1 warning (0.006 sec) 8 rows in set, 1 warning (0.01 sec)
MySQL [(none)]> use mytest; mysql> use mytest;
Database changed Database changed
MySQL [mytest]> show variables like '%char%'; mysql> show variables like '%char%';
+--------------------------+-----------------------------------------------+ +--------------------------+-----------------------------------------------+
| Variable_name | Value | | Variable_name | Value |
+--------------------------+-----------------------------------------------+ +--------------------------+-----------------------------------------------+
| character_set_client | utf8 | | character_set_client | gbk |
| character_set_connection | utf8 | | character_set_connection | gbk |
| character_set_database | utf8mb4 | | character_set_database | utf8mb4 |
| character_set_filesystem | binary | | character_set_filesystem | binary |
| character_set_results | utf8 | | character_set_results | gbk |
| character_set_server | latin1 | | character_set_server | latin1 |
| character_set_system | utf8 | | character_set_system | utf8 |
| character_sets_dir | d:\wamp\bin\mysql\mysql5.7.24\share\charsets\ | | character_sets_dir | d:\wamp\bin\mysql\mysql5.7.24\share\charsets\ |
+--------------------------+-----------------------------------------------+ +--------------------------+-----------------------------------------------+
8 rows in set, 1 warning (0.007 sec) 8 rows in set, 1 warning (0.00 sec)
上例：
使用MySQL 5.7。
左侧展示使用 Cygwin 连接的操作结果，右侧展示使用 cmd 连接的操作结果。
可以看出： character_set_client 、 character_set_connection 、 character_set_results 三个变量，跟客户端使用的字符集有关。Cygwin 使用 utf8 字符集，这三个变量就是 utf8，cmd 使用 gbk 字符集，这三个变量就是 bgk。
character_set_server 变量：此变量是数据库实例级别的字符集设置值。
character_set_database 变量：此变量是数据库级别的字符集设置值。此实例中，第一次执行 show variables like '%char%'; 时，还未选择任何数据库，此变量值默认等于 character_set_server ，即 latin1 。选择 mytest 数据库后，此变量变为 mytest 数据库的字符集设置，即 utf8mb4 。
乱码是怎样产生的（个人理解）
为方便说明，以下提到字符集时，同一个数据库中，数据表、数据列使用与数据库相同的字符集。
数据库相关的乱码，产生的时机有两个：一是往数据库中存储时产生，二是从数据库中取出时产生。
数据库为保证内部编码的正确性，使客户端的字符集与服务端的字符集无关。
大体流程是：
写入数据库时：
- 通过 character_set_client 判断客户端使用的字符集 charset_from
- 通过 character_set_server 、 character_set_database 、数据表、数据列、SQL语句的优先级，确定数据存储时使用的字符集charset_to
- 把客户端传入的字符数据，由charset_from 转换为charset_to ，存储在数据库文件中
读取数据库时：
- 通过 character_set_server 、 character_set_database 、数据库、数据列、SQL语句的优先级，确定数据存储时使用的字符集 charset_from
- 通过 character_set_results （注意不是 character_set_client ）判断客户端希望收到的字符集 charset_to
- 把查询结果中的字符数据，由 charset_from 转换为 charset_to ，返回给客户端
从流程上可以看出，客户端使用什么样的字符集，与数据库使用什么样的字符集，是无关的（不考虑转换不成功不能存储的问题）。
只要数据库设置为 utf8mb4（可以存储所有现有字符），客户端 set names ******; 设置好客户端使用的字符集， 不存在乱码问题。
现在主流版本的 MySQL（服务端和客户端配合使用），在存入字符时，会做字符有效性检查，在一定程度上保证存入字符的正确性。下例左侧服务端和客户端为 MySQL 5.0.77，右侧服务端和客户端为 MySQL 5.7.24：
客户端均使用cmd（gbk）
mysql> show variables like 'version'; mysql> show variables like 'version'; +---------------+------------------+ +---------------+--------+ | Variable_name | Value | | Variable_name | Value | +---------------+------------------+ +---------------+--------+ | version | 5.0.77-community | | version | 5.7.24 | +---------------+------------------+ +---------------+--------+ 1 row in set (0.00 sec) 1 row in set, 1 warning (0.01 sec) mysql> create database mytest default character set latin1; mysql> create database mytest default character set latin1; Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) mysql> use mytest; mysql> use mytest; Database changed Database changed mysql> create table mytable (name text); mysql> create table mytable (name text); Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.07 sec) mysql> insert into mytable (name) values ('中文'); mysql> insert into mytable (name) values ('中文'); Query OK, 1 row affected (0.00 sec) ERROR 1366 (HY000): Incorrect string value: '\xD6\xD0\xCE\xC4' for column 'name' at row 1 mysql> select * from mytable; mysql> select * from mytable; +------+ Empty set (0.00 sec) | name | +------+ | 中文 | +------+ 1 row in set (0.00 sec)
左侧的 MySQL 5.0.77，可以存入中文，可以取出中文。
但是，存入的字符串是从 gbk 转为 latin1 （实际是不能转换成功的，个人认为这种转换是将每个汉字的两个 gbk 字节转为两个单字节存入数据库），以乱码的方式存到数据库当中的（转换后的单字节很多不存在于 latin1 字符集）。
取出来时，又进行了一次转换（每遇到两个字节，转换为一个 gbk 字符）。由于客户端使用 gbk 字符集，所以碰巧显示成功。
使用 Cygwin（utf8）读取一下刚才存入的中文：
MySQL [mytest]> select * from mytable; +----------+ | name | +----------+ | ÖÐÎÄ | +----------+ 1 row in set (0.000 sec)
我曾经以为，使用不同字符编码的客户端，操作同一个数据库，会出现各种问题。
直到最近才搞清楚，数据库设置好（需要存储中文的库、表、列，设置为 utf8mb4 等可以存储所有字符的字符集），客户端搞清楚自己使用的字符编码（自己是 gbk 就不要非把自己设置为 utf8 ，hold 不住），服务端和客户端各司其职，将不会出现乱码问题。
内容总结
以上是互联网集市为您收集整理的MySQL 字符集问题全部内容，希望文章能够帮你解决MySQL 字符集问题所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】