mySQLデータベーススキーマを作成するためのビギナーズガイド

2021年2月9日ちらしずし

ソフトウェアプロジェクトを開発する場合、最も重要で基本的かつ本質的な側面の1つは、適切に構造化されたデータベーススキーマです。これは、家を建てるときに基礎が適切に敷設されていることを確認する必要があるのと同じです。そうしないと、高品質の家を建てる可能性が大幅に低下します。

想像以上に簡単に、うまく設計されたデータベーススキーマを作成するために使用されるさまざまな側面を学びましょう。

CREATETABLE構文

まず、お気に入りのテキストエディタを開きます。データベーススキーマの作成には、プレーンテキストファイルしか必要ありません。データベースは複数のテーブルで構成され、各テーブルは列で構成され、CREATETABLE構文を使用して単一のテーブルが作成されます。基本的な例は次のとおりです。

 
CREATE TABLE users (
 id INT NOT NULL,
 is_active TINY INT NOT NULL,
 full_name VAR CHAR(100) NOT NULL,
 email VARCHAR(100) NOT NULL
 );

ご覧のとおり、これにより、4つの列で構成されるusersという名前のデータベーステーブルが作成されます。これは、 CREATE TABLEで始まり、データベーステーブルの名前が続き、括弧内にコンマで区切られたテーブルの列が続く、かなり単純なSQLステートメントである必要があります。

正しい列タイプを使用する

上に示したように、テーブルを構成する列はコンマで区切られます。各列の定義は、次の3つの同じ部分で構成されています。

 COL_NAME    TYPE    [OPTIONS]

列の名前、列タイプ、オプションのパラメーター。オプションのパラメータについては後で説明しますが、列タイプに焦点を当てて、使用可能な最も一般的に使用される列タイプを以下に示します。

タイプ	説明
INT	整数、最大（+/-）21.4億の値をサポートします。最も一般的に使用されている整数型ですが、それぞれの範囲で次のものも使用できます。 TINYINT-128。ブール値（1または0）に最適です。 SMALLINT-32k MEDIUMINT-380万大きな-9.3兆。
VARCHAR（xxx）	事実上すべての非バイナリデータをサポートする可変長文字列。括弧内のxxxは、列が保持できる最大長です。
DECIMAL（x、y）	価格や整数ではない数値などの10進/浮動小数点値を格納します。（x、y）の括弧内の数値は、列の最大長と、格納する小数点の数を定義します。たとえば、 DECIMAL（8,2）を使用すると、数値の長さは最大6桁になり、小数点以下2桁にフォーマットされます。
日時/タイムスタンプ	どちらも日付と時刻をYYY-MM-DDHH：II：SS形式で保持します。すべての行メタデータ（つまり、作成時、最初の更新時など）にはTIMESTAMPを使用し、他のすべての日付（たとえば、生年月日など）にはDATETIMEを使用する必要があります。
日付	DATETIMEと似ていますが、日付をYYY-MM-DD形式でのみ保存し、時刻を保存しない点が異なります。
テキスト	テキストの大きなブロックは、最大65k文字を格納できます。以下もそれぞれの範囲で利用できます。 MEDIUMTEXT-1670万文字。 LONGTEXT-42億文字。
BLOB	画像などのバイナリデータを保存するために使用されます。最大64kbのサイズをサポートし、それぞれのサイズ制限のある以下もサポートされます。 TINYBLOG-255バイト MEDIUMBLOB-16MB LONGBLOG-4GB
ENUM（opt1、opt2、opt3 …）	値は、括弧内に指定された事前定義された値の1つにすることのみが許可されます。ステータス列（アクティブ、非アクティブ、保留中など）などに適しています。

すべての目的で、上記の列タイプは、適切に構築されたmySQLデータベーススキーマを作成するために必要なすべてです。

列オプションの定義

列を定義するときに、指定できるさまざまなオプションもあります。以下は、 CREATETABLEステートメントの別の例です。

 
CREATE TABLE users (
 id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 username VARCHAR(100) NOT NULL UNIQUE,
 status ENUM('active','inactive') NOT NULL DEFAULT 'active',
 balance DECIMAL(8,2) NOT NULL DEFAULT 0,
 date_of_birth DATETIME,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
 );

上記は少し気が遠くなるように見えるかもしれませんが、心配する必要はありません、それは非常に簡単です。内訳は、上記のステートメントで起こっていることです：

テーブルの速度とパフォーマンスを向上させるために、可能なすべての列で常にNOTNULLを使用する必要があります。これは単に、行が挿入されたときに列を空/ nullのままにできないことを指定します。
速度とパフォーマンスの向上に役立つため、常に列サイズをできるだけ現実的に小さくするようにしてください。
id列は整数であり、テーブルの主キーでもあり、一意であることを意味し、レコードが挿入されるたびに1ずつ増加します。これは通常、作成するすべてのテーブルで使用する必要があるため、テーブル内の任意の1行を簡単に参照できます。
ステータス列はENUMであり、「アクティブ」または「非アクティブ」のいずれかの値である必要があります。値が指定されていない場合、新しい行は「アクティブ」のステータスで始まります。
残高列は、新しい行ごとに0から始まり、小数点以下2桁でフォーマットされた金額です。
date_of_birth列は単なるDATEですが、作成時に生年月日がわからない場合があるため、null値も使用できます。
最後に、 created_at列はTIMESTAMPであり、デフォルトでは行が挿入された現在の時刻になります。

上記は、適切に構造化されたデータベーステーブルの例であり、今後の例として使用する必要があります。

外部キー制約を使用してテーブルをリンクする

mySQLなどのリレーショナルデータベースを使用する最大の利点の1つは、外部キー制約とカスケードの優れたサポートです。これは、2つのテーブルを列でリンクして親子関係を形成する場合です。したがって、親行が削除されると、必要な子行も自動的に削除されます。

次に例を示します。

 
CREATE TABLE users (
 id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 username VARCHAR(100) NOT NULL UNIQUE,
 full_name VARCHAR(100) NOT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
 ) engine=InnoDB;
 CREATE TABLE orders (
 id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 userid INT NOT NULL,
 amount DECIMAL(8,2) NOT NULL,
 product_name VARCHAR(200) NOT NULL,
 FOREIGN KEY (userid) REFERENCES users (id) ON DELETE CASCADE
 ) engine=InnoDB;

最後の行としてFOREIGNKEY句があります。この行は、このテーブルに、 userid列によってusersテーブルのid列である親行にリンクされている子行が含まれていることを示しています。つまり、 usersテーブルから行が削除されるたびに、mySQLはordersテーブルから対応するすべての行を自動的に削除し、データベース内の構造的整合性を確保します。

上記のステートメントの最後にあるengine = InnoDBにも注意してください。 InnoDBがデフォルトのmySQLテーブルタイプになりましたが、常にそうであるとは限りませんでした。カスケードはInnoDBテーブルでのみ機能するため、これは安全のために追加する必要があります。

自信を持ってデザイン

これで、堅固で適切に構造化されたmySQLデータベーススキーマの設計に向けて順調に進んでいます。上記の知識を使用して、パフォーマンスと構造的整合性の両方を提供する、適切に編成されたスキーマを作成できます。

スキーマを配置したら、これらの重要なSQLコマンドでスキーマを使用する方法を知っていることを確認してください。

ちらし寿司