MySQLのauto_incrementについてメモ

logo-mysql
MySQL には Oracle/PostgreSQL の sequence にあたるものがないので、自動採番するときはカラムを auto_increment にする。

http://dev.mysql.com/doc/refman/5.6/en/example-auto-increment.html

auto_increment の指定

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

初期値の指定

デフォルトでは 1 から採番される。

ALTER TABLEAUTO_INCREMENT の初期値を指定する

ALTER TABLE tbl_name AUTO_INCREMENT = 100;

AUTO_INCREMENT カラムの最大値より小さい値を指定した時は無視されるので注意が必要。

初期値は CREATE TABLE 時に指定することもできる。

CREATE TABLE bar (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) AUTO_INCREMENT=100;

初期値、増加幅を指定

Oracle/PostgreSQL は create sequence test_seq start with 5 increment by 10;とすると、初期値が 5 で 10 ずつ増えるシーケンスを作成できる。
MySQL では初期値やステップはシステム変数でこれらが管理されていて、 start with に対応するのが auto_increment_increment, increment by に対応するのが auto_increment_offset となる

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.00 sec)

mysql> set @@auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec)

mysql> set @@auto_increment_offset=5;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 5     |
+--------------------------+-------+
2 rows in set (0.01 sec)

mysql> create table test_incr(col int not null auto_increment primary key);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_incr values(null), (null), (null), (null);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select col from test_incr;
+-----+
| col |
+-----+
|   5 |
|  15 |
|  25 |
|  35 |
+-----+
4 rows in set (0.00 sec)

auto_increment_offset > auto_increment_increment となるように指定すると、auto_increment_offset は無視されるので注意が必要。

auto_increment の次の値を確認

INSERT する前に採番される値を確認したいことがある。

information_schema テーブルから取得できる。

mysql> show table status from `foo` like 'test_incr' \G
*************************** 1. row ***************************
           Name: test_incr
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 4
 Avg_row_length: 4096
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 9437184
 Auto_increment: 45
    Create_time: 2014-02-20 23:00:24
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> SELECT auto_increment
    ->   FROM information_schema.tables
    ->  WHERE table_name = 'test_incr';
+----------------+
| auto_increment |
+----------------+
|             45 |
+----------------+
1 row in set (0.00 sec)

テーブルを truncate すると auto_increment は初期化される

auto_increment をリセットしたくない時は truncate のかわりに delete を使うか、truncate 後に alter tableauto_increment の初期値を再設定しなければいけない。

Any AUTO_INCREMENT value is reset to its start value. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.
http://dev.mysql.com/doc/refman/5.6/en/truncate-table.html

Advertisements
Tagged with: ,
Posted in database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
%d bloggers like this: