質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.44%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Q&A

解決済

4回答

18111閲覧

【MySQL】auto_incrementの罠

退会済みユーザー

退会済みユーザー

総合スコア0

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

2グッド

4クリップ

投稿2016/06/02 05:49

以下のテーブルを作り

SQL

1CREATE TABLE IF NOT EXISTS users( 2 user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 3 user_name VARCHAR(30) NOT NULL 4);

以下の情報を追加しました。もちろん、auto_incrementをつけてるので問題なく1~4のidがuser_idカラムに追加されましたが、

SQL

1INSERT INTO users(user_name) VALUES('name01'); 2INSERT INTO users(user_name) VALUES('name02'); 3INSERT INTO users(user_name) VALUES('name03'); 4INSERT INTO users(user_name) VALUES('name04');

イメージ説明
例えば、name04を消し、

SQL

1DELETE FROM users WHERE user_id=4;

新たに情報を追加したときに、

SQL

1INSERT INTO users(user_name) VALUES('name05');

結果がuser_idに4追加されたと思いきや、5になってしまってます。
イメージ説明

○質問
上記のようにuser_idの情報が5にとぶのはなぜなのか、また解決策があれば、ご教授お願いします。
※idが5にとぶのではなく、最初みたいに1,2,3,4みたいに割り当てて欲しいです。

zico_teratail, DrqYuto👍を押しています

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答4

0

ベストアンサー

解決策は、「AUTO_INCREMENTを使うのをやめるか、飛び番を気にしない」ことです。

データベースの主キーは、単にレコードを識別するためのものであって、「それぞれの行で一意である」以上に意味をもたせるのは良くないことです。

そして、MySQLの場合、データを削除しなくても値が飛ぶことがあります

「IDが連番でないと気持ち悪い」という気持ちもわからないではないですが、「SQLアンチパターン」の第21章に挙げられているように、それを守るために発生する余計な手間・コストが大きく、実用的にはやるべきでない行為です(何かしらの処理が、「キーが連番であること」に依存しているなら、そちらを修正する必要があります)。

投稿2016/06/02 06:37

maisumakun

総合スコア145360

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2016/06/02 14:11

そうなると、おそらくidを直接指定する形になりますね。ありがとうございます。
guest

0

上記のようにuser_idの情報が5にとぶのはなぜなのか

それがauto_incrementの仕様だからです。
auto_incrementは現時点のデータとは別に、次に追加されるIDを記憶しています。
参考

なぜそういう実装になっているかの歴史的経緯は知りませんが、
例えばuser_idの場合、削除して次に挿入したら同じIDになると問題が出る可能性があります。
例えば別のテーブルで、user_idと紐づける形で住所を保存していたりした場合、他人のデータと結びつくことになってしまう可能性があります。

また解決策があれば、ご教授お願いします。

Auto_incrementの値は指定することが可能
ですが、これを日常的に使うくらいなら、
auto_incrementを指定せずに、
テーブルの中で一番大きなIDの値を取得して、それに1を足して指定するべきIDを生成してデータを挿入する
という形にするのが一般的な方法かと思います。

投稿2016/06/02 05:59

tanat

総合スコア18716

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

tanat

2016/06/02 09:32

とは言え、上記の様な問題が発生する可能性があるので、 お勧めの解決方法としては maisumakunさんの仰る通り > 解決策は、「AUTO_INCREMENTを使うのをやめるか、飛び番を気にしない」ことです。 です。
退会済みユーザー

退会済みユーザー

2016/06/02 14:05

わかりました。ありがとうございます。
guest

0

手前味噌になりますが、以前このような記事を書きました
DBの自動連番がロールバックしても戻らない理由

投稿2016/06/02 06:01

yuba

総合スコア5568

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2016/06/02 14:10

ありがとうございます。参考にさせていただきます。
guest

0

参考情報:

投稿2016/06/02 12:19

katoy

総合スコア22324

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2016/06/02 14:15

ストレージエンジンのデフォルト確かmyisamでしたよね? ストレージエンジン切り替えは行ってなかったです。 innoDBならありだと思ってたんですが、やはり同じでしたか… ご回答ありがとうございます。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.44%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問