插入或者更新过大数据失败--max_allowed_packet

出坑场景：

报表整个json字符串都要保存到数据库里，数据包非常大！！

MySQL根据配置文件会限制Server接受的数据包大小。有时候大的插入和更新会受 max_allowed_packet 参数限制，导致大数据写入或者更新失败。

查看目前配置：

mysql> show VARIABLES like 'max_allowed_packet';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| max_allowed_packet | 1073741824 |
+--------------------+------------+
1 row in set

max_allowed_packet的单位为字节:

-- 转化为Mb,就是1024Mb
mysql> select 1073741824/1024/1024;
+----------------------+
| 1073741824/1024/1024 |
+----------------------+
| 1024.00000000        |
+----------------------+
1 row in set

修改方法1-配置文件修改

可以编辑my.cnf,在[mysqld]段或者mysql的server配置段进行修改。

max_allowed_packet = 20M

修改my.cnf,配置要重载才能生效

修改方法2-命令修改

参数生效范围为global,不是session. 如果服务器重启设置会失效

set global max_allowed_packet = 2*1024*1024*10

注意

查询时使用的是show variables的话，发现设置好像并没有生效，这是因为show variables等同于show session variables，查询的是会话变量，只有使用show global variables，查询的才是全局变量。

示例:

• 查询 max_allowed_packet = 1073741824

mysql> show VARIABLES like 'max_allowed_packet';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| max_allowed_packet | 1073741824 |
+--------------------+------------+
1 row in set

• 设置 max_allowed_packet = 1048576

mysql> set global max_allowed_packet = 1*1024*1024;
Query OK, 0 rows affected

• 查询结果没变

mysql> show VARIABLES like 'max_allowed_packet';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| max_allowed_packet | 1073741824 |
+--------------------+------------+
1 row in set

• 全局查看

mysql> show global VARIABLES like 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set

max_allowed_packet 值设置过小将导致单个记录超过限制后写入数据库失败，且后续记录写入也将失败，为了数据完整性，需要考虑到事务因素。

作者：人生走马灯

來源：简书