[MySQL] AUTO_INCREMENTの値を設定/確認する

  • このエントリーをはてなブックマークに追加
  • LINEで送る

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テーブル */

それぞれ詳しく見ていきます

- Sponsored Link -

現在の値を設定

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_offset1初期値
auto_increment_increment1インクリメント時の増加量
$ cat /etc/my.cnf
[mysqld]
auto_increment_offset = 100    # 100からスタート
auto_increment_increment = 2   # 2ずつ増える

単一のDBで利用する機会はあまり無いと思いますが、例えばシステムを移行した際に旧システムと発番ルールを変更したい(一定量飛ばしたい)、または複数のマスターを稼働させるマルチマスターなどで活躍します。

参考ページ

- Sponsored Link -

ご質問やリクエストなどお気軽に。メールアドレスの入力は任意です。書き込みが反映されるまで時間がかかります。

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください