MySQL - テーブル

提供: MochiuWiki : SUSE, EC, PCB

概要

MySQLのテーブルは、リレーショナルデータベースにおいてデータを格納する基本的な構造単位である。
テーブルは、行 (レコード) と列 (カラム) で構成されたデータの集合であり、各カラムにはデータ型と制約が定義される。

MySQLにおけるテーブルの主要な操作には、テーブルの作成、変更、削除、レコードの挿入・更新・削除がある。
また、テーブルの構造を確認したり、テーブル一覧を取得することも可能である。

テーブルは、ストレージエンジンによって管理される。
MySQLでは、InnoDB、MyISAM、MEMORY、CSV、ARCHIVE等の複数のストレージエンジンが利用可能である。
MySQL 5.5以降、InnoDBがデフォルトのストレージエンジンとして設定されている。

InnoDBは、トランザクション対応 (ACID準拠)、行レベルロック、外部キーサポートを提供し、本番環境での使用に適している。
MyISAMは、トランザクション未対応でテーブルレベルロックを使用するが、読み取り専用または読み取り中心のアプリケーションに適している。
MEMORYストレージエンジンは、データをメモリ内に格納するため高速であるが、サーバ再起動時にデータは消失する。

テーブルには、文字コードと照合順序を設定することができ、多言語対応や大文字小文字の区別等の挙動を制御できる。
また、制約 (NOT NULL、PRIMARY KEY、FOREIGN KEY、UNIQUE、CHECK) を設定することにより、データの整合性を保つことができる。

MySQLでは、通常のテーブルの他に、一時テーブル (TEMPORARY TABLE) を作成することもできる。
一時テーブルは、現在のセッション内でのみ可視であり、セッション終了時に自動的に削除される。


テーブルの作成

基本構文

テーブルを作成するには、CREATE TABLE文を使用する。

 CREATE TABLE table_name (
    column1 datatype [constraint],
    column2 datatype [constraint],
    ...,
    table_level_constraint
 ) table_options;


主要なカラムオプション
オプション 説明
NOT NULL NULL値を禁止する
DEFAULT デフォルト値を設定する
AUTO_INCREMENT 自動インクリメント値を生成する
UNIQUE 一意性を保証する
PRIMARY KEY 主キーを定義する
CHECK 条件を指定してデータを検証する (MySQL 8.0.16以降)
COMMENT カラムにコメントを付与する
VISIBLE / INVISIBLE カラムの可視性を制御する (MySQL 8.0以降)


主要なテーブルオプション
オプション 説明
ENGINE ストレージエンジンを指定する。
DEFAULT CHARSET デフォルトの文字コードを指定する。
DEFAULT COLLATE デフォルトの照合順序を指定する。
COMMENT テーブルにコメントを付与する。(最大2048文字)
AUTO_INCREMENT AUTO_INCREMENT値の初期値を設定する。


例として、基本的なテーブル作成の構文を以下に示す。

 CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    age INT CHECK (age >= 18)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


ストレージエンジンの指定

MySQLでは、テーブルごとにストレージエンジンを指定できる。

下表に、主要なストレージエンジンを示す。

主要ストレージエンジンの比較
ストレージエンジン トランザクション ロック方式 外部キー 全文検索 用途
InnoDB 対応 (ACID準拠) 行レベル サポート MySQL 5.6以降 本番環境、トランザクション処理
MyISAM 未対応 テーブルレベル 未サポート サポート 読み取り専用、読み取り中心
MEMORY 未対応 テーブルレベル 未サポート 未サポート 高速一時データ、キャッシュ
CSV 未対応 テーブルレベル 未サポート 未サポート CSVファイル形式でのストレージ
ARCHIVE 未対応 行レベル 未サポート 未サポート 圧縮ストレージ、ログデータ


InnoDBは、MySQL 5.5以降でデフォルトのストレージエンジンとして設定されている。
InnoDBは、トランザクション対応、行レベルロック、外部キー制約、クラッシュリカバリ機能を提供する。

ストレージエンジンを指定する構文を以下に示す。

 CREATE TABLE t1 (
    id INT PRIMARY KEY,
    data VARCHAR(100)
 ) ENGINE=InnoDB;

 CREATE TABLE t2 (
    id INT PRIMARY KEY,
    cache_data VARCHAR(255)
 ) ENGINE=MEMORY;


既存テーブルのストレージエンジンを変更することも可能である。

 ALTER TABLE table_name ENGINE=InnoDB;


既存のテーブルの存在を確認して作成

既存のテーブルが存在していない場合はテーブルを作成する。
これは、テーブルの重複、または、既存のテーブルを上書き / 削除することを防ぐことができる。

 CREATE TABLE IF NOT EXISTS table_name (
    column1 datatype,
    column2 datatype
 );


文字コードと照合順序を指定した構文を以下に示す。

 CREATE TABLE IF NOT EXISTS users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
 ) ENGINE=InnoDB
   DEFAULT CHARSET=utf8mb4
   COLLATE=utf8mb4_general_ci;


IF NOT EXISTS 句を指定すると、テーブルが既に存在する場合はエラーにならず、警告のみが出力される。

テーブルのクローニングおよびコピー

CREATE TABLE ... LIKE文、または、CREATE TABLE ... AS SELECT文を使用して、
元のテーブルに定義されているカラム属性やインデックス等、別のテーブルの定義に基づいて空のテーブルを作成する。

 CREATE TABLE new_table LIKE original_table;

 -- または

 CREATE TABLE new_table AS SELECT * FROM original_table;


下表に、CREATE TABLE ... LIKE文とCREATE TABLE ... AS SELECT文の違いを示す。

CREATE TABLE ... LIKE と CREATE TABLE ... AS SELECT の比較
項目 CREATE TABLE ... LIKE CREATE TABLE ... AS SELECT
データのコピー コピーしない (空のテーブル) コピーする
インデックス コピーする 主キーのみコピー
外部キー制約 コピーする コピーしない
DEFAULT値 コピーする コピーしない
AUTO_INCREMENT コピーする コピーしない


テーブル構造のみをコピーして、その後でデータを挿入する場合は、以下のようにする。

 CREATE TABLE new_table LIKE original_table;
 INSERT INTO new_table SELECT * FROM original_table;



一時テーブル

一時テーブルの作成

MySQLでは、一時テーブルは現在のセッション内でのみ表示され、セッションがクローズされると自動的に削除される。
つまり、複数の異なるセッションが同じ一時テーブル名を使用することができ、互いに、同じ名前の既存の一時テーブル以外のテーブルと競合することはない。
(既存のテーブルは、一時テーブルが削除されるまで非表示になる)

  • 一時テーブルを作成する構文 
     CREATE TEMPORARY TABLE temp_table_name (
    column1 datatype,
    column2 datatype
 );
 
 -- または
 
 CREATE TEMPORARY TABLE IF NOT EXISTS temp_table_name (
    column1 datatype,
    column2 datatype
 );

  • 一時テーブルの使用例 
     CREATE TEMPORARY TABLE temp_results (
    user_id INT,
    total_amount DECIMAL(10,2)
 );
 
 INSERT INTO temp_results
 SELECT user_id, SUM(amount)
 FROM orders
 GROUP BY user_id;
 
 -- 一時テーブルを使用した処理
 SELECT * FROM temp_results WHERE total_amount > 1000;
 
 -- セッション終了時に自動的に削除される


一時テーブルの特徴と注意事項

一時テーブルの主要な特徴
特徴 説明
セッションスコープ
  • 一時テーブルは、作成したセッション内でのみ可視である
  • 他のセッションからは参照できない
自動削除
  • セッション終了時に自動的に削除される
  • 明示的に削除する場合は、DROP TEMPORARY TABLE文を使用する
名前の競合
  • 一時テーブルと同名の通常テーブルが存在する場合、一時テーブルが優先される
  • 一時テーブルが削除されるまで、通常テーブルは非表示になる
データベース依存性
  • 一時テーブルは、データベース (スキーマ) と疎な関係を持つ
  • データベースを削除しても、一時テーブルは自動的には削除されない
権限
  • CREATE TEMPORARY TABLES権限が必要
  • 作成後は、DROP TABLE、INSERT、UPDATE、SELECT等の操作が可能


InnoDBにおける一時テーブルの制約
制約 説明
圧縮一時テーブルは未サポート
  • innodb_strict_modeが有効 (デフォルト) な場合、
    ROW_FORMAT=COMPRESSEDまたはKEY_BLOCK_SIZEを指定するとエラーになる。
  • innodb_strict_modeが無効な場合は、警告が出力され、圧縮されていない行形式で作成される。
innodb_file_per_tableオプション InnoDB一時テーブルの作成には影響しない。


MySQL 8.0では、TempTableストレージエンジンがデフォルトの内部一時テーブル用エンジンとして使用される。

temptable_max_ram システム変数 (デフォルト1[GiB]) により、メモリ使用量を制御できる。
メモリ超過時は、メモリマップファイルまたはInnoDBオンディスクに自動的に遷移する。

トランザクションと一時テーブル

一時テーブルとトランザクションの関係には注意が必要である。

START TRANSACTION 文 または BEGIN 文の中において、一時テーブルを作成後に ROLLBACK 文を実行する場合、
一時テーブル自体は削除されないが、レコードは全て削除される。
つまり、一時テーブルを作成する CREATE TEMPORARY TABLE 文は、ROLLBACK 文の対象にならず、レコードを複製する箇所のみがロールバックされる。

動作例を以下に示す。

 START TRANSACTION;

 CREATE TEMPORARY TABLE temp_data (
    id INT,
    value VARCHAR(100)
 );
 
 INSERT INTO temp_data VALUES (1, 'test');
 
 ROLLBACK;
 
 -- 一時テーブル自体は存在するが、レコードは削除される
 SELECT * FROM temp_data;  -- 結果: 0行



テーブルの確認

テーブル一覧の確認

  • 現在使用しているデータベースのテーブル一覧を確認する。 
     USE database_name;
 SHOW TABLES;

  • 特定のデータベースのテーブル一覧を確認する。 
     SHOW TABLES FROM database_name;

  • パターンマッチングを使用してテーブルを検索する。 
     SHOW TABLES LIKE 'user%';

  • INFORMATION_SCHEMA データベースの TABLES テーブルから、詳細情報を取得する。

*:

 SELECT TABLE_NAME, ENGINE, TABLE_ROWS, TABLE_COLLATION
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'database_name';


テーブル構造の確認

テーブルの構造を確認するには、DESCRIBE 文 または SHOW CREATE TABLE 文を使用する。

DESCRIBE文は、カラム名、型、NULL許可、Key、Default、Extraの情報を表示する。

 DESCRIBE table_name;
 
 -- 省略形
 DESC table_name;


特定のカラムのみを確認する場合は、以下のようにする。

 DESCRIBE table_name column_name;


SHOW CREATE TABLE 文は、完全な CREATE TABLE 文を表示する。

 SHOW CREATE TABLE table_name;


この構文は、テーブルの完全な定義 (インデックス、外部キー制約、ストレージエンジン、文字コード等) を確認する場合に便利である。

詳細なカラム情報を確認する場合は、SHOW FULL COLUMNS 文を使用する。

 SHOW FULL COLUMNS FROM table_name;


SHOW FULL COLUMNS 文は、照合順序、コメント、権限情報を含む詳細情報を表示する。

テーブルのステータス確認

テーブルのステータス情報を確認するには、SHOW TABLE STATUS 文を使用する。

 SHOW TABLE STATUS LIKE 'table_name';


SHOW TABLE STATUS 文の出力には、以下に示す情報が含まれる。

SHOW TABLE STATUS文の主な出力項目
項目 説明
Name テーブル名
Engine ストレージエンジン
Version テーブルフォーマットバージョン
Row_format 行フォーマット (Dynamic、Fixed、Compressed等)
Rows 行数の推定値
Avg_row_length 平均行長
Data_length データファイルのサイズ (バイト)
Index_length インデックスファイルのサイズ (バイト)
Collation デフォルトの照合順序
Create_time テーブル作成時刻
Update_time 最終更新時刻
Comment テーブルコメント


特定のデータベースの全テーブルのステータスを確認する場合は、以下のようにする。

 SHOW TABLE STATUS FROM database_name;



テーブルの変更

テーブル名の変更

テーブル名を変更するには、RENAME TABLE 文を使用する。

 RENAME TABLE old_name TO new_name;


複数のテーブルを同時にリネームすることも可能である。
この構文は、テーブル名を入れ替える場合に便利である。
複数のリネーム操作は、アトミックに実行される。

 RENAME TABLE t1 TO temp, t2 TO t1, temp TO t2;


データベース間でテーブルを移動することもできる。

 RENAME TABLE db1.table_name TO db2.table_name;


一時テーブル (TEMPORARY TABLE) には、RENAME TABLE 文を使用できないことに注意する。
一時テーブルをリネームする場合は、ALTER TABLE 文を使用する。

 ALTER TABLE temp_table RENAME TO new_temp_table;


AUTO_INCREMENTの設定

AUTO_INCREMENT 属性は、整数カラムに対して自動的に増分値を生成する。

テーブル作成時に AUTO_INCREMENT の初期値を設定する構文を以下に示す。

 CREATE TABLE table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(100)
 ) AUTO_INCREMENT = 1000;


既存テーブルの AUTO_INCREMENT 値を変更する構文を以下に示す。

 ALTER TABLE table_name AUTO_INCREMENT = 1;


現在の AUTO_INCREMENT 値を確認する場合は、以下のようにする。

 SELECT AUTO_INCREMENT
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'database_name'
   AND TABLE_NAME = 'table_name';


MySQL 8.0では、AUTO_INCREMENT 値がREDOログに永続化されるため、サーバ再起動後もリセットされない。
MySQL 5.7以前では、サーバ再起動後に最大値+1にリセットされる動作であった。

InnoDBの AUTO_INCREMENT 動作を制御するシステム変数として、innodb_autoinc_lock_mode がある。

  • 0 (Traditional)
    テーブルレベルのAUTO_INCロックを使用
  • 1 (Consecutive)
    単純なINSERTでは軽量ロック、バルクINSERTではテーブルレベルロック
  • 2 (Interleaved)
    ロックなし (MySQL 8.0のデフォルト)


テーブルコメント

テーブルにコメントを付与することで、テーブルの目的や仕様を文書化できる。

テーブル作成時にコメントを付与する構文を以下に示す。

 CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
 ) COMMENT = 'ユーザー情報を管理するテーブル';


テーブルコメントは、最大2048文字まで設定可能である。
カラムコメントは、最大1024文字まで設定可能である。

カラムにコメントを付与する構文を以下に示す。

 CREATE TABLE products (
    product_id INT PRIMARY KEY COMMENT '商品ID',
    product_name VARCHAR(100) COMMENT '商品名',
    price DECIMAL(10,2) COMMENT '価格 (税込)'
 );


既存テーブルのコメントを変更する場合は、以下のようにする。

 ALTER TABLE table_name COMMENT = '新しいコメント';


コメントを確認する方法を以下に示す。

 -- テーブルコメントを確認
 SHOW CREATE TABLE table_name;
 
 -- または
 
 SELECT TABLE_COMMENT
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'database_name'
   AND TABLE_NAME = 'table_name';
 
 -- カラムコメントを確認
 SELECT COLUMN_NAME, COLUMN_COMMENT
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = 'database_name'
   AND TABLE_NAME = 'table_name';



文字コードと照合順序

テーブルの文字コード

テーブルの作成時において、文字コードを指定する。

 CREATE TABLE table_name (
    column1 VARCHAR(255),
    column2 TEXT
 ) CHARACTER SET utf8mb4;
 
 -- または、特定のカラムに文字コードを指定
 
 CREATE TABLE table_name (
    column1 VARCHAR(255) CHARACTER SET utf8mb4,
    column2 TEXT
 );


MySQLのデフォルトの文字コードを確認する。

 SHOW VARIABLES LIKE 'chara%';


MySQLのデフォルトの文字コードを設定する場合は、my.cnfファイルを編集する。
my.cnfファイルを変更した後は、MySQLを再起動する必要がある。

 # my.cnfファイル
 
 [mysqld]
 character-set-server=utf8mb4   # MySQLサーバのデフォルトの文字コードを指定する
 
 [client]
 default-character-set=utf8mb4  # MySQLクライアントのデフォルトの文字コードを指定する


既存のテーブルの文字コードは、INFORMATION_SCHEMA データベースの TABLES テーブルから確認することができる。
TABLE_COLLATION カラムの値が、そのテーブルに設定された文字コードに相当する。

 SELECT TABLE_NAME, TABLE_COLLATION
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'database_name';


既存のテーブルの文字コードを変更する。

 ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4;


照合順序とは

MySQLにおいて、照合順序 (Collation) はテーブルや列ごとに設定することができる。
照合順序は、文字列の比較やソートの際に使用され、異なる言語や地域に合わせてテキストの比較を行うために重要である。

MySQLでは、様々な照合順序が提供されている。
例えば、大文字小文字を区別やアクセントを考慮するかどうか等の違いがある。

下表に、代表的な照合順序を示す。

代表的な照合順序
照合順序 説明
utf8mb4_general_ci
  • 大文字小文字を区別せず、アクセントを区別しない
  • 通常の比較の場合には適しているが、文字の並び順は一般的な場合に従う
utf8mb4_unicode_ci
  • Unicode標準 (UCA) に基づいた比較を行う
  • utf8mb4_general_ciよりも正確なソート結果を返すが、やや低速である
utf8mb4_unicode_520_ci
  • Unicode 5.2.0標準に基づいた比較を行う
  • utf8mb4_unicode_ciよりも新しいUnicode規則に対応する
utf8mb4_0900_ai_ci
  • Unicode 9.0標準に基づいた比較を行う
  • MySQL 8.0のデフォルトの照合順序である
  • ai (Accent Insensitive)、ci (Case Insensitive) を意味する
utf8mb4_0900_as_cs
  • Unicode 9.0標準に基づいた比較を行う
  • as (Accent Sensitive)、cs (Case Sensitive) を意味する
  • 大文字小文字およびアクセントを区別する
utf8mb4_0900_as_ci
  • Unicode 9.0標準に基づいた比較を行う
  • アクセントを区別するが、大文字小文字は区別しない
utf8mb4_ja_0900_as_cs
  • 日本語向けの照合順序である (MySQL 8.0以降)
  • JIS X 4061規格に基づいた日本語のソート順を提供する
utf8mb4_ja_0900_as_cs_ks
  • 日本語向けの照合順序であり、カナの区別 (ks: Kana Sensitive) を行う
  • ひらがなとカタカナを区別して比較する
utf8mb4_bin
  • バイナリ比較を行う
  • 大文字小文字やアクセント等を区別して、バイト単位で比較する
  • これは厳密なバイナリ比較が必要な場合に使用される
utf8_general_ci
  • utf8mb4と同様、大文字小文字を区別せず、アクセントを区別しない
  • ただし、utf8mb4よりも少ない文字セットを使用する (3バイトUTF-8)
latin1_swedish_ci
  • ラテン1文字セットに基づいた照合順序であり、スウェーデンの標準に従う
  • 大文字小文字を区別せず、アクセントを区別しない
latin1_bin
  • ラテン1文字セットのバイナリ比較を行う
  • 大文字小文字およびアクセントを区別する
binary
  • 全てのデータをバイト列として比較する
  • 文字セットに依存しない厳密なバイナリ比較を行う


照合順序の詳細を知りたい場合は、MySQLの公式ドキュメントを参照すること。


使用できる照合順序の確認

使用できる文字コードと照合順序の組み合わせは、INFORMATION_SCHEMA データベースの COLLATION_CHARACTER_SET_APPLICABILITY テーブルから確認できる。
利用できる照合順序がMySQLのバージョンによって異なるため、どのような照合順序が使用できるかどうかを確認する必要がある。

 SELECT COLLATION_NAME, CHARACTER_SET_NAME
 FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
 WHERE COLLATION_NAME = 'collation_name';


例として、照合順序にutf8mb4_general_ciが使用できるかどうかを確認する。

 SELECT COLLATION_NAME, CHARACTER_SET_NAME
 FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
 WHERE COLLATION_NAME = 'utf8mb4_general_ci';


利用可能な全ての照合順序を一覧表示する場合は、以下のようにする。

 SHOW COLLATION;
 
 -- 特定の文字セットの照合順序のみを表示
 SHOW COLLATION WHERE Charset = 'utf8mb4';


カラムの文字コードと照合順序の確認

カラムに設定された文字コードと照合順序を確認する場合は、INFORMATION_SCHEMA.COLUMNS テーブルを使用する。

 SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = 'database_name'
   AND TABLE_NAME = 'table_name';


特定のカラムのみを確認する場合は、WHERE 句で条件を追加する。

 SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = 'database_name'
   AND TABLE_NAME = 'table_name'
   AND COLUMN_NAME LIKE 'column%';


テーブル作成時における文字コードと照合順序の指定

テーブル作成時において、文字コードと照合順序を指定する構文を以下に示す。

 CREATE TABLE table_name (
    column1 VARCHAR(255) COLLATE utf8mb4_unicode_ci,
    column2 INT
 ) CHARACTER SET utf8mb4
   COLLATE utf8mb4_general_ci;


カラムごとに異なる照合順序を指定することも可能である。

 CREATE TABLE table_name (
    column1 VARCHAR(255) COLLATE utf8_general_ci,
    column2 INT,
    column3 TEXT COLLATE utf8_unicode_ci
 );


テーブルに照合順序を指定した場合、カラムに照合順序が指定されていない場合は、テーブルの照合順序が継承される。
テーブルにも照合順序が指定されていない場合は、データベースのデフォルトの照合順序が継承される。

既存のテーブルやカラムの文字コードと照合順序の変更

既存のテーブルやカラムの照合順序を変更することも可能である。

テーブルの照合順序の変更

テーブルの照合順序を変更する。
ただし、各カラムの照合順序は変更されないことに注意する。

 ALTER TABLE table_name
 CHARACTER SET utf8mb4
 COLLATE utf8mb4_general_ci;


この構文は、テーブルのデフォルト照合順序のみを変更し、既存カラムの照合順序は変更しない。
新しく追加されるカラムには、変更後の照合順序が適用される。

全ての文字カラムの照合順序の変更

全ての文字カラムの文字コードおよび照合順序を変更する。
これは、テーブルの文字コードと各カラムの文字コードを揃える時に使用すると便利である。

 ALTER TABLE table_name
 CONVERT TO CHARACTER SET utf8mb4
 COLLATE utf8mb4_general_ci;


CONVERT TO CHARACTER SET 句を使用すると、既存の全ての文字カラムの文字コードと照合順序が一括変更される。
データの変換も自動的に行われる。

任意のカラムの照合順序の変更

特定のカラムのみの照合順序を変更する場合は、以下のようにする。

 ALTER TABLE table_name
 MODIFY column_name VARCHAR(255) COLLATE utf8mb4_unicode_ci;


MODIFY 句を使用する場合は、カラムの型も再指定する必要がある。

デフォルトの照合順序

テーブルにおけるデフォルトの照合順序を確認する場合、
テーブル自体に照合順序が指定されていない場合、テーブルはデータベースのデフォルトの照合順序を継承する。

テーブルのデフォルト照合順序を確認する。

 SHOW TABLE STATUS LIKE '<テーブル名>';


これは、テーブルのステータス情報を表示する。
出力結果において、Collation カラムの値がテーブルの照合順序である。

もし、テーブルに照合順序が指定されていない場合、データベースのデフォルトの照合順序が表示される。

データベースのデフォルト照合順序を確認する場合は、以下のようにする。

 SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
 FROM INFORMATION_SCHEMA.SCHEMATA
 WHERE SCHEMA_NAME = 'database_name';


サーバ全体のデフォルト照合順序を確認する場合は、以下のようにする。

 SHOW VARIABLES LIKE 'collation%';



制約

NOT NULL

NOT NULL 制約は、カラムにNULL値を禁止する。

 CREATE TABLE product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(16) NOT NULL,
    price INT
 );


NOT NULL 制約が設定されたカラムには、必ず値を挿入する必要がある。
NULL値を挿入しようとすると、エラーが発生する。

既存カラムにNOT NULL 制約を追加する場合は、以下のようにする。

 ALTER TABLE table_name
 MODIFY column_name datatype NOT NULL;


CHECK

CHECK 制約は、条件を指定して、条件を満たさないデータを禁止する。
CHECK 制約は、MySQL 8.0.16以降でサポートされている。

 CREATE TABLE user (
    user_id INT PRIMARY KEY,
    age INT,
    city_code CHAR(4),
    gender CHAR(1),
    CONSTRAINT agecity_check CHECK (age >= 18 OR city_code = '0003'),
    CONSTRAINT gendercheck CHECK (gender IN ('M', 'F'))
 );


CHECK 制約は、テーブルレベルまたはカラムレベルで指定できる。

 CREATE TABLE products (
    product_id INT PRIMARY KEY,
    price DECIMAL(10,2) CHECK (price > 0),
    discount_rate DECIMAL(3,2) CHECK (discount_rate BETWEEN 0 AND 1)
 );


UNIQUE KEY

UNIQUE 制約は、カラムに重複したデータを禁止する。
UNIQUE 制約の特徴を以下に示す。

  • 重複したデータを禁止する。
  • 複数のカラムに設定できる。
  • NULL値は禁止しない。(NULL値は複数許可される)


 CREATE TABLE user (
    user_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    telephone CHAR(8),
    address VARCHAR(255),
    UNIQUE (telephone, address)
 );


複数のカラムに対してUNIQUE 制約を設定した場合、それらのカラムの組み合わせが一意である必要がある。

既存テーブルに UNIQUE 制約を追加する場合は、以下のようにする。

 ALTER TABLE table_name
 ADD UNIQUE (column_name);
 
 -- または、複数カラムに対して
 
 ALTER TABLE table_name
 ADD UNIQUE (column1, column2);


PRIMARY KEY

PRIMARY KEY 制約は、テーブルの主キーを定義する。
PRIMARY KEY 制約の特徴を以下に示す。

  • 一意性を保証する
  • 重複とNULL値を禁止する
  • 1つのテーブルに1つしか指定できない
  • 自動的にインデックスが作成される


 CREATE TABLE user (
    user_id INT PRIMARY KEY,
    telephone CHAR(8),
    age INT
 );


複合主キーも指定できる。

 CREATE TABLE price (
    shop_code CHAR(4),
    product_code CHAR(4),
    price INT,
    PRIMARY KEY (shop_code, product_code)
 );


既存テーブルに主キーを追加する場合は、以下のようにする。

 ALTER TABLE table_name
 ADD PRIMARY KEY (column_name);


FOREIGN KEY

FOREIGN KEY 制約は、他のテーブルのカラムを参照して、そのカラムに存在しないデータを禁止する。
外部キー制約により、参照整合性を保つことができる。

 CREATE TABLE order_table (
    order_id INT PRIMARY KEY,
    shop_code CHAR(5),
    product_code CHAR(4),
    number INT,
    date DATE,
    FOREIGN KEY (shop_code) REFERENCES shop_table (shop_code),
    FOREIGN KEY (product_code) REFERENCES product_table (product_code)
 );


複数のカラムを外部参照することもできる。

 CREATE TABLE item (
    maker_id CHAR(10),
    product_id CHAR(10),
    PRIMARY KEY (maker_id, product_id)
 );
 
 CREATE TABLE dealer (
    shop_id CHAR(10) NOT NULL,
    maker_id CHAR(10) NOT NULL,
    product_id CHAR(10) NOT NULL,
    PRIMARY KEY (shop_id, maker_id, product_id),
    FOREIGN KEY (maker_id, product_id) REFERENCES item (maker_id, product_id)
 );


外部キー制約には、参照動作を指定できる。

 CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
       ON DELETE CASCADE
       ON UPDATE CASCADE
 );


下表に、参照動作のオプションを示す。

参照動作のオプション
オプション 説明
CASCADE 親テーブルの行が削除 / 更新されると、子テーブルの行も削除 / 更新される。
SET NULL 親テーブルの行が削除 / 更新されると、子テーブルの外部キーカラムがNULLに設定される。
RESTRICT 子テーブルに参照行が存在する場合、親テーブルの削除 / 更新を禁止する。(デフォルト)
NO ACTION RESTRICTと同じ動作
SET DEFAULT MySQLでは未サポート


制約の名前を設定

制約には、名前を付けることができる。
制約名を指定すると、後で制約を削除する際に便利である。

 CREATE TABLE table_name (
    column1 datatype,
    CONSTRAINT constraint_name constraint_definition
 );


例として、制約名を指定した構文を以下に示す。

 CREATE TABLE user (
    user_id INT PRIMARY KEY,
    age INT,
    gender CHAR(1),
    CONSTRAINT age_check CHECK (age >= 18),
    CONSTRAINT gender_check CHECK (gender IN ('M', 'F'))
 );


制約の削除

制約を削除するには、ALTER TABLE 文を使用する。

 ALTER TABLE table_name DROP CONSTRAINT constraint_name;


  • 主キーを削除する場合 
     ALTER TABLE table_name DROP PRIMARY KEY;

  • 外部キーを削除する場合 
     ALTER TABLE table_name DROP FOREIGN KEY constraint_name;

  • UNIQUE 制約を削除する場合 
     ALTER TABLE table_name DROP INDEX constraint_name;


デフォルト値の設定

カラムにデフォルト値を設定することで、値が指定されない場合に自動的に設定される値を定義できる。

 CREATE TABLE product (
    product_id INT NOT NULL PRIMARY KEY,
    name CHAR(16) UNIQUE,
    price INT DEFAULT 2000,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );


デフォルト値には、リテラル値や式を指定できる。

 CREATE TABLE users (
    user_id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 );


  • 既存カラムにデフォルト値を追加する場合 
     ALTER TABLE table_name
 ALTER COLUMN column_name SET DEFAULT default_value;

  • デフォルト値を削除する場合 
     ALTER TABLE table_name
 ALTER COLUMN column_name DROP DEFAULT;



テーブルの削除

テーブルを削除するには、DROP TABLE 文を使用する。
DROP TABLE 文により削除したテーブルは、復元することができないことに注意する。

 DROP TABLE table_name;


複数のテーブルを一括して削除する場合、カンマ区切りでテーブル名を列挙する。

 DROP TABLE table1, table2, table3;


テーブルが存在しない場合にエラーを発生させないようにするには、IF EXISTS 句を使用する。

 DROP TABLE IF EXISTS table_name;


一時テーブルを削除する場合は、DROP TEMPORARY TABLE 文を使用する。

 DROP TEMPORARY TABLE temp_table_name;


DROP TABLE 文は、テーブルの構造とデータの両方を削除する。
データのみを削除してテーブル構造を残す場合は、DELETE 文 または TRUNCATE
文を使用する。


レコードの削除

DELETE文

DELETE 文は、テーブル内のレコードを削除するクエリである。

 DELETE FROM table_name;


WHERE 句で条件を指定しない場合、テーブル内の全てのデータが削除される。
特定のレコードのみを削除する場合は、必ずWHERE 句で条件指定をする。

 DELETE FROM table_name WHERE column_name = value;


例として、特定の条件を満たすレコードを削除する構文を以下に示す。

 DELETE FROM users WHERE user_id = 123;
 
 DELETE FROM orders WHERE order_date < '2023-01-01';
 
 DELETE FROM products WHERE price < 100 AND stock = 0;


LIMIT 句を使用して、削除するレコード数を制限することもできる。

 DELETE FROM table_name WHERE condition LIMIT 10;


ORDER BY 句と組み合わせることで、特定の順序で削除することもできる。

 DELETE FROM table_name WHERE condition ORDER BY column_name LIMIT 10;


TRUNCATE文

TRUNCATE 文は、テーブル内のレコードを完全に削除するクエリである。

TRUNCATE 文は、DELETE 文とは異なり、WHERE 句での条件指定ができない。
そのため、テーブル内のレコードは必ず全て削除される。

 TRUNCATE TABLE table_name;


TRUNCATE 文は、条件を指定してデータの削除ができないことに注意する。

TRUNCATE 文は、DELETE 文よりも高速に動作する。
これは、TRUNCATE 文がテーブル全体を削除して再作成するためである。

DELETE文とTRUNCATE文の違い

DELETE 文 と TRUNCATE 文の違いを以下に示す。

DELETE文とTRUNCATE文の比較
特性 DELETE TRUNCATE
WHERE句 サポート 未サポート
トランザクション ROLLBACK可能 DDL的操作 (ROLLBACK不可)
AUTO_INCREMENT リセットなし リセット
トリガー 発火する 発火しない
ログ 行ごとのログ DDLログ
実行速度 低速 (行ごとに削除) 高速 (テーブル再作成)
必要権限 DELETE DROP
外部キー制約 チェックする チェックする
ディスク領域 即座に解放されない 即座に解放される


DELETE 文は、トランザクション内で使用でき、ROLLBACK で取り消すことができる。
TRUNCATE 文は、DDL操作であり、トランザクションの対象外である。

TRUNCATE 文は、AUTO_INCREMENT カウンタをリセットするが、DELETE 文はリセットしない。

外部キー制約が設定されているテーブルに対して TRUNCATE 文を実行すると、エラーが発生する場合がある。
この場合は、外部キー制約を一時的に無効化するか、DELETE 文を使用する。


インポートとエクスポート

CSVファイルのインポート

CSVファイルをテーブルにインポートするには、LOAD DATA INFILE 文を使用する。

以下に示すようなCSVファイルがあるとする。

 "1001","2024-01-05 09:10:00","0"
 "1002","2024-02-04 10:05:00","1"
 "1003","2024-02-23 21:27:00","0"


CSVファイルと合致するようなテーブルがあるとする。

 CREATE TABLE import_table (
    ID INT,
    F_DATE TIMESTAMP,
    FLAG INT,
    PRIMARY KEY (ID)
 );


CSVファイルをテーブルにインポートする。

 LOAD DATA LOCAL INFILE '/path/to/file.csv'
 INTO TABLE import_table
 FIELDS TERMINATED BY ','
 ENCLOSED BY '"'
 LINES TERMINATED BY '\n';


下表に、LOAD DATA INFILE 文の主要なオプションを示す。

LOAD DATA INFILE文の主要なオプション
オプション 説明
FIELDS TERMINATED BY フィールドの区切り文字を指定する。(CSVの場合は通常カンマ)
ENCLOSED BY フィールドを囲む文字を指定する。(CSVの場合は通常ダブルクォート)
LINES TERMINATED BY 行の区切り文字を指定する。(Linuxの場合は '\n'、Windowsの場合は '\r\n')
IGNORE n LINES 最初のn行をスキップする。(ヘッダ行がある場合)


ヘッダ行をスキップする例を以下に示す。

 LOAD DATA LOCAL INFILE '/path/to/file.csv'
 INTO TABLE import_table
 FIELDS TERMINATED BY ','
 ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 IGNORE 1 LINES;


特定のカラムのみにインポートする場合は、以下のようにする。

 LOAD DATA LOCAL INFILE '/path/to/file.csv'
 INTO TABLE import_table
 FIELDS TERMINATED BY ','
 (column1, column2, column3);


LOCAL キーワードを使用する場合は、クライアント側のファイルシステムからファイルを読み込む。
LOCALを省略した場合は、サーバ側のファイルシステムからファイルを読み込む。

セキュリティ上の理由から、LOCALオプションが無効化されている場合がある。
この場合は、local_infileシステム変数を有効にする必要がある。

 SET GLOBAL local_infile = 1;


CSVファイルへのエクスポート

テーブルのデータをCSVファイルにエクスポートするには、SELECT INTO OUTFILE 文を使用する。

 SELECT column1, column2, column3
 FROM table_name
 INTO OUTFILE '/path/to/output.csv'
 FIELDS TERMINATED BY ','
 ENCLOSED BY '"'
 LINES TERMINATED BY '\n';


文字コードを指定する場合は、CHARACTER SET 句を使用する。

 SELECT *
 FROM table_name
 INTO OUTFILE '/path/to/output.csv'
 CHARACTER SET utf8mb4
 FIELDS TERMINATED BY ','
 ENCLOSED BY '"'
 LINES TERMINATED BY '\n';


条件を指定してエクスポートする例を以下に示す。

 SELECT user_id, username, email
 FROM users
 WHERE created_at >= '2024-01-01'
 INTO OUTFILE '/path/to/users_2024.csv'
 FIELDS TERMINATED BY ','
 ENCLOSED BY '"'
 LINES TERMINATED BY '\n';


SELECT INTO OUTFILE 文には、いくつかの制約がある。

  • secure_file_priv システム変数で指定されたディレクトリ内にのみファイルを出力できる。
  • 出力先ファイルは既存ファイルがない場所を指定する必要がある。(上書き不可)


secure_file_privの設定を確認する。

 SHOW VARIABLES LIKE 'secure_file_priv';


secure_file_priv がNULLの場合は、SELECT INTO OUTFILE 文は使用できない。
secure_file_priv が空文字の場合は、任意のディレクトリに出力できる。
secure_file_priv にディレクトリが指定されている場合は、そのディレクトリ内にのみ出力できる。


「https://mochiu.net/index.php?title=MySQL_-_テーブル&oldid=14281」から取得