php - 为什么我的字符串在 php 中的 2995 个字符后停止

Question

我有一个由 PHP 生成的 MySQL 查询，它存储在一个字符串中。

插入的数据量比较大。当我运行查询以将数据插入数据库时​​，没有任何反应。当我将字符串复制到 vim 实例并将文件另存为 *.php 时，只有前 ~2995 个字符在字符串中突出显示。

即使我删除了前 2995 个字符的行，字符串中的下一行也会突出显示，因此前 ~2995 个字符始终会突出显示。

我一开始以为有最大字符串长度，but found it was 2GB in this thread我的字符串绝对不是。

不知道有没有用，但是字符串遵循格式

INSERT INTO Table_Name(Field1, Field2) VALUES('value1', 'value2'),......

注意:插入值中的所有引号都已转义或删除，因此不会造成问题。

Answer 1

这听起来像是 my.cnf 中的 max_allowed_pa​​cket 大小的问题。更多 details here .

下面的详细信息提到使用命令行中的 mysqld 来调整它。但如果可能，最好直接进入 my.cnf 文件并调整其中的 max_allowed_pa​​cket。例如，在我管理的一些使用 InnoDB 的服务器上，我将该值设置为 max_allowed_pa​​cket=64M。

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:

shell> mysqld --max_allowed_packet=16M

You can also use an option file to set max_allowed_packet. For example, to set the size for the server to 16MB, add the following lines in an option file:

[mysqld]
max_allowed_packet=16M

It is safe to increase the value of this variable because the extra memory is allocated only when needed. For example, mysqld allocates more memory only when you issue a long query or when mysqld must return a large result row. The small default value of the variable is a precaution to catch incorrect packets between the client and server and also to ensure that you do not run out of memory by using large packets accidentally.

编辑:我还发现了另一个可能有用的设置，net_buffer_length :

Each client thread is associated with a connection buffer and result buffer. Both begin with a size given by net_buffer_length but are dynamically enlarged up to max_allowed_packet bytes as needed. The result buffer shrinks to net_buffer_length after each SQL statement.

This variable should not normally be changed, but if you have very little memory, you can set it to the expected length of statements sent by clients. If statements exceed this length, the connection buffer is automatically enlarged. The maximum value to which net_buffer_length can be set is 1MB.

net_buffer_length 的默认值是 16384 字节，即 0.015625MB，然后是 16KB。尝试调整该值并重试。也许像 524288 字节是 512 KB 或 131072 字节是 128 KB。您需要知道的是 net_buffer_length 直接连接到 mysqldump 行长度。所以可能就是这样。