mysql的auto_increment詳解

轉載http://blog.csdn.net/fwkjdaghappy1/article/details/7663331

auto_increment的基本特性

創新互聯公司為您提適合企業的網站設計?讓您的網站在搜索引擎具有高度排名，讓您的網站具備超強的網絡競爭力！結合企業自身，進行網站設計及把握，最后結合企業文化和具體宗旨等，才能創作出一份性化解決方案。從網站策劃到成都網站建設、成都做網站，我們的網頁設計師為您提供的解決方案。

MySQL的中AUTO_INCREMENT類型的屬性用于為一個表中記錄自動生成ID功能，可在一定程度上代替Oracle，PostgreSQL等數據庫中的sequence。

在數據庫應用，我們經常要用到唯一編號，以標識記錄。在MySQL中可通過數據列的AUTO_INCREMENT屬性來自動生成。

可在建表時可用“AUTO_INCREMENT=n”選項來指定一個自增的初始值。
可用alter table table_name AUTO_INCREMENT=n命令來重設自增的起始值。

當插入記錄時，如果為AUTO_INCREMENT數據列明確指定了一個數值，則會出現兩種情況，
情況一，如果插入的值與已有的編號重復，則會出現出錯信息，因為AUTO_INCREMENT數據列的值必須是唯一的；
情況二，如果插入的值大于已編號的值，則會把該插入到數據列中，并使在下一個編號將從這個新值開始遞增。也就是說，可以跳過一些編號。
如果自增序列的最大值被刪除了，則在插入新記錄時，該值被重用。
如果用UPDATE命令更新自增列，如果列值與已有的值重復，則會出錯。如果大于已有值，則下一個編號從該值開始遞增。

在使用AUTO_INCREMENT時，應注意以下幾點：
AUTO_INCREMENT是數據列的一種屬性，只適用于整數類型數據列。
設置AUTO_INCREMENT屬性的數據列應該是一個正數序列，所以應該把該數據列聲明為UNSIGNED，這樣序列的編號個可增加一倍。
AUTO_INCREMENT數據列必須有唯一索引，以避免序號重復(即是主鍵或者主鍵的一部分)。
AUTO_INCREMENT數據列必須具備NOT NULL屬性。
AUTO_INCREMENT數據列序號的最大值受該列的數據類型約束，如TINYINT數據列的最大編號是127,如加上UNSIGNED，則最大為255。一旦達到上限，AUTO_INCREMENT就會失效。
當進行全表刪除時，MySQL AUTO_INCREMENT會從1重新開始編號。全表刪除的意思是發出以下兩條語句時：

[php] view plain

delete from table_name;

或者

truncate table table_name

這是因為進行全表操作時，MySQL(和PHP搭配之最佳組合)實際是做了這樣的優化操作：先把數據表里的所有數據和索引刪除，然后重建數據表。
如果想刪除所有的數據行又想保留序列編號信息，可這樣用一個帶where的delete命令以抑制MySQL(和PHP搭配之最佳組合)的優化：

[php] view plain

delete from table_name where 1;

可用last_insert_id（）獲取剛剛自增過的值。

關于mysql auto_increment所帶來的鎖表操作

在mysql5.1.22之前，mysql的“INSERT-like”語句（包INSERT, INSERT…SELECT, REPLACE,REPLACE…SELECT, and LOAD DATA)會在執行整個語句的過程中使用一個AUTO-INC鎖將表鎖住，直到整個語句結束（而不是事務結束）。
因此在使用INSERT…SELECT、INSERT…values(…),values(…)時，LOAD DATA等耗費時間較長的操作時，會將整個表鎖住，而阻塞其他的“INSERT-like”、Update等語句，推薦使用程序將這些語句分成多條語句，一一插入，減少單一時間的鎖表時間。
mysql5.1.22之后mysql進行了改進，引入了參數 innodb_autoinc_lock_mode，通過這個參數控制mysql的鎖表邏輯。
在介紹這個之前先引入幾個術語，方便說明 innodb_autoinc_lock_mode。
1.“INSERT-like”：
INSERT, INSERT … SELECT, REPLACE, REPLACE … SELECT, and LOAD DATA, INSERT … VALUES(),VALUES()
2.“Simple inserts”：
就是通過分析insert語句可以確定插入數量的insert語句, INSERT, INSERT … VALUES(),VALUES()
3.“Bulk inserts”：
就是通過分析insert語句不能確定插入數量的insert語句, INSERT … SELECT, REPLACE … SELECT, LOAD DATA
4.“Mixed-mode inserts”：
不確定是否需要分配auto_increment id，一般是下面兩種情況
INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');
INSERT … ON DUPLICATE KEY UPDATE

一、innodb_autoinc_lock_mode = 0 (“traditional” lock mod，傳統模式)。
這種方式就和mysql5.1.22以前一樣，為了向后兼容而保留了這種模式，如同前面介紹的一樣，這種方式的特點就是“表級鎖定”，并發性較差。
二、innodb_autoinc_lock_mode = 1 (“consecutive” lock mode，連續模式)。
這種方式是新版本中的默認方式，推薦使用，并發性相對較高，特點是“consecutive”，即保證同一條insert語句中新插入的auto_increment id都是連續的。
這種模式下：
“Simple inserts”：直接通過分析語句，獲得要插入的數量，然后一次性分配足夠的auto_increment id，只會將整個分配的過程鎖住。
“Bulk inserts”：因為不能確定插入的數量，因此使用和以前的模式相同的表級鎖定。
“Mixed-mode inserts”：直接分析語句，獲得最壞情況下需要插入的數量，然后一次性分配足夠的auto_increment id，只會將整個分配的過程鎖住。
需要注意的是，這種方式下，會分配過多的id，而導致“浪費”。
比如INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');會一次性的分配5個id，而不管用戶是否指定了部分id；
INSERT … ON DUPLICATE KEY UPDATE一次性分配，而不管將來插入過程中是否會因為duplicate key而僅僅執行update操作。
注意：當master mysql版本<5.1.22，slave mysql版本>=5.1.22時，slave需要將innodb_autoinc_lock_mode設置為0，因為默認的innodb_autoinc_lock_mode為1，對于INSERT … ON DUPLICATE KEY UPDATE和INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');的執行結果不同，現實環境一般會使用INSERT … ON DUPLICATE KEY UPDATE。
三、innodb_autoinc_lock_mode = 2 (“interleaved” lock mode，交叉模式)。
這種模式是來一個分配一個，而不會鎖表，只會鎖住分配id的過程，和innodb_autoinc_lock_mode = 1的區別在于，不會預分配多個，這種方式并發性最高。
但是在replication中當binlog_format為statement-based時（簡稱SBR statement-based replication）存在問題，因為是來一個分配一個，這樣當并發執行時，“Bulk inserts”在分配的時會同時向其他的INSERT分配，會出現主從不一致（從庫執行結果和主庫執行結果不一樣），因為binlog只會記錄開始的insert id。
測試SBR，執行begin;insert values(),();insert values(),();commit;會在binlog中每條insert values(),();前增加SET INSERT_ID=18/*!*/;。
但是row-based replication RBR時不會存在問題。
另外RBR的主要缺點是日志數量在包括語句中包含大量的update delete（update多條語句，delete多條語句）時，日志會比SBR大很多；假如實際語句中這樣語句不是很多的時候（現實中存在很多這樣的情況），推薦使用RBR配合innodb_autoinc_lock_mode，不過話說回來，現實生產中“Bulk inserts”本來就很少，因此innodb_autoinc_lock_mode = 1應該是夠用了。

最后說一句今天遇到這個問題，在LOCK_MODE為AUTO_INC，而且事物回滾非常的慢，不得已停止數據庫重新啟動
mysql> select * from innodb_locks;
+-------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| 2954466:518 | 2954466 | AUTO_INC | TABLE | `test`.`kkkm` | NULL | NULL | NULL | NULL | NULL |
| 2954465:518 | 2954465 | AUTO_INC | TABLE | `test`.`kkkm` | NULL | NULL | NULL | NULL | NULL |
+-------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+

本文名稱：mysql的auto_increment詳解
標題路徑：http://newbst.com/article26/jhcijg.html

成都網站建設公司_創新互聯，為您提供服務器托管、全網營銷推廣、標簽優化、定制網站、響應式網站、移動網站建設