MySQL 中 varchar(max) 的等价物？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/332798/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Equivalent of varchar(max) in MySQL?
提问by David Basarab
What is the equivalent of varchar(max) in MySQL?
MySQL 中 varchar(max) 的等价物是什么？
回答by Bill Karwin
The max length of a varchar is subject to the max row size in MySQL, which is 64KB (not counting BLOBs):
varchar 的最大长度取决于 MySQL 中的最大行大小，即 64KB（不包括 BLOB）：
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:
这里有些例子：
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.
最大行大小为 65535，但 varchar 还包括一两个字节来编码给定字符串的长度。所以你实际上不能声明最大行大小的 varchar，即使它是表中唯一的列。
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)
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 necessarilyuse multiple bytes per string, but MySQL can't assume you'll restrict all your future inserts to single-byte characters.
现在，如果我们尝试在表级别使用多字节字符集，我们会发现它将每个字符计为多个字节。UTF8 字符串不一定每个字符串使用多个字节，但 MySQL 不能假设您将所有未来的插入限制为单字节字符。
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
In spite of what the last error told us, InnoDB still doesn't like a length of 21845.
尽管最后一个错误告诉我们什么，InnoDB 仍然不喜欢 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
This makes perfect sense, if you calculate that 21845*3 = 65535, which wouldn't have worked anyway. Whereas 21844*3 = 65532, which does work.
这是完全有道理的，如果你计算出 21845*3 = 65535，这无论如何都行不通。而 21844*3 = 65532，确实有效。
mysql> CREATE TABLE foo ( v VARCHAR(21844) ) CHARSET=utf8;
Query OK, 0 rows affected (0.32 sec)
回答by joshperry
TLDR;MySql does not have an equivalent concept of varchar(max), this is a MS SQL Server feature.
TLDR；MySql 没有等效的概念varchar(max)，这是一个 MS SQL Server 功能。
What is VARCHAR(max)?
什么是 VARCHAR(max)？
varchar(max)is a feature of Microsoft SQL Server.
varchar(max)是 Microsoft SQL Server 的一项功能。
The amount of data that a column could store in Microsoft SQL server versions prior to version 2005 was limited to 8KB. In order to store more than 8KB you would have to use TEXT, NTEXT, or BLOBcolumns types, these column types stored their data as a collection of 8K pages separate from the the table data pages; they supported storing up to 2GB per row.
在 2005 版之前的 Microsoft SQL 服务器版本中，列可以存储的数据量限制为 8KB。为了存储超过 8KB 的数据，您必须使用TEXT、NTEXT或BLOB列类型，这些列类型将它们的数据存储为与表数据页分开的 8K 页的集合；他们支持每行最多存储 2GB。
The big caveat to these column types was that they usually required special functions and statements to access and modify the data (e.g. READTEXT, WRITETEXT, and UPDATETEXT)
对这些列类型的最大警告是它们通常需要特殊的函数和语句来访问和修改数据（例如READTEXT, WRITETEXT, 和UPDATETEXT）
In SQL Server 2005, varchar(max)was introduced to unify the data and queries used to retrieve and modify data in large columns. The data for varchar(max)columns is stored inline with the table data pages.
在 SQL Server 2005 中，varchar(max)引入了统一用于检索和修改大列中数据的数据和查询。varchar(max)列的数据与表数据页内联存储。
As the data in the MAX column fills an 8KB data page an overflow page is allocated and the previous page points to it forming a linked list. Unlike TEXT, NTEXT, and BLOBthe varchar(max)column type supports all the same query semantics as other column types.
当 MAX 列中的数据填满 8KB 数据页时，会分配一个溢出页，前一页指向它，形成一个链表。不像TEXT，NTEXT和BLOB所述varchar(max)列类型支持所有相同的查询的语义其他列类型。
So varchar(MAX)really means varchar(AS_MUCH_AS_I_WANT_TO_STUFF_IN_HERE_JUST_KEEP_GROWING)and not varchar(MAX_SIZE_OF_A_COLUMN).
所以varchar(MAX)真的意味着varchar(AS_MUCH_AS_I_WANT_TO_STUFF_IN_HERE_JUST_KEEP_GROWING)而不是varchar(MAX_SIZE_OF_A_COLUMN)。
MySql does not have an equivalent idiom.
MySql 没有等效的习惯用法。
In order to get the same amount of storage as a varchar(max)in MySql you would still need to resort to a BLOBcolumn type. This articlediscusses a very effective method of storing large amounts of data in MySql efficiently.
为了获得与varchar(max)MySql 中相同的存储量，您仍然需要求助于BLOB列类型。 本文讨论了一种在MySql 中高效存储大量数据的非常有效的方法。
回答by ???u
The max length of a varchar is
varchar 的最大长度是
65535
65535
divided by the max byte length of a character in the character set the column is set to (e.g. utf8=3 bytes, ucs2=2, latin1=1).
除以列设置的字符集中字符的最大字节长度（例如 utf8=3 字节，ucs2=2，latin1=1）。
minus 2 bytes to store the length
减去 2 个字节来存储长度
minus the length of all the other columns
减去所有其他列的长度
minus 1 byte for every 8 columns that are nullable. If your column is null/not null this gets stored as one bit in a byte/bytes called the null mask, 1 bit per column that is nullable.
每 8 个可为空的列减去 1 个字节。如果您的列为空/非空，这将存储为一个字节/字节中的一位，称为空掩码，每列 1 位可空。
回答by Dinanath Parit
For Sql Server
对于 SQL Server
alter table prg_ar_report_colors add Text_Color_Code VARCHAR(max);
更改表 prg_ar_report_colors 添加 Text_Color_Code VARCHAR(max);
For MySql
对于 MySql
alter table prg_ar_report_colors add Text_Color_Code longtext;
更改表 prg_ar_report_colors 添加 Text_Color_Code 长文本；
For Oracle
甲骨文
alter table prg_ar_report_colors add Text_Color_Code CLOB;
更改表 prg_ar_report_colors 添加 Text_Color_Code CLOB；
回答by zloctb
Mysql Converting column from VARCHAR to TEXT when under limit size!!!
Mysql 在限制大小时将列从 VARCHAR 转换为 TEXT！！！
mysql> CREATE TABLE varchars1(ch3 varchar(6),ch1 varchar(3),ch varchar(4000000))
;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+-------+------+---------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------+
| Note | 1246 | Converting column 'ch' from VARCHAR to TEXT |
+-------+------+---------------------------------------------+
1 row in set (0.00 sec)
mysql>
回答by ajabep
The max length of a varchar in MySQL 5.6.12 is 4294967295.
MySQL 5.6.12 中 varchar 的最大长度为 4294967295。
