MySQL シーケンスの使用(AUTO_INCREMENT)

MySQL において、シーケンスは自動増分で数値シーケンスを生成するオブジェクトであり、整数の集まりです1、2、3、...。1つのデータテーブルには自動増分の主キーを1つしか持てないためです。

MySQL 自体には組み込みのシーケンス型はありませんが、AUTO_INCREMENT 属性を使用してシーケンスの動作をシミュレートできます。通常AUTO_INCREMENTこの属性はテーブル内の特定の列の自動増分を指定するために使用されます。

AUTO_INCREMENT を使用してテーブルを作成する例:

例

CREATE TABLE example_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

上記の例では、id 列は INT AUTO_INCREMENT として定義されています。これは、データを1行挿入するたびに id 列の値が自動的に増加することを意味します。主キー制約により、id 列の一意性が保証されます。

データを1行挿入するとき、id 列の値を指定しなくても、データベースが自動的に一意の自動増分値を割り当てます:

INSERT INTO example_table (name) VALUES ('John');

LAST_INSERT_ID() 関数を使用して、挿入したばかりの行の自動増分値を取得することもできます:

SELECT LAST_INSERT_ID();

テーブルの現在の自動増分値を取得する必要がある場合は、次のステートメントを使用できます:

SHOW TABLE STATUS LIKE 'example_table';

結果セットでは、Auto_increment 列の値が現在のテーブルの自動増分値になります。

AUTO_INCREMENT 属性を使用する列は整数型(通常は INT または BIGINT)のみであることに注意してください。さらに、テーブル内の行を削除しても、その自動増分値は再利用されず、増加し続けます。自動増分値を手動で設定したい場合は SET ステートメントを使用できますが、一意性の競合を引き起こす可能性があるため、一般的な方法ではありません。


AUTO_INCREMENT を使用する

MySQL でシーケンスを使用する最も簡単な方法は、MySQL AUTO_INCREMENT を使用してシーケンスを定義することです。

例

以下の例では、データテーブル insect を作成しています。insect テーブルの id は値を指定しなくても自動的に増加します。

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, # type of insect
    -> date DATE NOT NULL, # date collected
    -> origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO insect (id,name,date,origin) VALUES
    -> (NULL,'housefly','2001-09-10','kitchen'),
    -> (NULL,'millipede','2001-09-10','driveway'),
    -> (NULL,'grasshopper','2001-09-10','front yard');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM insect ORDER BY id;
+----+-------------+------------+------------+
| id | name        | date       | origin     |
+----+-------------+------------+------------+
|  1 | housefly    | 2001-09-10 | kitchen    |
|  2 | millipede   | 2001-09-10 | driveway   |
|  3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)

AUTO_INCREMENT 値を取得する

MySQL クライアントでは、SQL の LAST_INSERT_ID() 関数を使用して、最後に挿入されたテーブルの自動増分列の値を取得できます。

PHP または PERL スクリプトにも、最後に挿入されたテーブルの自動増分列の値を取得するための対応する関数が用意されています。

PERL の例

mysql_insertid 属性を使用して AUTO_INCREMENT の値を取得します。例は次のとおりです:

$dbh->do ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh->{mysql_insertid};

PHP の例

PHP は mysql_insert_id() 関数を使用して、実行された INSERT SQL ステートメント内の AUTO_INCREMENT 列の値を取得します。

mysql_query ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_id);

シーケンスのリセット

データテーブルから複数のレコードを削除し、残りのデータの AUTO_INCREMENT 列を並べ替えたい場合は、自動増分列を削除してから再度追加することで実現できます。ただし、この操作は非常に注意が必要です。削除と同時に新しいレコードが追加されると、データの混乱が発生する可能性があります。操作は次のとおりです:

mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
    -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    -> ADD PRIMARY KEY (id);

シーケンスの開始値の設定

通常、シーケンスの開始値は 1 ですが、開始値を 100 に指定する必要がある場合は、次のステートメントで実現できます:

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, 
    -> date DATE NOT NULL,
    -> origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;

または、テーブルの作成が成功した後、次のステートメントで実現することもできます:

mysql> ALTER TABLE t AUTO_INCREMENT = 100;
その他の拡張