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

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

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

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

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/112442.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • Git 切换分支命令

    Git 切换分支命令从Github上clone下来的项目都是主分支branch,为了开发的安全性,如何切换到其它分支呢?gitbranch查看本地分支*表示当前所处的分支,如下图所示:gitbranch-a查看项目所有分支:gitcheckout-b切换分支,例如我切换到stardard-base-4.x-dev:gitcheckout-bstandard-base-4.x-devorigin/standard-base-4.x-dev第二次切换直接填入分支名称即可。

  • leetCode191/201/202/136 -Number of 1 Bits/Bitwise AND of Numbers Range/Happy Number/Single Number「建议收藏」

    leetCode191/201/202/136 -Number of 1 Bits/Bitwise AND of Numbers Range/Happy Number/Single Number

  • linux 设备树

    linux 设备树linux设备树参考地址http://blog.csdn.net/green1900/article/details/45646095http://www.cnblogs.com/xiaojiang1025/p/6131381.htmlhttp://blog.csdn.net/21cnbao/article/details/84575461.为什么要使用设备树(DeviceTree

  • Linux系统怎么修改用户名_怎么更改微软用户名

    Linux系统怎么修改用户名_怎么更改微软用户名修改用户名(以CentOS-8为例)①进入root用户进行操作,在/etc/sudoers添加admin(新用户名)ALL=(ALL)ALLsurootvim/etc/sudoers②进入/etc/passwd将旧用户名改为admin(新用户名)vim/etc/passwd③进入/etc/group将旧用户名改为admin(新用户名)vim/etc/group④进入/etc/shadow将旧用户名改为admin(新用户名)

  • http_build_query()函数使用方法

    http_build_query()函数使用方法

  • 永恒之蓝漏洞入侵_永恒之蓝漏洞探测

    永恒之蓝漏洞入侵_永恒之蓝漏洞探测一、基础知识介绍:1.何为永恒之蓝?永恒之蓝(EternalBlue)爆发于2017年4月14日晚,是一种利用Windows系统的SMB协议漏洞来获取系统的最高权限,以此来控制被入侵的计算机。甚至于2017年5月12日,不法分子通过改造“永恒之蓝”制作了wannacry勒索病毒,使全世界大范围内遭受了该勒索病毒,甚至波及到学校、大型企业、政府等机构,只能通过支付高额的赎…

    2022年10月17日

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号