等价于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屋！