「MySQL - テーブル」の版間の差分

==== 基本構文 ====

<br>

  CREATE TABLE table_name (

     column1 datatype [constraint],

     column2 datatype [constraint],

     ...,

     table_level_constraint

  ) table_options;

<br>

  CREATE TABLE users (

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

MySQLでは、テーブルごとにストレージエンジンを指定できる。<br>

<br>

下表に、主要なストレージエンジンを示す。<br>

<br>

<center>

{| class="wikitable"

| InnoDB || 対応 (ACID準拠) || 行レベル || サポート || MySQL 5.6以降 || 本番環境、トランザクション処理

|-

|-

</center>

ストレージエンジンを指定する構文を以下に示す。<br>

  CREATE TABLE t1 (

    id INT PRIMARY KEY,

     data VARCHAR(100)

 

     cache_data VARCHAR(255)

) ENGINE=MEMORY;

<br>

  ALTER TABLE table_name ENGINE=InnoDB;

これは、テーブルの重複、または、既存のテーブルを上書き / 削除することを防ぐことができる。<br>

<br>

  CREATE TABLE IF NOT EXISTS table_name (

     column1 datatype,

     column2 datatype

  CREATE TABLE new_table LIKE original_table;

 

 

  CREATE TABLE new_table AS SELECT * FROM original_table;

<br>

* <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一時テーブルの作成には影響しない。

<br>

<code>temptable_max_ram</code> システム変数 (デフォルト1[GiB]) により、メモリ使用量を制御できる。<br>

一時テーブルとトランザクションの関係には注意が必要である。<br>

<code>START TRANSACTION</code> 文 または <code>BEGIN</code> 文の中において、一時テーブルを作成後に <code>ROLLBACK</code> 文を実行する場合、<br>

動作例を以下に示す。<br>

 

  CREATE TEMPORARY TABLE temp_data (

     id INT,

     value VARCHAR(100)

  SELECT TABLE_COMMENT

</syntaxhighlight>

<br><br>

 

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

<br>

<syntaxhighlight lang="mysql">

     column2 TEXT

-- または、特定のカラムに文字コードを指定

MySQLのデフォルトの文字コードを確認する。<br>

<syntaxhighlight lang="mysql">

</syntaxhighlight>

MySQLのデフォルトの文字コードを設定する場合は、my.cnfファイルを編集する。<br>

<syntaxhighlight lang="mysql">

</syntaxhighlight>

<br>

</syntaxhighlight>

MySQLにおいて、照合順序 (Collation) はテーブルや列ごとに設定することができる。<br>

* <code>utf8mb4_general_ci</code>よりも正確なソート結果を返すが、やや低速である

* Unicode 5.2.0標準に基づいた比較を行う

* as (Accent Sensitive)、cs (Case Sensitive) を意味する

* 大文字小文字およびアクセントを区別する

| <code>utf8mb4_0900_as_ci</code>

* アクセントを区別するが、大文字小文字は区別しない

* 日本語向けの照合順序である (MySQL 8.0以降)

* JIS X 4061規格に基づいた日本語のソート順を提供する

* 日本語向けの照合順序であり、カナの区別 (ks: Kana Sensitive) を行う

* ひらがなとカタカナを区別して比較する

| <code>utf8mb4_bin</code>

* バイナリ比較を行う

| <code>utf8_general_ci</code>

* ただし、utf8mb4よりも少ない文字セットを使用する (3バイトUTF-8)

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

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

| <code>latin1_bin</code>

* 大文字小文字およびアクセントを区別する

* 全てのデータをバイト列として比較する

* 文字セットに依存しない厳密なバイナリ比較を行う

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

使用できる文字コードと照合順序の組み合わせは、<code>INFORMATION_SCHEMA</code> データベースの <code>COLLATION_CHARACTER_SET_APPLICABILITY</code> テーブルから確認できる。<br>

  SHOW COLLATION;

  -- 特定の文字セットの照合順序のみを表示

  SHOW COLLATION WHERE Charset = 'utf8mb4';

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = 'database_name'

  AND TABLE_NAME = 'table_name';

特定のカラムのみを確認する場合は、<code>WHERE</code> 句で条件を追加する。<br>

<syntaxhighlight lang="mysql">

WHERE TABLE_SCHEMA = 'database_name'

  AND TABLE_NAME = 'table_name'

  AND COLUMN_NAME LIKE 'column%';

<br>

  ) CHARACTER SET utf8mb4

<br>

  CREATE TABLE table_name (

     column1 VARCHAR(255) COLLATE utf8_general_ci,

     column2 INT,

     column3 TEXT COLLATE utf8_unicode_ci

<u>テーブルに照合順序を指定した場合、カラムに照合順序が指定されていない場合は、テーブルの照合順序が継承される。</u><br>

<u>テーブルにも照合順序が指定されていない場合は、データベースのデフォルトの照合順序が継承される。</u><br>

==== 既存のテーブルやカラムの文字コードと照合順序の変更 ====

ただし、各カラムの照合順序は変更されないことに注意する。<br>

<br>

  ALTER TABLE table_name

  CHARACTER SET utf8mb4

COLLATE utf8mb4_general_ci;

  ALTER TABLE table_name

  CONVERT TO CHARACTER SET utf8mb4

COLLATE utf8mb4_general_ci;

特定のカラムのみの照合順序を変更する場合は、以下のようにする。<br>

<br>

  ALTER TABLE table_name

  MODIFY column_name VARCHAR(255) COLLATE utf8mb4_unicode_ci;

<br>

出力結果において、<code>Collation</code> カラムの値がテーブルの照合順序である。<br>

<br>

データベースのデフォルト照合順序を確認する場合は、以下のようにする。<br>

<br>

  SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME

WHERE SCHEMA_NAME = 'database_name';

<br>

  SHOW VARIABLES LIKE 'collation%';

== 制約 ==

==== NOT NULL ====

<code>NOT NULL</code> 制約は、カラムにNULL値を禁止する。<br>

<br>

  CREATE TABLE product (

);

<code>NOT NULL</code> 制約が設定されたカラムには、必ず値を挿入する必要がある。<br>

NULL値を挿入しようとすると、エラーが発生する。<br>

既存カラムに<code>NOT NULL</code> 制約を追加する場合は、以下のようにする。<br>

  ALTER TABLE table_name

MODIFY column_name datatype NOT NULL;

<code>CHECK</code> 制約は、条件を指定して、条件を満たさないデータを禁止する。<br>

<code>CHECK</code> 制約は、MySQL 8.0.16以降でサポートされている。<br>

<br>

  CREATE TABLE user (

     user_id INT PRIMARY KEY,

     age INT,

     city_code CHAR(4),

     gender CHAR(1),

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

* NULL値は禁止しない。(NULL値は複数許可される)

  <syntaxhighlight lang="mysql">

  CREATE TABLE user (

     user_id INT PRIMARY KEY,

    email VARCHAR(100) UNIQUE,

     telephone CHAR(8),

     address VARCHAR(255),

<code>PRIMARY KEY</code> 制約の特徴を以下に示す。<br>

* 自動的にインデックスが作成される

  <syntaxhighlight lang="mysql">

  CREATE TABLE user (

     user_id INT PRIMARY KEY,

     telephone CHAR(8),

     age INT

  <syntaxhighlight lang="mysql">

  CREATE TABLE price (

     shop_code CHAR(4),

     product_code CHAR(4),

     price INT,

     PRIMARY KEY (shop_code, product_code)

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

<br>

  <syntaxhighlight lang="mysql">

  CREATE TABLE item (

  );

CREATE TABLE dealer (

     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 lang="mysql">

  CREATE TABLE orders (

     order_id INT PRIMARY KEY,

     customer_id INT,

     FOREIGN KEY (customer_id) REFERENCES customers (customer_id)

  <syntaxhighlight lang="mysql">

  CREATE TABLE table_name (

    CONSTRAINT constraint_name constraint_definition

</syntaxhighlight>

<br>

  </syntaxhighlight>

*: <syntaxhighlight lang="mysql">

  ALTER TABLE table_name DROP PRIMARY KEY;

*: <syntaxhighlight lang="mysql">

ALTER TABLE table_name DROP FOREIGN KEY constraint_name;

*: <syntaxhighlight lang="mysql">

ALTER TABLE table_name DROP INDEX constraint_name;

  <syntaxhighlight lang="mysql">

  CREATE TABLE product (

     product_id INT NOT NULL PRIMARY KEY,

     name CHAR(16) UNIQUE,

     price INT DEFAULT 2000,

== レコードの削除 ==