しがないOLのはじめてのMySQL（オートナンバーの設定）

はじめに

こんにちは。クニ吉です。
昨日フレンチプレス式コーヒーメーカーが届きました。
早速ハワイで買ったコナコーヒーを淹れてみたところ、とても美味しくできてました。
電動ミルも買ったので、次はカウコーヒーを挽いて飲んでみます。

さて、今回も引き続きMySQLについて。
前回さらっと書いていたオートナンバーについて書いてみようと思います。

前提

本ブログにおけるMySQLのバージョンは「5.7.17」です。
過去ブログで既に記載している構文や結果表示については、さらっと流して書いていますので、過去ブログをご確認ください。

本ブログの構成

• オートナンバー改めAUTO INCREMENTについて
• AUTO INCREMENTの設定方法
• AUTO INCREMENTの採番について
• AUTO INCREMENTの採番をリセットする方法
• おわりに

オートナンバー改めAUTO INCREMENTについて

概要

オートナンバーというとどういうものかすぐにイメージがつきやすいので書きましたが、MySQLでは「AUTO INCREMENT(オートインクリメント)」という設定になります。
「自動増加」という意味で、AUTO INCREMENTを設定したカラムに自動で番号を割り当てていきます。 デフォルト設定では値は1から始まり、データを追加する度に+1した値がAUTO INCREMENTに設定したカラムにデータとして挿入されます。

実践

しがないOLのはじめてのMySQLの「データ投入」の項では、以下のようにidを指定していました。

mysql> insert into item(id, item_name, price)values(1, 'カエルのオルゴール', 3500);

AUTO INCREMENTを設定している場合には、自動で番号を割り振ってくれるので、このid部分の記述を省略することができます。

idを省略してデータを追加：

データ追加：
mysql> insert into item(item_name, price)values('コナコーヒー7oz', 2999);
Query OK, 1 row affected (0.00 sec)

テーブルの中身を確認：
mysql> select * from item;
+----+-----------------------+-------+
| id | item_name             | price |
+----+-----------------------+-------+
|  1 | コナコーヒー7oz           |  2999 |
+----+-----------------------+-------+
1 row in set (0.00 sec)

このようにidを指定することも可能です。
idを指定してデータを追加：

データ追加：
mysql> insert into item(id, item_name, price) values(3, 'カウコーヒー8oz', 4212);
Query OK, 1 row affected (0.01 sec)

テーブルの中身を確認：
mysql> select * from item;
+----+-----------------------+-------+
| id | item_name             | price |
+----+-----------------------+-------+
|  1 | コナコーヒー7oz           |  2999 |
|  3 | カウコーヒー8oz           |  4212 |
+----+-----------------------+-------+
2 rows in set (0.00 sec)

AUTO INCREMENTの設定方法

概要

では、AUTO INCREMENTの設定をしたいと思いますが、その前にAUTO INCREMENTを設定するための条件があります。

• AUTO INCREMENTはテーブル内に1つしか設定できない
• AUTO INCREMENTを設定するカラムはPRIMARY KEY または UNIQUE KEYである必要がある
• AUTO INCREMENTのを設定したカラムには正の値しか格納できない

PRIMARY KEY（主キー）とは

しがないOLのはじめてのMySQLの「MySQLとは」の項でも書かせていただきましたが、MySQLはリレーショナルデータベースを扱います。リレーショナルデータベースは以下の表のような構造でデータを管理します。

テーブルの各データ（組、レコード）を識別するための値として利用されるのが「PRIMARY KEY」です。
PRIMARY KEYは以下の特性をもっています。

• UNIQUE KEYである（値の重複を許さない）
• NOT NULLが設定される（NULL値の格納を許さない）
• INDEXを作成する（データの検索）
• PRIMARY KEYはテーブルに1つしか設定できない

PRIMARY KEYはデータの住所のようなもので、上記の例でいうと「id」をPRIMARY KEYとして設定します。 テーブル内に「name=花子」というデータが複数あったとしても、データベースにおいてはidが「1」の花子さんと、「10」の花子さんは別人である、ということになります。

PRIMARY KEYは、複数のカラムをひとつのPRIMARY KEYとして扱うこともできます。
これを「複合主キー」といいますが、これはまた後日どこかで・・。

UNIQUE KEYとは

UNIQUE KEYはPRIMARY KEYの項でも書きましたが、「重複を許さない」ことだけです。
以下はPRIMARY KEY と比較したUNIQUE KEYの特性です。

• UNIQUE KEYである（値の重複を許さない）
• NULL値を格納できる（NOT NULLを設定することもできる）
• INDEXを作成しない（INDEXを作成することもできる）
• テーブルに複数設定できる

実践

長くなったので、そろそろ実践。

テーブルを新規作成する場合：

CREATE TABLE文でAUTO INCREMENT と PRIMARY KEYの設定をします。
構文：CREATE TABLE {テーブル名} ({カラム名} {データ型} AUTO_INCREMENT, {カラム名} {データ型}, …, PRIMARY KEY({KEYを設定するカラム名}));

mysql> create table item(id int auto_increment, item_name varchar(20), price int, primary key(id));

すでに作成済みのテーブルに設定する場合：

1.先にKEYの設定をする必要がありますので、ALTER TABLE文でPRIMARY KEYの設定をします。
構文：ALTER TABLE {テーブル名} ADD PRIMARY KEY({KEYを設定するカラム名})

mysql> alter table item add primary key(id);

これで「id」がPRIMARY KEYになりました。

2.ALTER TABLE文でAUTO INCREMENTを設定します。
構文：ALTER TABLE {テーブル名} MODIFY {AUTO INCREMENTを設定するカラム名} {カラムのデータ型} AUTO_INCREMENT;

mysql> alter table item modify id int auto_increment;

これでidにAUTO INCREMENTを設定することができました。

AUTO INCREMENTの採番について

概要

AUTO INCREMENTを設定すると自動採番してくれることがわかりましたが、 では、「id = 3」が割り当てられた後や、最後に登録したデータを削除した場合、どう採番されていくのでしょうか。
答えを先に言ってしまうと、テーブルに登録したことのある最大値に+1した番号が割り当てられます。

• 「id = 3」が割り当てられた後、追加するデータのidは「4」が割り当てられます。
• 「id = 4」のデータを削除した後、追加するデータのidは「5」が割り当てられます。

実践

idを省略して再度データ追加：

データ追加：
mysql> insert into item(item_name, price) values('ブルーマウンテン200g', 3300);
Query OK, 1 row affected (0.00 sec)

テーブルの中身を確認：
mysql> select * from item;
+----+------------------------------+-------+
| id | item_name                    | price |
+----+------------------------------+-------+
|  1 | コナコーヒー7oz                  |  2999 |
|  3 | カウコーヒー8oz                  |  4212 |
|  4 | ブルーマウンテン200g             |  3300 |
+----+------------------------------+-------+
3 rows in set (0.00 sec)

データ追加前は「id = 3」が最大値になるので、+1した「4」がidに割り当てられました。

「id = 4」のデータを削除して、再度データ追加：

データ削除：
mysql> delete from item where id = 4;
Query OK, 1 row affected (0.00 sec)

テーブルの中身を確認：
mysql> select * from item;
+----+-----------------------+-------+
| id | item_name             | price |
+----+-----------------------+-------+
|  1 | コナコーヒー7oz         |  2999 |
|  3 | カウコーヒー8oz         |  4212 |
+----+-----------------------+-------+
2 rows in set (0.00 sec)


データ追加：
mysql> insert into item(item_name, price) values('ブルーマウンテン200g', 3300);
Query OK, 1 row affected (0.00 sec)

テーブルの中身を確認：
mysql> select * from item;
+----+------------------------------+-------+
| id | item_name                    | price |
+----+------------------------------+-------+
|  1 | コナコーヒー7oz                  |  2999 |
|  3 | カウコーヒー8oz                  |  4212 |
|  5 | ブルーマウンテン200g             |  3300 |
+----+------------------------------+-------+
3 rows in set (0.00 sec)

「id = 4」のデータを削除しましたが、新しく追加したデータのidは「5」が割り当てられました。 これはテーブルに存在しているデータの最大値ではなく、過去に登録されたことがあるデータの最大値を取っているためです。

テーブルの詳細を確認してみましょう。
※「SHOW TABLE STATUS \G」でテーブルの状態を確認できます。（\Gは縦表示にするオプションです。）

mysql> show table status \G
*************************** 1. row ***************************
           Name: item
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 3
 Avg_row_length: 5461
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 6
    Create_time: 2017-05-23 15:04:17
    Update_time: 2017-05-23 15:20:59
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

「Rows: 3」と「Auto increment: 6」という項目がポイントです。

• Rows：データ行数（現在テーブルが保持しているデータ数）を表示
• Auto increment：AUTO INCREMENTが次に割り当てるべき番号を表示

1件のデータを削除した場合、「Rows」は-1された値に変更になりますが、「Auto increment」は変更されません。
AUTO INCREMENTはデータが追加された時に、AUTO INCREMENTが保持している値に+1するため、結果的に過去に登録されていた番号の最大値に+1する数が割り当てられることになります。

AUTO INCREMENTの採番をリセットする方法

「AUTO INCREMENT」の採番をリセットしたい場合は、以下の方法で行います。

手順

全データを削除してリセットする：
前回のしがないOLのはじめてのMySQL（データの削除）でご紹介した「TRUNCATE文」を使います。

データを削除せず、途中から番号をリセットする：
仮に、誤って「id = 4」のデータを削除してしまって、再度idを「4」で再登録したい場合は、強制的に「AUTO INCREMENT」の値を書き換えます。

1.「id = 5」のデータを削除し、テーブルの中身を確認します。

データ削除：
mysql> delete from item where id = 5;
Query OK, 1 row affected (0.00 sec)

テーブルの中身を確認：
mysql> select * from item;
+----+-----------------------+-------+
| id | item_name             | price |
+----+-----------------------+-------+
|  1 | コナコーヒー7oz           |  2999 |
|  3 | カウコーヒー8oz           |  4212 |
+----+-----------------------+-------+
2 rows in set (0.00 sec)

この時、AUTO INCREMENTは「6」のままです。

2.再登録するデータのidを「4」にしたいので、ALTER TABLE文でテーブル構造を書き換えます。
構文：ALTER TABLE {テーブル名} AUTO_INCREMENT = {次回登録時に設定したい番号}

mysql> alter table item auto_increment = 4;

結果：

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

SHOW TABLE STATUSでテーブルの情報を確認すると、AUTO INCREMENTが「4」になっていることが確認できます。

mysql> show table status \G;
*************************** 1. row ***************************
           Name: item
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 4
    Create_time: 2017-05-23 16:46:36
    Update_time: 2017-05-23 16:37:55
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

3.再度「ブルーマウンテン200g」を追加して、テーブルの中身を確認します。

データ追加：
mysql> insert into item(item_name, price) values('ブルーマウンテン200g', 3300);
Query OK, 1 row affected (0.00 sec)

テーブルの中身を確認：
mysql> select * from item;
+----+------------------------------+-------+
| id | item_name                    | price |
+----+------------------------------+-------+
|  1 | コナコーヒー7oz                  |  2999 |
|  3 | カウコーヒー8oz                  |  4212 |
|  4 | ブルーマウンテン200g             |  3300 |
+----+------------------------------+-------+
3 rows in set (0.00 sec)

idが「4」で登録されました。
このように、AUTO INCREMENTの値を書き換えることで採番をリセットすることができます。

おわりに

AUTO INCREMENTについては以上になります。

ブログを書くにあたって、PRIMARY KEYなどについて整理できたのが収穫でした。
余談ですが、今回登録するデータにコーヒーを使ってみたのですが、Amazonで値段見てびっくりしました。
こっちで買おうとすると高いんですねぇ・・・。
せっかくミルを買ったので、いろいろな豆を試したいと思います(^^) ではでは。