重啟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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 重啟對海外伺服器會有影響嗎?伺服器
- 關於資料庫開啟大頁對效能的影響資料庫
- 關於OPcache對Swoole影響的理解opcache
- MySQL alter 新增列對dml影響MySql
- 遊戲暗示對於遊戲玩家的影響遊戲
- mysql事務對效率的影響分析總結JILEMySql
- 伺服器IO瓶頸對MySQL效能的影響伺服器MySql
- 16、MySQL Case-索引key對select count(*)的影響MySql索引
- mysql的DDL操作對業務產生影響測試MySql
- 那些疫情影響下消失的旅遊勝地,重啟了嗎?
- 影響mysql效能的因素都有哪些MySql
- 影響MySQL效能的硬體因MySql
- 影響MySQL效能的硬體因素MySql
- MySQL:查詢欄位數量多少對查詢效率的影響MySql
- MySQL:slave_skip_errors引數對MGR可用性的影響MySqlError
- 關於LOL成就係統對玩家影響的簡略分析
- 國家資料局成立對企業的影響:啟用資料要素 重構資料價值
- unusable index對DML/QUERY的影響Index
- Nologging對恢復的影響(二)
- 語言對思維的影響
- Nologging對恢復的影響(一)
- rman開啟備份優化對備份歸檔的影響優化
- MySQL5.7之auto_increment回溯MySqlREM
- mysql中auto_increment是什麼MySqlREM
- 網線的分類與對網速的影響 網線對網速影響大嗎?
- 浮動的盒子對img的影響
- 影響網站權重的幾大因素網站
- MySQL運維實戰(5.6) 字符集設定對mysqldump的影響MySql運維
- 第49問:如何快速判斷 IO 延遲對 MySQL 效能的影響MySql
- MySQL中join語句的基本使用教程及其欄位對效能的影響MySql
- 元宇宙技術對於虛擬模擬應用的影響元宇宙
- 來電對播放音樂的影響
- python:super()對多繼承的影響Python繼承
- DB2 HADR對效能的影響DB2
- INDEX建立方式對SQL的影響IndexSQL
- linus mysql 重啟MySql
- 小記: 關於CSS display 屬性對錶格語義的影響CSS
- 人工智慧對於IT行業的從業者影響分析報告人工智慧行業
- Java教程:影響MySQL效能的配置引數JavaMySql