首页 MySQL 正文
212

Mysql中一条sql到底能写多长?

  • yiqingpeng
  • 2020-10-31
  • 0
  •  

先看来https://php.golaravel.com/mysqli.query.html中的一段话:
where you pass a statement to mysqli_query() that is longer than max_allowed_packet of the server, the returned error codes are different depending on whether you are using MySQL Native Driver (mysqlnd) or MySQL Client Library (libmysqlclient). The behavior is as follows:

  • mysqlnd on Linux returns an error code of 1153. The error message means got a packet bigger than max_allowed_packet bytes.



  • mysqlnd on Windows returns an error code 2006. This error message means server has gone away.



  • libmysqlclient on all platforms returns an error code 2006. This error message means server has gone away.




所以说,mysql中一条sql的长度是有限制的。
到底允许多长,请看官方解释(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 source replication server to a replica.

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

When a MySQL client or the mysqld server receives a packet bigger than max_allowed_packetbytes, 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.

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.

If you are using the mysql client program, its default max_allowed_packet variable is 16MB. To set a larger value, start mysql like this:

shell> mysql --max_allowed_packet=32M

That sets the packet size to 32MB.

The server's default max_allowed_packet value is 64MB. You can increase this if the server needs to handle big queries (for example, if you are working with big BLOB columns). For example, to set the variable to 128MB, start the server like this:

		
shell> mysqld --max_allowed_packet=128M

You can also use an option file to set max_allowed_packet. For example, to set the size for the server to 128MB, add the following lines in an option file:

[mysqld] max_allowed_packet=128M

It is safe to increase the value of this variable because the extra memory is allocated only when needed. For example, mysqld allocates more memory only when you issue a long query or when mysqld must return a large result row. The small default value of the variable is a precaution to catch incorrect packets between the client and server and also to ensure that you do not run out of memory by using large packets accidentally.

You can also get strange problems with large packets if you are using large BLOB values but have not given mysqld access to enough memory to handle the query. If you suspect this is the case, try adding ulimit -d 256000 to the beginning of the mysqld_safe script and restarting mysqld.

正在加载评论...