#14 MySQL: wait for table metadata lock

2019-03-29

问题

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using  EditLine wrapper

Connection id:      17190053
Current database:   gkbb
Current user:       root@10.9.165.246
SSL:            Not in use
Current pager:      less
Using outfile:      ''
Using delimiter:    ;
Server version:     5.5.5-10.1.26-MariaDB MariaDB Server
Protocol version:   10
Connection:     10.9.108.125 via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       3306
Uptime:         13 days 21 hours 14 min 38 sec

Threads: 276  Questions: 31378648  Slow queries: 212  Opens: 2977  Flush tables: 1  Open tables: 2761  Queries per second avg: 26.155
--------------

mysql> show global variables like "innodb_version";
+----------------+-------------+
| Variable_name  | Value       |
+----------------+-------------+
| innodb_version | 5.6.36-82.1 |
+----------------+-------------+
1 row in set (0.06 sec)

PS: 查看 status 还有一个快捷方式 \s

编辑测试库表结构(添加字段),卡住,任何操作都不行了,等一个多小时,还是不行。。
还一度怀疑是不是表结构设计问题,字段、数据是不是太多了。

过程

偶尔想起看看会话情况:

SELECT * FROM information_schema.processlist WHERE db = 'mydb';

或命令:

  • mysqladmin -uroot -p123456 processlist
  • mysql -uroot -p123456 -e 'SHOW PROCESSLIST'

看到里面好几个会话的状态都是 wait for table metadata lock,这就有点奇怪了,之前没有见过。

网上的资料显示:

为了在并发环境下维护表元数据的数据一致性,在表上有活动事务(显式或隐式)的时候,不可以对元数据进行写入操作。因此 MySQL 引入了 metadata lock ,来保护表的元数据信息。
因此在对表进行上述操作时,如果表上有活动事务(未提交或回滚),请求写入的会话会等待在 Metadata lock wait 。

如果资料没错,那么就是说,如果有事务没有结束,DDL 操作请求 MDL(metadata lock)时会卡住这张表。

我想起我们的服务中确实存在会话没有关闭的情况。

  1. 用了 SQLAlchemy 做 ORM
  2. 每次查询都使用一个会话,包括 SELECT
  3. 增删改操作都立即 commit 了,SELECT 却没有(记得是有个什么原因特意如此)

合理怀疑:这个查询 SESSION 没有关闭,导致 ALTER 语句进入 MDL 等待状态,然后导致了表无法进行任何操作(包括查询,至于为什么这样,我不知道)。

本地复现

  1. 开两个终端,分别建立 MySQL 连接。
  2. 其中一个终端(A):
  3. SET SESSION auto_commit = 0;
  4. SELECT * FROM test.test LIMIT 1;
  5. 另一个终端(B)只需要:TRUNCATE test.test;,然后发现:卡住了。

PS:

  1. DDL 需要 metadata 锁。
  2. TRUNCATE 属于 DDL,可能因为其非事务性(不支持提交和回滚)。参考:https://dba.stackexchange.com/questions/36607/why-is-truncate-ddl

现在,回到终端 A:

mysql> select * from information_schema.processlist where db = 'test';
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------------------------------+
| ID | USER | HOST      | DB   | COMMAND | TIME | STATE                           | INFO                                                           |
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------------------------------+
|  3 | root | localhost | test | Query   |    0 | executing                       | select * from information_schema.processlist where db = 'test' |
|  5 | root | localhost | test | Query   | 6111 | Waiting for table metadata lock | truncate test                                                  |
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 421232684444408
                 trx_state: RUNNING
               trx_started: 2019-03-29 16:06:14
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 3
                 trx_query: select * from information_schema.innodb_trx
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2019-03-29 19:04:40 0x7f1bcc1d6700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 3 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 7 srv_active, 0 srv_shutdown, 11228 srv_idle
srv_master_thread log flush and writes: 11234
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 16
OS WAIT ARRAY INFO: signal count 10
RW-shared spins 0, rounds 27, OS waits 12
RW-excl spins 0, rounds 32, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 27.00 RW-shared, 32.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 54542
Purge done for trx's n:o < 54542 undo n:o < 0 state: running but idle
History list length 53
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421232684445328, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421232684443488, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
639 OS file reads, 99 OS file writes, 21 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 4 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 26417867
Log flushed up to   26417867
Pages flushed up to 26417867
Last checkpoint at  26417858
0 pending log flushes, 0 pending chkp writes
17 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 133441
Buffer pool size   8192
Free buffers       7710
Database pages     482
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 446, created 41, written 72
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 482, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Process ID=4492, Main thread ID=139757271107328, state: sleeping
Number of rows inserted 6, updated 0, deleted 0, read 20
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

表明:事务存在,TRUNCATE 锁等待。

如果,kill 3 干掉这个没有 commit 的查询 SESSION,TRUNCATE 就会正常执行下去。

结论

  1. 不管怎样,应该保持事务的短小精干,快速执行和退出。
    PS:业务代码中 SELECT 之前为什么不提交的问题需要进一步检查。
  2. DDL 需要 MDL,而没有结束的事务会阻止 MDL。
    更多细节,需要更多深入了解。

#13 MySQL 分区

2019-02-01
partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES
            {LESS THAN {(expr | value_list) | MAXVALUE}
            |
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]

分区类型

  • [LINEAR] HASH(expr) 根据值的哈希分区
  • [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
  • RANGE{(expr) | COLUMNS(column_list)} 根据值得范围分区
  • LIST{(expr) | COLUMNS(column_list)} 根据不同的值分区

COLUMNS 不限于整数

创建分区

PARTITION BY LIST(column) (
    PARTITION a VALUES IN (a1, a2, a3),
    PARTITION b VALUES IN (b1, b2, b3),
    PARTITION c VALUES IN (c1, c2, c3)
)

PARTITION BY RANGE(column) (
    PARTITION 2012q1 VALUES LESS THAN('2012-04-01'),
    PARTITION 2012q2 VALUES LESS THAN('2012-07-01'),
    PARTITION 2012q3 VALUES LESS THAN('2012-10-01'),
    PARTITION 2012q4 VALUES LESS THAN('2013-01-01')
)

PARTITION BY HASH(column) PARTITIONS 128
PARTITION BY HASH(dayofmonth(date)) PARTITIONS 31

查看分区信息

SELECT * FROM `information_schema`.`PARTITIONS`;

子分区

  1. PARTITION 关键字换成 SUBPARTITIONPARTITIONS 关键字换成 SUBPARTITIONS,接在分区语句后面。
  2. 可以是不同类型。

比如:

PARTITION BY HASH (prod_id) SUBPARTITION BY HASH (cust_id)
PARTITIONS 4 SUBPARTITIONS 4;

脚本

如果是 By Range 分区,一般需要自动创建新的分区,删除久的分区。

比如:

CREATE TABLE `test` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `date` DATE NOT NULL,
    `key` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_general_ci',
    `value` VARCHAR(300) NOT NULL COLLATE 'utf8mb4_general_ci',
    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`, `date`) USING BTREE,
    UNIQUE INDEX `key` (`date`, `key`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
/*!50100 PARTITION BY RANGE (to_days(`date`))
(PARTITION p20230123 VALUES LESS THAN (738909) ENGINE = InnoDB,
 PARTITION p20230124 VALUES LESS THAN (738910) ENGINE = InnoDB,
 PARTITION p20230125 VALUES LESS THAN (738911) ENGINE = InnoDB)  */;

然后,通过下面这个 cron 任务自动更新分区:

#!/bin/bash

# 开启调试模式,输出每条执行的命令及其执行结果
set -x

# 检查当前机器 IP 地址中是否包含指定的 VIP(虚拟 IP)
# 确认在主 MySQL 上执行
vip_w="192.168.12.34"
if [ $(/sbin/ip a | grep "${vip_w}" | wc -l) -eq 0 ]; then echo 'WARN: Wrong Machine!!!'; exit 1; fi

# 删除 90 天前的分区
# PS:如果分区不存在,TRUNCATE 不会报错。
delete_date=$(date -d '90 days ago' +%Y%m%d)
mysql -uroot -p123456 -e "USE test; ALTER TABLE test TRUNCATE PARTITION p$delete_date;"  # DROP

# 创建未来分区
create_date=$(date -d '7 days' +%Y%m%d)
mysql -uroot -p123456 -e "USE test; ALTER TABLE test ADD PARTITION (PARTITION p$delete_date VALUES LESS THAN (TO_DAYS("$delete_date")));"

#11 MySQL Boolean

2017-09-01

我们通常使用以下几种方式表示布尔值:

  1. tinyint(1)
  2. bit(1)
  3. enum('Y','N')

在 MySQL 中 bool, booleantinyint(1) 的别名。

如果只是一个 True OR False 的布尔型,没有比 bit(1) 更合适的了。
但是也有些时候,我们有好几个 bool 型用一个字段表示,最好用 bit(m),我也用过 int 型。

附:不同值的长度

Type Bit Byte Note
tinyint 8 1  
smallint 16 2  
middleint 24 3  
int 32 4  
bigint 64 8  
bit(m) m (m+7)/8  
binary[(m)]   m m 默认值:1
varbinary(m)   (m+1)  
tinyblob   (L+1) 1B 长度,最长 255B
blob[(m)]   (L+2) 2B 长度,最长 64KB - 1
mediumblob   (L+3) 3B 长度,最长 16MB - 1
longblob   (L+4) 4B 长度,最长 4GB - 1
enum('a',..)   1/2 最多可以存储 2^16 - 1 个值
set('a',..)   1/2/3/4/8 最多可以存储 64 个值

PS: blob 如果指定长度 m,MySQL 会选择足够存储数据的最小长度。
PS: MySQL set 类型

参考资料与拓展阅读

#10 数据库:应该怎么存储 IP 地址

2017-08-25

IP 地址的存储方式就两种:字符串,整型数。

表示方法

ipv4 VARCHAR(15) -- xx.xx.xx.xx
ipv6 VARCHAR(39) -- xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx

ipv4 INT UNSIGNED
ipv6 BINARY(16)

-- 如果同时需要表达 IPv4 和 IPv6
ip VARCHAR(39)
ip BINARY(16)

PS:我还在有些地方看到过这种采用 12 位整数表示 IPv4 的方法:'192.168.0.1' => 192168000001, 如果存到 MySQL 的话,只能采用 BIGINTVARCHAR(12) 了。
如果省略中间的分号,ipv6 只需要 VARCHAR(32) 就行了。

整型和字符串的转换

  • INET_ATON / INET_NTOA
  • INET6_ATON / INET6_NTOA

#9 MySQL 技巧

2017-04-02

安装之后初始化

忘记密码

改密码

查看版本

  1. mysql 命令行登录的时候显示的欢迎信息
  2. select @@version
  3. select version()
  4. SHOW VARIABLES WHERE variable_name LIKE 'version%';
  5. status

导出数据为 JSON

导出数据为 CSV

#8 MySQL 增加或修改注释

2017-02-19

表注释

SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = '5mzb' AND TABLE_NAME = 'user';
ALTER TABLE `user` COMMENT = '用户';

字段注释

SELECT COLUMN_NAME, COLUMN_COMMENT FROM `information_schema`.`COLUMNS`
WHERE TABLE_SCHEMA = 'sendcloud' AND TABLE_NAME = 'user_info' ORDER BY ORDINAL_POSITION;
ALTER TABLE `user`
CHANGE COLUMN `create_time`
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' AFTER `expire_time`;
ALTER TABLE `user`
MODIFY COLUMN
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' AFTER `expire_time`;

好傻 X 啊,只改个备注,却必须要把字段声明带上,增加出错的可能。

#7 MySQL 索引

2016-02-08
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part:
    col_name [(length)] [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

功能类型

  • 主键索引(PrimaryKey)
  • 唯一索引(UNIQUE)
  • 普通索引(KEY)
  • 全文索引(FULLTEXT)
  • 空间索引(SPATIAL)

算法类型

  • B-Tree
  • Hash
  • R-TREE(空间索引使用)
  • 倒排索引(Inverted Index,全文索引使用)

聚簇索引 Clustered Index

聚簇索引(Clustered Index)是一种特殊的索引类型,它决定了表中数据的物理存储顺序。
在聚簇索引中,数据行按照索引键的顺序存储在磁盘上,因此具有相邻的物理位置,这样可以提高查询效率。
聚簇索引只能有一个,因为它决定了表中数据的物理存储顺序,如果有多个聚簇索引,就会导致数据在磁盘上存储的位置不确定,影响查询效率。

其他索引叫二级索引(Secondary Index),或者辅助索引。

对于 InnoDB,有限使用主键做聚簇索引,其次找一个不含 NULL 值的唯一索引,还没有,就自动生成一个隐式的自增型 ROW_ID 字段(BIGINT UNSIGNED)做聚簇索引 GEN_CLUST_INDEX。

注意:所有索引在提升查询效率的同时,都会影响插入和删除的性能。尤其是聚簇索引需要重新组织数据行的物理存储顺序

#6 MySQL 编码字符集

2015-05-11

各种类型的编码

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | gbk                        |
| character_set_connection | gbk                        |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | gbk                        |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8mb3                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

5.7 及更早版本默认字符集和 Collation 是 latin1 和 latin1_swedish_ci
8.0 之后改成 utf8mb4 和 utf8mb4_0900_ai_ci

上面就有 6 种 全局的编码:

  • client 客户端编码
  • connection 连接编码
  • database 数据库编码,创建表时的默认编码
    load data, 以及创建
  • filesystem 文件系统编码
  • results 结果编码
  • server 服务端编码,创建数据库时的默认编码
  • system 系统编码

还有两种局部的编码:表的编码和列(字段)的编码。
列主要是 char, varchar, text 类型。

字符集

# 都可以 LIKE 搜索
SHOW CHARACTER SET;
SHOW CHARSET;
SHOW CHAR 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
utf8mb3 UTF-8 Unicode utf8mb3_general_ci 3
utf8mb4 UTF-8 Unicode utf8mb4_0900_ai_ci 4
SELECT * FROM information_schema.character_sets;
SELECT * FROM information_schema.character_sets WHERE CHARACTER_SET_NAME LIKE "%utf%";
CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN
utf8mb3 utf8mb3_general_ci UTF-8 Unicode 3
utf16 utf16_general_ci UTF-16 Unicode 4
utf16le utf16le_general_ci UTF-16LE Unicode 4
utf32 utf32_general_ci UTF-32 Unicode 4
utf8mb4 utf8mb4_0900_ai_ci UTF-8 Unicode 4

排序规则

SHOW COLLATION;

这就多了,两百多。

SHOW COLLATION LIKE "%ascii%";
Collation Charset Id Default Compiled Sortlen Pad_attribute
ascii_bin ascii 65 Yes 1 PAD SPACE
ascii_general_ci ascii 11 Yes Yes 1 PAD SPACE
SELECT * FROM information_schema.collations WHERE CHARACTER_SET_NAME = "utf8mb4" AND COLLATION_NAME LIKE "%zh%";
COLLATION_NAME CHARACTER_SET_NAME ID IS_DEFAULT IS_COMPILED SORTLEN PAD_ATTRIBUTE
utf8mb4_zh_0900_as_cs utf8mb4 308 Yes 0 NO PAD

命名规则

字符集名称,语言,通用后缀

  • ai Accent-insensitive 重音不敏感
  • as Accent-sensitive 重音敏感
  • ci Case-insensitive 大小写不敏感
  • cs Case-sensitive 大小写敏感
  • ks Kana-sensitive 假名敏感(日语)
  • bin 二进制

8.0 之后,很多编码中多了 0900 字样,表示 Unicode 9.0 规范。

参考资料与拓展阅读