mysqli_query中的最大查询长度。

How can I determine the maximum $query parameter received by function mysqli_multi_query (or mysqli_query), in PHP? I have a php program which generates a large string made of UPDATE sql commands, separated by ';' The Problem is that if that string exceeds a certain length mysqli_query generates an error like 'MySQL server has gone away'. I notice that that length seems to be around 1MB, but how can I probe-it so that I can make sure that I never exceed that length?

在PHP中，如何确定函数mysqli_multi_query(或mysqli_query)接收的最大查询参数?我有一个php程序，它生成一个由UPDATE sql命令组成的大字符串，由“;”问题是，如果这个字符串超过了某个长度，mysqli_query会生成一个类似“MySQL服务器已经消失”的错误。我注意到这个长度大概是1MB左右，但是我怎么才能保证我的长度不会超过这个长度呢?

The script needs to run about 7000 updates, on 25 or so fields. Executing one update at a time proved very slow, Concatenating multiple updates runs much faster.

该脚本需要在大约25个字段中运行大约7000个更新。一次执行一个更新是非常慢的，连接多个更新要快得多。

Any possibility to run multiple queries even faster?

是否有可能更快地运行多个查询?

Thanck you for any advice!

谢谢你的建议!

3 个解决方案

#1

You should take a look at MySQL error logs.

您应该看看MySQL错误日志。

If you dont have access to machine (hosting etc) you may ask your administrator or helpdesk for that log.

如果您无法访问机器(主机等)，您可以向您的管理员或求助台询问该日志。

MySQL supports very big queries. Im not sure if there is any limit, but when you are using network - you may have problem with packet size.

MySQL支持非常大的查询。我不确定是否有限制，但是当你使用网络时，你可能会有数据包大小的问题。

You may check --max_allowed_packet in MySQL configuration, and try to set bigger packet size. Im not sure about default configuration, but it may be 1MB which may be too small value to get query with 7000 updates at once.

您可以在MySQL配置中检查max_allowed_packet，并尝试设置较大的数据包大小。对于默认配置，我不太确定，但是可能是1MB，这可能太小了，无法一次查询7000个更新。

MySQL may need more RAM to process query like this.

MySQL可能需要更多的RAM来处理这样的查询。

If you cant reconfigure MySQL you have to split your big query to smaller queries somehow.

如果不能重新配置MySQL，则必须将大查询分解为较小的查询。

You may also read this for more information:

你也可以通过阅读了解更多信息:

devshed - MySQL server has gone away

MySQL服务器已经消失。

You asked:

你问:

Any possibility to run multiple queries even faster?

是否有可能更快地运行多个查询?

there is no simple answer for that question. It depends on query, database schema etc.

对于这个问题没有简单的答案。它取决于查询、数据库模式等。

Increasing MySQL cache size in configuration file may help a lot in most cases related with big simple updates with not much computing, because database engine will operate on RAM memory, not on hard disk. When big cache is used - sometimes first big query may be slower, because data is not yet loaded into RAM, but when it finally loads - queries that need a lot of read/write operations will work much faster.

在配置文件中增加MySQL缓存大小可能会在大多数情况下帮助很大的简单更新，但是没有太多的计算，因为数据库引擎将运行在RAM内存上，而不是在硬盘上。当使用大缓存时——有时第一个大的查询可能会比较慢，因为数据还没有加载到RAM中，但是当它最终加载时——需要大量读/写操作的查询会运行得更快。

Added later: I assume your data processing needs php deserialize() function which may be hard to implement in pure SQL and you have to do it in PHP :) If you have access to server console you may create cron (linux sheduler) job, that call PHP script from shell during night.

补充道:我猜你的数据处理需要php反序列化()函数可能很难实现在纯SQL和php你必须做到:)如果你有访问到服务器控制台您可能创建cron(linux时间表)工作,期间从shell调用php脚本。

Added later later After discussion in comments i have one more idea. You can make full database or one table backup from phpmyadmin, download it, restore data on home computer (on Windows you may use XAMPP, WAMP server). On your home computer you can run mysql.exe and process data locally.

后来在讨论后补充说，我还有一个想法。您可以从phpmyadmin中创建完整的数据库或一个表备份，下载它，在家用计算机上恢复数据(在Windows上，您可以使用XAMPP, WAMP服务器)。在你的家用电脑上，你可以运行mysql。在本地执行exe和处理数据。

3 个解决方案

#1

更多相关文章

随机推荐