innodb對於update,insert,delete關於auto_incremant的影響
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/
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- myisam對於update,insert,delete關於auto_incremant的影響deleteREM
- 檢視insert,delete,update對基表的影響(檢視初識)delete
- mysql innodb新建索引堵塞update ,insert,deleteMySql索引delete
- 關於OPcache對Swoole影響的理解opcache
- 關於drop操作對role的影響
- Update操作對索引的影響索引
- Delete,insert,update與undo的關係[轉載TOM文章]delete
- PL/sql中怎樣得到insert,update,delete語句執行後,受影響的紀錄行數關鍵字SQLdelete
- 34、VIEW可以insert,delete,update.Viewdelete
- 遊戲暗示對於遊戲玩家的影響遊戲
- JAVA 異常對於效能的影響Java
- sql server 帶有OUTPUT的INSERT,DELETE,UPDATESQLServerdelete
- 關於LOL成就係統對玩家影響的簡略分析
- delete語句對索引的影響之分析delete索引
- Default Locking for INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE (351)delete
- 物化檢視日誌對UPDATE的影響
- java-Mybatis XML 對映器(select,insert, update 和 delete)JavaMyBatisXMLdelete
- 嚴格模式下對於this指向的影響模式
- reverse index 對於 MAX/MIN操作的影響Index
- InnoDB 隔離模式對 MySQL 效能的影響模式MySql
- mysql 在delete、insert、update 時,page的變化MySqldelete
- MySQL之資料的insert-delete-update操作MySqldelete
- 模擬insert,update和delete造成阻塞的示例delete
- 關於delete還是update會產生更多日誌的問題delete
- C# 關於Try/Catch對系統效能影響的總結C#
- SQLite語句(二):INSERT DELETE UPDATE SELECTSQLitedelete
- MongoDB入門系列(二):Insert、Update、Delete、DropMongoDBdelete
- padding對於元素position定位的影響padding
- 重啟mysql對於auto_increment的影響MySqlREM
- 小記: 關於CSS display 屬性對錶格語義的影響CSS
- 基於row cache object latch研究對於sga抖動的影響Object
- 利用insert,update和delete注入獲取資料delete
- MERGE新特性(UPDATE WHERE,DELETE WHERE,INSERT WHERE)delete
- LINQ to SQL語句之Insert/Update/Delete操作SQLdelete
- Oracle資料庫中Insert、Update、Delete操作速度Oracle資料庫delete
- padding和margin對於position定位的影響padding
- oracle cardinality對於執行計劃的影響Oracle
- 關於java檔名字影響系統配置Java