重啟mysql對於auto_increment的影響
重啟mysql對於auto_increment的影響：
mysql> alter table inc_innodb AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table inc_myisam AUTO_INCREMENT=100;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 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=100 DEFAULT CHARSET=utf8 |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table inc_myisam;
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| inc_myisam | CREATE TABLE `inc_myisam` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
重啟資料庫後，
mysql> use test;
No connection. Trying to reconnect...
Connection id: 2
Current database: *** NONE ***
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
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> show create table inc_myisam;
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| inc_myisam | CREATE TABLE `inc_myisam` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from inc_innodb;
+----+--------+
| id | name |
+----+--------+
| 1 | name1 |
| 2 | name22 |
+----+--------+
2 rows in set (0.00 sec)
再次實驗update對於innodb的影響：
mysql> update inc_innodb set id=10 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from inc_innodb;
+----+-------+
| id | name |
+----+-------+
| 10 | name1 |
+----+-------+
1 row 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=2 DEFAULT CHARSET=utf8 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
重啟庫
mysql> use test;
No connection. Trying to reconnect...
Connection id: 2
Current database: *** NONE ***
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
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=11 DEFAULT CHARSET=utf8 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
對於innodb來說，重啟庫之後，會根據現有最大值來判斷重啟後的AUTO_INCREMENT是多少
對於myisam來說，AUTO_INCREMENT是不會變的
轉載請註明源出處
QQ 273002188 歡迎一起學習
QQ 群 236941212
oracle,mysql,mongo 相互交流
http://blog.itpub.net/25099483/viewspace-1869361/
http://blog.itpub.net/25099483/viewspace-1869360/
mysql> alter table inc_innodb AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table inc_myisam AUTO_INCREMENT=100;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 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=100 DEFAULT CHARSET=utf8 |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table inc_myisam;
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| inc_myisam | CREATE TABLE `inc_myisam` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
重啟資料庫後，
mysql> use test;
No connection. Trying to reconnect...
Connection id: 2
Current database: *** NONE ***
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
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> show create table inc_myisam;
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| inc_myisam | CREATE TABLE `inc_myisam` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from inc_innodb;
+----+--------+
| id | name |
+----+--------+
| 1 | name1 |
| 2 | name22 |
+----+--------+
2 rows in set (0.00 sec)
再次實驗update對於innodb的影響：
mysql> update inc_innodb set id=10 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from inc_innodb;
+----+-------+
| id | name |
+----+-------+
| 10 | name1 |
+----+-------+
1 row 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=2 DEFAULT CHARSET=utf8 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
重啟庫
mysql> use test;
No connection. Trying to reconnect...
Connection id: 2
Current database: *** NONE ***
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
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=11 DEFAULT CHARSET=utf8 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
對於innodb來說，重啟庫之後，會根據現有最大值來判斷重啟後的AUTO_INCREMENT是多少
對於myisam來說，AUTO_INCREMENT是不會變的
轉載請註明源出處
QQ 273002188 歡迎一起學習
QQ 群 236941212
oracle,mysql,mongo 相互交流
http://blog.itpub.net/25099483/viewspace-1869361/
http://blog.itpub.net/25099483/viewspace-1869360/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25099483/viewspace-1869362/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 表型別對AUTO_INCREMENT的影響型別REM
- 重啟對海外伺服器會有影響嗎？伺服器
- mysql event對主從的影響MySql
- 遊戲暗示對於遊戲玩家的影響遊戲
- 關於OPcache對Swoole影響的理解opcache
- JAVA 異常對於效能的影響Java
- 關於drop操作對role的影響
- MySQL alter 新增列對dml影響MySql
- InnoDB 隔離模式對 MySQL 效能的影響模式MySql
- 嚴格模式下對於this指向的影響模式
- reverse index 對於 MAX/MIN操作的影響Index
- mysql刪除和更新操作對效能的影響MySql
- ORALCE 的AUDIT 以及開啟AUDIT對REDO 的影響
- padding對於元素position定位的影響padding
- MySQL的AUTO_INCREMENTMySqlREM
- 伺服器IO瓶頸對MySQL效能的影響伺服器MySql
- mysql的DDL操作對業務產生影響測試MySql
- mysql事務對效率的影響分析總結JILEMySql
- table_open_cache引數對mysql效能的影響MySql
- 並行查詢對於響應時間的影響實驗並行
- padding和margin對於position定位的影響padding
- oracle cardinality對於執行計劃的影響Oracle
- 16、MySQL Case-索引key對select count(*)的影響MySql索引
- 負外邊距margin對於絕對定位元素的影響
- 【Oracle】-【COMMIT對索引的影響】-從trace看COMMIT對索引的影響OracleMIT索引
- 基於row cache object latch研究對於sga抖動的影響Object
- myisam對於update，insert,delete關於auto_incremant的影響deleteREM
- innodb對於update，insert,delete關於auto_incremant的影響deleteREM
- MySQL AUTO_INCREMENTMySqlREM
- shrink 操作對索引的影響索引
- Update操作對索引的影響索引
- 資料對於製造業的國際化影響
- padding對於margin的影響簡單介紹padding
- 前端框架對於未來web移動端的影響前端框架Web
- db_files對於oracle使用記憶體的影響Oracle記憶體
- RAID的概念和RAID對於SQL效能的影響AISQL
- MYSQL sync_relay_log對I/O thread的影響分析MySqlthread
- 國家資料局成立對企業的影響：啟用資料要素 重構資料價值