mysqlの自動増加IDが使い果たされた場合の解決方法

MySQL で自動増加する ID が不足しました。どうすればよいですか?

プログラマとして、あなたは就職面接中にこのような問題に遭遇したことがあるでしょうか。

Zhang Gong は Java プログラマーで、最近インターネット企業に面接に行ったところ、面接官からこのような質問を受けました。

インタビュアー: 「mysql を使用したことがありますか? データ テーブルの主キー ID として自動インクリメント主キーまたは UUID を使用しますか?」

Zhang Gong: 「自動インクリメントの主キーを使用していますね」

インタビュアー: 「なぜ自動インクリメントの主キーなのでしょうか?」

Zhang Gong: 「自動インクリメントの主キーを使用しているため、データは物理構造に順番に保存され、パフォーマンスは良好です。」

インタビュアー: 「自動インクリメントされた主キーが最大値に達しました。使い果たした場合はどうすればよいですか?」戻って通知を待ってください。」

今日は、自動インクリメントされた主キーが使い果たされた場合の対処方法について説明します。

mysql では、int 整数型の範囲は次のとおりです。int の値の範囲は、-2^31——2^31-1、つまり、-2147483648—2147483647

# となります。 ##図に示すように:

たとえば、符号なし整数は 0 ～ 4294967295 の範囲 (約 43 億) を格納できます。自動インクリメントされる ID が最大値に達した場合、挿入を続けるとどのような例外が発生しますか?

練習してみましょう。

まず、自動インクリメント ID のみを含むテーブル tb_user を作成します。

create table  tb_user(id int unsigned auto_increment primary key) ;

次に、このテーブルにデータを挿入します。

insert into tb_user values(null);

show コマンド show を使用します。 create table tb_user; テーブルの状況を確認します:

CREATE TABLE `tb_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

注意して見ると、AUTO_INCREMENT が 2 になっていることがわかりますが、これは最大値の 4294967295 には程遠いです。大量のレコードを挿入する必要はありませんが、実際にはテーブル作成時に AUTO_INCREMENT の初期値を直接宣言することができます。

作成したテーブル作成ステートメントを調整します。最初に先ほどのテーブルを削除し、次にテーブルの作成時に auto_increment = 4294967295

create table tb_user(id int unsigned auto_increment primary key) auto_increment = 4294967295;

を追加し、さらにテーブルにレコードを挿入します

insert into tb_user values(null);

同様に、show コマンドを使用してテーブル tb_user のテーブル構造を表示します。

CREATE TABLE `tb_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8

select * from tb_user

を通じて、ID がすでに最大値である 4294967295 であることをクエリします。

テーブルにデータを挿入しようとすると、以下に示すように主キー競合例外が報告されます。

[SQL]insert into tb_user values(null);
[Err] 1062 - Duplicate entry '4294967295' for key 'PRIMARY'

これは、再度挿入するときに、使用される自動インクリメント ID が依然として 4294967295 であり、主キーの競合例外が報告されることを示している可能性があります。

4294967295、この数はすでにほとんどのシナリオに対応できますが、サービスでデータの挿入と削除が頻繁に行われる場合は、不足するリスクがまだあります。

bigint unsigned を使用することをお勧めします。この数は大きくなります。

何か解決策はありますか? 答えは「はい」で、解決策は非常に簡単です。Int 型を BigInt 型に変更します。BigInt の範囲は次のとおりです。

-2^63- 1 to 2 ^63-1

-9223372036854775808 9223372036854775807

#毎秒 10,000 個のデータがデータ テーブルに挿入され、100 年間実行されたとしても、データ量を見てみましょう

10000*24*3600*365*100=31536000000000

この数値はまだ BigInt の上限には程遠いため、次の設定を行うことで問題を解決できます。 BigInt 型への自動インクリメント ID。

面接中に面接官にこう答えたら。

あなた: 「これは簡単ではありません。自動インクリメント主キーの型を BigInt 型に変更するだけです。」

インタビュアー: 「オンラインで列を変更するにはどうすればよいですか? データtype?"

あなた:"alter table tb_user change id id bigint;"

面接官:「実務経験はありますか?」

あなた:" ... 、ほとんどの場合操作を行うと、元のテーブルの読み取りと書き込みが可能になります。

同時 DML 操作は、データ型の変更などの操作ではサポートされていません。つまり、alter などのステートメントを直接使用してテーブルのデータ構造をオンラインで変更すると、このテーブルは更新操作 (削除、更新、挿入) を実行できなくなります。したがって、生産ラインでテーブル構造を変更することは現実的ではありません。

もっと良い方法はありますか? この問題については後で説明します。

このような状況にお気づきでしょうか?主キーの自動インクリメントIDは0から始まり、つまり現在使用できる範囲は0~2147483647ですが、いくつかのIDが存在します。実際のデータの値は連続的ではありません。

実際の運用テーブルに数億を超えるデータを含む単一のテーブルがあり、この時点でデータ テーブルにデータを書き込みたい場合、パフォーマンスは確実に影響を受けるため、早急に検討する必要があります。サブデータベースとテーブル。

データベースが複数のテーブルに分割されている場合、各テーブルの自動インクリメント ID を使用してデータをグローバルに一意に識別することはできません。シャーディング データベースとシャーディング テーブルの環境をサポートするには、グローバルに一意の ID 番号を生成する戦略を提供する必要があります。

したがって、実際には、自動インクリメントされた主キーが使い果たされるまで待つ方法はありません。

より分かりやすい回答については、この

インタビュアー: 「自動インクリメントされた主キーが最大値に達しました。その場合はどうすればよいですか?」を参照してください。使い終わっていますか?"

あなた: これまでこの問題に遭遇したことがありません。なぜなら、自動増加する主キーに int 型を使用しているためです。通常、最大値に達することができないため、テーブルとテーブルの分割を考慮する必要があります。データベース。

面接官があなたを追いかけ続け、サブデータベースとサブテーブルの重要なポイントについて質問し続けた場合は、的を絞った方法で答えることができ、あなたが以下の分野で十分な開発経験があることを示します。この分野でお役に立てると信じています。このインタビューのボーナス ポイント。

以上がmysqlの自動増加IDが使い果たされた場合の解決方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。