有什么方法可以在不更改max_allowed_packet的情况下在mysql数据库中插入较大的值? [英] Is there any way to insert a large value in a mysql DB without changing max_allowed_packet?
问题描述
我知道，为了将大于max_allowed_packet个字节的值插入MySQL数据库，默认解决方案是确保客户端和服务器端max_allowed_packet变量都大于查询插入的数据块进入数据库.
I know that in order to insert values larger than max_allowed_packet bytes into a MySQL database, the default solution would be to assure that both client and server side max_allowed_packet variables are bigger than the chunk of data that a query inserts into the DB.
但是，有什么方法可以不更改上述服务器端变量?当我必须将数据插入到ISP托管的不允许不允许更改max_allowed_packet限制的数据库中时，这将很有用.
However, is there any way to do so without changing the server side variable mentioned above? This would be useful when I have to insert data into a database that is hosted in an ISP that doesn't allow me to change the max_allowed_packet limit.
另一个相关问题:MySql longblob的限制为4GB，但max_allowed_packet的限制为1GB.那么，是否可以在longblob表列中插入大于1GB的值?
Another related question: MySql longblob limit is 4GB, but max_allowed_packet limit is 1GB. So, is it possible to insert values larger than 1GB in a longblob table column?
推荐答案
我最近偶然发现了这个问题.就我而言，服务器的max_allowed_packet为1 MB，我无能为力.我插入的数据刚好超过1 MB.我找到了两个候选解决方案.
I recently stumbled upon this problem. In my case, the server's max_allowed_packet was 1 MB and I could do nothing to change it. And I was inserting some data just above 1 MBs. I found two solution candidates.
1)首先，使用JDBC. 从 MySQL Connector/J v3.1.9 开始，您可以设置一些参数，这是我在JDBC URL中设置的参数:
1) First, using JDBC. Since MySQL Connector/J v3.1.9, there are a few parameters that you could set, here's my set of parameters in the JDBC URL:
附加这些:
blobSendChunkSize=50000&useServerPrepStmts=true&emulateUnsupportedPstmts=false&maxAllowedPacket=20000000
产生JDBC URL:
Resulting in JDBC URL:
jdbc:mysql://serverip:3306/databasename?noDatetimeStringSync=true&blobSendChunkSize=50000&useServerPrepStmts=true&emulateUnsupportedPstmts=false&maxAllowedPacket=20000000
然后，您必须使用PreparedStatement进行插入，并使用InputStream将字节内容作为参数传递给setObject.请注意，使用字节数组的setObject不会启用blob拆分.参数，最新的MySQL服务器(5.0.45或更高版本)和InputStream的组合将使用LONG DATA机制发送blob数据，并根据blobSendChunkSize拆分blob.
Then you must use PreparedStatement to do your inserts, and use the InputStream to pass the byte content as a parameter to setObject. Note that setObject using byte arrays won't enable the blob splitting. The combination of parameters, recent MySQL server (5.0.45 or later), and InputStream will send the blob data using LONG DATA mechanism, splitting the blob according to blobSendChunkSize.
JDBC解决方案有效并且我已经对其进行了测试.
The JDBC solution works and I have tested it.
2)现在，第二个候选对象是使用PHP的 mysqli 驱动程序并使用mysqli_send_long_data.为了方便起见，从PHP手册示例中复制了
2) Now, the second candidate, is to use PHP's mysqli driver and use mysqli_send_long_data. For your convenience, copied from PHP manual example :
<?php
$stmt = $mysqli->prepare("INSERT INTO messages (message) VALUES (?)");
$null = NULL;
$stmt->bind_param("b", $null);
$fp = fopen("messages.txt", "r");
while (!feof($fp)) {
$stmt->send_long_data(0, fread($fp, 8192));
}
fclose($fp);
$stmt->execute();
?>
这篇关于有什么方法可以在不更改max_allowed_packet的情况下在mysql数据库中插入较大的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
