MySQL字符集与排序规则

什么是字符集（Character Set）和排序规则（collation）

MySQL :: MySQL 5.6 Reference Manual :: 10.1 Character Sets and Collations in General

查看MySQL支持的字符集和默认排序规则

mysql> show character set;

mysql> show collation;

mysql> show collation where Charset like 'utf8mb4';

排序规则名称规则

MySQL collation names follow these rules:
• A name ending in _ci indicates a case-insensitive collation.
• A name ending in _cs indicates a case-sensitive collation.
• A name ending in _bin indicates a binary collation. Character comparisons are based on character
binary code values.
• Unicode collation names may include a version number to indicate the version of the Unicode Collation
Algorithm (UCA) on which the collation is based. UCA-based collations without a version number in the
name use the version-4.0.0 UCA weight keys: http://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt.
A collation name such as utf8_unicode_520_ci is based on UCA 5.2.0 weight keys: http://
www.unicode.org/Public/UCA/5.2.0/allkeys.txt.

utf8mb4字符集

The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding)
The character set named utf8 uses a maximum of three bytes per character and contains only
BMP characters. The utf8mb4 character set uses a maximum of four bytes per character supports

supplemental characters:
• For a BMP character, utf8 and utf8mb4 have identical storage characteristics: same code values,
same encoding, same length.
• For a supplementary character, utf8 cannot store the character at all, while utf8mb4 requires four
bytes to store it. Since utf8 cannot store the character at all, you do not have any supplementary
characters in utf8 columns and you need not worry about converting characters or losing data when
upgrading utf8 data from older versions of MySQL.

utf8mb4 is a superset of utf8, so for an operation such as the following concatenation, the result has
character set utf8mb4 and the collation of utf8mb4_col:
SELECT CONCAT(utf8_col, utf8mb4_col);

Similarly, the following comparison in the WHERE clause works according to the collation of utf8mb4_col:
SELECT * FROM utf8_tbl, utf8mb4_tbl WHERE utf8_tbl.utf8_col = utf8mb4_tbl.utf8mb4_col;

Tip: To save space with utf8mb4, use VARCHAR instead of CHAR. Otherwise, MySQL must reserve four
bytes for each character in a CHAR CHARACTER SET utf8mb4 column because that is the maximum
possible length. For example, MySQL must reserve 40 bytes for a CHAR(10) CHARACTER SET utf8mb4
column.

utf8mb4字符集与emoji

登录 - 推酷

字符集详解

http://www.jb51.net/article/29960.htm

MySQL处理一个请求，编解码流程

http://stackoverflow.com/questions/1566602/is-set-character-set-utf8-necessary

character_set_connection系统变量的作用

http://stackoverflow.com/questions/16082480/what-is-the-purpose-of-character-set-connection

官方手册关于字符集的介绍

http://dev.mysql.com/doc/refman/5.7/en/charset.html

这个帖子告诉我，支持emoji不只是修改表的字符集那么简单

http://blog.csdn.net/e_laoye/article/details/51544161

编辑my.cnf

character-set-server=utf8
collation-server=utf8_bin
init-connect='SET NAMES utf8'

# 数据库默认字符集

 default-character-set=utf8

重启

字符集查看与修改

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       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

show variables like 'collation_%';

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_bin        |
| collation_server     | utf8_bin        |
+----------------------+-----------------+
3 rows in set (0.00 sec)

修改数据库字符集

ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];

把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集

ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...] 

修改表默认字符集

ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...];

修改字段字符集

ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];

查看数据库编码

SHOW CREATE DATABASE db_name;

查看表编码

SHOW CREATE TABLE tbl_name;

查看字段编码

SHOW FULL COLUMNS FROM tbl_name;