字符集及排序

[diguage]

简单一句话总结： MySQL 8.0 支持新的排序规则 utf8mb4_0900_ai_ci，优先考虑使用这个规则。

1. New collations in MySQL 8.0.0 | MySQL Server Blog
2. MySQL 8.0 Collations: The devil is in the details. | MySQL Server Blog
3. An in depth DBA's guide to migrating a MySQL database from the utf8 to the utf8mb4 charset – Saverio Miroddi – 64K RAM SYSTEM  38911 BASIC BYTES FREE -- 这篇文章值得好好读一读。
4. MySQL :: MySQL 5.7 Reference Manual :: 10.4 Connection Character Sets and Collations

• The default collation for utf8mb4 differs between MySQL 5.7 and 8.0 (utf8mb4_general_ci for 5.7, utf8mb4_0900_ai_ci for 8.0).
• When the 8.0 client requests a character set of utf8mb4, what it sends to the server is the default 8.0 utf8mb4 collation; that is, the utf8mb4_0900_ai_ci.
• utf8mb4_0900_ai_ci is implemented only as of MySQL 8.0, so the 5.7 server does not recognize it.
• Because the 5.7 server does not recognize utf8mb4_0900_ai_ci, it cannot satisfy the client character set request, and falls back to its default character set and collation (latin1 and latin1_swedish_ci).

-- 查看数据库字符集
USE db_name;
SELECT @@character_set_database, @@collation_database;
show variables like 'character_set_database';
show variables like 'collation_database';


SHOW CREATE DATABASE "schemaName";

-- 查看数据库字符集
-- https://stackoverflow.com/a/1049958/951836
SELECT schema_name,
       default_character_set_name,
       DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA;

-- 查看表字符集
SELECT t.table_name, ccsa.character_set_name
FROM information_schema.`TABLES` t,
     information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` ccsa
WHERE ccsa.collation_name = t.table_collation
  AND t.table_schema = "schemaName";

-- 查看字段字符集
SELECT column_name, character_set_name
FROM information_schema.`COLUMNS`
WHERE table_schema = "schemaName"
  AND table_name = "tableName";

-- 查看当前 Schema 中特定类型字段字符集
-- https://stackoverflow.com/a/4805964/951836
SELECT TABLE_SCHEMA,
       TABLE_NAME,
       ccsa.CHARACTER_SET_NAME AS DEFAULT_CHAR_SET,
       COLUMN_NAME,
       COLUMN_TYPE,
       c.CHARACTER_SET_NAME
FROM information_schema.TABLES AS T
         JOIN information_schema.COLUMNS AS c USING (TABLE_SCHEMA, TABLE_NAME)
         JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS ccsa
              ON (T.TABLE_COLLATION = ccsa.COLLATION_NAME)
WHERE TABLE_SCHEMA = SCHEMA()
  AND c.DATA_TYPE IN ('enum', 'varchar', 'char', 'text', 'mediumtext', 'longtext')
ORDER BY TABLE_SCHEMA,
         TABLE_NAME,
         COLUMN_NAME;

-- https://stackoverflow.com/a/38996782/951836
SHOW FULL COLUMNS FROM my_tablename;

-- 查看默认字符集
SHOW VARIABLES LIKE 'character%';

-- 查询支持的中日韩字符集
-- https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/faqs-cjk.html
SELECT CHARACTER_SET_NAME, DESCRIPTION
FROM INFORMATION_SCHEMA.CHARACTER_SETS
WHERE DESCRIPTION LIKE '%Chin%'
   OR DESCRIPTION LIKE '%Japanese%'
   OR DESCRIPTION LIKE '%Korean%'
ORDER BY CHARACTER_SET_NAME;

-- 查看默认存储引擎
SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+

-- 查看 InnoDB 的运行状态
-- https://mariadb.com/kb/en/show-engine-innodb-status/
SHOW ENGINE INNODB STATUS;