MySQL主キー命名戦略関連

最近、データライフサイクル管理の詳細を整理していたときに、小さな問題を発見しました。それは、MySQL の主キー命名戦略が、あらゆる形式のカスタム命名を無視しているように見えることです。

つまり、主キーに idx_pk_id という名前を付けると、MySQL では PRIMARY として扱われます。

もちろん、これを基にして拡張や追加を行うこともできます。

まず、問題を再現してみましょう。データベース test に接続し、テーブル test_data2 を作成します。

mysql> テストを使用する

mysql> テーブル test_data2 (id int 、name varchar(30)) を作成します。

クエリは正常、影響を受けた行は 0 行 (0.05 秒)

次に、主キーを作成し、idx_pk_id という名前を付けます。実行状況からすると、MySQL はこれを正常に処理します。

mysql> テーブル test_data2 を変更し、主キー idx_pk_id(id) を追加します。
クエリは正常、影響を受けた行は 0 行 (0.02 秒)
レコード: 0 重複: 0 警告: 0

さらに比較するために、一意のインデックス（セカンダリインデックス）を追加して違いを確認します。

mysql> テーブル test_data2 を変更し、一意のキー idx_uniq_name(name) を追加します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
レコード: 0 重複: 0 警告: 0

主キーの命名方法1を表示: show indexesコマンドを使用する

MySQL インデックス情報を表示するには、test_data2 から show indexes を使用します。

mysql> test_data2\G のインデックスを表示
************************** 1. 行 ****************************
    テーブル: test_data2
  非ユニーク: 0
   Key_name: PRIMARY
 インデックス内のシーケンス: 1
 列名: id
  照合: A
 カーディナリティ: 0
   サブパート: NULL
    パック: NULL
     ヌル: 
  インデックスタイプ: BTREE
   コメント： 
インデックスコメント: 
************************** 2. 行 ****************************
    テーブル: test_data2
  非ユニーク: 0
   キー名: idx_uniq_name
 インデックス内のシーケンス: 1
 列名: 名前
  照合: A
 カーディナリティ: 0
   サブパート: NULL
    パック: NULL
     ヌル: はい
  インデックスタイプ: BTREE
   コメント： 
インデックスコメント: 
セット内の 2 行 (0.00 秒)

主キーの命名方法 2 を表示: データディクショナリ information_schema.statistics を使用する

コマンド方式は汎用性が十分ではありません。データ辞書 information_schema.statistics を使用してデータを抽出できます。

mysql> information_schema.statistics から * を選択します。ここで、table_schema='test' および table_name='test_data2' は 20 を制限します \G        
************************** 1. 行 ****************************
TABLE_CATALOG: 定義
 TABLE_SCHEMA: テスト
  テーブル名: test_data2
  非ユニーク: 0
 INDEX_SCHEMA: テスト
  インデックス名: プライマリ
 SEQ_IN_INDEX: 1
 列名: id
  照合: A
 カーディナリティ: 0
   サブパート: NULL
    パック: NULL
   NULL可能: 
  インデックスタイプ: BTREE
   コメント： 
インデックスコメント: 
************************** 2. 行 ****************************
TABLE_CATALOG: 定義
 TABLE_SCHEMA: テスト
  テーブル名: test_data2
  非ユニーク: 0
 INDEX_SCHEMA: テスト
  インデックス名: idx_uniq_name
 SEQ_IN_INDEX: 1
 COLUMN_NAME: 名前
  照合: A
 カーディナリティ: 0
   サブパート: NULL
    パック: NULL
   NULL可能: はい
  インデックスタイプ: BTREE
   コメント： 
インデックスコメント: 
セット内の 2 行 (0.00 秒)

主キーの命名方法3を表示: show create tableコマンドを使用する

テーブル作成ステートメントを表示すると、主キー名がフィルター処理されていることがわかります。

mysql> テーブル test_data2\G の作成を表示します
************************** 1. 行 ****************************
    テーブル: test_data2
テーブルの作成: CREATE TABLE `test_data2` (
 `id` int(11) NULLではない、
 `name` varchar(30) デフォルト NULL,
 主キー (`id`)、
 ユニークキー `idx_uniq_name` (`name`)
) エンジン=InnoDB デフォルト文字セット=utf8
セット内の 1 行 (0.00 秒)
作成ステートメントと変更ステートメントが別々に実行されるため、処理方法が異なるのではないかと疑問に思う受講者もいるかもしれません。これは 1 つのステップで実行でき、作成ステートメントで主キー名を宣言できます。
テーブル `test_data3` を作成します (
 `id` int(11) NULLではない、
 `name` varchar(30) デフォルト NULL,
 主キー idx_pk_id(`id`),
 ユニークキー `idx_uniq_name` (`name`)
)ENGINE=InnoDB デフォルト文字セット=utf8;

この時、テーブル作成文を確認すると、結果は上記と同じで、主キー名は PRIMARY となっていることがわかります。

mysql> テーブル test_data3\G の作成を表示します    
************************** 1. 行 ****************************
    テーブル: test_data3
テーブルの作成: CREATE TABLE `test_data3` (
 `id` int(11) NULLではない、
 `name` varchar(30) デフォルト NULL,
 主キー (`id`)、
 ユニークキー `idx_uniq_name` (`name`)
) エンジン=InnoDB デフォルト文字セット=utf8
セット内の 1 行 (0.00 秒)

ビュー主キーの命名方法 4: ビュー制約の命名

もちろん、検証する方法は他にもたくさんあります。たとえば、制約を使用して主キーに名前を付けると、取得される主キー名は PRIMARY になります。

存在しない場合はテーブルを作成 `default_test` (
 `default_test`.`id` SMALLINT NOT NULL AUTO_INCREMENT、
 `default_test`.`name` LONGTEXT NOT NULL、
制約 `pk_id` 主キー (`id`)
);

主キーの命名方法 5 を表示する: DML エラー情報を使用する

もちろん、DML ステートメントを使用するなど、検証する方法は他にもたくさんあります。

mysql> test_data2 に値(1,'aa')を挿入します。
クエリは正常、1 行が影響を受けました (0.02 秒)
mysql> test_data2 に値(1,'aa')を挿入します。
エラー 1062 (23000): キー 'PRIMARY' のエントリ '1' が重複しています

上記の方法により、この詳細についてより深く理解することができ、もちろんさらに深く理解することもできます。

主キーの命名方法 6 を表示: 公式ドキュメント

公式ドキュメントには実際にこの情報が含まれていますが、あまり明確ではありません。

主キーの説明は以下のとおりです。主キーの名前が PRIMARY であるという特別な記述があります。

テーブルには PRIMARY KEY を 1 つだけ設定できます。
PRIMARY KEY の名前は常に PRIMARY であるため、他のタイプのインデックスの名前として使用することはできません。
PRIMARY KEY がなく、アプリケーションでテーブルに PRIMARY KEY を指定する必要がある場合、MySQL は NULL 列を持たない最初の UNIQUE インデックスを PRIMARY KEY として返します。
InnoDB テーブルでは、セカンダリインデックスのストレージオーバーヘッドを最小限に抑えるために、PRIMARY KEY を短く保ちます。各セカンダリインデックスエントリには、対応する行のプライマリキー列のコピーが含まれます。
作成されたテーブルでは、最初に PRIMARY KEY を配置し、次にすべての UNIQUE インデックスを配置し、最後に非一意のインデックスを配置します。これにより、MySQL オプティマイザは使用するインデックスを優先順位付けし、重複する UNIQUE キーをより速く検出できるようになります。