MySQL の自動増分 ID (主キー) が不足した場合の解決策

MySQL で使用される自動インクリメント ID には多くの種類があり、各自動インクリメント ID には初期値が設定されています。通常、初期値は 0 から始まり、一定のステップ サイズ (通常は 1) で増加します。一般的に、データテーブルの自動インクリメントIDとしてはint(11)を使用します。MySQLでは、この数値のバイト長が定義されている限り、上限が存在します。

MySQLの自動インクリメントID（主キー）が使い果たされた場合はどうすればよいですか？

int unsigned (int, 4 バイト) を使用すると、現在宣言されている自動インクリメント ID の最大値を計算することができます。ここで int unsigned が定義されているため、最大値は 2 の 32 乗 - 1 = 4294967295 に達する可能性があります。

ここでちょっとしたコツがあります。テーブルを作成するときに、AUTO_INCREMENT の初期値を 4294967295 として直接宣言することができます。

テーブル `test` を作成します (
 `id` int(10) 符号なし NOT NULL AUTO_INCREMENT,
 主キー (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=4294967295;

SQL 挿入ステートメント

`test` に値（null）を挿入します。

別のデータを挿入しようとすると、次の異常な結果が得られました。

[SQL] `test` 値に (null) を挿入します。
[エラー] 1062 - キー「PRIMARY」のエントリ「4294967295」が重複しています

つまり、データが再度挿入されると、使用される自動増分 ID は依然として 4294967295 であり、主キー競合エラーが報告されます。つまり、ID 値が上限に達すると、それ以上変更されなくなります。 4294967295 であれば、ほとんどのシナリオに対応できます。サービスで頻繁にデータの挿入と削除が行われる場合は、不足するリスクが依然としてあります。大きな数値である bigint unsigned を使用することをお勧めします。

bigint unsigned は、-2^63 (-9223372036854775808) から 2^63-1 (9223372036854775807) までの範囲の整数データ (すべての数値) であり、そのストレージ サイズは 8 バイトです。

しかし、別の状況もあります。テーブルを作成するときに主キーが明示的に宣言されていない場合はどうなるでしょうか?

この場合、InnoDB は自動的に 6 バイトの非表示の row_id を作成し、InnoDB はグローバル dictsys.row_id を維持するため、主キーが定義されていないテーブルは row_id を共有します。データが挿入されるたびに、グローバル row_id が主キー ID として使用され、グローバル row_id が 1 ずつ増加します。

グローバル row_id は bigint unsigned 型を使用して実装されていますが、実際には row_id 用に予約されているのは 6 バイトだけです。この設計には問題があります。グローバル row_id が 2 の 48 乗 - 1 に達するまで増加し続け、その後 1 ずつ増加すると、row_id の下位 48 ビットがすべて 0 になります。その結果、新しいデータ行が挿入されると、取得される row_id は 0 になり、主キーの競合が発生する可能性があります。

したがって、この隠れた危険を回避するには、各テーブルに主キーが必要です。

要約する

データベーステーブルの自動インクリメント ID が上限に達すると、再度適用しても値は変更されません。データの挿入を続行すると、主キーの競合エラーが発生します。したがって、データ テーブルを設計するときは、ビジネス ニーズに基づいて適切なフィールド タイプを選択するようにしてください。

以上がMySQLの自動増分ID（主キー）が使い果たされた問題の解決方法の詳細内容です。MySQLの自動増分ID（主キー）についての詳細は、123WORDPRESS.COMの他の関連記事にも注目してください！