MySQLのint主キーの自己増分の問題を解決する

導入

MySQL データベースを使用する場合、int を主キーとして使用し、自動インクリメントに設定することに慣れています。これにより、一意性が保証され、使いやすいです。ただし、int 型の長さには制限があります。長さを超えた場合はどうなるでしょうか?

問題を明らかにする

まずテストテーブルを作成しましょう。作成ステートメントは次のとおりです。

テーブルtest1を作成します（
  id INT 主キー AUTO_INCREMENT、
  名前 VARCHAR(20)
）

次に、2 つのデータを挿入します。

test1 に VALUES(NULL, 'Calf') を挿入します。
INSERT INTO test1 VALUES(NULL,'大牛');

クエリ テーブルは通常どおり表示されます。

signed int 型の範囲は 231 -1 = 2147483647 です。次のように、ID 2147483647 のデータを直接挿入します。

test1 に挿入 VALUES(2147483647 ,'Xiaohua')

結果は正常です:

この時点で、自動インクリメント ID は int 型の上限に達しています。さらにデータを挿入すると、エラーが報告されます。

test1 に値(NULL、'cow')を挿入します。

この時点では、主キーを増分することはできず、挿入された ID は依然として 2147483647 のままであり、主キーの一意の条件に違反しているため、エラーが報告されます。

問題を解決する

（１）より大きなデータ型bigintを使用する

bigintの範囲は263-1、いわゆる指数爆発で、このときのサイズは9,223,372,036,854,775,807という恐ろしい大きさに達します。簡単に言えば、bigintを使用して1日に100万個のデータを保存すると、爆発するまでに200億年かかります。したがって、現在のシナリオでは、bigintの自動インクリメントを心配する必要はほとんどありません。

図に示すように、データ型をbigintに変更します。

次に、挿入ステートメントを実行します。

test1 に値(NULL、'cow')を挿入します。

再び正常に挿入できます:

（２）UUIDを主キーとして使う

UUID は、現在のシステム パフォーマンスやタイムスタンプなどの一連のパラメータに基づいて計算され、世界で唯一の文字列を取得することは周知の事実であり、MySQL は UUID を生成する方法を提供しています。これを主キーとして使用すると、データの一意性を確保できます。

次のコードを使用して 32 ビットの UUID を生成できます。

-- 32ビットUUIDを生成する
UUID として REPLACE(UUID(),'-','') を選択します。

次に、テスト テーブルを作成しましょう。

テーブルtest2を作成します(
  id VARCHAR(50) 主キー、
  名前 VARCHAR(20) NULLではない
）

データを挿入します:

-- UUID を挿入
test2 に挿入 VALUES(REPLACE(UUID(),'-',''),'老王');

しかし、挿入文を書くたびに UUID 関数を手動で書くのはちょっと面倒に思えます。トリガーを書いて、トリガーが自動的に ID を設定するようにすることができます。

-- トリガー DELIMITER $$ を作成
作成する
TRIGGER auto_id -- 名前 BEFORE INSERT -- トリガー時間 ON test2 FOR EACH ROW -- test2 テーブルに作用し、データの各行に効果があります BEGIN
IF new.id = '' THEN -- id が空の文字列の場合は UUID を設定します
  SET new.id = REPLACE(UUID(),'-','');
終了の場合;
終わり$$

データを挿入します:

-- データを挿入します INSERT INTO test2 VALUES('','Xiao Wang');

結果は普通に追加できる

要約する

(1) int型とbigInt型はUUIDよりも追加、削除、変更、クエリが高速で、より多くのスペースを節約できます。

（２）UUIDを使うと便利です。

自動インクリメント int を主キーとして使用するのはなぜですか?

主キーのデータ型として unsigned auto-incrementing int を使用する必要があることは誰もが知っていると思いますが、varchar、text、varchar などの型ではなく auto-incrementing int を使用する必要がある理由をご存知ですか?

誰もがいくつかの利点を指摘することができます。上位レベルのビジネスに対して透過的であり、データを挿入するときに明示的に指定する必要がないこと、シンプルなデータ型、テーブル構造の保存と維持が容易であることなどです。

実は、自動インクリメント int を主キーとして使用することには多くの利点があります。今日はそれについて学び、実際の開発では自動インクリメント int を主キーとして使用することを強くお勧めします。

アドバンテージ：

1. Int は、varchar、char、text よりもストレージ スペースが少なく、データ型が単純なため、CPU オーバーヘッドを節約でき、テーブル構造を維持しやすくなります。

2. デフォルトでは、主キーに主キーインデックスが作成されます。主キーとして整数を使用すると、より多くのインデックスをメモリにロードし、クエリのパフォーマンスを向上させることができます。

3. InnoDB ストレージ エンジンの場合、各セカンダリ インデックスは、インデックス値のサフィックスとして主キーを使用します。自動増分主キーを使用すると、インデックスの長さ (サイズ) が削減され、より多くのインデックス データをメモリにロードしやすくなります。

4. インデックス データをよりコンパクトにできます。データの挿入、削除、更新時に、インデックス データのページの移動と分割を最小限に抑え、フラグメントの生成を減らし (テーブルの最適化を通じてテーブルを再構築できます)、メンテナンス コストを削減できます。

5. データを挿入する際に、論理的に隣接する要素が物理的にも隣接していることを保証できるため、範囲検索に便利です。

もちろん、自動増分 int を主キーとして使用することには欠点がないわけではありません。また、同時実行性が高い状況ではロック競合の問題が発生する可能性もあります。

上記は私の個人的な経験です。参考になれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。