创建数据表（CREATE TABLE）

Exisi 2023-01-29 14:49:46
Categories： Tags：
  • CREATE TABLE用给定名称创建一个 table。您必须具有该 table 的CREATE特权。

 

  • 默认情况下，table 是使用InnoDB存储引擎在默认数据库中创建的。如果该 table 存在，没有默认数据库或该数据库不存在，则会发生错误。

 

  • MySQL 对 table 的数量没有限制。基础文件系统可能会对 table 示 table 的文件数量有所限制。各个存储引擎可能会强加特定于引擎的约束。 InnoDB允许多达 40 亿张表

 语法

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (

create_definition,

…

);

[table_options]

[partition_options]

 

参数

描述

[TEMPORARY]

TEMPORARYtable 仅在当前会话中可见，并且在关闭会话时会自动删除

[IF NOT EXISTS]

仅在 tbl_name 不存在时间创建

create_definition

添加变量参数

[table_options]

可选参数：

参数

值

描述

AUTO_INCREMENT

value

字段自增

AVG_ROW_LENGTH

value

table 的平均行长的近似值。您仅需要为具有可变大小行的大型 table 设置此选项

[DEFAULT] CHARACTER SET

charset_name

设置默认字符集

[DEFAULT] COLLATE

collation_name

设置默认校验规则

CHECKSUM

0 | 1

是否对表中的数据进行逐行较验和计算

COMMENT

'string'

添加注释，最多 2048 个字符

COMPRESSION

'ZLIB' | 'LZ4' | 'NONE'

用于InnoDB个 table 的页面级压缩的压缩算法

CONNECTION

'connect_string'

FEDERATEDtable 的连接字符串

{DATA | INDEX} DIRECTORY

'absolute path to directory'

指定数据文件存储路径

创建MyISAMtable 时，它们指定分别放置MyISAMtable 的数据文件和索引文件的位置。

与InnoDBtable 不同，MySQL 不会创建与数据库名称相对应的子目录。在指定的目录中创建文件。

DELAY_KEY_WRITE

0 | 1

是否在表关闭之前，将对表的update操作指跟新数据到磁盘，而不更新索引到磁盘，把对索引的更改记录在内存

如果要在关闭 table 之前延迟 table 的键更新，请将其设置为 1

ENCRYPTION

'Y' | 'N'

是否加密，

将ENCRYPTION选项设置为'Y'以对在file-per-tabletable 空间中创建的InnoDBtable 启用页面级数据加密。选项值不区分大小写。

InnoDBtable 空间加密功能引入了ENCRYPTION选项

ENGINE

engine_name

存储引擎

INSERT_METHOD

NO | FIRST | LAST

表的插入的方式

如果要将数据插入MERGE table，则必须用INSERT_METHOD指定应将行插入到的 table。

INSERT_METHOD是仅对MERGEtable 有用的选项。

使用FIRST或LAST的值可将插入内容移到第一个或最后一个 table，或使用NO的值可防止插入

KEY_BLOCK_SIZE

value

对于MyISAM个 table，KEY_BLOCK_SIZE(可选)以字节为单位指定用于索引键块的大小。该值被视为提示；如有必要，可以使用其他大小。为单个索引定义指定的KEY_BLOCK_SIZE值将覆盖 table 级KEY_BLOCK_SIZE的值。

MAX_ROWS

value

计划在 table 中存储的最大行数

MIN_ROWS

value

计划在 table 中存储的最小行数

PACK_KEYS

0 | 1 | DEFAULT

仅对MyISAM个 table 生效。

如果要使用较小的索引，请将此选项设置为 1。这通常会使更新速度变慢，读取速度也会加快。

将选项设置为 0 将禁用所有键打包。

将其设置为DEFAULT会告诉存储引擎仅打包长CHAR，VARCHAR，BINARY或VARBINARY列。

如果不使用PACK_KEYS，则默认值为打包字符串，但不打包数字。

如果您使用PACK_KEYS=1，数字也会被打包

ROW_FORMAT

DEFAULT

DYNAMIC

FIXED

COMPRESSED

REDUNDANT

COMPACT

显式定义表的行格式

STATS_AUTO_RECALC

DEFAULT | 0 | 1

指定是否自动重新计算持久统计信息。值 DEFAULT 导致 table 的持久统计设置由 innodb_stats_auto_recalc 设置确定。

值 1 会导致在 10% 的表数据发生更改时重新计算统计信息。

STATS_PERSISTENT

DEFAULT | 0 | 1

指定是否为 InnoDB 表启用持久统计。

值 DEFAULT 导致表的持久统计信息设置由 innodb_stats_persistent 设置确定。

值 1 启用表的持久统计信息，而值 0 禁用该功能。

STATS_SAMPLE_PAGES

value

指定在为索引列计算基数和其他统计信息时要采样的索引页数

TABLESPACE  

 

 

 

 

 

(STORAGE)

tablespace_name

 

 

 

 

 

DISK | MEMORY

可用于在现有的常规 table 空间，每 table 文件 table 空间或系统 table 空间中创建 table

指定的常规 table 空间在使用TABLESPACE子句之前必须存在

 

STORAGE 将 table 分配给 NDB 群集磁盘数据 table 空间，STORAGE 选项仅与NDBtable 一起使用

UNION

(tbl_name[,tbl_name]...)

用于访问一个相同的MyISAMtable 的集合。仅适用于合并多个 table

[partition_options]

可用于控制用CREATE TABLE创建的 table 的分区

参数

描述

PARTITION BY

 

包含用于确定分区的函数；该函数返回一个整数值，范围是 1 到 num ，其中 num *是分区数。

(一个 table 可能包含的用户定义的分区的最大数量为 1024；该分区的最大数量包括了子分区的数

子句

描述

[LINEAR] HASH(expr)

散列一个或多个列以创建用于放置和定位行的键。 * expr *是使用一个或多个 table 列的 table 达式。这可以是产生单个整数值的任何有效 MySQLtable 达式(包括 MySQL 函数)

 

您不得将VALUES LESS THAN或VALUES IN子句与PARTITION BY HASH一起使用。

PARTITION BY HASH使用* expr *的余数除以分区数(即模数)

[LINEAR] KEY(column_list)

这类似于HASH，除了 MySQL 提供散列功能以保证均匀的数据分布。 * column_list *参数只是一个 1 或多个 table 列的列 table(最多 16 个)

 

对于按键分区的 table，可以通过使用LINEAR关键字来采用线性分区。这与由HASH分区的 table 具有相同的效果。也就是说，使用&运算符而不是模数来找到分区号

[LINEAR] RANGE(expr)

在这种情况下，* expr *使用一组VALUES LESS THAN运算符显示值的范围。使用范围分区时，必须使用VALUES LESS THAN定义至少一个分区。您不能将VALUES IN与范围分区一起使用

 

对于由RANGE分区的 table，必须将VALUES LESS THAN与整数 Literals 值或计算结果为单个整数值的 table 达式一起使用

RANGE COLUMNS(column_list)

RANGE上的此变体有助于使用多个列上的范围条件(即具有诸如WHERE a = 1 AND b < 10或WHERE a = 1 AND b = 10 AND c < 10之类的条件)对查询进行分区修剪。它使您可以通过使用COLUMNS子句中的列列 table 和每个PARTITION ... VALUES LESS THAN (value_list)分区定义子句中的一组列值来指定多列中的值范围。 (在最简单的情况下，此集合由单个列组成.)* column_list 和 value_list *中可以引用的最大列数为 16.

 

COLUMNS子句中使用的* column_list *只能包含列名；列 table 中的每一列必须是以下 MySQL 数据类型之一

[LINEAR] LIST(expr)

基于具有有限的可能值集(例如 State 或国家/locale 代码)的 table 列分配分区时，此功能很有用。在这种情况下，可以将与某个 State 或国家/locale 有关的所有行分配给单个分区，或者可以为某个 State 或国家/locale 的某个组保留一个分区。它类似于RANGE，不同之处在于仅VALUES IN可以用于指定每个分区的允许值

 

您不能将VALUES LESS THAN与PARTITION BY LIST结合使用

LIST COLUMNS(column_list)

LIST上的此变体使用多个列上的比较条件(即具有WHERE a = 5 AND b = 5或WHERE a = 1 AND b = 10 AND c = 5之类的条件)来简化查询的分区修剪。它使您可以通过使用COLUMNS子句中的列列 table 和每个PARTITION ... VALUES IN (value_list)分区定义子句中的一组列值来指定多列中的值。

 

关于LIST COLUMNS(column_list)中使用的列列 table 和VALUES IN(value_list)中使用的值列 table 的数据类型的规则分别与RANGE COLUMNS(column_list)和VALUES LESS THAN(value_list)中使用的列列 table 的数据类型相同，除了VALUES IN子句MAXVALUE不允许，您可以使用NULL。

 

与PARTITION BY LIST一起使用时，与PARTITION BY LIST COLUMNS一起用于VALUES IN的值列 table 之间有一个重要区别。与PARTITION BY LIST COLUMNS一起使用时，VALUES IN子句中的每个元素都必须是一组*列值；每个集合中的值数必须与COLUMNS子句中使用的列数相同，并且这些值的数据类型必须与列的数据类型匹配(并以相同的 Sequences 出现)。在最简单的情况下，集合由单个列组成。 * column_list 和组成 value_list *的元素中可以使用的最大列数为 16.

[PARTITIONS num]

可以选择使用PARTITIONS num子句指定分区的数量，其中* num 是分区的数量。如果此子句和*都使用了任何PARTITION子句，则num *必须等于使用PARTITION子句声明的任何分区的总数

[SUBPARTITION BY]

分区可以可选地划分为多个子分区。这可以通过使用可选的SUBPARTITION BY子句来指示。可以通过HASH或KEY进行分区。这些都可以是LINEAR。这些工作方式与先前针对等效分区类型所述的方式相同。 (无法通过LIST或RANGE进行子分区.)

 

可以使用SUBPARTITIONS关键字后跟一个整数值来指示子分区的数量

 

子句

描述

[LINEAR] HASH(expr)

散列一个或多个列以创建用于放置和定位行的键。 * expr *是使用一个或多个 table 列的 table 达式。这可以是产生单个整数值的任何有效 MySQLtable 达式(包括 MySQL 函数)

 

您不得将VALUES LESS THAN或VALUES IN子句与PARTITION BY HASH一起使用。

PARTITION BY HASH使用* expr *的余数除以分区数(即模数)

[LINEAR] KEY(column_list)

这类似于HASH，除了 MySQL 提供散列功能以保证均匀的数据分布。 * column_list *参数只是一个 1 或多个 table 列的列 table(最多 16 个)

 

对于按键分区的 table，可以通过使用LINEAR关键字来采用线性分区。这与由HASH分区的 table 具有相同的效果。也就是说，使用&运算符而不是模数来找到分区号

[(partition_definition [, partition_definition] ...)]

每个分区可以使用* partition_definition *子句单独定义

子句

描述

PARTITION partition_name

指定分区的逻辑名

VALUES

对于范围分区，每个分区必须包含VALUES LESS THAN子句；对于列 table 分区，必须为每个分区指定一个VALUES IN子句。这用于确定哪些行要存储在此分区中。

[STORAGE] ENGINE

分区处理程序为PARTITION和SUBPARTITION接受[STORAGE] ENGINE选项。当前，唯一可以使用此方法的方法是将所有分区或所有子分区设置为同一存储引擎，并且尝试为同一 table 中的分区或子分区设置不同的存储引擎将产生错误

COMMENT

可选的COMMENT子句可用于指定描述分区的字符串

DATA | INDEX DIRECTORY

DATA DIRECTORY和INDEX DIRECTORY可用于指示目录，该分区的数据和索引将分别存储在该目录中。 data_dir和index_dir都必须是绝对系统路径名

 

从 MySQL 5.7.17 开始，您必须具有FILE特权才能使用DATA DIRECTORY或INDEX DIRECTORY分区选项

MAX_ROWS

指定要存储在分区中的最大行数

MIN_ROWS

指定要存储在分区中的最小行数

TABLESPACE

可用于为分区指定 table 空间。受 NDB 群集支持。对于InnoDB个 table，可以通过指定 TABLESPACE``来为分区指定每 table 文件 table 空间。所有分区必须属于同一存储引擎

subpartition_definition

分区定义可以选择包含一个或多个* subpartition_definition 子句。每一个都至少由SUBPARTITION name组成，其中 name *是该子分区的标识符。除了用SUBPARTITION替换PARTITION关键字之外，子分区定义的语法与分区定义的语法相同。

 

子分区必须由HASH或KEY完成，并且只能在RANGE或LIST分区上进行

示例

CREATE TABLE tb_emp1(

id INT(11) PRIMARY KEY NOT NULL,

name VARCHAR(25) NOT NULL,

deptId INT(11) NOT NULL,

salary FLOAT NOT NULL

);ENGINE = InnoDB DEFAULT CHARSET = utf8;

 

Query OK, 0 rows affected (0.37 sec)

注

  • 如果InnoDB数据表没有创建主键，那么MySQL会自动创建一个以行号为准的隐藏主键

 

来自< https://www.docs4dev.com/docs/zh/mysql/5.7/reference/create-table.html>

Table 数据表（DDL）
查看数据表（SHOW TABLE／DESC／SHOW CREATE TABLE／SHOW COLUMNS FROM