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

举报
lxw1844912514 发表于 2022/03/27 00:48:00 2022/03/27
【摘要】 1)对于MyISAM表,如果用UPDATE更新自增列,如果列值与已有的值重复,则会出错;如果大于已有的最大值,则会自动更新表的AUTO_INCREMENT,操作是安全的。 (2)对于innodb表,update auto_increment字段,如果列值与已有的值重复,则会出错;如果大于已有的最大值,可能会引入一个坑,会造成编...

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

在维护有AUTO_INCREMENT列的表时,另外一个注意点,参考:

MySQL使用AUTO_INCREMENT列的表注意事项之delete数据篇

http://blog.csdn.net/hw_libo/article/details/40149173

 

下面实验证实:

 

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'


原文:https://blog.csdn.net/HW_LiBo/article/details/40097125

文章来源: blog.csdn.net,作者:lxw1844912514,版权归原作者所有,如需转载,请联系作者。

原文链接:blog.csdn.net/lxw1844912514/article/details/100029242

【版权声明】本文为华为云社区用户转载文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。