MySQL - テーブル

テーブルの作成

文字コード

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

 CREATE TABLE <テーブル名> (
    <カラム名 1> <型名>
    <カラム名 2> <型名>
    -- ...略
    CHARACTER SET=utf8mb4  -- テーブル全体の文字コードをutf8mb4に指定する場合
 );
 
 -- または
 
 CREATE TABLE <テーブル名> (
    <カラム名 1> <型名> CHARACTER SET=utf8mb4  -- 特定のカラムの文字コードをutf8mb4に指定する場合
    <カラム名 2> <型名>
    -- ...略
 );

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='<データベース名>';

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

 ALTER TABLE <テーブル名> CONVERT TO CHARACTER SET <文字コード>;
 
 # 例: 文字コードをutf8mb4に変更
 ALTER TABLE <テーブル名> CONVERT TO CHARACTER SET　utf8mb4

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

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

 CREATE TABLE IF NOT EXISTS <テーブル名> (
    <カラム名 1> <型名>
    CHARACTER SET=utf8mb4       -- 文字コード
    COLLATE utf8mb4_general_ci  -- 照合順序
 );

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

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

 CREATE TABLE <新しいテーブル名> LIKE <クローン元のテーブル名>;
 -- または
 CREATE TABLE <新しいテーブル名> AS SELECT * FROM <クローン元のテーブル名>;

一時テーブルの作成

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

InnoDBでは、圧縮一時テーブルはサポートされていない。
innodb_strict_modeオプションが有効(デフォルト)な場合、
ROW_FORMAT=COMPRESSED、または、KEY_BLOCK_SIZEが指定されている場合、CREATE TEMPORARY TABLE文はエラーとなる。
innodb_strict_modeオプションが無効な場合は、警告が出力されて、圧縮されていない行形式を使用して一時テーブルが作成される。

innodb_file_per-tableオプションは、InnoDB一時テーブルの作成には影響しない。

 CREATE TEMPORARY TABLE <テーブル名> (
    <カラム名 1> <型名> <カラム 1のオプション>
    <カラム名 2> <型名> <カラム 2のオプション>
    -- ...略
 );
 
 -- または
 
 CREATE TEMPORARY TABLE IF NOT EXISTS <テーブル名> (
    <カラム名 1> <型名> <カラム 1のオプション>
    <カラム名 2> <型名> <カラム 2のオプション>
    -- ...略
 );

一時テーブルを作成するには、CREATE TEMPORARY TABLES権限が必要となる。

ただし、セッションが一時テーブルを作成した後は、サーバはそのテーブルに対するそれ以上の権限を確認しないため、
セッションの作成により、DROP TABLE文、INSERT文、UPDATE文、SELECT文等のあらゆる操作をテーブル上で実行できる。

一時テーブルは、データベース(スキーマ)と非常に疎な関係を持っており、データベースの削除後も、そのデータベース内で作成された一時テーブルは自動的には削除されない。

照合順序

照合順序とは

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

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

• utf8mb4_general_ci

  大文字小文字を区別せず、アクセントを区別しない。

  通常の比較の場合には適しているが、文字の並び順は一般的な場合に従う。

  
  

• utf8mb4_bin

  バイナリ比較を行う。

  大文字小文字やアクセント等を区別して、バイト単位で比較する。

  これは厳密なバイナリ比較が必要な場合に使用される。

  
  

• utf8_general_ci

  utf8mb4と同様、大文字小文字を区別せず、アクセントを区別しない。

  ただし、utf8mb4よりも少ない文字セットを使用する。

  
  

• latin1_swedish_ci

  ラテン1文字セットに基づいた照合順序であり、スウェーデンの標準に従う。

  大文字小文字を区別せず、アクセントを区別しない。

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

• MySQL 8

  https://dev.mysql.com/doc/refman/8.0/en/charset-mysql.html

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

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

 SELECT COLLATION_NAME,CHARACTER_SET_NAME
    FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
    WHERE COLLATION_NAME='<照合順序>';
 
 -- 例: 照合順序にutf8mb4_general_ciが使用できるかどうかを確認
 SELECT COLLATION_NAME,CHARACTER_SET_NAME
    FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
    WHERE COLLATION_NAME='utf8mb4_general_ci';

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

 SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = '<テーブル名>'
    AND table_schema = '<データベース名>'   -- オプション
    AND column_name LIKE '<ワイルドカード>'  -- オプション

テーブル作成時における照合順序の指定

以下の例では、データベースの作成時において、照合順序をutf8mb4_unicode_ciに指定している。

 CREATE TABLE <テーブル名> (
    <カラム名 1> VARCHAR(255) COLLATE utf8mb4_unicode_ci,
    <カラム名 2> INT
 );
 
 CREATE TABLE <テーブル名> (
    <カラム名 1> VARCHAR(255) COLLATE utf8_general_ci,
    <カラム名 2> INT,
    <カラム名 3> TEXT COLLATE utf8_unicode_ci
 );

※注意
テーブル全体に対する照合順序を一括で指定する方法は提供されていないことに注意する。

既存のテーブルやカラムの照合順序の変更

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

全てのカラムの照合順序の変換

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

 ALTER TABLE <テーブル名>
    CONVERT TO CHARACTER SET <文字コード>
    COLLATE <照合順序>  -- オプション
 
 -- 例: sample_tableテーブルの文字コードをutf8mb4、照合順序をutf8mb4_general_ciに変更する
 ALTER TABLE sample_table
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_general_ci

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

以下の例では、テーブルの任意のカラムの照合順序をutf8mb4_unicode_ciに変更している。

 ALTER TABLE <テーブル名> MODIFY <カラム名> <カラムの型名> COLLATE utf8mb4_unicode_ci;
 
 -- 例: sample_tableテーブルのsample_colカラムをutf8mb4_unicode_ciに変更する
 ALTER TABLE sample_table MODIFY sample_col VARCHAR(255) COLLATE utf8mb4_unicode_ci;

デフォルトの照合順序

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

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

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

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

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

テーブルの削除

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

 DROP TABLE <テーブル名>;

テーブルの確認

現在使用しているデータベースのテーブルを確認する。

 USE <データベース名>;
 SHOW TABLES;

インポート

以下に示すような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 <テーブル名> (
    ID INT,
    F_DATE TIMESTAMP,
    FLAG INT,
    PRIMARY KEY(ID)
 );

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

 LOAD DATA LOCAL INLINE "<CSVファイルのパス>"
    INTO TABLE <インポート先のテーブル名>
    FIELDS TERMINATED by '<デリミタ>'  -- CSVの場合は,(カンマ)を指定する