MySQL 簡易リファレンス ～ テーブル作成

覚書です。

 

 

自分用のリファレンスとして、書いています。

回りくどい細かい説明抜きに、ひたすら例 (?) のみを上げています。

 

テーブルの作成

 

基本

CREATE TABLE `table_name` (
    `column1` データ型 DEFAULT 初期値 PRYMARY KEY,
    `column2` データ型 DEFAULT 初期地,
        :
        :
)

• カラム（列、フィールド）定義に指定できる PRIMARY KEY は、1カラムのみ。
• データ型は必須。
• DEFAULT初期値についてはオプション。ただし、PRYMARY KEY を指定するカラムには NULL は指定できない。

 

存在を確認して作成

DROP TABLE IF EXISTS `table_name`;
CREATE TABLE `table_name` ... ;

 

単一カラムの主キーを指定してテーブル作成

単一カラムにプライマリキーを指定する場合は、カラム定義に直接書ける。

CREATE TABLE `thread` (
    `thread_id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
    `bbs_id` INT(11) NOT NULL,
    `thread_no` INT(11) NOT NULL,
    `thread_sid` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8mb4_bin',
    `thread_url` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8mb4_bin',
    `thread_title` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8mb4_bin',
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT current_timestamp()
)
COLLATE='utf8mb4_bin'
ENGINE=InnoDB
;

 

単一カラム・複数カラムの主キーを指定して作成

複数のカラムに渡ってプライマリキーを指定する場合は、次の様にして定義する （単一カラムでも同様に定義できる）。

CREATE TABLE `thread` (
    `thread_id` INT(11) NOT NULL AUTO_INCREMENT
    `bbs_id` INT(11) NOT NULL,
    `thread_no` INT(11) NOT NULL,
    `thread_sid` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8mb4_bin',
    `thread_url` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8mb4_bin',
    `thread_title` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8mb4_bin',
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT current_timestamp(),
    PRIMARY KEY(`thread_id`, `bbs_id`)
)
COLLATE='utf8mb4_bin'
ENGINE=InnoDB
;

 

また、次のようプライマリキーに名前 ( symbol ) をつけることもできる。

CREATE TABLE `thread` (
    `thread_id` INT(11) NOT NULL AUTO_INCREMENT
    `bbs_id` INT(11) NOT NULL,
    `thread_no` INT(11) NOT NULL,
    `thread_sid` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8mb4_bin',
    `thread_url` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8mb4_bin',
    `thread_title` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8mb4_bin',
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT current_timestamp(),
    CONSTRAINT `primary_key_name` PRIMARY KEY (`thread_id, bbs_id`)
)
COLLATE='utf8mb4_bin'
ENGINE=InnoDB
;

 

UNIQUE 制約と INDEX 制約を指定して作成

CREATE TABLE `thread` (
    `thread_id` INT(11) NOT NULL AUTO_INCREMENT,
    `bbs_id` INT(11) NOT NULL,
    `thread_no` INT(11) NOT NULL,
    `thread_sid` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8mb4_bin',
    `thread_url` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8mb4_bin',
    `thread_title` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8mb4_bin',
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT current_timestamp(),
    PRIMARY KEY (`thread_id`),
    UNIQUE INDEX `ux_thread_id_bbs_id` (`thread_id`, `bbs_id`),
    INDEX `ix_thread_no` (`thread_no`),
    INDEX `ix_thread_sid` (`thread_sid`)
)
COLLATE='utf8mb4_bin'
ENGINE=InnoDB
;

 

上記は、MySQL のリファレンスによれば、次のようにも書けます。

CREATE TABLE `thread` (
    `thread_id` INT(11) NOT NULL AUTO_INCREMENT,
    `bbs_id` INT(11) NOT NULL,
    `thread_no` INT(11) NOT NULL,
    `thread_sid` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8mb4_bin',
    `thread_url` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8mb4_bin',
    `thread_title` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8mb4_bin',
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT current_timestamp(),
    CONSTRAINT `primary_key_name` PRIMARY KEY (`thread_id`),
    CONSTRAINT `unique_index_name` UNIQUE INDEX (`thread_id`, `bbs_id`),
    CONSTRAINT `index_name1` INDEX (`thread_no`),
    CONSTRAINT `index_name2` INDEX (`thread_sid`)
)
COLLATE='utf8mb4_bin'
ENGINE=InnoDB
;

 

外部キー制約を指定して作成

thread テーブルの bbs_id カラムに、bbs テーブルの bbs_id カラムを参照する外部キー制約を設定しています。

CREATE TABLE `thread` (
    `thread_id` INT(11) NOT NULL AUTO_INCREMENT,
    `bbs_id` INT(11) NOT NULL,
    `thread_no` INT(11) NOT NULL,
    `thread_sid` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8mb4_bin',
    `thread_url` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8mb4_bin',
    `thread_title` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8mb4_bin',
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT current_timestamp(),
    CONSTRAINT `primary_key_name` PRIMARY KEY (`thread_id`),
    CONSTRAINT `unique_index_name` UNIQUE INDEX (`thread_id`, `bbs_id`),
    CONSTRAINT `index_name1` INDEX (`thread_no`),
    CONSTRAINT `index_name2` INDEX (`thread_sid`)
    CONSTRAINT `foreign_key_name` FOREIGN KEY (`bbs_id`) REFERENCES `bbs` (`bbs_id`)
)
COLLATE='utf8mb4_bin'
ENGINE=InnoDB
;

 

AUTO_INCREMENT を指定して作成

通常は、プライマリキーを指定して使うことが多いと思います。

CREATE TABLE table_name(
    `column1` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    :
    :
)

 

テーブル作成時に、AUTO_INCREMENT の初期値を指定。例では 連続番号の初期値を 100 としています。

CREATE TABLE table_name(
    `column1` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    :
    :
) ENGINE=InnoDB AUTO_INCREMENT=100 CHARSET=utf8 ...

 

テーブルを複製する

CREATE TABLE `destination` SELECT * FROM `source`;

 

テーブルの構造を複写する

CREATE TABLE `destination` LIKE `source`;