mysql显示用户之间不同的数据

Question

我最近将我的mysql迁移到另一台服务器，迁移后，出现一些奇怪的情况。

当我使用mysql作为远程主机时:

$ mysql -u root -p -h remote
mysql> select id from People order by id desc limit 10;
+-----+
| id  |
+-----+
| 941 |
| 940 |
| 939 |
| 938 |
| 937 |
| 936 |
| 935 |
| 934 |
| 933 |
| 932 |
+-----+

但是当我从本地主机使用时:

$ mysql -u root -p 
mysql> select id from People order by id desc limit 10;
+-----+
| id  |
+-----+
| 939 |
| 938 |
| 937 |
| 936 |
| 935 |
| 934 |
| 933 |
| 932 |
| 931 |
| 930 |
+-----+

查询结果缺少2行!

我还检查了权限:

对于远程情况，它显示

mysql> show grants;
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for root@example.com                                                                                    |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'example.com' IDENTIFIED BY PASSWORD '521f3dd02c6d22f8' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

对于本地主机，它具有相同的权限，

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

有人可以帮我解释为什么 user@host 之间的查询结果不同吗？

Answer 1

我猜您没有提交交易。当使用 InnoDB 表并激活隐式事务设置时，您首先必须在第一个客户端中强制执行提交，以使您的更改对所有用户可见。