AWS

This is an explanation of each item when creating a table with MySQL Workbench.

MySQL Workbenchでテーブルを作成する

　MySQL Workbenchでテーブルを作成する際の各項目の説明です。

• Table
  • Table Name
  • Charset/Collation
  • Engine
  • Comments
• Column
  • Column Name
  • Data Type
  • Default
  • Comments
  • Constraints
  • Storage

Table

Table Name

テーブル名（Table Name）は、リレーショナルデータベース管理システム（RDBMS）内でデータを整理し、格納するための構造体（テーブル）を識別するために使用される名前です。テーブルはデータベース内で最も基本的なストレージの単位であり、行（レコード）と列（フィールドまたは属性）の２次元のグリッドでデータを格納します。

テーブル名の重要性

識別

データベース内の特定のテーブルを一意に識別します。これにより、クエリ、データの挿入、更新、削除などの操作を正確にそのテーブルに対して行うことができます。

組織化

関連するデータを論理的にグループ化し、データ管理とアクセスを容易にします。例えば、employees、products、ordersなどのテーブル名からは、それぞれのテーブルに何が格納されているかが直感的に理解できます。

スキーマ定義

テーブル名は、そのテーブルのスキーマ（テーブル構造を定義する属性や制約の集合）の一部として機能します。スキーマは、テーブルにどのようなデータが格納されるか、どのように整理されるかを定義します。

テーブル名の命名規則

明確性

テーブルの内容を明確に表す名前を選ぶことが重要です。例えば、従業員のデータを格納するテーブルは employees のように命名します。

一貫性

データベース内で一貫した命名規則を使用します。これにより、データベースの構造がより理解しやすくなります。

短縮形の回避

可能な限り、短縮形や略語の使用を避け、完全な単語を使用して名前をつけることが望ましいです。ただし、業界標準の略語は例外となることがあります。

大文字小文字の一貫性

一部のデータベースシステムでは大文字と小文字が区別されるため、大文字小文字の使用に一貫性を持たせることが重要です。

テーブル名の制限

文字数

MySQLにはテーブル名の文字数制限があります。MySQLのドキュメントによると、テーブル名（および他の識別子）の最大長はデータベースシステムの設定によって異なりますが、一般的には64文字までとされています。この制限は、MySQLのバージョンや使用しているファイルシステム、およびデータベースの設定によって異なる場合があります。

使用可能な文字

一般的にはアルファベット（大文字・小文字）、数字、アンダースコアが使用できますが、特殊文字やスペースの使用は避けるべきです。

予約語を避ける

SQLの予約語（SELECT、INSERT、DELETEなど）はテーブル名として使用しないでください。

テーブル名を選択する際には、その名前がデータの内容と目的を正確に反映し、データベース全体での一貫性と整理を促進するように心掛けることが重要です。

Charset/Collation

MySQLにおける Charset（文字セット）と Collation（照合順序）は、データベース、テーブル、またはカラムレベルで文字データの扱い方を定義するために使用される属性です。これらは文字データの格納、比較、および検索の振る舞いを制御します。

Charset（文字セット）

説明

文字セットは、文字を表現するために使用されるエンコーディングのセットです。MySQLでは、utf8mb4、latin1、sjisなど、さまざまな文字セットをサポートしています。文字セットは、使用する文字とその文字をバイトとしてどのように格納するかを定義します。

目的

異なる言語の文字を適切に格納し、処理するために使用されます。例えば、多言語サポートが必要な場合は utf8mb4 を使用することで、絵文字を含むあらゆる Unicode文字を扱うことができます。

使用例

データベースを作成する際に、下記のように指定します。

SQL

CREATE DATABASE mydb CHARACTER SET utf8mb4;

Collation（照合順序）

説明

照合順序は、文字セット内の文字が比較される際のルールを定義します。これには、大文字と小文字の区別、アクセントの有無の扱い、および文字間のソート順が含まれます。例えば、utf8mb4_general_ciは、大文字小文字を区別せず（ciは case-insensitiveの略）、一般的な比較ルールに基づいて utf8mb4文字セットの文字を比較します。

目的

文字列のソートや比較を正確に行うため、特定の言語や地域に合わせた照合順序を選択することができます。

使用例

テーブルを作成する際に、下記のように指定します。

SQL

CREATE TABLE mytable (name VARCHAR(100)) COLLATE utf8mb4_unicode_ci;

文字セットと照合順序の関係

文字セットと照合順序は密接に関連しています。照合順序は特定の文字セットに属し、その文字セット内での文字の比較とソートの方法を決定します。したがって、照合順序を指定する際には、それが適用される文字セットを意識する必要があります。また、MySQLでは、デフォルトの文字セットと照合順序をサーバー、データベース、テーブル、カラムレベルで異なるものに設定することができます。これにより、アプリケーションの国際化や多言語対応を柔軟に実現することが可能になります。

適切な文字セットと照合順序の選択は、グローバルなアプリケーションの開発において特に重要です。これにより、さまざまな言語のデータを正確に扱い、ユーザーにとって自然な形でデータを表示および処理することができます。

Engine

MySQLでは、さまざまなストレージエンジンが用意されており、それぞれが異なる特性や機能を提供します。ストレージエンジンは、データの格納、取得、更新の方法を定義します。データベースの設計や要件に応じて、最適なエンジンを選択することが重要です。

InnoDB

MySQLのデフォルトストレージエンジンで、トランザクション安全性（ACID準拠）、ロールバック、クラッシュリカバリ能力を備えています。外部キー制約をサポートし、大量の並行トランザクションを効率的に処理できます。ほとんどのユースケースに適しています。

MyISAM

高速な読み取り操作、フルテキスト検索インデックスをサポートしていますが、トランザクション安全性は提供しません。InnoDBに置き換えられる前は、MySQLのデフォルトエンジンでした。

ndbcluster

分散コンピューティング環境向けに設計されたストレージエンジンで、高可用性とスケーラビリティを提供します。リアルタイムデータベースの要件や、フォールトトレラントなシステムに適しています。

MEMORY

データをメモリ上に格納するため、非常に高速なアクセスを提供しますが、サーバーが停止するとデータは失われます。主に一時データや読み取り専用のデータキャッシュに使用されます。

FEDERATED

FEDERATEDストレージエンジンは、リモートの MySQLサーバーに格納されているテーブルに対して、ローカルサーバーから直接 SQL操作を行うことを可能にします。つまり、FEDERATEDテーブルを介して、別のサーバー上にあるデータベーステーブルを操作できるようになります。これにより、分散データベースの設計や、複数のサーバー間でデータを共有するアーキテクチャを実装することが可能になります。ただし、FEDERATEDエンジンを使用するには、リモートサーバーも MySQLを実行している必要があります。

ARCHIVE

圧縮を使用してデータを格納するため、大量の履歴データやログデータの保管に適しています。読み取り専用のクエリに対応し、書き込み操作時にのみ圧縮を行います。

CSV

データをコンマ区切り値（CSV）ファイル形式で格納します。テキストエディタやスプレッドシートソフトウェアで簡単に読み書きできるため、データのインポート・エクスポートに便利です。

BLACKHOLE

書き込まれたデータをすべて破棄し、実際にはデータを格納しません。レプリケーションのフィルタリングやベンチマークテストに有用です。

MRG_MyISAM

複数の MyISAMテーブルを１つのマージテーブルとして扱うことができる。このエンジンは、主に読み取り専用のデータに対して使用され、大量のデータを持つテーブルを効率的に管理するための仮想的なビューを提供します。MRG_MyISAMを使用することで、複数のMyISAMテーブルを１つのテーブルのように参照し、クエリを実行することが可能になります。

これらのストレージエンジンは、特定の要件やパフォーマンスの目標に応じて選択されます。例えば、トランザクションの整合性を保証する必要があるアプリケーションでは InnoDB が推奨され、一方で、高速な読み取りが重要でトランザクションの整合性がそれほど重要でない場合は MyISAM が適している場合があります。選択するエンジンによって、データの処理方法やアプリケーションのパフォーマンスに大きな影響をあたえる事があります。

Comments

テーブルに関する説明を追加できます。このコメントは、データベースのメタデータとして格納され、後で参照できます。

SQL

ADD COLUMN age INT;

Column

Column Name（カラム名）

追加するカラムの名称です。カラム名はそのテーブル内で一意でなければなりません。

Data Type（データ型）

MySQLで使用できるデータ型は多岐にわたり、それぞれ特定の種類のデータを格納するために設計されています。

バイナリ型

BINARY

固定長のバイナリデータを格納します。格納されるデータが指定された長さに満たない場合、残りはゼロで埋められます。

VARBINARY

可変長のバイナリデータを格納します。指定された最大長までのデータを格納できます。

BLOB

バイナリデータを格納するための大きなオブジェクト。画像や音声などの大きなファイルを格納するのに適しています。

TINYBLOB

非常に小さいバイナリデータを格納します。

MEDIUMBLOB

中程度のサイズのバイナリデータを格納します。

LONGBLOB

非常に大きなバイナリデータを格納します。

日付および時刻型

DATE

日付のみを格納します（例: 2024-02-19）。

DATETIME

日付と時刻を格納します（例: 2024-02-19 14:30:00）。

TIME

時刻のみを格納します（例: 14:30:00）。

TIMESTAMP

UNIXタイムスタンプとして日付と時刻を格納します。自動的に現在の日付と時刻に更新されることがあります。

YEAR

年を格納します（例: 2024）。4桁または 2桁の形式が可能です。

空間データ型

GEOMETRY

任意の種類の空間データ（点、線、多角形など）を格納します。

POINT

単一の地点を表します。

LINESTRING

2点以上の地点を結んだ線を表します。

POLYGON

閉じた領域を表します。

MULTIPOINT

複数の点を格納します。

MULTILINESTRING

複数の線を格納します。

MULTIPOLYGON

複数の多角形を格納します。

GEOMETRYCOLLECTION

上記の任意の組み合わせを格納します。

数値型

TINYINT

非常に小さい整数を格納します。例えば、-128 から 127 まで、または符号なしで 0 から 255 まで。

SMALLINT

小さい整数を格納します。例えば、-32768 から 32767 まで、または符号なしで 0 から 65535 まで。

MEDIUMINT

中程度の大きさの整数を格納します。例えば、-8388608 から 8388607 まで、または符号なしで 0 から 16777215 まで。

INT

標準的な整数を格納します。例えば、-2147483648 から 2147483647 まで、または符号なしで 0 から 4294967295 まで。

BIGINT

非常に大きな整数を格納します。例えば、-2^63 から 2^63-1 まで、または符号なしで 0 から 2^64-1 まで。

FLOAT

単精度浮動小数点数を格納します。小数点以下の精度は約 7桁です。

DOUBLE

倍精度浮動小数点数を格納します。小数点以下の精度は約 15桁です。

DECIMAL

固定小数点数を格納します。精度（桁数）とスケール（小数点以下の桁数）を指定できます。

REAL

DOUBLEと同じ倍精度浮動小数点数を格納します（SQL標準に基づく）。

文字列型

CHAR

固定長の文字列を格納します。指定された長さに満たない場合、空白で埋められます。

VARCHAR

可変長の文字列を格納します。指定された最大長までの文字列を格納できます。

JSON

JSON形式のデータを格納します。

NCHAR

固定長の国際化文字列を格納します。CHARと同様ですが、国際化文字セットを使用します。

NVARCHAR

可変長の国際化文字列を格納します。VARCHARと同様ですが、国際化文字セットを使用します。

テキスト型

TINYTEXT

非常に小さいテキストを格納します。

TEXT

テキストデータを格納します。

MEDIUMTEXT

中程度の長さのテキストを格納します。

LONGTEXT

非常に長いテキストを格納します。

その他の型

BIT

ビット値を格納します。フラグや状態を表すのに便利です。

BOOLEAN

真偽値を格納します。実際には TINYINT(1) のエイリアスです。

ENUM

事前に定義されたリストから選択された文字列を格納します。

SET

事前に定義されたリストから0個以上選択された文字列を格納します。

これらのデータ型を理解し適切に使い分けることで、データベースの効率的な設計と運用が可能になります。

Default

カラムのデフォルト値を指定します。レコード挿入時に値が指定されなかった場合、このデフォルト値が使用されます。これは厳密には制約ではありませんが、データの整合性を保つためによく使用されます。

SQL

CREATE TABLE users (created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

Comments

カラムに関する説明を追加できます。このコメントは、データベースのメタデータとして格納され、後で参照できます。

SQL

ADD COLUMN age INT COMMENT 'Applicant age';

Constraints（制約）

MySQLにおけるカラムに指定可能な制約（Constraints）は、データの整合性を維持するためのルールや条件を設定することを可能にします。

Primary Key

テーブル内の各レコードを一意に識別するためのカラム（またはカラムの組）に設定します。PRIMARY KEY制約は、自動的にNOT NULL制約を含み、その値はテーブル内で一意でなければなりません。

SQL

CREATE TABLE users (id INT PRIMARY KEY);

Not Null

この制約が設定されたカラムは、NULL値を受け付けません。つまり、そのカラムには必ず何らかの値が格納される必要があります。

SQL

CREATE TABLE users (id INT NOT NULL);

Unique

同じテーブル内で、この制約が設定されたカラムの値はユニーク（一意）でなければなりません。つまり、重複する値を持つレコードを挿入することはできません。

SQL

CREATE TABLE users (email VARCHAR(255) UNIQUE);

Binary

BINARY属性は、文字列型のデータに適用されます。これにより、データはバイナリの正確な比較が可能になる二進形式で格納されます。例えば、VARCHAR型やCHAR型のカラムにBINARYを指定することで、大文字と小文字を区別するバイナリ比較が行われます。

SQL

CREATE TABLE example (name CHAR(10) BINARY);

Unsigned

UNSIGNED属性は、数値型のデータ（例えば、INT、FLOATなど）に適用され、負の数を格納できないようにします。これにより、その型が扱える正の数値の範囲が拡大します。

SQL

CREATE TABLE example (age INT UNSIGNED);

Zero Fill

ZEROFILLは、数値型のカラムに対して使用されます。これを指定すると、格納された値がカラムの表示幅に達しない場合、残りのスペースをゼロで埋めます。ZEROFILLを指定すると自動的にUNSIGNEDが適用されます。

SQL

CREATE TABLE example (id INT(4) ZEROFILL);

この例では、idが100の場合、0100と表示されます。

Auto Increment

整数型のカラムに対して使用でき、レコードが追加されるたびに自動で値が 1ずつ増加します。通常、主キーとして設定されたカラムで使用されます。

SQL

ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;

Generated

GENERATED属性を使用して、仮想カラムまたは格納カラムを定義します。これは、他のカラムの値から計算される値を持つカラムを作成するために使用されます。仮想カラムは、アクセスされるたびにオンザフライで値が計算されるのに対し、格納カラムはテーブルに物理的に値が格納されます。

SQL

CREATE TABLE example (a INT, b INT, c INT GENERATED ALWAYS AS (a + b) STORED);

これらの属性を適用することで、データの格納方法、表示方法、および計算方法を細かく制御することができます。これにより、データベースの柔軟性と効率性が向上し、アプリケーションの要件に合わせた最適なデータ構造を実現することが可能になります。

Storage

MySQLでは、GENERATEDカラムを定義する際に VIRTUAL または STORED のキーワードを使用して、そのカラムが仮想か格納されるかを指定できます。これらは計算された値を持つカラム（Generated Columns）に関連する概念で、データベース内の他のカラムの値から動的に計算される値を格納する方法を提供します。

VIRTUAL Generated Columns

VIRTUALキーワードを使用した Generated Columnは、クエリが実行されるたびにオンザフライで値が計算されるカラムです。物理的にディスク上に格納されることはなく、テーブルの行が読み込まれるたびに計算されます。そのため、ディスクスペースを節約できますが、計算には毎回コスト（パフォーマンスコスト）がかかります。

SQL

CREATE TABLE example (a INT, b INT, sum INT AS (a + b) VIRTUAL);

STORED Generated Columns

STOREDキーワードを使用した Generated Columnは、テーブルに行が挿入または更新されるときに計算され、その結果がディスク上に物理的に格納されるカラムです。一度計算された値は、関連する他のカラムの値が変更されない限り、再計算されることはありません。これにより、クエリのパフォーマンスが向上しますが、追加のディスクスペースを消費します。

SQL

CREATE TABLE example (a INT, b INT, sum INT AS (a + b) STORED);

選択基準

• ディスクスペースの節約が優先される場合や、計算結果が頻繁にはアクセスされない場合は、VIRTUALカラムが適しています。
• クエリのパフォーマンスが最優先事項であり、計算結果へのアクセスが頻繁に発生する場合は、STOREDカラムが適しています。