This question already has an answer here:
这个问题在这里已有答案：
- What is maximum query size for mysql? 2 answers
- mysql的最大查询大小是多少？ 2个答案
I want to insert a query with 1,397,025 characters and doesn't get it inserted. The server doesn't provide me with an error message. So my thought was, is there a character limit for a query?
我想插入一个1,397,025个字符的查询，但不插入。服务器没有向我提供错误消息。所以我的想法是，查询是否有字符限制？
1 个解决方案
#1
0
I am not sure about the datatype that you are using, but from your question it seems its TEXT.
我不确定您使用的数据类型，但从您的问题看来它似乎是TEXT。
Quote from Mysql Doc
从Mysql Doc引用
A TEXT column with a maximum length of 65,535 (216 − 1) characters. The effective maximum length is less if the value contains multibyte characters. Each TEXT value is stored using a 2-byte length prefix that indicates the number of bytes in the value.
一个TEXT列，最大长度为65,535（216 - 1）个字符。如果值包含多字节字符，则有效最大长度会减少。每个TEXT值使用2字节长度前缀存储，该前缀指示值中的字节数。
An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest TEXT type large enough to hold values M characters long.
可以为此类型提供可选长度M.如果这样做，MySQL会将列创建为最小的TEXT类型，其大小足以容纳M个字符长的值。
If you are talking about buffer size,
如果你在谈论缓冲区大小，
Most of these program variables also can be set at server startup by using the same syntax that applies to specifying program options. For example, mysql has a max_allowed_packet variable that controls the maximum size of its communication buffer. To set the max_allowed_packet variable for mysql to a value of 16MB, use either of the following commands:
大多数这些程序变量也可以通过使用适用于指定程序选项的相同语法在服务器启动时设置。例如，mysql有一个max_allowed_packet变量，用于控制其通信缓冲区的最大大小。要将mysql的max_allowed_packet变量设置为16MB的值，请使用以下任一命令：
shell> mysql --max_allowed_packet=16777216 shell> mysql --max_allowed_packet=16M The first command specifies the value in bytes. The second specifies the value in megabytes. For variables that take a numeric value, the value can be given with a suffix of K, M, or G (either uppercase or lowercase) to indicate a multiplier of 1024, 10242 or 10243. (For example, when used to set max_allowed_packet, the suffixes indicate units of kilobytes, megabytes, or gigabytes.)
shell> mysql --max_allowed_packet = 16777216 shell> mysql --max_allowed_packet = 16M第一个命令指定以字节为单位的值。第二个指定以兆字节为单位的值。对于采用数值的变量，可以使用后缀K，M或G（大写或小写）指定值，以指示乘数1024,10242或10243.（例如，当用于设置max_allowed_packet时，后缀表示千字节，兆字节或千兆字节的单位。）
If you are using the mysql client program, its default max_allowed_packet variable is 16MB. To set a larger value, start mysql like this:
如果您使用的是mysql客户端程序，则其默认的max_allowed_packet变量为16MB。要设置更大的值，请启动mysql，如下所示：
shell> mysql --max_allowed_packet=32M That sets the packet size to 32MB.
shell> mysql --max_allowed_packet = 32M将数据包大小设置为32MB。
The server's default max_allowed_packet value is 1MB. You can increase this if the server needs to handle big queries (for example, if you are working with big BLOB columns). For example, to set the variable to 16MB, start the server like this:
服务器的默认max_allowed_packet值为1MB。如果服务器需要处理大型查询（例如，如果您正在处理大型BLOB列），则可以增加此值。例如，要将变量设置为16MB，请按以下方式启动服务器：
shell> mysqld --max_allowed_packet=16M
shell> mysqld --max_allowed_packet = 16M
Refer https://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html
请参阅https://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html
#1
0
I am not sure about the datatype that you are using, but from your question it seems its TEXT.
我不确定您使用的数据类型，但从您的问题看来它似乎是TEXT。
Quote from Mysql Doc
从Mysql Doc引用
A TEXT column with a maximum length of 65,535 (216 − 1) characters. The effective maximum length is less if the value contains multibyte characters. Each TEXT value is stored using a 2-byte length prefix that indicates the number of bytes in the value.
一个TEXT列，最大长度为65,535（216 - 1）个字符。如果值包含多字节字符，则有效最大长度会减少。每个TEXT值使用2字节长度前缀存储，该前缀指示值中的字节数。
An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest TEXT type large enough to hold values M characters long.
可以为此类型提供可选长度M.如果这样做，MySQL会将列创建为最小的TEXT类型，其大小足以容纳M个字符长的值。
If you are talking about buffer size,
如果你在谈论缓冲区大小，
Most of these program variables also can be set at server startup by using the same syntax that applies to specifying program options. For example, mysql has a max_allowed_packet variable that controls the maximum size of its communication buffer. To set the max_allowed_packet variable for mysql to a value of 16MB, use either of the following commands:
大多数这些程序变量也可以通过使用适用于指定程序选项的相同语法在服务器启动时设置。例如，mysql有一个max_allowed_packet变量，用于控制其通信缓冲区的最大大小。要将mysql的max_allowed_packet变量设置为16MB的值，请使用以下任一命令：
shell> mysql --max_allowed_packet=16777216 shell> mysql --max_allowed_packet=16M The first command specifies the value in bytes. The second specifies the value in megabytes. For variables that take a numeric value, the value can be given with a suffix of K, M, or G (either uppercase or lowercase) to indicate a multiplier of 1024, 10242 or 10243. (For example, when used to set max_allowed_packet, the suffixes indicate units of kilobytes, megabytes, or gigabytes.)
shell> mysql --max_allowed_packet = 16777216 shell> mysql --max_allowed_packet = 16M第一个命令指定以字节为单位的值。第二个指定以兆字节为单位的值。对于采用数值的变量，可以使用后缀K，M或G（大写或小写）指定值，以指示乘数1024,10242或10243.（例如，当用于设置max_allowed_packet时，后缀表示千字节，兆字节或千兆字节的单位。）
If you are using the mysql client program, its default max_allowed_packet variable is 16MB. To set a larger value, start mysql like this:
如果您使用的是mysql客户端程序，则其默认的max_allowed_packet变量为16MB。要设置更大的值，请启动mysql，如下所示：
shell> mysql --max_allowed_packet=32M That sets the packet size to 32MB.
shell> mysql --max_allowed_packet = 32M将数据包大小设置为32MB。
The server's default max_allowed_packet value is 1MB. You can increase this if the server needs to handle big queries (for example, if you are working with big BLOB columns). For example, to set the variable to 16MB, start the server like this:
服务器的默认max_allowed_packet值为1MB。如果服务器需要处理大型查询（例如，如果您正在处理大型BLOB列），则可以增加此值。例如，要将变量设置为16MB，请按以下方式启动服务器：
shell> mysqld --max_allowed_packet=16M
shell> mysqld --max_allowed_packet = 16M
Refer https://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html
请参阅https://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html