首页 / MYSQL / MySQL使用AUTO_INCREMENT列的表注意事项之update自增列篇_MySQL

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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL使用AUTO_INCREMENT列的表注意事项之update自增列篇_MySQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5702字，纯文字阅读大概需要9分钟。

内容图文

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。

可见，MyISAM表的update自增列不会存在风险。

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)
可见，InnoDB表update自增列时，由大值改为小值，除了可能会出现重复数据修改失败外，没有其他风险。

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 ----
-------------------------------------------------------------------------------------------------------
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!

内容总结

以上是互联网集市为您收集整理的MySQL使用AUTO_INCREMENT列的表注意事项之update自增列篇_MySQL全部内容，希望文章能够帮你解决MySQL使用AUTO_INCREMENT列的表注意事项之update自增列篇_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/579274.html

来源：【匿名】