MySQL max_allowed_packet 参数说明

max_allowed_packet 参数说明

参考文档：https://dev.mysql.com/doc/refman/8.0/en/packet-too-large.html

A communication packet is a single SQL statement sent to the MySQL server, a single row that is sent to the client, or a binary log event sent from a master replication server to a slave.

一个通讯packet表示client传给server的一个sql语句，或者server发送给client的一行记录，或者master传给slave的一个binary log event。

The largest possible packet that can be transmitted to or from a MySQL 8.0 server or client is 1GB.

在MySQL8.0,packet最大为1GB。

When a MySQL client or the mysqld server receives a packet bigger than max_allowed_packet bytes, it issues an ER_NET_PACKET_TOO_LARGE error and closes the connection. With some clients, you may also get a Lost connection to MySQL server during query error if the communication packet is too large.

当client或者server的packet大于max_allowed_packet的值时，会报ER_NET_PACKET_TOO_LARGE错并关闭连接，在一些client的实现中，还会得到lost connection的错误。

Both the client and the server have their own max_allowed_packet variable, so if you want to handle big packets, you must increase this variable both in the client and in the server.

client和server都有自己的max_allowed_packet参数，要处理更大的packet，必须要同时设置client和server的参数才行。

查看

MariaDB [(none)]> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

MySQL和MariaDB的默认max_allowed_packet为1MB。

设置

1.mysql默认client客户端

在连接时设置

mysql --max_allowed_packet=32M

2.MySQL server的设置

在配置文件my.cnf中设置

[mysqld]
max_allowed_packet=128M

3.设置全局参数

设置为MySQL的所有客户端都生效，直到MySQL重启

SET GLOBAL max_allowed_packet=1073741824;

对于MEDIUMTEXT和LONGTEXT存储长数据字符串会报错

text类型的最大长度分别为：

      Type | Maximum length
-----------+-------------------------------------
  TINYTEXT |           255 (2 8−1) bytes
      TEXT |        65,535 (216−1) bytes = 64 KiB
MEDIUMTEXT |    16,777,215 (224−1) bytes = 16 MiB
  LONGTEXT | 4,294,967,295 (232−1) bytes =  4 GiB

但是MEDIUMTEXT和LONGTEXT的最大长度超过了max_allowed_packet默认的1MB，因此如果保存超过1MB的数据，会报错。

因此这个时候就需要增大max_allowed_packet的值，才能保存更大的MEDIUMTEXT和LONGTEXT。

SimpleDao