🐬
MySQLのCREATE TABLEの構文リファレンスを読みたい
MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.1.20 CREATE TABLE ステートメント
に出てくる定義文の記号の意味について。
※ 基本的にPerplexityに聞いたのと、一般的なSQL知識からの推測も混ざっています。
全文
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
[IGNORE | REPLACE]
[AS] query_expression
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
create_definition: {
col_name column_definition
| {INDEX | KEY} [index_name] [index_type] (key_part,...)
[index_option] ...
| {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
[index_name] [index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name,...)
reference_definition
| check_constraint_definition
}
---記事での説明は↑まで
column_definition: {
data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
[VISIBLE | INVISIBLE]
[AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[COLLATE collation_name]
[COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
[ENGINE_ATTRIBUTE [=] 'string']
[SECONDARY_ENGINE_ATTRIBUTE [=] 'string']
[STORAGE {DISK | MEMORY}]
[reference_definition]
[check_constraint_definition]
| data_type
[COLLATE collation_name]
[GENERATED ALWAYS] AS (expr)
[VIRTUAL | STORED] [NOT NULL | NULL]
[VISIBLE | INVISIBLE]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[reference_definition]
[check_constraint_definition]
}
data_type:
(see 第11章「データ型」)
key_part: {col_name [(length)] | (expr)} [ASC | DESC]
index_type:
USING {BTREE | HASH}
index_option: {
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
| {VISIBLE | INVISIBLE}
|ENGINE_ATTRIBUTE [=] 'string'
|SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}
check_constraint_definition:
[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
reference_definition:
REFERENCES tbl_name (key_part,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
table_options:
table_option [[,] table_option] ...
table_option: {
AUTOEXTEND_SIZE [=] value
| AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
| CONNECTION [=] 'connect_string'
| {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
| DELAY_KEY_WRITE [=] {0 | 1}
| ENCRYPTION [=] {'Y' | 'N'}
| ENGINE [=] engine_name
| ENGINE_ATTRIBUTE [=] 'string'
| INSERT_METHOD [=] { NO | FIRST | LAST }
| KEY_BLOCK_SIZE [=] value
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] 'string'
| ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
| SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
| STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
| STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
| STATS_SAMPLE_PAGES [=] value
| TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]
| UNION [=] (tbl_name[,tbl_name]...)
}
partition_options:
PARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
| RANGE{(expr) | COLUMNS(column_list)}
| LIST{(expr) | COLUMNS(column_list)} }
[PARTITIONS num]
[SUBPARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) }
[SUBPARTITIONS num]
]
[(partition_definition [, partition_definition] ...)]
partition_definition:
PARTITION partition_name
[VALUES
{LESS THAN {(expr | value_list) | MAXVALUE}
|
IN (value_list)}]
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[(subpartition_definition [, subpartition_definition] ...)]
subpartition_definition:
SUBPARTITION logical_name
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
query_expression:
SELECT ... (Some valid select or union statement)
上から順に
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
[TEMPORARY]や[IF NOT EXISTS]について
-
[]がつくとoptional。 - つまり
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_nameはTEMPORARYやIF NOT EXISTSがあってもなくてもOK
(create_definition,...)について
create_definitionを複数指定できる。その際、()が必要。
CREATE TABLE employees ( <- ここの括弧
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
hire_date DATE
);
次に、似た形が出てくる (CREATE TABLE ... SELECT ステートメントの構文)
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
[IGNORE | REPLACE]
[AS] query_expression
(create_definition,...)と[(create_definition,...)]について
-
[(create_definition,...)]は[]があるのでoptional。-
query_expression(SELECT文)からテーブルを作るので、create_definitionがoptionalになる。多分。
-
|は、どちらかしか設定できない。
-
IGNORE | REPLACEはどちらかしか設定できない- つまり
[IGNORE | REPLACE]になると、[]がつくのでoptionalで、設定するときはどちらかしか設定できない、ということを表す。
- つまり
次に
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
{}は必ず指定しなければならない
-
{ hoge | huga }でhogeかhugaのどちらかを指定しなければならない。 - ここでは
LIKE old_tbl_nameか(LIKE old_tbl_name)の違いだが、これは構文上の()が省略できることを表す。
CREATE TABLE new_table LIKE old_table;
CREATE TABLE new_table (LIKE old_table);
備考: MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.1.20.3 CREATE TABLE ... LIKE ステートメントでは括弧を省略している。
CREATE TABLE new_tbl LIKE orig_tbl;
最後にcreate_definitionについて。
最初に出てきた(create_definition,...)の構文
ここら辺から複雑になってくるが、基本的には上の組み合わせ。
create_definition: {
col_name column_definition
| {INDEX | KEY} [index_name] [index_type] (key_part,...)
[index_option] ...
| {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
[index_name] [index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name,...)
reference_definition
| check_constraint_definition
}
[index_option] ... はindex_optionを複数指定できるし、[]がありoptionalなので指定しなくても良いことを表す。
全体的な形は以下の様になっていて、A ~ Gのどれかを1つ指定しなければならない。{ hoge | huga }パターン
create_definition: {
A
| B
[index_option] ...
| C
[index_option] ...
| D
[index_type] (key_part,...)
[index_option] ...
| E
[index_name] [index_type] (key_part,...)
[index_option] ...
| F
[index_name] (col_name,...)
reference_definition
| G
}
ちなみにAは col_name column_definition
構文的にはDのPRIMARY KEYだけ指定することもできるらしい
CREATE TABLE products (
PRIMARY KEY (product_id)
);
Discussion