MySQL 中 varchar(max) 的等价物? [英] Equivalent of varchar(max) in MySQL?
问题描述
MySQL 中 varchar(max) 的等价物是什么?
What is the equivalent of varchar(max) in MySQL?
推荐答案
varchar 的最大长度取决于 MySQL 中的最大行大小，即 64KB(不包括 BLOB):
The max length of a varchar is subject to the max row size in MySQL, which is 64KB (not counting BLOBs):
VARCHAR(65535)
但是，请注意，如果您使用多字节字符集，则限制会更低:
However, note that the limit is lower if you use a multi-byte character set:
VARCHAR(21844) CHARACTER SET utf8
<小时>
以下是一些示例:
Here are some examples:
最大行大小为 65535，但 varchar 还包括一两个字节来编码给定字符串的长度.所以你实际上不能声明一个最大行大小的 varchar，即使它是表中唯一的列.
The maximum row size is 65535, but a varchar also includes a byte or two to encode the length of a given string. So you actually can't declare a varchar of the maximum row size, even if it's the only column in the table.
mysql> CREATE TABLE foo ( v VARCHAR(65534) );
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
但是如果我们尝试减少长度，我们会找到最有效的长度:
But if we try decreasing lengths, we find the greatest length that works:
mysql> CREATE TABLE foo ( v VARCHAR(65532) );
Query OK, 0 rows affected (0.01 sec)
现在，如果我们尝试在表级别使用多字节字符集，我们会发现它会将每个字符计为多个字节.UTF8 字符串不会必然为每个字符串使用多个字节，但 MySQL 不能假设您将所有未来的插入限制为单字节字符.
Now if we try to use a multibyte charset at the table level, we find that it counts each character as multiple bytes. UTF8 strings don't necessarily use multiple bytes per string, but MySQL can't assume you'll restrict all your future inserts to single-byte characters.
mysql> CREATE TABLE foo ( v VARCHAR(65532) ) CHARSET=utf8;
ERROR 1074 (42000): Column length too big for column 'v' (max = 21845); use BLOB or TEXT instead
尽管上一个错误告诉我们什么，InnoDB 仍然不喜欢 21845 的长度.
In spite of what the last error told us, InnoDB still doesn't like a length of 21845.
mysql> CREATE TABLE foo ( v VARCHAR(21845) ) CHARSET=utf8;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
这是完全有道理的，如果你计算出 21845*3 = 65535，那无论如何都行不通.而 21844*3 = 65532，确实有效.
This makes perfect sense, if you calculate that 21845*3 = 65535, which wouldn't have worked anyway. Whereas 21844*3 = 65532, which does work.
mysql> CREATE TABLE foo ( v VARCHAR(21844) ) CHARSET=utf8;
Query OK, 0 rows affected (0.32 sec)
这篇关于MySQL 中 varchar(max) 的等价物?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
