mysql中的查询长度是否有限制？

I am asking this question because I need to know this limitation as I am generating SELECT query in my PHP script and the part of WHERE in this query is generated inside the loop.
Precisely it looks like this

我问这个问题是因为我需要知道这个限制,因为我在我的PHP脚本中生成SELECT查询,并且在查询中WHERE的部分是在循环内部生成的。确切地说,它看起来像这样

 $query="SELECT field_names FROM table_name WHERE ";
 $condition="metadata like \"%$uol_metadata_arr[0]%\" ";
 for($i=1; $i<count($uol_metadata_arr); $i++){
    $condition.=" OR metadata like \"%$uol_metadata_arr[$i]%\" ";
 }
 $query.=$condition;
 $result=mysql_query($query);

So, that's why I need to know how long my $query string can be, because the array $uol_metadata_arr could contain many items.

所以,这就是为什么我需要知道我的$查询字符串可以有多长,因为数组$ uol_metadata_arr可能包含很多项。

2 个解决方案

#1

(if possible) Use WHERE metadata IN ('value1', 'value2')

(如果可能)使用WHERE元数据IN('value1','value2')

You may need to increase max_allowed_packet. It defaults to 16MB (client-side, and as low as 1MB server-side in older versions), and it's not that hard to construct a query that runs up against that limit (e.g., importing data from elsewhere with a giant INSERT query)

您可能需要增加max_allowed_packet。它默认为16MB(客户端,在旧版本中低至1MB服务器端),并且构建一个超过该限制的查询并不困难(例如,使用巨大的INSERT查询从其他地方导入数据)

LIKE '%string%' is a performance killer. Such a query can't use an index on that column. LIKE 'string%' on the other hand, is indexable

LIKE'%string%'是一个性能杀手。此类查询无法在该列上使用索引。另一方面,LIKE'string%'是可索引的

#2

See the max_allowed_packet global variable. You'll need access to the my.cnf file to adjust it (and need to adjust it on your client as well). The typical defaults are either 1mb or 16mb...

请参阅max_allowed_packet全局变量。您需要访问my.cnf文件进行调整(并且需要在客户端上进行调整)。典型的默认值是1mb或16mb ......

#1

(if possible) Use WHERE metadata IN ('value1', 'value2')