MySQL使用AUTO_INCREMENT列的表注意事項之update自增列篇

MySQL使用AUTO_INCREMENT列的表注意事項之update自增列篇

1. 說明

（1）對於MyISAM表，如果用UPDATE更新自增列，如果列值與已有的值重復，則會出錯；如果大於已有的最大值，則會自動更新表的AUTO_INCREMENT，操作是安全的。

（2）對於innodb表，update auto_increment字段，如果列值與已有的值重復，則會出錯；如果大於已有的最大值，可能會引入一個坑，會造成編號重復錯誤，插入數據失敗的情況，可見在update自增列值是要注意。

環境描述：RHEL 6.4 x86_64 + MySQL 5.6.19

blog地址：http://blog.csdn.net/hw_libo/article/details/40097125

下面實驗證實：

2. MyISAM表

MySQL [bosco]> CREATE TABLE `t5` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)


MySQL [bosco]> insert into t5 values(null);
Query OK, 1 row affected (0.07 sec)


MySQL [bosco]> select * from t5;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)


MySQL [bosco]> insert into t5 values(5),(9);
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0


MySQL [bosco]> select * from t5;
+----+
| id |
+----+
|  1 |
|  5 |
|  9 |
+----+
3 rows in set (0.00 sec)

2.1 MyISAM表update自增列，由大改小

MySQL [bosco]> show create table t5\G
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


MySQL [bosco]> update t5 set id=4 where id=9;   ## 將自增列由大改小，沒有問題
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0


MySQL [bosco]> show create table t5\G
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

2.2 MyISAM表update自增列，由小改大

MySQL [bosco]> show create table t5\G
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


MySQL [bosco]> update t5 set id=12 where id=5;   ## 將自增列由小改大，而且大於當前的AUTO_INCREMENT，同樣是沒有問題
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0


MySQL [bosco]> show create table t5\G
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
這裡自動修改最新的auto_increment變為13。

3. InnoDB表

MySQL [bosco]> CREATE TABLE `t6` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)


MySQL [bosco]> insert into t6 values(null);
Query OK, 1 row affected (0.05 sec)


MySQL [bosco]> insert into t6 values(5),(9);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0


MySQL [bosco]> select * from t6;
+----+
| id |
+----+
|  1 |
|  5 |
|  9 |
+----+
3 rows in set (0.00 sec)

3.1 InnoDB表update自增列，由大改小

MySQL [bosco]> show create table t6\G
*************************** 1. row ***************************
       Table: t6
Create Table: CREATE TABLE `t6` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


MySQL [bosco]> update t6 set id=4 where id=9;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0


MySQL [bosco]> show create table t6\G
*************************** 1. row ***************************
       Table: t6
Create Table: CREATE TABLE `t6` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

3.2 InnoDB表update自增列，由小改大

MySQL [bosco]> select * from t6;
+----+
| id |
+----+
|  1 |
|  4 |
|  5 |
+----+
3 rows in set (0.00 sec)


MySQL [bosco]> show create table t6\G
*************************** 1. row ***************************
       Table: t6
Create Table: CREATE TABLE `t6` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


MySQL [bosco]> update t6 set id=12 where id=5;   ## 將自增列由小改大，而且大於當前的AUTO_INCREMENT，這就相當於挖了坑了
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0


MySQL [bosco]> select * from t6;
+----+
| id |
+----+
|  1 |
|  4 |
| 12 |
+----+
3 rows in set (0.01 sec)


MySQL [bosco]> show create table t6\G
*************************** 1. row ***************************
       Table: t6
Create Table: CREATE TABLE `t6` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
表中自增列最大值已經是12，這個update操作不會自動修改最新的auto_increment變為13，那麼這就會有問題，以後增加到12後，就會出現沖突，導致數據插入失敗：
MySQL [bosco]> insert into t6 values(null),(null);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0


MySQL [bosco]> insert into t6 values(null);   ## 錯誤出現了。
ERROR 1062 (23000): Duplicate entry '12' for key 'PRIMARY'

blog地址：http://blog.csdn.net/hw_libo/article/details/40097125

-- Bosco QQ：375612082

---- END ----
-------------------------------------------------------------------------------------------------------
版權所有，文章允許轉載，但必須以鏈接方式注明源地址，否則追究法律責任!