tweeeetyのぶろぐ的めも

アウトプットが少なかったダメな自分をアウトプット<br>\(^o^)/

【mysql】auto_incrementの値を確認、変更。からの、0(ゼロ)入れたり歯抜け直してみたり

はじめに

ありきたりなメモなのですが久しぶりの息抜きメモ。
mysqlのauto_incrementについて、
下記みたいなことがちょくちょくありますがその度に忘れてるのでメモ。

  • auto_incrementの値知るのどうやんだっけなー
  • deleteしちゃったからauto_incrementの値変えなきゃなー
  • deleteしすぎて歯抜けになりすぎたから連番揃えてauto_incrementの値も変えてキレイにしたいなー

あじぇんだ

  1. auto_incrementの設定を確認する
  2. auto_increment値を確認する
  3. auto_increment値を更新する
  4. auto_increment使ってるテーブルの歯抜けの連番を整理する
  5. 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属性のカラムは、
そのカラムに0かnullをインサートしようとした時に自動採番した数値をカラムに代入するから

入れるには?

入れるにはset globalsql_modeNO_AUTO_VALUE_ON_ZEROを指定します。
だんだん長くなってきたので、これはこちらを見てみてください
AUTO_INCREMENTに0はダメ?

まとめ

ということで、簡単ではありますがauto_incrementについて書いてみました。
息抜き程度なので簡単に書きましたが結局思ったより長くなるという。。。