MySQL auto_increment_increment，auto_increment_offset 用法，autoincrement

MySQL auto_increment_increment，auto_increment_offset 用法，autoincrement

    MySQL中對於表上ID自增列可以在建立表的時候來指定列上的auto_increment屬性；等同於SQL server中的identity屬性；Oracle則是通過Sequence方式來實現。在MySQL中，系統變數auto_increment_increment，auto_increment_offset 影響自增列的值及其變化規則。本文主要描述這兩個系統變數的相關用法。

 

1、auto_increment_increment與auto_increment_offset作用

auto_increment_increment控制列中的值的增量值，也就是步長。auto_increment_offset確定AUTO_INCREMENT列值的起點，也就是初始值。變數範圍：可以在全域以及session層級設定這2個變數--當前系統內容root@localhost[(none)]> show variables like 'version';+---------------+------------+| Variable_name | Value      |+---------------+------------+| version       | 5.5.39-log |+---------------+------------+root@localhost[mysql]> create database tempdb;root@localhost[mysql]> use tempdb;--查看變數auto_increment_increment與auto_increment_offsetroot@localhost[tempdb]> show variables like '%auto_incre%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     || auto_increment_offset    | 1     |+--------------------------+-------+

2、示範auto_increment_increment與auto_increment_offset

--建立示範表，使用auto_increment子句root@localhost[tempdb]> create table t1(id int not null auto_increment primary key, col varchar(20));--插入記錄root@localhost[tempdb]> insert into t1(col) values('robin'),('fred'),('jack'),('james');--下面可以看到id列起始值為1，增量為1root@localhost[tempdb]> select * from t1;+----+-------+| id | col   |+----+-------+|  1 | robin ||  2 | fred  ||  3 | jack  ||  4 | james |+----+-------+--設定步長為5root@localhost[tempdb]> set session auto_increment_increment=5;root@localhost[tempdb]> show variables like '%auto_incre%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 5     || auto_increment_offset    | 1     |+--------------------------+-------+--清空表t1root@localhost[tempdb]> truncate table t1;--再次插入記錄root@localhost[tempdb]> insert into t1(col) values('robin'),('fred'),('jack'),('james');--如下查詢可以看到步長以5位基數發生變化root@localhost[tempdb]> select * from t1;+----+-------+| id | col   |+----+-------+|  1 | robin ||  6 | fred  || 11 | jack  || 16 | james |+----+-------+--設定初始值為5root@localhost[tempdb]> set session auto_increment_offset=5;root@localhost[tempdb]> show variables like '%auto_incre%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 5     || auto_increment_offset    | 5     |+--------------------------+-------+root@localhost[tempdb]> truncate table t1;root@localhost[tempdb]> insert into t1(col) values('robin'),('fred'),('jack'),('james');--下面是新的結果root@localhost[tempdb]> select * from t1;+----+-------+| id | col   |+----+-------+|  5 | robin || 10 | fred  || 15 | jack  || 20 | james |+----+-------+

3、auto_increment_increment與auto_increment_offset取值範圍

--將變數auto_increment_increment設定為0root@localhost[tempdb]> set session auto_increment_increment=0;--實際值變成了1root@localhost[tempdb]> show variables like '%auto_increment%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     || auto_increment_offset    | 5     |+--------------------------+-------+--同樣將auto_increment_offset設定為0root@localhost[tempdb]> set session auto_increment_offset=0;--實際值也變成了1root@localhost[tempdb]> show variables like '%auto_increment%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     || auto_increment_offset    | 1     |+--------------------------+-------+--下面嘗試將2個變數設定為大於65535root@localhost[tempdb]> set session auto_increment_increment=65537;root@localhost[tempdb]> set session auto_increment_offset=65537;--其實際的值都變成了65535root@localhost[tempdb]> show variables like '%auto_increment%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 65535 || auto_increment_offset    | 65535 |+--------------------------+-------+--嘗試為2個變數設定為負值root@localhost[tempdb]> set session auto_increment_offset=-2;root@localhost[tempdb]> set session auto_increment_increment=-5;--下面的查詢可以看出全部恢複到預設值1root@localhost[tempdb]> show variables like '%auto_increment%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     || auto_increment_offset    | 1     |+--------------------------+-------+由上可以看出2個變數只能設定為1至65535之間的整數值。所有非正整數全部會置為預設值1，大於65535的值會被自動置為65535。

4、全域與session層級的設定

--查看全域範圍這2個變數的值root@localhost[tempdb]> show global variables like '%auto_increment%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     || auto_increment_offset    | 1     |+--------------------------+-------+--下面分別設定session基本的值root@localhost[tempdb]> set session auto_increment_increment=5;root@localhost[tempdb]> set session auto_increment_offset=10;--查看session層級的值root@localhost[tempdb]> show session variables like '%auto_increment%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 5     || auto_increment_offset    | 10    |+--------------------------+-------+--查看全域層級的值root@localhost[tempdb]> show global variables like '%auto_increment%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     || auto_increment_offset    | 1     |+--------------------------+-------+--設定全域層級的值root@localhost[tempdb]> set global auto_increment_increment=2;root@localhost[tempdb]> set global auto_increment_offset=3;root@localhost[tempdb]> show global variables like '%auto_increment%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 2     || auto_increment_offset    | 3     |+--------------------------+-------+

5、已有auto_increment列值任一變數變化的情形

root@localhost[tempdb]> truncate table t1;root@localhost[tempdb]> show variables like '%auto_increment%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     || auto_increment_offset    | 1     |+--------------------------+-------+root@localhost[tempdb]> insert into t1(col) values('robin'),('fred'),('jack');          root@localhost[tempdb]> select * from t1;+----+-------+| id | col   |+----+-------+|  1 | robin ||  2 | fred  ||  3 | jack  |+----+-------+root@localhost[tempdb]> set session auto_increment_increment=5;root@localhost[tempdb]> show variables like '%auto_increment%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 5     || auto_increment_offset    | 1     |+--------------------------+-------+--Author: Leshami--Blog  : http://blog.csdn.net/leshamiroot@localhost[tempdb]> insert into t1(col) values('david'),('tim'),('jerry');root@localhost[tempdb]> select * from t1;+----+-------+| id | col   |+----+-------+|  1 | robin ||  2 | fred  ||  3 | jack  ||  6 | david || 11 | tim   || 16 | jerry |+----+-------+New_value = auto_increment_offset+ N * auto_increment_incrementNew_value1 = 1 + 1 * 5 = 6New_value2 = 1 + 2 * 5 = 11--下面是修改auto_increment_offset後的結果root@localhost[tempdb]> set session auto_increment_offset=2;root@localhost[tempdb]> insert into t1(col) values('lewis'),('ian');root@localhost[tempdb]> select * from t1;+----+-------+| id | col   |+----+-------+|  1 | robin ||  2 | fred  ||  3 | jack  ||  6 | david || 11 | tim   || 16 | jerry || 22 | lewis || 27 | ian   |+----+-------+這個id為22，應該是這樣推算來的：max(id)+(new_offset-old_offset)+increment也就是說變化auto_increment_offset後的第一個值為max(id)+(new_offset-old_offset)+increment之後再按步長遞增。

mysql的auto_increment問題

ALTER TABLE <表名> AUTO_INCREMENT=<你期望的起始id值>
 
mysql對於auto_increment問題

主鍵已經設定為自增流水了，那麼往裡插入資料的時候，主鍵列是不需要維護的，比如你的表就兩列，id和name，那麼插入語句就是：
insert into A(name)
values('XXXX');

如果想主鍵是你需要的值，那麼就都寫全，比如就要主鍵是10：

insert into A(id, name)
values(10, 'XXXX');

載入本地檔案的時候，如果你檔案裡主鍵列是有值的，那麼將按照檔案中的主索引值插入。
如果沒有，就自增流水了。
希望可以協助你