innodb對於update，insert,delete關於auto_incremant的影響

psufnxk2000發表於2015-12-09
deleteREM
innodb對於update，insert,delete關於auto_incremant的影響
mysql> create table inc_innodb (id int auto_increment primary key,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)


mysql> insert into inc_innodb (name) values ('name1');
Query OK, 1 row affected (0.00 sec)


mysql> insert into inc_innodb (name) values ('name22');
Query OK, 1 row affected (0.00 sec)


mysql> show create table inc_innodb;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                      |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| inc_innodb | CREATE TABLE `inc_innodb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> insert into inc_innodb values (4,'name4');
Query OK, 1 row affected (0.01 sec)


mysql> select * from inc_innodb;
+----+--------+
| id | name   |
+----+--------+
|  1 | name1  |
|  2 | name22 |
|  4 | name4  |
+----+--------+
3 rows in set (0.00 sec)


mysql> show create table inc_innodb;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                      |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| inc_innodb | CREATE TABLE `inc_innodb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> update inc_innodb set id=10 where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql>  show create table inc_innodb;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                      |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| inc_innodb | CREATE TABLE `inc_innodb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> delete from inc_innodb where id=10;
Query OK, 1 row affected (0.00 sec)


mysql> show create table inc_innodb;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                      |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| inc_innodb | CREATE TABLE `inc_innodb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


對於innodb來說，insert來說 都會把 AUTO_INCREMENT的值升上去，
update不會把AUTO_INCREMENT值升上去
但是delete不會把AUTO_INCREMENT的值降下去。


轉載請註明源出處 
QQ 273002188 歡迎一起學習 
QQ 群 236941212 
oracle,mysql,mongo 相互交流

相關：
http://blog.itpub.net/25099483/viewspace-1869360/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25099483/viewspace-1869361/，如需轉載，請註明出處，否則將追究法律責任。

相關文章