mysql auto_increment 列按随机值递增

Question

今天我遇到了我所见过的 MySQL 中最奇怪的事情之一。 我有一个简单的表：

CREATE TABLE `features`
(
  `feature_id` mediumint(6) unsigned NOT NULL AUTO_INCREMENT,
  `feature_name` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
  PRIMARY KEY (`feature_id`),
  UNIQUE KEY `feature_name_key` (`feature_name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

我正在使用 Java 和 mysql-connector-java-5.1.15 库在其中插入数据。 feature_name 中的数据可能重复，我只想要唯一的值。我可能会使用 INSERT IGNORE，但如果数据太长，我可能会忽略它，所以我使用这个：

pstmt = conn.prepareStatement( 
        "INSERT INTO features (feature_name) VALUES (?)" );

for ( String featureName: data4db.keySet() )
{
    pstmt.setString(1, featureName );

    try
    {
        pstmt.executeUpdate();
    }
    catch ( SQLException se )
    {
        if ( se.getErrorCode() == 1062 ) // duplicate entry
        {
            continue; // ignore
        }

        throw se; // do not ignore anything else
    }
}

将数据插入数据库后，我注意到存在一些我什至没有预料到的问题。上表中大约有 4000 条记录，这是可以的。唯一的问题是，由于重复的主键，某些数据无法插入，因此我查看了该表的 auto inc 值的外观。事实证明，对于大多数数据来说，下一个相邻行的 id 按预期增加了 1。由于我不知道的原因，有时 feature_id 会增加 3、5、1000、100000 - 完全随机的值。因此，我“在这个表中已经没有位置了”，因为一旦 id 达到中等整数的最大值，就无法插入它。

怎么会发生这种事呢？ 有没有人遇到过类似的事情？ 值得一提的是，只有一个程序有一个线程写入该表。 我还有一张几乎相同的表 - 列宽和名称不同。对于这个也有类似的问题。

顺便说一句 - 更多数据：

mysql> show global variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.01 sec)

mysql> show global variables like 'ver%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.5.10                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86                          |
| version_compile_os      | Win32                        |
+-------------------------+------------------------------+

感谢您提前提供任何提示。

Answer 1

这是正常的 MySQL 行为。发生的情况如下：您将数据插入到 auto_increment key 3 然后您得到重复的键，因为您的 feature_name_key 被定义为唯一的。
事实是，MySQL 将“浪费”整数 4 并继续处理下一个，它不会重用由于键约束而写入失败的整数。

如果你有这样的事情：

PK | feature_name_key
1 | key1
2 | key2
3 | key3
4 | key1 (fails due to constraint, 4 is not going to be used for next successful insertion, hence the gaps in your primary key)

那么你就会失去可用于主键/自动增量的整数。在插入或构建表来保存数据时重新考虑您的策略。

Answer 2

在插入之间，您是否删除了行？ MySQL 可能只是记住自动增量计数器。