MySQL Create Table

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

tbl_name

tbl_name 建议使用db_name.tbl_name的形式

create_definition

1.克隆其他表或select其他表数据

CREATE TABLE new_tbl LIKE orig_tbl; 克隆
CREATE TABLE new_tbl AS SELECT * FROM orig_tbl; select创建

2.新建表

CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);

列属性

属性	说明
data_type
NOT NULL or NULL	是否非空
DEFAULT	默认值
AUTO_INCREMENT	自增,只对numeric类型有效
COMMENT
COLUMN_FORMAT
STORAGE	存在硬盘还是内存
GENERATED ALWAYS
CONSTRAINT symbol
PRIMARY KEY
KEY or INDEX
UNIQUE	是否唯一
FULLTEXT
SPATIAL
FOREIGN KEY
CHECK

table_options

参数	描述
ENGINE
AUTO_INCREMENT
AVG_ROW_LENGTH
[DEFAULT] CHARACTER SET
CHECKSUM
[DEFAULT] COLLATE
COMMENT
COMPRESSION
CONNECTION
DATA DIRECTORY, INDEX DIRECTORY
DELAY_KEY_WRITE
ENCRYPTION
INSERT_METHOD
KEY_BLOCK_SIZE
MAX_ROWS
MIN_ROWS
PACK_KEYS
ROW_FORMAT
STATS_AUTO_RECALC

partition_options

参数	描述
PARTITION BY
HASH(expr)
KEY(column_list)
RANGE(expr)
RANGE COLUMNS(column_list)
LIST(expr)
LIST COLUMNS(column_list)
PARTITIONS num
SUBPARTITION BY

参考:
https://dev.mysql.com/doc/refman/8.0/en/create-table.html