はじめに
ありきたりなメモなのですが久しぶりの息抜きメモ。
mysqlのauto_incrementについて、
下記みたいなことがちょくちょくありますがその度に忘れてるのでメモ。
- auto_incrementの値知るのどうやんだっけなー
- deleteしちゃったからauto_incrementの値変えなきゃなー
- deleteしすぎて歯抜けになりすぎたから連番揃えてauto_incrementの値も変えてキレイにしたいなー
あじぇんだ
- auto_incrementの設定を確認する
- auto_increment値を確認する
- auto_increment値を更新する
- auto_increment使ってるテーブルの歯抜けの連番を整理する
- auto_increment属性のカラムに0(ZERO)入れるとどうなるか
4と5はauto_incrementそのものというよりは
auto_incrementテーブル使っててのぷちてぃっぷす的なヤツですね
その前に
アジェンダやるまえにそれを確認するためのテーブルを作ります 今回はこんな感じ
※ データベース作成 CREATE DATABASE IF NOT EXISTS test_db_201504; ※ テーブル作成 CREATE TABLE autoincrement_test ( id INT(2) NOT NULL AUTO_INCREMENT, name VARCHAR(64), PRIMARY KEY (id) ); ※ データのinsert insert into autoincrement_test (name) VALUES ('hoge'), ('piyo'), ('fuga'); ※ でーた取得してみる select * from autoincrement_test; +----+------+ | id | name | +----+------+ | 1 | hoge | | 2 | piyo | | 3 | fuga | +----+------+ 3 rows in set (0.00 sec)
あとあと考えるとautoincrement_testってテーブル名は
わかりずらいから違うのにしときゃよかった。。。と思いましたw
1. auto_incrementの設定を確認する
auto_incrementの値を見る前に設定を見てみます。
普段はそんなに変えることはないと思いますが、
レプリ設定やらでもしかしたら使うことはあるかもしれないですね。
mysql> show variables like '%auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set (0.00 sec)
- auto_increment_increment : 更新する値（何づつ足すか的な）
- auto_increment_offset : 一番最初にinsertしたときに採番される値
ということでこれは見るだけですね
2. auto_increment値を確認する
auto_incrementの値の確認としては主に下記の方法があります
show table statusから見るinformation_schema.tablesから見る
show table statusから見る
これも普通にやってみます。
上から11番目のAuto_incrementがauto_incrementの値(次に採番される値)です
mysql> show table status like 'autoincrement_test%'\G *************************** 1. row *************************** Name: autoincrement_test Engine: InnoDB Version: 10 Row_format: Compact Rows: 3 Avg_row_length: 5461 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 9437184 Auto_increment: 4 Create_time: 2015-04-28 18:43:54 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
information_schema.tablesから見る
show table statusは情報がいろいろ見れるのでいいですが、
すっきり見たい場合はinformation_schema.tablesからカラムをしぼって見るのもおすすめです
mysql> SELECT auto_increment FROM information_schema.tables WHERE table_name = 'autoincrement_test'; +----------------+ | auto_increment | +----------------+ | 4 | +----------------+ 1 rows in set (0.01 sec)
information_schemaについてもうちょい見てみる
脱線しそうですが、information_schema.tablesをカラムをしぼらないで出してみます。
見てわかる通りTABLE_SCHEMA(データベース名)なんかも入っているので
特定のデータベースのテーブル一覧なんかもここから取れますね。
SELECT * FROM information_schema.tables WHERE table_name = 'autoincrement_test'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test_db_201504_ TABLE_NAME: autoincrement_test TABLE_TYPE: BASE TABLE ENGINE: InnoDB VERSION: 10 ROW_FORMAT: Compact TABLE_ROWS: 3 AVG_ROW_LENGTH: 5461 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 9437184 AUTO_INCREMENT: 4 CREATE_TIME: 2015-04-28 18:43:54 UPDATE_TIME: NULL CHECK_TIME: NULL TABLE_COLLATION: utf8_general_ci CHECKSUM: NULL CREATE_OPTIONS: TABLE_COMMENT: 1 row in set (0.00 sec)
参考
information_schemaについてはこの記事がわかりやすかったです
→MySQLユーザーとinformation_schemaデータベースについて
3. auto_increment値を更新する
これもやってみるだけですが、良くあるのは
insertしたデータが間違っていたのでdeleteしたからauto_incrementの値も戻したい
とかだと思うのでその流れで確認。
まずはinsertしてから無駄にdeleteしてauto_increment値確認
mysql> insert into autoincrement_test (name) VALUES ('muda'); mysql> select * from autoincrement_test; +----+------+ | id | name | +----+------+ | 1 | hoge | | 2 | piyo | | 3 | fuga | | 4 | muda | +----+------+ 4 rows in set (0.00 sec) mysql> delete from autoincrement_test where id=4; mysql> select * from autoincrement_test; +----+------+ | id | name | +----+------+ | 1 | hoge | | 2 | piyo | | 3 | fuga | +----+------+ 3 rows in set (0.00 sec) mysql＞ SELECT auto_increment FROM information_schema.tables WHERE table_name = 'autoincrement_test'; +----------------+ | auto_increment | +----------------+ | 5 | +----------------+ 1 rows in set (0.00 sec)
レコードは3件で、次にinsertするときはauto_increment値が4になっていてほしいのに
5になっている状態ができました
auto_increment値を更新する
auto_incrementの値(次に採番される値)を4に変更します
mysql> ALTER TABLE autoincrement_test AUTO_INCREMENT=4; Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT auto_increment FROM information_schema.tables WHERE table_name = 'autoincrement_test'; +----------------+ | auto_increment | +----------------+ | 4 | +----------------+ 1 rows in set (0.00 sec)
無事4になりました。試しませんがinsertすると4から採番されます
4. auto_increment使ってるテーブルの歯抜けの連番を整理する
基本的なことは上記まで事足りますが、
今回記事書きたかった本当のメモはここらへんですね。
3. auto_increment値を更新するのように最後の行だけの場合はいいですが
deleteしすぎて歯抜けになったときはauto_increment値を更新するだけじゃなく
歯抜けになったほうも連番を詰め直して（採番しなおして）、
それからauto_increment値を更新してすっきりしたいですよね。
そんなとき偏。
歯抜けを作る
※ 適当にinsert mysql > insert into autoincrement_test (name) VALUES ('a'), ('b'), ('c'), ('d'), ('e'), ('f'), ('g'), ('h'); ※ 適当にdelete mysql> delete from autoincrement_test where id >= 3 AND id <= 5; mysql> delete from autoincrement_test where id >= 7 AND id <= 9; mysql> delete from autoincrement_test where id >= 11; mysql> select * from autoincrement_test; +----+------+ | id | name | +----+------+ | 1 | hoge | | 2 | piyo | | 6 | c | | 10 | g | +----+------+ 4 rows in set (0.00 sec) mysql> SELECT auto_increment FROM information_schema.tables WHERE table_name = 'autoincrement_test'; +----------------+ | auto_increment | +----------------+ | 12 | +----------------+ 1 rows in set (0.00 sec)
autoincrement_testテーブるのデータは歯抜けな上にauto_incrementの値は12になってます。
歯抜けをキレイに詰め直してauto_incrementも更新
歯抜けの解消には変数を利用してidカラムをUPDATEし直します。
歯抜けの解消
mysql> SET @i := 0; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE `autoincrement_test` SET id = (@i := @i +1) ; mysql> select * from autoincrement_test; +----+------+ | id | name | +----+------+ | 1 | hoge | | 2 | piyo | | 3 | c | | 4 | g | +----+------+ 4 rows in set (0.00 sec)
auto_increment値の更新
これで後は上記でもやった通りauto_incrementの値を更新するだけです。
次にinsertしたときは5が採番されて欲しいので5でalterします。
mysql> ALTER TABLE autoincrement_test AUTO_INCREMENT=5;
5. auto_increment属性のカラムに0(ZERO)入れるとどうなるか
これは単純にやってみるとどうなるか程度のメモです、
最初に結論を言ってしまうとauto_increment属性のカラムには0(ゼロ)は使えません。
※ 正確には設定を変えることで使える
では使えないながら0(ゼロ)でinsertするとどうなるでしょうか
まずは確認
ここまでやった状態のままですね。
レコードは4件で、auto_incrementの値は5です。
mysql> select * from autoincrement_test; +----+------+ | id | name | +----+------+ | 1 | hoge | | 2 | piyo | | 3 | c | | 4 | g | +----+------+ 4 rows in set (0.00 sec) mysql> SELECT auto_increment FROM information_schema.tables WHERE table_name = 'autoincrement_test'; +----------------+ | auto_increment | +----------------+ | 5 | +----------------+ 1 rows in set (0.00 sec)
auto_increment属性のカラムに値が0(ゼロ)のレコードをinsertしてみる
結果を言うと使えないといいつつもinsertは成功します。
※ insertしてみると成功する mysql> insert into autoincrement_test (id, name) VALUES (0, 'zero'); Query OK, 1 row affected (0.00 sec) ※ 確認する mysql> select * from autoincrement_test; +----+------+ | id | name | +----+------+ | 1 | hoge | | 2 | piyo | | 3 | c | | 4 | g | | 5 | zero | +----+------+ 5 rows in set (0.00 sec) mysql> SELECT auto_increment FROM information_schema.tables WHERE table_name = 'autoincrement_test'; +----------------+ | auto_increment | +----------------+ | 6 | +----------------+ 1 rows in set (0.00 sec)
確認結果を見るとわかる通り、
insert into autoincrement_test (name) VALUES ('zero');
をしたかのような結果になってます。
なんでそうなるか
なんでこうなるかですが、説明はまるっと他の方のを引用するとこんな感じですね。
auto_increment属性のカラムは、
そのカラムに０かnullをインサートしようとした時に自動採番した数値をカラムに代入するから
入れるには？
入れるにはset globalでsql_modeにNO_AUTO_VALUE_ON_ZEROを指定します。
だんだん長くなってきたので、これはこちらを見てみてください
→AUTO_INCREMENTに0はダメ？
まとめ
ということで、簡単ではありますがauto_incrementについて書いてみました。
息抜き程度なので簡単に書きましたが結局思ったより長くなるという。。。
