MySQL - テーブルのソースを表示

== 概要 ==
MySQLのテーブルは、リレーショナルデータベースにおいてデータを格納する基本的な構造単位である。<br>
テーブルは、行 (レコード) と列 (カラム) で構成されたデータの集合であり、各カラムにはデータ型と制約が定義される。<br>
<br>
MySQLにおけるテーブルの主要な操作には、テーブルの作成、変更、削除、レコードの挿入・更新・削除がある。<br>
また、テーブルの構造を確認したり、テーブル一覧を取得することも可能である。<br>
<br>
テーブルは、ストレージエンジンによって管理される。<br>
MySQLでは、InnoDB、MyISAM、MEMORY、CSV、ARCHIVE等の複数のストレージエンジンが利用可能である。<br>
MySQL 5.5以降、InnoDBがデフォルトのストレージエンジンとして設定されている。<br>
<br>
InnoDBは、トランザクション対応 (ACID準拠)、行レベルロック、外部キーサポートを提供し、本番環境での使用に適している。<br>
MyISAMは、トランザクション未対応でテーブルレベルロックを使用するが、読み取り専用または読み取り中心のアプリケーションに適している。<br>
MEMORYストレージエンジンは、データをメモリ内に格納するため高速であるが、サーバ再起動時にデータは消失する。<br>
<br>
テーブルには、文字コードと照合順序を設定することができ、多言語対応や大文字小文字の区別等の挙動を制御できる。<br>
また、制約 (<code>NOT NULL</code>、<code>PRIMARY KEY</code>、<code>FOREIGN KEY</code>、<code>UNIQUE</code>、<code>CHECK</code>) を設定することにより、データの整合性を保つことができる。<br>
<br>
MySQLでは、通常のテーブルの他に、一時テーブル (TEMPORARY TABLE) を作成することもできる。<br>
一時テーブルは、現在のセッション内でのみ可視であり、セッション終了時に自動的に削除される。<br>
<br><br>

== テーブルの作成 ==
==== 基本構文 ====
テーブルを作成するには、<code>CREATE TABLE</code>文を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE table_name (
    column1 datatype [constraint],
    column2 datatype [constraint],
    ...,
    table_level_constraint
 ) table_options;
 </syntaxhighlight>
<br>
<center>
{| class="wikitable"
|+ 主要なカラムオプション
|-
! オプション !! 説明
|-
| <code>NOT NULL</code> || NULL値を禁止する
|-
| <code>DEFAULT</code> || デフォルト値を設定する
|-
| <code>AUTO_INCREMENT</code> || 自動インクリメント値を生成する
|-
| <code>UNIQUE</code> || 一意性を保証する
|-
| <code>PRIMARY KEY</code> || 主キーを定義する
|-
| <code>CHECK</code> || 条件を指定してデータを検証する (MySQL 8.0.16以降)
|-
| <code>COMMENT</code> || カラムにコメントを付与する
|-
| <code>VISIBLE</code> / <code>INVISIBLE</code> || カラムの可視性を制御する (MySQL 8.0以降)
|}
</center>
<br>
<center>
{| class="wikitable"
|+ 主要なテーブルオプション
|-
! オプション !! 説明
|-
| <code>ENGINE</code> || ストレージエンジンを指定する。
|-
| <code>DEFAULT CHARSET</code> || デフォルトの文字コードを指定する。
|-
| <code>DEFAULT COLLATE</code> || デフォルトの照合順序を指定する。
|-
| <code>COMMENT</code> || テーブルにコメントを付与する。(最大2048文字)
|-
| <code>AUTO_INCREMENT</code> || AUTO_INCREMENT値の初期値を設定する。
|}
</center>
<br>
例として、基本的なテーブル作成の構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
==== ストレージエンジンの指定 ====
MySQLでは、テーブルごとにストレージエンジンを指定できる。<br>
<br>
下表に、主要なストレージエンジンを示す。<br>
<br>
<center>
{| class="wikitable"
|+ 主要ストレージエンジンの比較
! ストレージエンジン !! トランザクション !! ロック方式 !! 外部キー !! 全文検索 !! 用途
|-
| InnoDB || 対応 (ACID準拠) || 行レベル || サポート || MySQL 5.6以降 || 本番環境、トランザクション処理
|-
| MyISAM || 未対応 || テーブルレベル || 未サポート || サポート || 読み取り専用、読み取り中心
|-
| MEMORY || 未対応 || テーブルレベル || 未サポート || 未サポート || 高速一時データ、キャッシュ
|-
| CSV || 未対応 || テーブルレベル || 未サポート || 未サポート || CSVファイル形式でのストレージ
|-
| ARCHIVE || 未対応 || 行レベル || 未サポート || 未サポート || 圧縮ストレージ、ログデータ
|}
</center>
<br>
InnoDBは、MySQL 5.5以降でデフォルトのストレージエンジンとして設定されている。<br>
InnoDBは、トランザクション対応、行レベルロック、外部キー制約、クラッシュリカバリ機能を提供する。<br>
<br>
ストレージエンジンを指定する構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
既存テーブルのストレージエンジンを変更することも可能である。<br>
<br>
 <syntaxhighlight lang="mysql">
 ALTER TABLE table_name ENGINE=InnoDB;
 </syntaxhighlight>
<br>
==== 既存のテーブルの存在を確認して作成 ====
既存のテーブルが存在していない場合はテーブルを作成する。<br>
これは、テーブルの重複、または、既存のテーブルを上書き / 削除することを防ぐことができる。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE IF NOT EXISTS table_name (
    column1 datatype,
    column2 datatype
 );
 </syntaxhighlight>
<br>
文字コードと照合順序を指定した構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
<u><code>IF NOT EXISTS</code> 句を指定すると、テーブルが既に存在する場合はエラーにならず、警告のみが出力される。</u><br>
<br>
==== テーブルのクローニングおよびコピー ====
<code>CREATE TABLE ... LIKE</code>文、または、<code>CREATE TABLE ... AS SELECT</code>文を使用して、<br>
元のテーブルに定義されているカラム属性やインデックス等、別のテーブルの定義に基づいて空のテーブルを作成する。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE new_table LIKE original_table;

 -- または

 CREATE TABLE new_table AS SELECT * FROM original_table;
 </syntaxhighlight>
<br>
下表に、<code>CREATE TABLE ... LIKE</code>文と<code>CREATE TABLE ... AS SELECT</code>文の違いを示す。<br>
<br>
<center>
{| class="wikitable"
|+ CREATE TABLE ... LIKE と CREATE TABLE ... AS SELECT の比較
! 項目 !! CREATE TABLE ... LIKE !! CREATE TABLE ... AS SELECT
|-
| データのコピー || コピーしない (空のテーブル) || コピーする
|-
| インデックス || コピーする || 主キーのみコピー
|-
| 外部キー制約 || コピーする || コピーしない
|-
| DEFAULT値 || コピーする || コピーしない
|-
| AUTO_INCREMENT || コピーする || コピーしない
|}
</center>
<br>
テーブル構造のみをコピーして、その後でデータを挿入する場合は、以下のようにする。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE new_table LIKE original_table;
 INSERT INTO new_table SELECT * FROM original_table;
 </syntaxhighlight>
<br><br>

== 一時テーブル ==
==== 一時テーブルの作成 ====
MySQLでは、一時テーブルは現在のセッション内でのみ表示され、セッションがクローズされると自動的に削除される。<br>
つまり、複数の異なるセッションが同じ一時テーブル名を使用することができ、互いに、同じ名前の既存の一時テーブル以外のテーブルと競合することはない。<br>
(既存のテーブルは、一時テーブルが削除されるまで非表示になる)<br>
<br>
* 一時テーブルを作成する構文
*: <syntaxhighlight lang="mysql">
 CREATE TEMPORARY TABLE temp_table_name (
    column1 datatype,
    column2 datatype
 );
 
 -- または
 
 CREATE TEMPORARY TABLE IF NOT EXISTS temp_table_name (
    column1 datatype,
    column2 datatype
 );
 </syntaxhighlight>
*: <br>
* 一時テーブルの使用例
*: <syntaxhighlight lang="mysql">
 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;
 
 -- セッション終了時に自動的に削除される
 </syntaxhighlight>
<br>
==== 一時テーブルの特徴と注意事項 ====
<center>
{| class="wikitable"
|+ 一時テーブルの主要な特徴
|-
! 特徴 !! 説明
|-
| セッションスコープ
|
* 一時テーブルは、作成したセッション内でのみ可視である
* 他のセッションからは参照できない
|-
| 自動削除
|
* セッション終了時に自動的に削除される
* 明示的に削除する場合は、<code>DROP TEMPORARY TABLE</code>文を使用する
|-
| 名前の競合
|
* 一時テーブルと同名の通常テーブルが存在する場合、一時テーブルが優先される
* 一時テーブルが削除されるまで、通常テーブルは非表示になる
|-
| データベース依存性
|
* 一時テーブルは、データベース (スキーマ) と疎な関係を持つ
* データベースを削除しても、一時テーブルは自動的には削除されない
|-
| 権限
|
* <code>CREATE TEMPORARY TABLES</code>権限が必要
* 作成後は、<code>DROP TABLE</code>、<code>INSERT</code>、<code>UPDATE</code>、<code>SELECT</code>等の操作が可能
|}
</center>
<br>
<center>
{| class="wikitable"
|+ InnoDBにおける一時テーブルの制約
|-
! 制約 !! 説明
|-
| 圧縮一時テーブルは未サポート
|
* <code>innodb_strict_mode</code>が有効 (デフォルト) な場合、<br><code>ROW_FORMAT=COMPRESSED</code>または<code>KEY_BLOCK_SIZE</code>を指定するとエラーになる。
* <code>innodb_strict_mode</code>が無効な場合は、警告が出力され、圧縮されていない行形式で作成される。
|-
| <code>innodb_file_per_table</code>オプション || InnoDB一時テーブルの作成には影響しない。
|}
</center>
<br>
MySQL 8.0では、TempTableストレージエンジンがデフォルトの内部一時テーブル用エンジンとして使用される。<br>
<br>
<code>temptable_max_ram</code> システム変数 (デフォルト1[GiB]) により、メモリ使用量を制御できる。<br>
メモリ超過時は、メモリマップファイルまたはInnoDBオンディスクに自動的に遷移する。<br>
<br>
==== トランザクションと一時テーブル ====
一時テーブルとトランザクションの関係には注意が必要である。<br>
<br>
<code>START TRANSACTION</code> 文 または <code>BEGIN</code> 文の中において、一時テーブルを作成後に <code>ROLLBACK</code> 文を実行する場合、<br>
一時テーブル自体は削除されないが、レコードは全て削除される。<br>
つまり、一時テーブルを作成する <code>CREATE TEMPORARY TABLE</code> 文は、<code>ROLLBACK</code> 文の対象にならず、レコードを複製する箇所のみがロールバックされる。<br>
<br>
動作例を以下に示す。<br>
 <syntaxhighlight lang="mysql">
 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行
 </syntaxhighlight>
<br><br>

== テーブルの確認 ==
==== テーブル一覧の確認 ====
* 現在使用しているデータベースのテーブル一覧を確認する。
*: <syntaxhighlight lang="mysql">
 USE database_name;
 SHOW TABLES;
 </syntaxhighlight>
*: <br>
* 特定のデータベースのテーブル一覧を確認する。
*: <syntaxhighlight lang="mysql">
 SHOW TABLES FROM database_name;
 </syntaxhighlight>
*: <br>
* パターンマッチングを使用してテーブルを検索する。
*: <syntaxhighlight lang="mysql">
 SHOW TABLES LIKE 'user%';
 </syntaxhighlight>
*: <br>
* <code>INFORMATION_SCHEMA</code> データベースの <code>TABLES</code> テーブルから、詳細情報を取得する。
 *:<syntaxhighlight lang="mysql">
 SELECT TABLE_NAME, ENGINE, TABLE_ROWS, TABLE_COLLATION
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'database_name';
 </syntaxhighlight>
<br>
==== テーブル構造の確認 ====
テーブルの構造を確認するには、<code>DESCRIBE</code> 文 または <code>SHOW CREATE TABLE</code> 文を使用する。<br>
<br>
<code>DESCRIBE</code>文は、カラム名、型、NULL許可、Key、Default、Extraの情報を表示する。<br>
<br>
 <syntaxhighlight lang="mysql">
 DESCRIBE table_name;
 
 -- 省略形
 DESC table_name;
 </syntaxhighlight>
<br>
特定のカラムのみを確認する場合は、以下のようにする。<br>
<br>
 <syntaxhighlight lang="mysql">
 DESCRIBE table_name column_name;
 </syntaxhighlight>
<br>
<code>SHOW CREATE TABLE</code> 文は、完全な <code>CREATE TABLE</code> 文を表示する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SHOW CREATE TABLE table_name;
 </syntaxhighlight>
<br>
この構文は、テーブルの完全な定義 (インデックス、外部キー制約、ストレージエンジン、文字コード等) を確認する場合に便利である。<br>
<br>
詳細なカラム情報を確認する場合は、<code>SHOW FULL COLUMNS</code> 文を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SHOW FULL COLUMNS FROM table_name;
 </syntaxhighlight>
<br>
<code>SHOW FULL COLUMNS</code> 文は、照合順序、コメント、権限情報を含む詳細情報を表示する。<br>
<br>
==== テーブルのステータス確認 ====
テーブルのステータス情報を確認するには、<code>SHOW TABLE STATUS</code> 文を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SHOW TABLE STATUS LIKE 'table_name';
 </syntaxhighlight>
<br>
<code>SHOW TABLE STATUS</code> 文の出力には、以下に示す情報が含まれる。<br>
<br>
<center>
{| class="wikitable"
|+ SHOW TABLE STATUS文の主な出力項目
|-
! 項目 !! 説明
|-
| <code>Name</code> || テーブル名
|-
| <code>Engine</code> || ストレージエンジン
|-
| <code>Version</code> || テーブルフォーマットバージョン
|-
| <code>Row_format</code> || 行フォーマット (Dynamic、Fixed、Compressed等)
|-
| <code>Rows</code> || 行数の推定値
|-
| <code>Avg_row_length</code> || 平均行長
|-
| <code>Data_length</code> || データファイルのサイズ (バイト)
|-
| <code>Index_length</code> || インデックスファイルのサイズ (バイト)
|-
| <code>Collation</code> || デフォルトの照合順序
|-
| <code>Create_time</code> || テーブル作成時刻
|-
| <code>Update_time</code> || 最終更新時刻
|-
| <code>Comment</code> || テーブルコメント
|}
</center>
<br>
特定のデータベースの全テーブルのステータスを確認する場合は、以下のようにする。<br>
<br>
 <syntaxhighlight lang="mysql">
 SHOW TABLE STATUS FROM database_name;
 </syntaxhighlight>
<br><br>

== テーブルの変更 ==
==== テーブル名の変更 ====
テーブル名を変更するには、<code>RENAME TABLE</code> 文を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 RENAME TABLE old_name TO new_name;
 </syntaxhighlight>
<br>
複数のテーブルを同時にリネームすることも可能である。<br>
この構文は、テーブル名を入れ替える場合に便利である。<br>
<u>複数のリネーム操作は、アトミックに実行される。</u><br>
<br>
 <syntaxhighlight lang="mysql">
 RENAME TABLE t1 TO temp, t2 TO t1, temp TO t2;
 </syntaxhighlight>
<br>
データベース間でテーブルを移動することもできる。<br>
<br>
 <syntaxhighlight lang="mysql">
 RENAME TABLE db1.table_name TO db2.table_name;
 </syntaxhighlight>
<br>
一時テーブル (TEMPORARY TABLE) には、<code>RENAME TABLE</code> 文を使用できないことに注意する。<br>
一時テーブルをリネームする場合は、<code>ALTER TABLE</code> 文を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 ALTER TABLE temp_table RENAME TO new_temp_table;
 </syntaxhighlight>
<br>
==== AUTO_INCREMENTの設定 ====
<code>AUTO_INCREMENT</code> 属性は、整数カラムに対して自動的に増分値を生成する。<br>
<br>
テーブル作成時に <code>AUTO_INCREMENT</code> の初期値を設定する構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(100)
 ) AUTO_INCREMENT = 1000;
 </syntaxhighlight>
<br>
既存テーブルの <code>AUTO_INCREMENT</code> 値を変更する構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 ALTER TABLE table_name AUTO_INCREMENT = 1;
 </syntaxhighlight>
<br>
現在の <code>AUTO_INCREMENT</code> 値を確認する場合は、以下のようにする。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT AUTO_INCREMENT
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'database_name'
   AND TABLE_NAME = 'table_name';
 </syntaxhighlight>
<br>
MySQL 8.0では、<code>AUTO_INCREMENT</code> 値がREDOログに永続化されるため、サーバ再起動後もリセットされない。<br>
MySQL 5.7以前では、サーバ再起動後に最大値+1にリセットされる動作であった。<br>
<br>
InnoDBの <code>AUTO_INCREMENT</code> 動作を制御するシステム変数として、<code>innodb_autoinc_lock_mode</code> がある。<br>
* 0 (Traditional)
*: テーブルレベルのAUTO_INCロックを使用
* 1 (Consecutive)
*: 単純なINSERTでは軽量ロック、バルクINSERTではテーブルレベルロック
* 2 (Interleaved)
*: ロックなし (MySQL 8.0のデフォルト)
<br>
==== テーブルコメント ====
テーブルにコメントを付与することで、テーブルの目的や仕様を文書化できる。<br>
<br>
テーブル作成時にコメントを付与する構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
 ) COMMENT = 'ユーザー情報を管理するテーブル';
 </syntaxhighlight>
<br>
<u>テーブルコメントは、最大2048文字まで設定可能である。</u><br>
<u>カラムコメントは、最大1024文字まで設定可能である。</u><br>
<br>
カラムにコメントを付与する構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE products (
    product_id INT PRIMARY KEY COMMENT '商品ID',
    product_name VARCHAR(100) COMMENT '商品名',
    price DECIMAL(10,2) COMMENT '価格 (税込)'
 );
 </syntaxhighlight>
<br>
既存テーブルのコメントを変更する場合は、以下のようにする。<br>
<br>
 <syntaxhighlight lang="mysql">
 ALTER TABLE table_name COMMENT = '新しいコメント';
 </syntaxhighlight>
<br>
コメントを確認する方法を以下に示す。<br>
 <syntaxhighlight lang="mysql">
 -- テーブルコメントを確認
 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';
 </syntaxhighlight>
<br><br>

== 文字コードと照合順序 ==
==== テーブルの文字コード ====
テーブルの作成時において、文字コードを指定する。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE table_name (
    column1 VARCHAR(255),
    column2 TEXT
 ) CHARACTER SET utf8mb4;
 
 -- または、特定のカラムに文字コードを指定
 
 CREATE TABLE table_name (
    column1 VARCHAR(255) CHARACTER SET utf8mb4,
    column2 TEXT
 );
 </syntaxhighlight>
<br>
MySQLのデフォルトの文字コードを確認する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SHOW VARIABLES LIKE 'chara%';
 </syntaxhighlight>
<br>
MySQLのデフォルトの文字コードを設定する場合は、my.cnfファイルを編集する。<br>
my.cnfファイルを変更した後は、MySQLを再起動する必要がある。<br>
<br>
 <syntaxhighlight lang="ini">
 # my.cnfファイル
 
 [mysqld]
 character-set-server=utf8mb4   # MySQLサーバのデフォルトの文字コードを指定する
 
 [client]
 default-character-set=utf8mb4  # MySQLクライアントのデフォルトの文字コードを指定する
 </syntaxhighlight>
<br>
既存のテーブルの文字コードは、<code>INFORMATION_SCHEMA</code> データベースの <code>TABLES</code> テーブルから確認することができる。<br>
<code>TABLE_COLLATION</code> カラムの値が、そのテーブルに設定された文字コードに相当する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT TABLE_NAME, TABLE_COLLATION
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'database_name';
 </syntaxhighlight>
<br>
既存のテーブルの文字コードを変更する。<br>
<br>
 <syntaxhighlight lang="mysql">
 ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4;
 </syntaxhighlight>
<br>
==== 照合順序とは ====
MySQLにおいて、照合順序 (Collation) はテーブルや列ごとに設定することができる。<br>
照合順序は、文字列の比較やソートの際に使用され、異なる言語や地域に合わせてテキストの比較を行うために重要である。<br>
<br>
MySQLでは、様々な照合順序が提供されている。<br>
例えば、大文字小文字を区別やアクセントを考慮するかどうか等の違いがある。<br>
<br>
下表に、代表的な照合順序を示す。<br>
<br>
<center>
{| class="wikitable"
|+ 代表的な照合順序
|-
! 照合順序 !! 説明
|-
| <code>utf8mb4_general_ci</code>
|
* 大文字小文字を区別せず、アクセントを区別しない
* 通常の比較の場合には適しているが、文字の並び順は一般的な場合に従う
|-
| <code>utf8mb4_unicode_ci</code>
|
* Unicode標準 (UCA) に基づいた比較を行う
* <code>utf8mb4_general_ci</code>よりも正確なソート結果を返すが、やや低速である
|-
| <code>utf8mb4_unicode_520_ci</code>
|
* Unicode 5.2.0標準に基づいた比較を行う
* <code>utf8mb4_unicode_ci</code>よりも新しいUnicode規則に対応する
|-
| <code>utf8mb4_0900_ai_ci</code>
|
* Unicode 9.0標準に基づいた比較を行う
* MySQL 8.0のデフォルトの照合順序である
* ai (Accent Insensitive)、ci (Case Insensitive) を意味する
|-
| <code>utf8mb4_0900_as_cs</code>
|
* Unicode 9.0標準に基づいた比較を行う
* as (Accent Sensitive)、cs (Case Sensitive) を意味する
* 大文字小文字およびアクセントを区別する
|-
| <code>utf8mb4_0900_as_ci</code>
|
* Unicode 9.0標準に基づいた比較を行う
* アクセントを区別するが、大文字小文字は区別しない
|-
| <code>utf8mb4_ja_0900_as_cs</code>
|
* 日本語向けの照合順序である (MySQL 8.0以降)
* JIS X 4061規格に基づいた日本語のソート順を提供する
|-
| <code>utf8mb4_ja_0900_as_cs_ks</code>
|
* 日本語向けの照合順序であり、カナの区別 (ks: Kana Sensitive) を行う
* ひらがなとカタカナを区別して比較する
|-
| <code>utf8mb4_bin</code>
|
* バイナリ比較を行う
* 大文字小文字やアクセント等を区別して、バイト単位で比較する
* これは厳密なバイナリ比較が必要な場合に使用される
|-
| <code>utf8_general_ci</code>
|
* utf8mb4と同様、大文字小文字を区別せず、アクセントを区別しない
* ただし、utf8mb4よりも少ない文字セットを使用する (3バイトUTF-8)
|-
| <code>latin1_swedish_ci</code>
|
* ラテン1文字セットに基づいた照合順序であり、スウェーデンの標準に従う
* 大文字小文字を区別せず、アクセントを区別しない
|-
| <code>latin1_bin</code>
|
* ラテン1文字セットのバイナリ比較を行う
* 大文字小文字およびアクセントを区別する
|-
| <code>binary</code>
|
* 全てのデータをバイト列として比較する
* 文字セットに依存しない厳密なバイナリ比較を行う
|}
</center>
<br>
照合順序の詳細を知りたい場合は、MySQLの公式ドキュメントを参照すること。<br>
* https://dev.mysql.com/doc/refman/8.0/en/charset-mysql.html
<br>
==== 使用できる照合順序の確認 ====
使用できる文字コードと照合順序の組み合わせは、<code>INFORMATION_SCHEMA</code> データベースの <code>COLLATION_CHARACTER_SET_APPLICABILITY</code> テーブルから確認できる。<br>
利用できる照合順序がMySQLのバージョンによって異なるため、どのような照合順序が使用できるかどうかを確認する必要がある。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT COLLATION_NAME, CHARACTER_SET_NAME
 FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
 WHERE COLLATION_NAME = 'collation_name';
 </syntaxhighlight>
<br>
例として、照合順序にutf8mb4_general_ciが使用できるかどうかを確認する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT COLLATION_NAME, CHARACTER_SET_NAME
 FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
 WHERE COLLATION_NAME = 'utf8mb4_general_ci';
 </syntaxhighlight>
<br>
利用可能な全ての照合順序を一覧表示する場合は、以下のようにする。<br>
<br>
 <syntaxhighlight lang="mysql">
 SHOW COLLATION;
 
 -- 特定の文字セットの照合順序のみを表示
 SHOW COLLATION WHERE Charset = 'utf8mb4';
 </syntaxhighlight>
<br>
==== カラムの文字コードと照合順序の確認 ====
カラムに設定された文字コードと照合順序を確認する場合は、<code>INFORMATION_SCHEMA.COLUMNS</code> テーブルを使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = 'database_name'
   AND TABLE_NAME = 'table_name';
 </syntaxhighlight>
<br>
特定のカラムのみを確認する場合は、<code>WHERE</code> 句で条件を追加する。<br>
<br>
 <syntaxhighlight lang="mysql">
 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%';
 </syntaxhighlight>
<br>
==== テーブル作成時における文字コードと照合順序の指定 ====
テーブル作成時において、文字コードと照合順序を指定する構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE table_name (
    column1 VARCHAR(255) COLLATE utf8mb4_unicode_ci,
    column2 INT
 ) CHARACTER SET utf8mb4
   COLLATE utf8mb4_general_ci;
 </syntaxhighlight>
<br>
カラムごとに異なる照合順序を指定することも可能である。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE table_name (
    column1 VARCHAR(255) COLLATE utf8_general_ci,
    column2 INT,
    column3 TEXT COLLATE utf8_unicode_ci
 );
 </syntaxhighlight>
<br>
<u>テーブルに照合順序を指定した場合、カラムに照合順序が指定されていない場合は、テーブルの照合順序が継承される。</u><br>
<u>テーブルにも照合順序が指定されていない場合は、データベースのデフォルトの照合順序が継承される。</u><br>
<br>
==== 既存のテーブルやカラムの文字コードと照合順序の変更 ====
既存のテーブルやカラムの照合順序を変更することも可能である。<br>
<br>
===== テーブルの照合順序の変更 =====
テーブルの照合順序を変更する。<br>
ただし、各カラムの照合順序は変更されないことに注意する。<br>
<br>
 <syntaxhighlight lang="mysql">
 ALTER TABLE table_name
 CHARACTER SET utf8mb4
 COLLATE utf8mb4_general_ci;
 </syntaxhighlight>
<br>
<u>この構文は、テーブルのデフォルト照合順序のみを変更し、既存カラムの照合順序は変更しない。</u><br>
<u>新しく追加されるカラムには、変更後の照合順序が適用される。</u><br>
<br>
===== 全ての文字カラムの照合順序の変更 =====
全ての文字カラムの文字コードおよび照合順序を変更する。<br>
これは、テーブルの文字コードと各カラムの文字コードを揃える時に使用すると便利である。<br>
<br>
 <syntaxhighlight lang="mysql">
 ALTER TABLE table_name
 CONVERT TO CHARACTER SET utf8mb4
 COLLATE utf8mb4_general_ci;
 </syntaxhighlight>
<br>
<code>CONVERT TO CHARACTER SET</code> 句を使用すると、既存の全ての文字カラムの文字コードと照合順序が一括変更される。<br>
データの変換も自動的に行われる。<br>
<br>
===== 任意のカラムの照合順序の変更 =====
特定のカラムのみの照合順序を変更する場合は、以下のようにする。<br>
<br>
 <syntaxhighlight lang="mysql">
 ALTER TABLE table_name
 MODIFY column_name VARCHAR(255) COLLATE utf8mb4_unicode_ci;
 </syntaxhighlight>
<br>
<code>MODIFY</code> 句を使用する場合は、カラムの型も再指定する必要がある。<br>
<br>
==== デフォルトの照合順序 ====
テーブルにおけるデフォルトの照合順序を確認する場合、<br>
テーブル自体に照合順序が指定されていない場合、テーブルはデータベースのデフォルトの照合順序を継承する。<br>
<br>
テーブルのデフォルト照合順序を確認する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SHOW TABLE STATUS LIKE '<テーブル名>';
 </syntaxhighlight>
<br>
これは、テーブルのステータス情報を表示する。<br>
出力結果において、<code>Collation</code> カラムの値がテーブルの照合順序である。<br>
<br>
もし、テーブルに照合順序が指定されていない場合、データベースのデフォルトの照合順序が表示される。<br>
<br>
データベースのデフォルト照合順序を確認する場合は、以下のようにする。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
 FROM INFORMATION_SCHEMA.SCHEMATA
 WHERE SCHEMA_NAME = 'database_name';
 </syntaxhighlight>
<br>
サーバ全体のデフォルト照合順序を確認する場合は、以下のようにする。<br>
<br>
 <syntaxhighlight lang="mysql">
 SHOW VARIABLES LIKE 'collation%';
 </syntaxhighlight>
<br><br>

== 制約 ==
==== NOT NULL ====
<code>NOT NULL</code> 制約は、カラムにNULL値を禁止する。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(16) NOT NULL,
    price INT
 );
 </syntaxhighlight>
<br>
<code>NOT NULL</code> 制約が設定されたカラムには、必ず値を挿入する必要がある。<br>
NULL値を挿入しようとすると、エラーが発生する。<br>
<br>
既存カラムに<code>NOT NULL</code> 制約を追加する場合は、以下のようにする。<br>
<br>
 <syntaxhighlight lang="mysql">
 ALTER TABLE table_name
 MODIFY column_name datatype NOT NULL;
 </syntaxhighlight>
<br>
==== CHECK ====
<code>CHECK</code> 制約は、条件を指定して、条件を満たさないデータを禁止する。<br>
<code>CHECK</code> 制約は、MySQL 8.0.16以降でサポートされている。<br>
<br>
 <syntaxhighlight lang="mysql">
 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'))
 );
 </syntaxhighlight>
<br>
<code>CHECK</code> 制約は、テーブルレベルまたはカラムレベルで指定できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 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)
 );
 </syntaxhighlight>
<br>
==== UNIQUE KEY ====
<code>UNIQUE</code> 制約は、カラムに重複したデータを禁止する。<br>
<code>UNIQUE</code> 制約の特徴を以下に示す。<br>
* 重複したデータを禁止する。
* 複数のカラムに設定できる。
* NULL値は禁止しない。(NULL値は複数許可される)
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE user (
    user_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    telephone CHAR(8),
    address VARCHAR(255),
    UNIQUE (telephone, address)
 );
 </syntaxhighlight>
<br>
複数のカラムに対して<code>UNIQUE</code> 制約を設定した場合、それらのカラムの組み合わせが一意である必要がある。<br>
<br>
既存テーブルに <code>UNIQUE</code> 制約を追加する場合は、以下のようにする。<br>
<br>
 <syntaxhighlight lang="mysql">
 ALTER TABLE table_name
 ADD UNIQUE (column_name);
 
 -- または、複数カラムに対して
 
 ALTER TABLE table_name
 ADD UNIQUE (column1, column2);
 </syntaxhighlight>
<br>
==== PRIMARY KEY ====
<code>PRIMARY KEY</code> 制約は、テーブルの主キーを定義する。<br>
<code>PRIMARY KEY</code> 制約の特徴を以下に示す。<br>
* 一意性を保証する
* 重複とNULL値を禁止する
* 1つのテーブルに1つしか指定できない
* 自動的にインデックスが作成される
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE user (
    user_id INT PRIMARY KEY,
    telephone CHAR(8),
    age INT
 );
 </syntaxhighlight>
<br>
複合主キーも指定できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE price (
    shop_code CHAR(4),
    product_code CHAR(4),
    price INT,
    PRIMARY KEY (shop_code, product_code)
 );
 </syntaxhighlight>
<br>
既存テーブルに主キーを追加する場合は、以下のようにする。<br>
<br>
 <syntaxhighlight lang="mysql">
 ALTER TABLE table_name
 ADD PRIMARY KEY (column_name);
 </syntaxhighlight>
<br>
==== FOREIGN KEY ====
<code>FOREIGN KEY</code> 制約は、他のテーブルのカラムを参照して、そのカラムに存在しないデータを禁止する。<br>
外部キー制約により、参照整合性を保つことができる。<br>
<br>
 <syntaxhighlight lang="mysql">
 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)
 );
 </syntaxhighlight>
<br>
複数のカラムを外部参照することもできる。<br>
<br>
 <syntaxhighlight lang="mysql">
 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)
 );
 </syntaxhighlight>
<br>
外部キー制約には、参照動作を指定できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 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
 );
 </syntaxhighlight>
<br>
下表に、参照動作のオプションを示す。<br>
<br>
<center>
{| class="wikitable"
|+ 参照動作のオプション
|-
! オプション !! 説明
|-
| <code>CASCADE</code> || 親テーブルの行が削除 / 更新されると、子テーブルの行も削除 / 更新される。
|-
| <code>SET NULL</code> || 親テーブルの行が削除 / 更新されると、子テーブルの外部キーカラムがNULLに設定される。
|-
| <code>RESTRICT</code> || 子テーブルに参照行が存在する場合、親テーブルの削除 / 更新を禁止する。(デフォルト)
|-
| <code>NO ACTION</code> || RESTRICTと同じ動作
|-
| <code>SET DEFAULT</code> || MySQLでは未サポート
|}
</center>
<br>
==== 制約の名前を設定 ====
制約には、名前を付けることができる。<br>
制約名を指定すると、後で制約を削除する際に便利である。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE table_name (
    column1 datatype,
    CONSTRAINT constraint_name constraint_definition
 );
 </syntaxhighlight>
<br>
例として、制約名を指定した構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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'))
 );
 </syntaxhighlight>
<br>
==== 制約の削除 ====
制約を削除するには、<code>ALTER TABLE</code> 文を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 ALTER TABLE table_name DROP CONSTRAINT constraint_name;
 </syntaxhighlight>
<br>
* 主キーを削除する場合
*: <syntaxhighlight lang="mysql">
 ALTER TABLE table_name DROP PRIMARY KEY;
 </syntaxhighlight>
*: <br>
* 外部キーを削除する場合
*: <syntaxhighlight lang="mysql">
 ALTER TABLE table_name DROP FOREIGN KEY constraint_name;
 </syntaxhighlight>
*: <br>
* <code>UNIQUE</code> 制約を削除する場合
*: <syntaxhighlight lang="mysql">
 ALTER TABLE table_name DROP INDEX constraint_name;
 </syntaxhighlight>
<br>
==== デフォルト値の設定 ====
カラムにデフォルト値を設定することで、値が指定されない場合に自動的に設定される値を定義できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE product (
    product_id INT NOT NULL PRIMARY KEY,
    name CHAR(16) UNIQUE,
    price INT DEFAULT 2000,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );
 </syntaxhighlight>
<br>
デフォルト値には、リテラル値や式を指定できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 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
 );
 </syntaxhighlight>
<br>
* 既存カラムにデフォルト値を追加する場合
*: <syntaxhighlight lang="mysql">
 ALTER TABLE table_name
 ALTER COLUMN column_name SET DEFAULT default_value;
 </syntaxhighlight>
*: <br>
* デフォルト値を削除する場合
*: <syntaxhighlight lang="mysql">
 ALTER TABLE table_name
 ALTER COLUMN column_name DROP DEFAULT;
 </syntaxhighlight>
<br><br>

== テーブルの削除 ==
テーブルを削除するには、<code>DROP TABLE</code> 文を使用する。<br>
<code>DROP TABLE</code> 文により削除したテーブルは、復元することができないことに注意する。<br>
<br>
 <syntaxhighlight lang="mysql">
 DROP TABLE table_name;
 </syntaxhighlight>
<br>
複数のテーブルを一括して削除する場合、カンマ区切りでテーブル名を列挙する。<br>
<br>
 <syntaxhighlight lang="mysql">
 DROP TABLE table1, table2, table3;
 </syntaxhighlight>
<br>
テーブルが存在しない場合にエラーを発生させないようにするには、<code>IF EXISTS</code> 句を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 DROP TABLE IF EXISTS table_name;
 </syntaxhighlight>
<br>
一時テーブルを削除する場合は、<code>DROP TEMPORARY TABLE</code> 文を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 DROP TEMPORARY TABLE temp_table_name;
 </syntaxhighlight>
<br>
<code>DROP TABLE</code> 文は、テーブルの構造とデータの両方を削除する。<br>
データのみを削除してテーブル構造を残す場合は、<code>DELETE</code> 文 または <code>TRUNCATE</code><br>文を使用する。<br>
<br><br>

== レコードの削除 ==
==== DELETE文 ====
<code>DELETE</code> 文は、テーブル内のレコードを削除するクエリである。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELETE FROM table_name;
 </syntaxhighlight>
<br>
<code>WHERE</code> 句で条件を指定しない場合、テーブル内の全てのデータが削除される。<br>
特定のレコードのみを削除する場合は、必ず<code>WHERE</code> 句で条件指定をする。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELETE FROM table_name WHERE column_name = value;
 </syntaxhighlight>
<br>
例として、特定の条件を満たすレコードを削除する構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
<code>LIMIT</code> 句を使用して、削除するレコード数を制限することもできる。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELETE FROM table_name WHERE condition LIMIT 10;
 </syntaxhighlight>
<br>
<code>ORDER BY</code> 句と組み合わせることで、特定の順序で削除することもできる。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELETE FROM table_name WHERE condition ORDER BY column_name LIMIT 10;
 </syntaxhighlight>
<br>
==== TRUNCATE文 ====
<code>TRUNCATE</code> 文は、テーブル内のレコードを完全に削除するクエリである。<br>
<br>
<code>TRUNCATE</code> 文は、<code>DELETE</code> 文とは異なり、<code>WHERE</code> 句での条件指定ができない。<br>
そのため、テーブル内のレコードは必ず全て削除される。<br>
<br>
 <syntaxhighlight lang="mysql">
 TRUNCATE TABLE table_name;
 </syntaxhighlight>
<br>
<u><code>TRUNCATE</code> 文は、条件を指定してデータの削除ができないことに注意する。</u><br>
<br>
<code>TRUNCATE</code> 文は、<code>DELETE</code> 文よりも高速に動作する。<br>
これは、<code>TRUNCATE</code> 文がテーブル全体を削除して再作成するためである。<br>
<br>
==== DELETE文とTRUNCATE文の違い ====
<code>DELETE</code> 文 と <code>TRUNCATE</code> 文の違いを以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ DELETE文とTRUNCATE文の比較
! 特性 !! DELETE !! TRUNCATE
|-
| WHERE句 || サポート || 未サポート
|-
| トランザクション || ROLLBACK可能 || DDL的操作 (ROLLBACK不可)
|-
| AUTO_INCREMENT || リセットなし || リセット
|-
| トリガー || 発火する || 発火しない
|-
| ログ || 行ごとのログ || DDLログ
|-
| 実行速度 || 低速 (行ごとに削除) || 高速 (テーブル再作成)
|-
| 必要権限 || DELETE || DROP
|-
| 外部キー制約 || チェックする || チェックする
|-
| ディスク領域 || 即座に解放されない || 即座に解放される
|}
</center>
<br>
<code>DELETE</code> 文は、トランザクション内で使用でき、<code>ROLLBACK</code> で取り消すことができる。<br>
<code>TRUNCATE</code> 文は、DDL操作であり、トランザクションの対象外である。<br>
<br>
<code>TRUNCATE</code> 文は、<code>AUTO_INCREMENT</code> カウンタをリセットするが、<code>DELETE</code> 文はリセットしない。<br>
<br>
外部キー制約が設定されているテーブルに対して <code>TRUNCATE</code> 文を実行すると、エラーが発生する場合がある。<br>
この場合は、外部キー制約を一時的に無効化するか、<code>DELETE</code> 文を使用する。<br>
<br><br>

== インポートとエクスポート ==
==== CSVファイルのインポート ====
CSVファイルをテーブルにインポートするには、<code>LOAD DATA INFILE</code> 文を使用する。<br>
<br>
以下に示すようなCSVファイルがあるとする。<br>
 <syntaxhighlight lang="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"
 </syntaxhighlight>
<br>
CSVファイルと合致するようなテーブルがあるとする。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE import_table (
    ID INT,
    F_DATE TIMESTAMP,
    FLAG INT,
    PRIMARY KEY (ID)
 );
 </syntaxhighlight>
<br>
CSVファイルをテーブルにインポートする。<br>
<br>
 <syntaxhighlight lang="mysql">
 LOAD DATA LOCAL INFILE '/path/to/file.csv'
 INTO TABLE import_table
 FIELDS TERMINATED BY ','
 ENCLOSED BY '"'
 LINES TERMINATED BY '\n';
 </syntaxhighlight>
<br>
下表に、<code>LOAD DATA INFILE</code> 文の主要なオプションを示す。<br>
<br>
<center>
{| class="wikitable"
|+ LOAD DATA INFILE文の主要なオプション
|-
! オプション !! 説明
|-
| <code>FIELDS TERMINATED BY</code> || フィールドの区切り文字を指定する。(CSVの場合は通常カンマ)
|-
| <code>ENCLOSED BY</code> || フィールドを囲む文字を指定する。(CSVの場合は通常ダブルクォート)
|-
| <code>LINES TERMINATED BY</code> || 行の区切り文字を指定する。(Linuxの場合は '\n'、Windowsの場合は '\r\n')
|-
| <code>IGNORE n LINES</code> || 最初のn行をスキップする。(ヘッダ行がある場合)
|}
</center>
<br>
ヘッダ行をスキップする例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 LOAD DATA LOCAL INFILE '/path/to/file.csv'
 INTO TABLE import_table
 FIELDS TERMINATED BY ','
 ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 IGNORE 1 LINES;
 </syntaxhighlight>
<br>
特定のカラムのみにインポートする場合は、以下のようにする。<br>
<br>
 <syntaxhighlight lang="mysql">
 LOAD DATA LOCAL INFILE '/path/to/file.csv'
 INTO TABLE import_table
 FIELDS TERMINATED BY ','
 (column1, column2, column3);
 </syntaxhighlight>
<br>
<code>LOCAL</code> キーワードを使用する場合は、クライアント側のファイルシステムからファイルを読み込む。<br>
<code>LOCAL</code >を省略した場合は、サーバ側のファイルシステムからファイルを読み込む。<br>
<br>
セキュリティ上の理由から、<code>LOCAL</code>オプションが無効化されている場合がある。<br>
この場合は、<code>local_infile</code>システム変数を有効にする必要がある。<br>
<br>
 <syntaxhighlight lang="mysql">
 SET GLOBAL local_infile = 1;
 </syntaxhighlight>
<br>
==== CSVファイルへのエクスポート ====
テーブルのデータをCSVファイルにエクスポートするには、<code>SELECT INTO OUTFILE</code> 文を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT column1, column2, column3
 FROM table_name
 INTO OUTFILE '/path/to/output.csv'
 FIELDS TERMINATED BY ','
 ENCLOSED BY '"'
 LINES TERMINATED BY '\n';
 </syntaxhighlight>
<br>
文字コードを指定する場合は、<code>CHARACTER SET</code> 句を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT *
 FROM table_name
 INTO OUTFILE '/path/to/output.csv'
 CHARACTER SET utf8mb4
 FIELDS TERMINATED BY ','
 ENCLOSED BY '"'
 LINES TERMINATED BY '\n';
 </syntaxhighlight>
<br>
条件を指定してエクスポートする例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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';
 </syntaxhighlight>
<br>
<code>SELECT INTO OUTFILE</code> 文には、いくつかの制約がある。<br>
* <code>secure_file_priv</code> システム変数で指定されたディレクトリ内にのみファイルを出力できる。
* 出力先ファイルは既存ファイルがない場所を指定する必要がある。(上書き不可)
<br>
<code>secure_file_priv</code>の設定を確認する。<br>
 <syntaxhighlight lang="mysql">
 SHOW VARIABLES LIKE 'secure_file_priv';
 </syntaxhighlight>
<br>
<code>secure_file_priv</code> がNULLの場合は、<code>SELECT INTO OUTFILE</code> 文は使用できない。<br>
<code>secure_file_priv</code> が空文字の場合は、任意のディレクトリに出力できる。<br>
<code>secure_file_priv</code> にディレクトリが指定されている場合は、そのディレクトリ内にのみ出力できる。<br>
<br><br>


{{#seo:
|title={{PAGENAME}} : Exploring Electronics and SUSE Linux | MochiuWiki
|keywords=MochiuWiki,Mochiu,Wiki,Mochiu Wiki,MySQL,Table,テーブル,CREATE TABLE,ALTER TABLE,DROP TABLE,Database,データベース,SQL,InnoDB,MyISAM,ストレージエンジン,制約,PRIMARY KEY,FOREIGN KEY,NOT NULL,UNIQUE,CHECK,文字コード,照合順序,Collation,TRUNCATE,DELETE,インポート,エクスポート,CSV,LOAD DATA,SELECT INTO OUTFILE,電気回路,電子回路,基板,プリント基板
|description={{PAGENAME}} - MySQLのテーブル作成、変更、削除、制約、文字コード、照合順序、インポート、エクスポートに関する総合ガイド | This page is {{PAGENAME}} in our wiki about electronic circuits and SUSE Linux
|image=/resources/assets/MochiuLogo_Single_Blue.png
}}

__FORCETOC__
[[カテゴリ:MySQL]]