MySQL 表的自增欄位 預判功能innodb_autoinc_lock_mode

shytodear發表於2016-09-19
MySql
在 5.1.22之後，innodb對於可以預判行數的insert語句，innodb使用一個輕量級的互斥量，而不是表級鎖。
但有可能會造成自增欄位的值不連續。


注：如果使用新的自增互斥方式，對於replication應該避免使用INSERT ... ON DUPLICATE KEY UPDATE語句。

設定新自增互斥方式：透過配置選項：innodb_autoinc_lock_mode：調整鎖策略：

innodb_autoinc_lock_mode = 0 (“traditional” lock mode：全部使用表鎖)
innodb_autoinc_lock_mode = 1 (預設)(“consecutive” lock mode：可預判行數時使用新方式，不可時使用表鎖) 
innodb_autoinc_lock_mode = 2 (“interleaved” lock mode：全部使用新方式，不安全，不適合replication)


##建立自增欄位

方法1、建立:
mysql> create table c(id int auto_increment,name varchar(20),primary key(id));
Query OK, 0 rows affected (0.52 sec)

mysql> desc c;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)

方法2、修改:
mysql> create table cc (id int,name varchar(20));
Query OK, 0 rows affected (0.42 sec)

mysql> alter table cc change id id int primary key auto_increment;
Query OK, 0 rows affected (1.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc cc;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.11 sec)

mysql> insert into cc(id,name) values(1,'a'),(NULL,'b'),(NULL,'c'),(5,'d');

mysql> select * from cc;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  5 | d    |
+----+------+
4 rows in set (0.00 sec)

注:只有int型別且為primary key 才可以使用auto_increment.


##對存在記錄的表的列修改為自增列
mysql> create table ccc (id int,name varchar(20));
Query OK, 0 rows affected (0.27 sec)

mysql> insert into ccc(id,name) values(1,'a'),(NULL,'b'),(NULL,'c'),(5,'d');
Query OK, 4 rows affected (0.53 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from ccc;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
| NULL | b    |
| NULL | c    |
|    5 | d    |
+------+------+
4 rows in set (0.00 sec)

mysql> alter table ccc change id id int primary key auto_increment;
Query OK, 4 rows affected (1.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc ccc;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> insert into ccc(id,name) values(1,'a'),(NULL,'b'),(NULL,'c'),(5,'d');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into ccc(id,name) values(6,'aa'),(NULL,'ab'),(NULL,'ac'),(10,'ad')
;
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> select * from ccc;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  5 | d    |
|  6 | aa   |
|  7 | ab   |
|  8 | ac   |
| 10 | ad   |
+----+------+
8 rows in set (0.00 sec)

mysql>



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

相關文章