MySQL,CONCAT,一段时间后结果为null [英] MySQL, CONCAT, the result is null after a while
问题描述
我将大数据放入表中,并放入LONGBLOB
字段中,但是随着表的增长,该字段变为空.代码:
Im putting big data to table, to a LONGBLOB
field, but as the table grows, the field becomes empty. Code:
mysql_query ('CREATE TABLE IF NOT EXISTS testtable (content LONGBLOB NOT NULL) ENGINE = MyISAM');
mysql_query('TRUNCATE TABLE testtable');
mysql_query('REPLACE INTO testtable VALUES (".")');
$bigData = str_repeat('A', 1024*1024*2); // 2 MB!
foreach (str_split($bigData, 1024*64) as $item)
{
mysql_query ('UPDATE testtable SET content = CONCAT(content, "'.mysql_real_escape_string($item).'")');
$rec = mysql_fetch_row(mysql_query ('SELECT content FROM testtable'));
echo 'Size of the content: '.strlen($rec[0]).'<br>';
}
输出:
Size of the content: 65537
Size of the content: 131073
Size of the content: 196609
Size of the content: 262145
Size of the content: 327681
Size of the content: 393217
Size of the content: 458753
Size of the content: 524289
Size of the content: 589825
Size of the content: 655361
Size of the content: 720897
Size of the content: 786433
Size of the content: 851969
Size of the content: 917505
Size of the content: 983041
Size of the content: 0
Size of the content: 65536
Size of the content: 131072
Size of the content: 196608
发生了什么事? LONGBLOB应该获取更多的数据.
Whats happening? LONGBLOB should take more data than that.
推荐答案
增大max_allowed_packet
大小.
It looks like it is failing at 1MB, and according to https://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html the default max packet size is 1MB:
服务器的默认max_allowed_packet值为1MB.如果服务器需要处理大查询,则可以增加此值
The server's default max_allowed_packet value is 1MB. You can increase this if the server needs to handle big queries
在my.cnf
文件中设置值,例如:
Set the value in the my.cnf
file, e.g.:
[mysqld]
max_allowed_packet=16M
在PHP中
如果您无权访问MySQL配置,则可以尝试通过查询进行设置(注意:我尚未检查这是否可行).
In PHP
If you don't have access to the MySQL config you could try setting via a query (note: I've not checked if this will work).
$db->query( 'SET @@global.max_allowed_packet = 16777216' );
这篇关于MySQL,CONCAT,一段时间后结果为null的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!