mysql varchar 范围_为什么MySQL VARCHAR允许超过最大长度？

我创建了一个带有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)

这是为什么？

解决方法:

好的,问题是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,因为字符串只包含一个双字节字符！

标签：mysql,varchar

来源： https://codeday.me/bug/20190723/1511340.html