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

== 概要 ==
<br><br>

== テーブルの作成 ==
==== 文字コード ====
テーブルの作成時において、文字コードを指定する。<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE <テーブル名> (
    <カラム名 1> <型名>
    <カラム名 2> <型名>
    -- ...略
    CHARACTER SET=utf8mb4  -- テーブル全体の文字コードをutf8mb4に指定する場合
 );
 
 -- または
 
 CREATE TABLE <テーブル名> (
    <カラム名 1> <型名> CHARACTER SET=utf8mb4  -- 特定のカラムの文字コードをutf8mb4に指定する場合
    <カラム名 2> <型名>
    -- ...略
 );
 </syntaxhighlight>
<br>
MySQLのデフォルトの文字コードを確認する。<br>
 <syntaxhighlight lang="mysql">
 Show Variables Like "chara%";
 </syntaxhighlight>
<br>
MySQLのデフォルトの文字コードを設定する場合は、my.cnfファイルを編集する。<br>
<u>my.cnfファイルを変更した後は、MySQLを再起動する必要がある。</u><br>
 <syntaxhighlight lang="ini">
 # my.cnfファイル
 
 [mysqld]
 character-set-server=utf8mb4   # MySQLサーバのデフォルトの文字コードを指定する
 
 [client]
 default-character-set=utf8mb4  # MySQLクライアントのデフォルトの文字コードを指定する
 </syntaxhighlight>
<br>
既存のテーブルの文字コードは、INFORMATION_SCHEMAデータベースのTABLESテーブルから確認することができる。<br>
<code>TABLE_COLLATION</code>カラムの値が、そのテーブルに設定された文字コードに相当する。<br>
 <syntaxhighlight lang="mysql">
 SELECT TABLE_NAME,TABLE_COLLATION
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA='<データベース名>';
 </syntaxhighlight>
<br>
既存のテーブルの文字コードを変更する。<br>
 <syntaxhighlight lang="mysql">
 ALTER TABLE <テーブル名> CONVERT TO CHARACTER SET <文字コード>;
 
 # 例: 文字コードをutf8mb4に変更
 ALTER TABLE <テーブル名> CONVERT TO CHARACTER SET　utf8mb4
 </syntaxhighlight>
<br>

==== 既存のテーブルの存在を確認して作成 ====
既存のテーブルが存在していない場合はテーブルを作成する。<br>
これは、テーブルの重複、または、既存のテーブルを上書き/削除することを防ぐことができる。<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE IF NOT EXISTS <テーブル名> (
    <カラム名 1> <型名>
    CHARACTER SET=utf8mb4       -- 文字コード
    COLLATE utf8mb4_general_ci  -- 照合順序
 );
 </syntaxhighlight>
<br>
==== テーブルのクローニングおよびコピー ====
<code>CREATE TABLE ... LIKE</code>文、または、<code>CREATE TABLE ... AS SELECT</code>文を使用して、<br>
元のテーブルに定義されているカラム属性やインデックス等、別のテーブルの定義に基づいて空のテーブルを作成する。<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE <新しいテーブル名> LIKE <クローン元のテーブル名>;
 -- または
 CREATE TABLE <新しいテーブル名> AS SELECT * FROM <クローン元のテーブル名>;
 </syntaxhighlight>
<br>
==== 一時テーブルの作成 ====
MySQLでは、一時テーブルは現在のセッション内でのみ表示され、セッションがクローズされると自動的に削除される。<br>
つまり、複数の異なるセッションが同じ一時テーブル名を使用することができ、互いに、同じ名前の既存の一時テーブル以外のテーブルと競合することはない。<br>
(既存のテーブルは、一時テーブルが削除されるまで非表示になる)<br>
<br>
InnoDBでは、圧縮一時テーブルはサポートされていない。<br>
<code>innodb_strict_mode</code>オプションが有効(デフォルト)な場合、<br>
<code>ROW_FORMAT=COMPRESSED</code>、または、<code>KEY_BLOCK_SIZE</code>が指定されている場合、<code>CREATE TEMPORARY TABLE</code>文はエラーとなる。<br>
<code>innodb_strict_mode</code>オプションが無効な場合は、警告が出力されて、圧縮されていない行形式を使用して一時テーブルが作成される。<br>
<br>
<code>innodb_file_per-table</code>オプションは、InnoDB一時テーブルの作成には影響しない。<br>
 <syntaxhighlight lang="mysql">
 CREATE TEMPORARY TABLE <テーブル名> (
    <カラム名 1> <型名> <カラム 1のオプション>
    <カラム名 2> <型名> <カラム 2のオプション>
    -- ...略
 );
 
 -- または
 
 CREATE TEMPORARY TABLE IF NOT EXISTS <テーブル名> (
    <カラム名 1> <型名> <カラム 1のオプション>
    <カラム名 2> <型名> <カラム 2のオプション>
    -- ...略
 );
 </syntaxhighlight>
<br>
一時テーブルを作成するには、<code>CREATE TEMPORARY TABLES</code>権限が必要となる。<br>
<br>
ただし、セッションが一時テーブルを作成した後は、サーバはそのテーブルに対するそれ以上の権限を確認しないため、<br>
セッションの作成により、<code>DROP TABLE</code>文、<code>INSERT</code>文、<code>UPDATE</code>文、<code>SELECT</code>文等のあらゆる操作をテーブル上で実行できる。<br>
<br>
<u>一時テーブルは、データベース(スキーマ)と非常に疎な関係を持っており、データベースの削除後も、そのデータベース内で作成された一時テーブルは自動的には削除されない。</u><br>
<br>
<u>※注意</u><br>
<u><code>START TRANSACTION</code>文または<code>BEGIN</code>文の中において、一時テーブルを作成後に<code>ROLLBACK</code>文を実行する場合、</u><br>
<u>一時テーブル自体は削除されないが、レコードは全ては削除される。</u><br>
<u>つまり、一時テーブルを作成する<code>CREATE TEMPORARY TABLE</code>文は、<code>ROLLBACK</code>文の対象にならず、レコードを複製する箇所のみがロールバックされる。</u><br>
<br><br>

== 照合順序 ==
==== 照合順序とは ====
MySQLにおいて、照合順序（Collation）はテーブルや列ごとに設定することができる。<br>
照合順序は、文字列の比較やソートの際に使用され、異なる言語や地域に合わせてテキストの比較を行うために重要である。<br>
<br>
MySQLでは、様々な照合順序が提供されている。<br>
例えば、大文字小文字を区別やアクセントを考慮するかどうか等の違いがある。<br>
<br>
* utf8mb4_general_ci
*: 大文字小文字を区別せず、アクセントを区別しない。
*: 通常の比較の場合には適しているが、文字の並び順は一般的な場合に従う。
*: <br>
* utf8mb4_bin
*: バイナリ比較を行う。
*: 大文字小文字やアクセント等を区別して、バイト単位で比較する。
*: これは厳密なバイナリ比較が必要な場合に使用される。
*: <br>
* utf8_general_ci
*: utf8mb4と同様、大文字小文字を区別せず、アクセントを区別しない。
*: ただし、utf8mb4よりも少ない文字セットを使用する。
*: <br>
* latin1_swedish_ci
*: ラテン1文字セットに基づいた照合順序であり、スウェーデンの標準に従う。
*: 大文字小文字を区別せず、アクセントを区別しない。
<br>
照合順序の詳細を知りたい場合は、MySQLの公式ドキュメントを参照すること。<br>
* MySQL 8
*: 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>
 <syntaxhighlight lang="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';
 </syntaxhighlight>
<br>
==== カラムの文字コードと照合順序の確認 ====
 <syntaxhighlight lang="mysql">
 SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = '<テーブル名>'
    AND table_schema = '<データベース名>'   -- オプション
    AND column_name LIKE '<ワイルドカード>'  -- オプション
 </syntaxhighlight>
<br>

==== テーブル作成時における照合順序の指定 ====
以下の例では、データベースの作成時において、照合順序をutf8mb4_unicode_ciに指定している。<br>
 <syntaxhighlight lang="mysql">
 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
 );
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u>テーブル全体に対する照合順序を一括で指定する方法は提供されていないことに注意する。</u><br>
<br>
==== 既存のテーブルやカラムの照合順序の変更 ====
既存のテーブルやカラムの照合順序を変更することも可能である。<br>
<br>
===== テーブルの照合順序の変更 =====
テーブルの照合順序を変更する。<br>
<u>ただし、各カラムの照合順序は変更されないことに注意する。</u><br>
 <syntaxhighlight lang="mysql">
 ALTER TABLE tbl_name
    CHARACTER SET <文字コード>
    COLLATE <照合順序>
 
 -- 例: sample_tableテーブルの照合順序をutf8mb4_general_ciに変更する
 ALTER TABLE sample_table
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_general_ci
 </syntaxhighlight>
<br>
===== 全ての文字カラムの照合順序の変更 =====
全ての文字カラムの文字コードおよび照合順序を変更する。<br>
これは、テーブルの文字コードと各カラムの文字コードを揃える時に使用すると便利である。<br>
 <syntaxhighlight lang="mysql">
 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
 </syntaxhighlight>
<br>
===== 任意のカラムの照合順序の変更 =====
以下の例では、テーブルの任意のカラムの照合順序を<code>utf8mb4_unicode_ci</code>に変更している。<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>

==== デフォルトの照合順序 ====
テーブルにおけるデフォルトの照合順序を確認する場合、<br>
テーブル自体に照合順序が指定されていない場合、テーブルはデータベースのデフォルトの照合順序を継承する。<br>
<br>
デフォルトのテーブル照合順序を確認する。<br>
 <syntaxhighlight lang="mysql">
 SHOW TABLE STATUS LIKE '<テーブル名>';
 </syntaxhighlight>
<br>
これは、テーブルのステータス情報を表示する。<br>
出力結果において、<code>Collation</code>カラムの値がテーブルの照合順序である。<br>
<br>
もし、テーブルに照合順序が指定されていない場合、データベースのデフォルトの照合順序が表示される。<br>
<br><br>

== テーブルの削除 ==
<u><code>DROP TABLE</code>文により削除したテーブルは、復元することができないことに注意する。</u><br>
 <syntaxhighlight lang="mysql">
 DROP TABLE <テーブル名>;
 </syntaxhighlight>
<br>
複数のテーブルを一括して削除する場合、カンマ区切りでテーブル名を列挙する。<br>
 <syntaxhighlight lang="mysql">
 DROP TABLE <テーブル名 1>, <テーブル名 2>, <テーブル名 3>;
 </syntaxhighlight>
<br><br>

== レコードの削除 ==
==== DELETE文 ====
<code>DELETE</code>文は、テーブル内のレコードを削除するクエリである。<br>
 <syntaxhighlight lang="mysql">
 DELETE FROM <テーブル名>;
 </syntaxhighlight>
<br>
<code>WHERE</code>句で条件を指定しない場合、テーブル内の全てのデータが削除される。<br>
特定のレコードのみを削除する場合は、必ず<code>WHERE</code>句で条件指定をする。<br>
 <syntaxhighlight lang="mysql">
 DELETE FROM <テーブル名> WHERE <カラム名> = <値>;
 </syntaxhighlight>
<br>
==== TRUNCATE文 ====
<code>TRUNCATE</code>文は、テーブル内のレコードを完全に削除するクエリである。<br>
<br>
<code>TRUNCATE</code>文は<code>DELETE</code>文とは異なり、<code>WHERE</code>句での条件指定ができない。<br>
そのため、テーブル内のレコードは必ず全て削除される。<br>
 <syntaxhighlight lang="mysql">
 TRUNCATE TABLE <テーブル名>;
 </syntaxhighlight>
<br>
<code>TRUNCATE</code>文は、条件を指定してデータの削除ができないことに注意する。<br>
<br><br>

== テーブルの確認 ==
現在使用しているデータベースのテーブルを確認する。<br>
 <syntaxhighlight lang="mysql">
 USE <データベース名>;
 SHOW TABLES;
 </syntaxhighlight>
<br><br>

== 制約 ==
==== NOT NULL ====
NULL値を禁止する。<br>
 <syntaxhighlight lang="sql">
 CREATE TABLE product  
 (
    product_id       INT PRIMARY KEY,
    product_name     VARCHAR(16) UNIQUE NOT NULL,
    price            INT
 );
 </syntaxhighlight>
<br>
==== CHECK ====
条件を指定して、条件を満たさないデータを禁止する。<br>
 <syntaxhighlight lang="sql">
 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>
==== UNIQUE KEY ====
* 重複したデータを禁止する。
* 複数のカラムに設定できる。
* ただし、<code>NULL</code>を禁止するわけではない。
<br>
 <syntaxhighlight lang="sql">
 CREATE TABLE user
 (
    user_id      INT PRIMARY KEY,
    telephone    CHAR(8),
    addreess     VARCHAR(255),
    UNIQUE (telephone, address)
 );
 </syntaxhighlight>
<br>
==== PRIMARY KEY ====
* 一意を保証する。
* 重複と<code>NULL</code>を禁止する。
* 1つのテーブルに1つしか指定できない。
<br>
 <syntaxhighlight lang="sql">
 CREATE TABLE user
 (
    user_id      INT PRIMARY KEY,
    telephone    CHAR(8),
    age          INT
 );
 </syntaxhighlight>
<br>
複合主キーも指定できる。<br>
 <syntaxhighlight lang="sql">
 CREATE TABLE price
 (
    shop_code      CHAR(4),
    product_code   CHAR(4),
    price          INT,
    PRIMARY KEY(shop_code , product_code)
 );
 </syntaxhighlight>
<br>
==== FOREIGN KEY ====
他のテーブルのカラムを参照して、そのカラムに存在しないデータを禁止する。<br>
 <syntaxhighlight lang="sql">
 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)
 );
 </syntaxhighlight>
<br>
2つのカラムを外部参照することもできる。<br>
 <syntaxhighlight lang="sql">
 CREATE TABLE item
 (
    maker_id    CHAR(10),
    product_id  CHAR(10),
    PRIMARY KEY (maker_id , product_id)
 );
 </syntaxhighlight>
<br>
 <syntaxhighlight lang="sql">
 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>
==== 制約の名前を設定 ====
 <syntaxhighlight lang="sql">
 CONSTRAINT <制約の名前> <制約>
 </syntaxhighlight>
<br>
==== 制約の削除 ====
 <syntaxhighlight lang="sql">
 ALTER TABLE <テーブル名> DROP CONSTRAINT <制約の名前>;
 </syntaxhighlight>
<br>
==== デフォルト値の設定 ====
 <syntaxhighlight lang="sql">
 CREATE TABLE product
 (
    product_id  INT NOT NULL PRIMARY KEY,
    name        CHAR(16) UNIQUE,
    price       INT DEFAULT 2000
 );
 </syntaxhighlight>
<br><br>

== インポート ==
以下に示すようなCSVファイルがあるとする。<br>
 "1001","2024-01-05 09:10:00","0"
 "1002","2024-02-04 10:05:00","1"
 "1003","2024-02-23 21:27:00","0"
<br>
CSVファイルと合致するようなテーブルがあるとする。<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE <テーブル名> (
    ID INT,
    F_DATE TIMESTAMP,
    FLAG INT,
    PRIMARY KEY(ID)
 );
 </syntaxhighlight>
<br>
CSVファイルをテーブルにインポートする。<br>
 <syntaxhighlight lang="mysql">
 LOAD DATA LOCAL INLINE "<CSVファイルのパス>"
    INTO TABLE <インポート先のテーブル名>
    FIELDS TERMINATED by '<デリミタ>'  -- CSVの場合は,(カンマ)を指定する
 </syntaxhighlight>
<br><br>


__FORCETOC__
[[カテゴリ:MySQL]]