很多次通过本机的mysql命令发现,select 或者show
的时候,最多能显示1000行,并且每次
show variables like 'max_join%';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| max_join_size | 1000000 |
+---------------+---------+
1 row in set (0.00 sec)
而通过其他机器远程连接
确是这样的
show variables like 'max_join%';
+---------------+----------------------+
| Variable_name | Value
|
+---------------+----------------------+
| max_join_size | 18446744073709551615 |
+---------------+----------------------+
1 row in set (0.00 sec)
一直搞的我很疑惑,终于最近,找到原因了,一切都是mysql命令的一个参数(--safe_update)弄的,在--safe_update状态登录的话,在他连接的时候,会设置
SET sql_safe_updates=1, sql_select_limit=1000,
sql_max_join_size=1000000;
这样登录后,当然会出现select最多只能显示1000行了,
而在我的my.cnf配置中,很早之前别人给我设置了,所以才导致我曾经的疑惑
如:
[mysql]
safe-updates
其实我们登录后,通过status命令或者“\s”也能发现这个问题,当通过safe_update登录,我们查看得如下信息
mysql--root@localhostnone)
10:48:02>>\s
--------------------
--------------------
Note that you are running in safe_update_mode:
UPDATEs and DELETEs that don't use a key in the WHERE clause are
not allowed.
(One can force an UPDATE/DELETE by adding LIMIT # at the end of the
command.)
SELECT has an automatic 'LIMIT 1000' if LIMIT is not used.
Max number of examined row combination in a join is set to:
1000000
当然在mysql登录的时候,sql_select_limit
跟sql_max_join_size也是可以跟改的
如:mysql --safe-updates --select_limit=500
--max_join_size=10000