数据库|mysql教程
注意事项
数据库-mysql教程
方维o2o商业系统源码,vscode没有插件提示,vnc连不上ubuntu,访问tomcat下地址,分布式爬虫增量式爬虫,php 替换数字,福田百度seo外包,网站免费订单系统源码,destoon 企业模板lzw
1. 说明
（1）对于MyISAM表，如果用UPDATE更新自增列，如果列值与已有的值重复，则会出错；如果大于已有的最大值，则会自动更新表的AUTO_INCREMENT，操作是安全的。
微信平台微网站源码,ubuntu 无鼠标,tomcat7 cmd乱码,爬虫 亚马逊邮箱,php5项目开发实战详解,如何进行关键词分析seolzw
（2）对于innodb表，update auto_increment字段，如果列值与已有的值重复，则会出错；如果大于已有的最大值，可能会引入一个坑，会造成编号重复错误，插入数据失败的情况，可见在update自增列值是要注意。
asp充值 源码,vscode 相同代码检验,ubuntu 美化壁纸,查看指定tomcat版本,sqlite怎样增加一列,爬虫爬中华英才网代码,php 加密解密字符串,印刷行业seo优化公司,html手机版网站,网上书城html模板lzw
环境描述：RHEL 6.4 x86_64 + MySQL 5.6.19
blog地址：/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: 0MySQL [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: t5Create Table: CREATE TABLE `t5` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf81 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: 0MySQL [bosco]> show create table t5\G*************************** 1. row *************************** Table: t5Create Table: CREATE TABLE `t5` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf81 row in set (0.00 sec)
2.2 MyISAM表update自增列，由小改大
MySQL [bosco]> show create table t5\G*************************** 1. row *************************** Table: t5Create Table: CREATE TABLE `t5` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf81 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: 0MySQL [bosco]> show create table t5\G*************************** 1. row *************************** Table: t5Create Table: CREATE TABLE `t5` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf81 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: 0MySQL [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: t6Create Table: CREATE TABLE `t6` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf81 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: 0MySQL [bosco]> show create table t6\G*************************** 1. row *************************** Table: t6Create Table: CREATE TABLE `t6` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf81 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: t6Create Table: CREATE TABLE `t6` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf81 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: 0MySQL [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: t6Create Table: CREATE TABLE `t6` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf81 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: 0MySQL [bosco]> insert into t6 values(null); ## 错误出现了。ERROR 1062 (23000): Duplicate entry 12 for key PRIMARY
blog地址：/hw_libo/article/details/40097125
— Bosco QQ：375612082
—- END —-
——————————————————————————————————-
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!
