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の公式ドキュメントを参照すること。
テーブル作成時における照合順序の指定
以下の例では、データベースの作成時において、照合順序を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
);
※注意
テーブル全体に対する照合順序を一括で指定する方法は提供されていないことに注意する。
既存のテーブルや列の照合順序の変更
既存のテーブルや列の照合順序を変更することも可能である。
以下の例では、テーブルの該当カラムの照合順序をutf8mb4_unicode_ciに変更している。
ALTER TABLE <テーブル名> MODIFY <カラム名> <カラムの型名> COLLATE utf8mb4_unicode_ci;
-- 例
ALTER TABLE <テーブル名> MODIFY hoge_col VARCHAR(255) COLLATE utf8mb4_unicode_ci;
デフォルトの照合順序
テーブルにおけるデフォルトの照合順序を確認する場合、
テーブル自体に照合順序が指定されていない場合、テーブルはデータベースのデフォルトの照合順序を継承する。
デフォルトのテーブル照合順序を確認する。
SHOW TABLE STATUS LIKE '<テーブル名>';
これは、テーブルのステータス情報を表示する。
出力結果において、Collationカラムの値がテーブルの照合順序である。
もし、テーブルに照合順序が指定されていない場合、データベースのデフォルトの照合順序が表示される。
テーブルの削除
DROP TABLE文により削除したテーブルは、復元することができないことに注意する。
DROP TABLE <テーブル名>;
テーブルの確認
現在使用しているデータベースのテーブルを確認する。
USE <データベース名>;
SHOW TABLES;