mysql - 为什么 MySQL VARCHAR 允许超过最大长度？

Question

我用 UTF-8 VARCHAR(5000) 创建了一个表，并用数据填充了它。但看起来这个字段允许的数据比指示的要多:

mysql> DESCRIBE test;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| comment | varchar(5000)    | YES  |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> SELECT MAX(LENGTH(comment)) FROM test;
+----------------------+
| MAX(LENGTH(comment)) |
+----------------------+
|                 5001 |
+----------------------+
1 row in set (0.01 sec)

这是为什么？

Answer 1

好的，问题是LENGTH()返回以字节 为单位的长度，而不是字符。因为字符串是 UTF-8，所以我需要使用 CHAR_LENGTH()相反:

mysql> DESCRIBE test;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| comment | varchar(5000)    | YES  |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> SELECT MAX(LENGTH(comment)) FROM test;
+----------------------+
| MAX(LENGTH(comment)) |
+----------------------+
|                 5001 |
+----------------------+
1 row in set (0.01 sec)

mysql> SELECT MAX(CHAR_LENGTH(comment)) FROM test;
+----------------------+
| MAX(LENGTH(comment)) |
+----------------------+
|                 5000 |
+----------------------+
1 row in set (0.01 sec)

长度为 5001，因为该字符串恰好包含一个两字节字符!