MySQL が外部キーを作成できない理由と解決策

2 つのテーブルを関連付けるときに、外部キーを作成できませんでした。このブログから、問題は、ポイント 6 の Charset および Collat​​e オプションのテーブル レベルとフィールド レベルの一貫性にあることがわかりました。 2 つのテーブルのエンコーディング文字セットと照合順序が一致していません。両方のテーブルで次の SQL ステートメントが実行されます。

テーブル名を変更し、文字セットを utf8 に変換します。

問題に対する完璧な解決策。

ps: MySQLは外部キーを作成できず、外部キーのプロパティをクエリできないことを確認しましょう

MyISAM と InnoDB の説明

InnoDB と MyISAM は、MySQL を使用する際に多くの人が最もよく使用する 2 つのテーブル タイプです。これらの 2 つのテーブル タイプには、特定のアプリケーションに応じて、それぞれ長所と短所があります。基本的な違いは、MyISAM タイプはトランザクション処理などの高度な処理をサポートしていないのに対し、InnoDB タイプはサポートしていることです。 MyISAM タイプのテーブルはパフォーマンスを重視しています。実行時間は InnoDB タイプよりも高速ですが、トランザクション サポートは提供されません。InnoDB はトランザクション サポートと、外部キーなどの高度なデータベース機能を提供します。

詳細と実装の違いは次のとおりです。

◆1. InnoDBはFULLTEXTタイプのインデックスをサポートしていません。

◆2. InnoDB はテーブル内の特定の行数を保存しません。つまり、select count(*) from table を実行すると、InnoDB はテーブル全体をスキャンして行数を計算する必要がありますが、MyISAM は保存された行数を単に読み取ります。 count(*) ステートメントに where 条件が含まれている場合、操作は両方のテーブルに対して同じになることに注意してください。

◆3. AUTO_INCREMENT 型のフィールドの場合、InnoDB にはこのフィールドのみを持つインデックスが含まれている必要がありますが、MyISAM テーブルでは他のフィールドとの結合インデックスを作成できます。

◆4. DELETE FROM table を実行すると、InnoDB はテーブルを再作成せず、行を 1 つずつ削除します。

◆5. LOAD TABLE FROM MASTER 操作は InnoDB では機能しません。解決策としては、まず InnoDB テーブルを MyISAM テーブルに変更し、データをインポートした後に InnoDB テーブルに戻すことです。ただし、これは追加の InnoDB 機能 (外部キーなど) を使用するテーブルには適用されません。

さらに、InnoDB テーブルの行ロックは絶対的ではありません。MySQL が SQL ステートメントの実行時にスキャンする範囲を決定できない場合、InnoDB テーブルはテーブル全体をロックします。たとえば、update table set num=1 where name like "%aaa%" などです。

2 つのタイプの主な違いは、Innodb がトランザクションと外部キーおよび行レベルのロックをサポートしていることです。 MyISAM はこれをサポートしていません。そのため、MyISAM は小規模なプロジェクトでの使用にのみ適していると考えられることが多いです。

MySQL ユーザーの観点からは、Innodb と MyISAM の両方が好まれます。データベース プラットフォームが 99.9% の安定性、便利なスケーラビリティ、高可用性の要件を満たす必要がある場合、MyISAM が間違いなく最初の選択肢です。

理由は次のとおりです。

1. プラットフォーム上で実行されるプロジェクトのほとんどは読み取りが多く書き込みが少ないプロジェクトであり、MyISAM の読み取りパフォーマンスは Innodb よりもはるかに優れています。

2. MyISAM はインデックスとデータが分離されており、インデックスが圧縮されているため、メモリ使用率が大幅に向上します。 Innodb はより多くのインデックスをロードできますが、Innodb のインデックスとデータは緊密にバンドルされており、圧縮は使用されていないため、Innodb は MyISAM よりもはるかに大きくなります。

3. 1～2 か月に 1 回、アプリケーション開発者が誤って範囲が間違っているテーブルを更新し、テーブルが使用できなくなることがよくあります。このとき、MyISAM の優位性が反映されます。その日にコピーした圧縮パッケージから該当するテーブルのファイルを取り出し、データベース ディレクトリに配置し、SQL にダンプしてメイン データベースにインポートし、該当する binlog に記入するだけです。 Innodb であれば、それほど高速にはならないと思います。最小のデータベース インスタンスのデータ量は基本的に数十 GB なので、Innodb で定期的に export xxx.sql メカニズムを使用してバックアップするように言わないでください。

4. アプリケーション ロジックの観点では、select count(*) と order by が最も頻繁に実行される操作で、SQL ステートメント全体の 60% 以上を占めています。Innodb は実際にこの操作のためにテーブルをロックします。Innodb は行レベルのロックであり、主キーに対してのみ有効であると考えている人が多いです。主キー以外のキーはテーブル全体をロックします。

5. また、定期的に特定のテーブルのデータを提供する必要があるアプリケーション部門が多数あります。MyISAM は非常に便利です。テーブルに対応する frm.MYD、MYI ファイルを送信し、対応するバージョンでデータベースを起動させるだけです。Innodb では、xxx.sql をエクスポートする必要があります。ファイルのみを他の人に渡すと、辞書データ ファイルの影響で相手が使用できなくなるためです。

6. 挿入書き込み操作で MyISAM と比較した場合、Innodb は MyISAM の書き込みパフォーマンスに到達できません。インデックスベースの更新操作の場合、MyISAM は Innodb より劣るかもしれませんが、スレーブ データベースがこのような高同時書き込みに対応できるかどうかも問題です。この問題は、マルチインスタンス シャーディング アーキテクチャを通じて解決する方がよいでしょう。

7. MyISAM を使用すると、マージ エンジンによってアプリケーション部門の開発速度が大幅に向上します。マージ テーブルでいくつかの select count(*) 操作を実行するだけで済みます。大規模プロジェクトで合計数億行程度の特定の種類のビジネス テーブル (ログ、調査統計など) に非常に適しています。

もちろん、Innodb が絶対に使用されないわけではありません。トランザクションを使用するプロジェクトでは、Innodb を使用する必要があります。さらに、MyISAM は書き込み操作が多すぎると耐えられないと言う人もいるかもしれませんが、これはアーキテクチャによって補うことができます。

SELECT * FROM information_schema.key_column_usage WHERE table_name='テーブル名';
show create table テーブル名;

要約する

以上が、MySQL が外部キーを作成できない原因と解決方法の紹介です。参考になれば幸いです。