この記事は 2019年11月30日 に書かれたものです
MySQLのAUTO_INCREMENTの値を確認したり設定します。
値を設定する
ALTER TABLE tbl AUTO_INCREMENT = 100;
値を確認する
SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'test' /* testデータベース */
AND TABLE_NAME = 'foo'; /* fooテーブル */
それぞれ詳しく見ていきます
現在の値を更新
ALTER TABLEで一発です。
ALTER TABLE tbl AUTO_INCREMENT = 100;
現在の値を確認
SHOW TABLE STATUS
単純にmax()関数で調べることができます。引数には調べたいAUTO_INCREMENTの設定がしてあるカラム名を渡します。お手軽ですがこの方法はレコードが物理的に削除される運用をしていると実際の値とずれてしまいます。
SELECT max(id) FROM foo;
そこでテーブルの情報を表示してくれるSHOW TABLE STATUSを利用します。LIKEにはテーブル名を文字列として渡します。以下の例だと3であることがわかります。
mysql> SHOW TABLE STATUS LIKE 'foo';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| foo | InnoDB | 10 | Dynamic | 2 | 8192 | 16384 | 0 | 16384 | 0 | 3 | 2019-11-30 22:35:09 | 2019-11-30 22:35:38 | NULL | utf8_general_ci | NULL | | |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
INFORMATION_SCHEMA
MySQLその物のデータを管理しているinformation_schemaの中にあるtablesテーブルをのぞいても確認が可能です。
SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'test' /* testデータベース */
AND TABLE_NAME = 'foo'; /* fooテーブル */
information_schema.tablesは他にも様々な情報が詰まってますので、必要に応じて参照したいですね。
mysql> desc information_schema.tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| TABLE_TYPE | varchar(64) | NO | | | |
| ENGINE | varchar(64) | YES | | NULL | |
| VERSION | bigint(21) unsigned | YES | | NULL | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_FREE | bigint(21) unsigned | YES | | NULL | |
| AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(32) | YES | | NULL | |
| CHECKSUM | bigint(21) unsigned | YES | | NULL | |
| CREATE_OPTIONS | varchar(255) | YES | | NULL | |
| TABLE_COMMENT | varchar(2048) | NO | | | |
+-----------------+---------------------+------+-----+---------+-------+
サーバ全体の初期値を変更する
/etc/my.cnfなどでAUTO_INCREMENTの初期値や増加量を設定できます。これはテーブルやデータベース単体ではなくサーバ全体に影響する物です。
| 項目 | デフォルト値 | 内容 |
|---|---|---|
| auto_increment_offset | 1 | 初期値 |
| auto_increment_increment | 1 | インクリメント時の増加量 |
$ cat /etc/my.cnf
[mysqld]
auto_increment_offset = 100 # 100からスタート
auto_increment_increment = 2 # 2ずつ増える
単一のDBで利用する機会はあまり無いと思いますが、例えばシステムを移行した際に旧システムと発番ルールを変更したい(一定量飛ばしたい)、または複数のマスターを稼働させるマルチマスターなどで活躍します。
参考ページ
- MySQL :: MySQL 5.6 リファレンスマニュアル :: 3.6.9 AUTO_INCREMENT の使用
- MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.5.37 SHOW TABLE STATUS 構文
- MySQL :: MySQL 5.6 リファレンスマニュアル :: 21.22 INFORMATION_SCHEMA TABLES テーブル
- MySQL :: MySQL 5.6 リファレンスマニュアル :: 17.1.4.2 レプリケーションマスターのオプションと変数
このブログを応援する
お寄せいただいたお気持ちは全額サーバ代や次の記事を執筆するための原資として活用させていただいております。この記事が参考になった場合などぜひご検討ください。
