MySQL:插入具有自动增量列的表后出现意外的 SELECT 结果

Question

当我将一些数据插入表中然后在同一个表上运行 SELECT 查询时，我看到了一个奇怪的行为。此表有一个自动递增的主键 (uid)，当我尝试选择“uid IS NULL”的结果时会出现此问题。

我将其归结为以下 SQL 命令:

DROP TABLE IF EXISTS test_users;
CREATE TABLE test_users (uid INTEGER PRIMARY KEY AUTO_INCREMENT, name varchar(20) NOT NULL);
INSERT INTO test_users(name) values('foo');
SELECT uid FROM test_users WHERE uid IS NULL;
SELECT uid FROM test_users WHERE uid IS NULL; -- no output from this query

我希望 SELECT uid FROM test_users WHERE uid IS NULL 永远不会返回任何东西，但有时确实如此。这是我发现的:

• MySQL/MariaDB 的版本似乎很重要。出现此问题的机器正在运行 MySQL 5.1.73(CentOS 6.5，32 位和 64 位)。我的另一台机器运行 5.5.37-MariaDB(Fedora 19，64 位)。除了被配置为使用 MyISAM 表之外，两者都运行默认配置。
• 仅影响 INSERT 之后的第一个 SELECT 查询。
• 如果我为 uid 指定一个值而不是让它自动递增，那没问题。
• 如果我在 INSERT 和 SELECT 之间断开连接并重新连接，那么我不会得到预期的结果。这在我管理连接对象的 Perl 之类的东西中最容易看到。我在 https://gist.github.com/avuserow/1c20cc03c007eda43c82 有一个测试脚本演示了这一点

Answer 1

此行为是设计使然。

它显然等同于 SELECT * FROM t1 WHERE id = LAST_INSERT_ID(); 也只能在您刚刚执行插入的连接上工作，正如您所描述的那样。

这显然是一种解决方法，您可以在某些环境中使用，这些环境使得很难以更传统的方式获取最后插入的(通过您的连接)行的自动增量值。

准确地说，它实际上是分配给连接的last 插入语句插入的first 行的auto_increment 值。当您只插入一行时，情况是一样的，但是当您使用单个插入语句插入多行时，情况就不一样了。

http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-usagenotes-functionality-last-insert-id.html