MySQL - テーブル

提供：MochiuWiki : SUSE, EC, PCB
ナビゲーションに移動 検索に移動

概要



テーブルの作成

文字コード

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

 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文等のあらゆる操作をテーブル上で実行できる。

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

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


照合順序

照合順序とは

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

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

  • utf8mb4_general_ci
    大文字小文字を区別せず、アクセントを区別しない。
    通常の比較の場合には適しているが、文字の並び順は一般的な場合に従う。

  • utf8mb4_bin
    バイナリ比較を行う。
    大文字小文字やアクセント等を区別して、バイト単位で比較する。
    これは厳密なバイナリ比較が必要な場合に使用される。

  • utf8_general_ci
    utf8mb4と同様、大文字小文字を区別せず、アクセントを区別しない。
    ただし、utf8mb4よりも少ない文字セットを使用する。

  • latin1_swedish_ci
    ラテン1文字セットに基づいた照合順序であり、スウェーデンの標準に従う。
    大文字小文字を区別せず、アクセントを区別しない。


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


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

使用できる文字コードと照合順序の組み合わせは、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 tbl_name
    CHARACTER SET <文字コード>
    COLLATE <照合順序>
 
 -- 例: sample_tableテーブルの照合順序をutf8mb4_general_ciに変更する
 ALTER TABLE sample_table
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_general_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 <カラム名> <カラムの型名: CHAR, VARCHAR, TEXT> 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 <テーブル名>;


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

 DROP TABLE <テーブル名 1>, <テーブル名 2>, <テーブル名 3>;



レコードの削除

DELETE文

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

 DELETE FROM <テーブル名>;


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

 DELETE FROM <テーブル名> WHERE <カラム名> = <値>;


TRUNCATE文

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

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

 TRUNCATE TABLE <テーブル名>;


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


テーブルの確認

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

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



制約

NOT NULL

NULL値を禁止する。

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


CHECK

条件を指定して、条件を満たさないデータを禁止する。

 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')))
 );


UNIQUE KEY

  • 重複したデータを禁止する。
  • 複数のカラムに設定できる。
  • ただし、NULLを禁止するわけではない。


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


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)
 );


FOREIGN KEY

他のテーブルのカラムを参照して、そのカラムに存在しないデータを禁止する。

 CREATE TABLE order
 (
    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)
 );


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

 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)
 );


制約の名前を設定

 CONSTRAINT <制約の名前> <制約>


制約の削除

 ALTER TABLE <テーブル名> DROP CONSTRAINT <制約の名前>;


デフォルト値の設定

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



インポート

以下に示すような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の場合は,(カンマ)を指定する



「http://mochiuwiki.e2.valueserver.jp/index.php?title=MySQL_-_テーブル&oldid=12023」から取得