MySQL を使用して小規模なデータベース駆動型 Web アプリケーションを開発したことがある人は、リレーショナル データベース内のテーブルの作成、取得、更新、削除が比較的単純なプロセスであることを知っています。理論的には、最も一般的な SQL ステートメントの使用法をマスターし、使用するサーバー側スクリプト言語に精通していれば、特に高速な MyISAM を使用する場合には、MySQL テーブルで必要なさまざまな操作を処理するのに十分です。データベースエンジンのとき。しかし、最も単純なケースであっても、物事は私たちが思っているよりも複雑です。以下では、典型的な例を使用して説明します。ほぼ毎日更新するブログ サイトを運営しており、そのサイトでは訪問者があなたの投稿にコメントできるとします。
この場合、データベース スキーマには少なくとも 2 つの MyISAM テーブルが含まれている必要があります。1 つはブログ投稿を保存するため、もう 1 つは訪問者のコメントを処理するためです。明らかに、これら 2 つのテーブルの間には 1 対多の関係があるため、データ行が更新または削除されたときにデータベースの整合性が維持できるように、2 番目のテーブルに外部キーを定義する必要があります。
上記のようなアプリケーションの場合、2 つのテーブルの整合性を維持することが重大な課題であるだけでなく、最大の困難はアプリケーション レベルで整合性を維持しなければならないことです。これは、MyISAM テーブルが優れたパフォーマンスを提供するため、トランザクションの使用を必要としないほとんどの Web プロジェクトの開発中に採用されるアプローチです。
もちろん、これにはコストもかかります。アプリケーションはデータベースの整合性と一貫性を維持する必要があります。これは、さまざまなテーブル間の関係を処理するために、より複雑なプログラミング ロジックを実装することを意味します。抽象化レイヤーと ORM モジュールを使用することでデータベース アクセスを簡素化できますが、アプリケーションに必要なデータ テーブルの数が増加するにつれて、それらを処理するために必要なロジックは間違いなくより複雑になります。
では、MySQL の場合、データベースの整合性を維持するためのデータベース レベルの外部キー処理方法はあるのでしょうか? 幸いなことに、MySQL は InnoDB テーブルもサポートしているため、非常に簡単な方法で外部キー制約を処理できます。この機能を使用すると、テーブル内の特定のデータ行の更新や削除などの特定のアクションをトリガーして、事前定義された関係を維持できます。
何事にも長所と短所があり、InnoDB テーブルを使用する主な欠点は、特に多くのテーブルをクエリする必要がある大規模なアプリケーションでは、MyISAM よりも遅いことです。幸いなことに、新しいバージョンの MySQL の MyISAM テーブルは外部キー制約もサポートしています。
この記事では、InnoDB テーブルに外部キー制約を適用する方法を紹介します。さらに、単純な PHP ベースの MySQL 抽象クラスを使用して、関連するサンプル コードを作成します。もちろん、好みの他のサーバー側言語を使用することもできます。ここで、MySQL に外部キー制約を適用する方法を紹介します。
外部キー制約を使用する場合
正直なところ、MySQL で InnoDB テーブルを使用する場合、必ずしも外部キー制約を使用する必要はありませんが、特定の状況で外部キー制約を使用するため、前述の例のコードを使用して詳しく説明します。これには、ブログ投稿とコメントを保存するために使用される 2 つの MyISAM テーブルが含まれています。
データベース スキーマを定義するときは、コメントが保存されるテーブルに外部キーを作成して、データ行 (つまりコメント) を特定のブログ記事にマッピングすることで、2 つのテーブル間に 1 対多の関係を確立する必要があります。サンプル MyISAM テーブルを作成する基本的な SQL コードは次のとおりです。
存在する場合はテーブルを削除 `test`.`blogs`;
CREATE TABLE `test`.`blogs` (
`id` INT(10) UNSIGNED AUTO_INCREMENT,
`タイトル` テキスト、
「コンテンツ」テキスト、
`作成者` VARCHAR(45) デフォルト NULL、
プライローズキー (`id`)
) ENGINE=MyISAM デフォルト CHARSET=utf8;
存在する場合はテーブルを削除 `test`.`comments`;
CREATE TABLE `test`.`comments` (
`id` INT(10) UNSIGNED AUTO_INCREMENT,
`blog_id` INT(10) UNSIGNED デフォルト NULL、
「コメント」テキスト、
`作成者` VARCHAR(45) デフォルト NULL、
プライローズキー (`id`)
) ENGINE=MyISAM デフォルト CHARSET=utf8;
上記では、ブログ アプリケーションのデータ層を形成する 2 つの MyISAM テーブルを定義しました。ご覧のとおり、最初のテーブルはブログと呼ばれ、各ブログ投稿の ID、タイトル、コンテンツ、そして最後に作成者を保存するために使用されるいくつかの明らかなフィールドで構成されています。 2 番目のテーブルは comments という名前で、各ブログ投稿に関連するコメントを格納するために使用されます。ブログ投稿の ID を外部キーとして使用して、1 対多の関係を確立します。
これまでのところ、単純な MyISAM テーブルを 2 つ作成しただけなので、作業は簡単でした。次に、最初のテーブルでエントリが削除されたときに他のテーブルで何をすべきかをさらに示すために、これらのテーブルにいくつかのレコードを設定します。
データベースの整合性を更新および維持する
前のパートでは、ブログ アプリケーションのデータ層として機能する 2 つの MyISAM テーブルを作成しました。もちろん、上記の紹介はまだ非常に単純なので、さらに議論する必要があります。これを行うには、次のように SQL コマンドを使用して、これらのテーブルにいくつかのレコードを設定します。
INSERT INTO ブログ (ID、タイトル、コンテンツ、著者) VALUES (NULL、'最初のブログ エントリのタイトル'、'最初のブログ エントリのコンテンツ'、'Ian')
INSERT INTO コメント (id, blog_id, comment, author) VALUES (NULL, 1, '最初のブログ エントリにコメント中', 'スーザン ノートン'), (NULL, 1, '最初のブログ エントリにコメント中', 'ローズ ウィルソン')
上記のコードは、読者のスーザンとローズが最初のブログにコメントした状況を実際にシミュレートしています。ここで、最初のブログを別の投稿で更新したいとします。もちろん、このような状況も考えられます。
この場合、データベースの一貫性を維持するために、コメント テーブルも手動で、またはデータ層を処理するアプリケーションによって更新する必要があります。この例では、次のように SQL コマンドを使用して更新を完了します。
UPDATE ブログ SET id = 2、title = '最初のブログ エントリのタイトル'、content = '最初のブログ エントリのコンテンツ'、author = 'John Doe' WHERE id = 1
コメントを更新 SET blog_id = 2 WHERE blod_id = 1
前述したように、最初のブログのデータ項目の内容が更新されているため、コメント テーブルにもこの変更が反映されている必要があります。もちろん、実際には、この更新操作は手動ではなくアプリケーション層で完了する必要があります。つまり、このロジックはサーバー側の言語を使用して実装する必要があります。
この操作を完了するために、PHP は単純なサブプロセスを使用できますが、実際には、外部キー制約が使用されている場合、コメント テーブルの更新操作をデータベースに委任できます。
記事の前半で述べたように、InnoDB MySQL テーブルはこの機能をシームレスにサポートします。したがって、後の部分では、外部キー制約を使用して前のコード例を再作成します。
データベースのカスケード更新
以下では、(デフォルトの MyISAM タイプの代わりに) 外部キー制約と InnoDB テーブルを使用して、前のサンプル コードを再構築します。これを行うには、まず 2 つのサンプル テーブルを再定義して、特定のデータベース エンジンを使用できるようにします。これを行うには、次のような SQL コードを使用できます。
存在する場合はテーブルを削除 `test`.`blogs`;
CREATE TABLE `test`.`blogs` (
`id` INT(10) UNSIGNED AUTO_INCREMENT,
`タイトル` テキスト、
「コンテンツ」テキスト、
`作成者` VARCHAR(45) デフォルト NULL、
プライローズキー (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
存在する場合はテーブルを削除 `test`.`comments`;
CREATE TABLE `test`.`comments` (
`id` INT(10) UNSIGNED AUTO_INCREMENT,
`blog_id` INT(10) UNSIGNED デフォルト NULL、
「コメント」テキスト、
`作成者` VARCHAR(45) デフォルト NULL、
優先キー (`id`)、
KEY `blog_ind` (`blog_id`)、
CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ここのコードと前のコードの明らかな違いは、2 つのテーブルが InnoDB ストレージ エンジンを使用しているため、外部キー制約をサポートできることです。さらに、コメント テーブルを定義するコードにも注意する必要があります。
CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`) ON UPDATE CASCADE
実際、このステートメントは、blogs テーブルが更新されると、comments テーブル内の外部キー blog_id の値も更新される必要があることを MySQL に通知します。言い換えれば、ここで行われているのは、MySQL にカスケード方式でデータベースの整合性を維持させることです。これは、ブログが更新されると、それに接続されているコメントもこの変更を即座に反映する必要があることを意味します。重要なのは、この関数の実装です。アプリケーション層では行われません。
2 つの MySQL テーブルの例が定義されました。以下に示すように、これら 2 つのテーブルの更新は UPDATE ステートメントを実行するだけで簡単になります。
"UPDATE blogs SET id = 2、title = '最初のブログ エントリのタイトル'、content = '最初のブログ エントリのコンテンツ'、author = 'John Doe' WHERE id = 1"
前述したように、MySQL がこれを自動的に処理するため、コメント テーブルを更新する必要はありません。さらに、クエリの "ON UPDATE" 部分を削除するか、"NO ACTION" と "RESTRICT" を指定することで、blogs テーブル内の行を更新しようとするときに MySQL に何も行わせないようにすることもできます。もちろん、MySQL に他のことを実行させることもできます。これについては、後続の記事で紹介します。
上記の紹介を通じて、MySQL で InnoDB テーブルと組み合わせて外部キー制約を使用する方法を明確に理解できたと思います。もちろん、この便利なデータベース関数の理解をさらに深めるために、さらに即時コードを記述することもできます。