字符集及排序
diguage opened this issue · 0 comments
diguage commented
简单一句话总结: MySQL 8.0 支持新的排序规则 utf8mb4_0900_ai_ci
,优先考虑使用这个规则。
- New collations in MySQL 8.0.0 | MySQL Server Blog
- MySQL 8.0 Collations: The devil is in the details. | MySQL Server Blog
- An in depth DBA's guide to migrating a MySQL database from the
utf8
to theutf8mb4
charset – Saverio Miroddi – 64K RAM SYSTEM 38911 BASIC BYTES FREE -- 这篇文章值得好好读一读。 - 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.0utf8mb4
collation; that is, theutf8mb4_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
andlatin1_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;