Mysql 8 - SQL全新版本之CREATE TABLE（表名、临时表、表克隆和复制、列数据类型和属性）

CREATE TABLE

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 Chapter 11, Data Types)

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 TABLE​​​创建一个具有给定名称的表。必须拥有​​CREATE​​该表的权限。

默认情况下，使用​​InnoDB​​​存储引擎在默认数据库（可以通过​​use​​指定）中创建表 。

use mysql;
select DATABASE();
use sys;
select DATABASE();

如果表存在或没有默认数据库或数据库不存在，则会发生错误。

MySQL对表的数量没有限制。底层文件系统可能对表示表的文件数量有限制。单个存储引擎可能会施加特定于引擎的约束。​​InnoDB​​​允许多达​​40​​亿张表。

表名

可以指定表名为​​db_name.tbl_name​​以在特定数据库中创建表。假设数据库存在，无论是否存在默认数据库，这都有效。如果使用带引号的标识符，请分别引用数据库和表名。例如:

`mydb`.`mytbl`  YES
`mydb.mytbl`    NO

drop database if exists jack;
create database jack;
create table jack.kaven(
    id integer auto_increment ,
    primary key PRIMARY_KEY(id)
);
drop database if exists jack2;
create database jack2;
use jack2;
create table `jack.kaven`(
    id integer auto_increment ,
    primary key PRIMARY_KEY(id)
);
create table `jack`.`kaven2`(
    id integer auto_increment ,
    primary key PRIMARY_KEY(id)
);

如果表存在，为了防止发生错误，可以使用​​IF NOT EXISTS​​​。但是，不会验证现有表是否具有与​​CREATE TABLE​​语句指示的结构相同的结构 ，即只判断表名（表名是数据库中的标识符），而表结构不会进行判断。

create database if not exists jack;
use jack;
create table if not exists kaven(
    id integer auto_increment primary key ,
    username varchar(128) not null unique ,
    password varchar(256) not null default 'password'
)engine 'innodb' character set 'utf8mb4';

create table if not exists kaven(
    id integer auto_increment primary key ,
    username varchar(128) not null unique ,
    password varchar(256) not null default 'password' ,
    address varchar(128) not null
)engine 'innodb' character set 'utf8mb4';

临时表

创建表时，可以使用​​TEMPORARY​​​关键字。一个​​TEMPORARY​​表只在当前会话中可见，而当会话关闭时会自动删除。

这意味着两个不同的会话可以使用相同的临时表名而不会相互冲突。

​​TEMPORARY​​​表与数据库的关系非常松散。删除数据库不会自动删除在该数据库中创建的任何​​TEMPORARY​​表。

删除数据库后，​​TEMPORARY​​表还是可以在当前会话中使用。

create database if not exists jack;
use jack;
create table kaven_1 (
    id integer auto_increment ,
    primary key PRIMARY_KEY(id)
);
create table kaven_2 (
    id integer auto_increment ,
    primary key PRIMARY_KEY(id)
);
create temporary table kaven_3 (
    id integer auto_increment ,
    primary key PRIMARY_KEY(id)
);
create temporary table kaven_4 (
    id integer auto_increment ,
    primary key PRIMARY_KEY(id)
);
drop database jack;
insert into jack.kaven_4(id) values (1),(2),(3);

要创建临时表，必须具有​​CREATE TEMPORARY TABLES​​权限。

表克隆和复制

LIKE

用​​CREATE TABLE ... LIKE​​根据另一个表的定义创建一个空表，包括原始表中定义的任何列属性和索引：

CREATE TABLE new_tbl LIKE orig_tbl;

副本是使用与原始表相同版本的表存储格式创建的。需要有原始表的​​SELECT​​权限。

​​LIKE​​仅适用于基表，不适用于视图。

1. 不允许在已经执行了​​LOCK TABLES​​​语句的会话中使用​​CREATE TABLE​​​或 ​​CREATE TABLE ... LIKE​​。
2. ​​CREATE TABLE ... LIKE​​​与​​CREATE TABLE​​​进行相同的检查，即操作是差不多的，只不过​​CREATE TABLE ... LIKE​​​是根据原始表的表结构以及其他信息来创建表，确实还是​​CREATE TABLE​​​。这意味着如果当前的 ​​SQL​​ 模式与创建原始表时生效的模式不同，则表定义可能被认为对新模式无效并导致语句失败。
3. 对于​​CREATE TABLE ... LIKE​​，目标表保留原始表中生成的列信息。
4. 对于​​CREATE TABLE ... LIKE​​，目标表保留原始表中的表达式默认值。
5. 对于​​CREATE TABLE ... LIKE​​，目标表保留原始表的检查约束，除了生成所有约束名称。
6. ​​CREATE TABLE ... LIKE​​​不保留为原始表指定的任何 ​​DATA DIRECTORY​​​或​​INDEX DIRECTORY​​表选项或任何外键定义。
7. 如果原始表是​​TEMPORARY​​​表， ​​CREATE TABLE ... LIKE​​​则不保留 ​​TEMPORARY​​​， 要创建 ​​TEMPORARY​​​目标表，使用 ​​CREATE TEMPORARY TABLE ... LIKE​​。
8. 在​​mysql​​​表空间、 ​​InnoDB​​​系统表空间 ( ​​innodb_system​​​) 或通用表空间​​TABLESPACE​​​中创建的表在表定义中包含一个属性，该属性定义了表所在的表空间。由于临时回归， 无论设置如何，​​CREATE TABLE ... LIKE​​​都会保留​​TABLESPACE​​​属性并在定义的表空间中创建表 ​​innodb_file_per_table​​​。为了在​​TABLESPACE​​​基于此类表的定义创建空表时避免使用该属性，改用语法为：​​CREATE TABLE new_tbl SELECT * FROM orig_tbl LIMIT 0;​​。
9. ​​CREATE TABLE ... LIKE​​​操作将原始表所有的​​ENGINE_ATTRIBUTE​​​和​​SECONDARY_ENGINE_ATTRIBUTE​​值应用于目标表。

这些规则不需要记住，遇到问题，再回来看看，如果发现错误，也感谢大家可以帮博主指正。

drop database if exists jack;
create database jack;
create table jack.kaven(
  id integer auto_increment ,
  username varchar(128) not null ,
  password varchar(256) not null ,
  PRIMARY KEY PRIMARY_KEY(id) ,
  UNIQUE KEY UNI_USERNAME(username)
)engine 'innodb' character set 'utf8mb4';

create table jack.kaven2 like jack.kaven;

[AS] query_expression

如果还需要将原始表的数据复制到目标表，可以在​​CREATE TABLE​​​语句末尾添加一条​​SELECT​​语句：

CREATE TABLE new_tbl AS SELECT * FROM orig_tbl;

不进行复制，目标表是空表。

drop database if exists jack;
create database jack;
create table jack.kaven(
  id integer auto_increment ,
  username varchar(128) not null ,
  password varchar(256) not null ,
  PRIMARY KEY PRIMARY_KEY(id) ,
  UNIQUE KEY UNI_USERNAME(username)
)engine 'innodb' character set 'utf8mb4';

insert into jack.kaven(username, password) values ('k' , '123456') , ('j' , '1234567') , ('w' , '1234567890') , ('q' , '1234563210.');

create table jack.kaven2 like jack.kaven;
select * from jack.kaven2;

进行复制，目标表的数据和复制时原始表的数据是一样的。

drop database if exists jack;
create database jack;
create table jack.kaven(
  id integer auto_increment ,
  username varchar(128) not null ,
  password varchar(256) not null ,
  PRIMARY KEY PRIMARY_KEY(id) ,
  UNIQUE KEY UNI_USERNAME(username)
)engine 'innodb' character set 'utf8mb4';

insert into jack.kaven(username, password) values ('k' , '123456') , ('j' , '1234567') , ('w' , '1234567890') , ('q' , '1234563210.');

create table jack.kaven2 as select * from jack.kaven;
select * from jack.kaven2;

进行复制，但目标表只需要原始表中满足​​username​​​字段包含字母​​k​​的行。

drop database if exists jack;
create database jack;
create table jack.kaven
(
    id       integer auto_increment,
    username varchar(128) not null,
    password varchar(256) not null,
    PRIMARY KEY PRIMARY_KEY (id),
    UNIQUE KEY UNI_USERNAME (username)
) engine 'innodb' character set 'utf8mb4';

insert into jack.kaven(username, password)
values ('k', '123456'),('kll', '1234567'),
       ('wqw', '1234567890'),('qsdd', '1234563210.'),
       ('qskd', '1234563210.');

create table jack.kaven2 as select * from jack.kaven where username like '%k%';
select * from jack.kaven2;

这里只是简单介绍一下，其实还有很多更加复制的操作，以后有机会再进行介绍。

IGNORE | REPLACE

​​IGNORE​​​和​​REPLACE​​​ 选项指示在复制表时如何处理使用​​SELECT​​语句复制重复唯一键值的行。

可以在​​SELECT​​​语句中通过​​IGNORE​​​或​​REPLACE​​​选项指示如何处理重复唯一键值的行。使用​​IGNORE​​​，如果表中已经具有和新行相同唯一键值的行，新行将被丢弃。使用​​REPLACE​​​，新行替换具有相同唯一键值的行。如果既不指定​​IGNORE​​​也不指定​​REPLACE​​，重复的唯一键值会导致错误。

使用​​IGNORE​​进行复制。

drop database if exists jack;
create database jack;
create table jack.kaven
(
    id       integer auto_increment primary key ,
    username varchar(128) not null,
    password varchar(256) not null
) engine 'innodb' character set 'utf8mb4';

insert into jack.kaven(username, password) values ('k', '123456'),('k', '1234567'),
       ('k', '1234567890'),('qsdd', '1234563210.'),
       ('qskd', '1234563210.');
create table jack.kaven2(
    id       integer auto_increment primary key ,
    username varchar(128) not null unique ,
    password varchar(256) not null
) ignore select username , password from jack.kaven;
select * from jack.kaven2;

使用​​REPLACE​​进行复制。

drop database if exists jack;
create database jack;
create table jack.kaven
(
    id       integer auto_increment primary key ,
    username varchar(128) not null,
    password varchar(256) not null
) engine 'innodb' character set 'utf8mb4';

insert into jack.kaven(username, password) values ('k', '123456'),('k', '1234567'),
       ('k', '1234567890'),('qsdd', '1234563210.'),
       ('qskd', '1234563210.');
create table jack.kaven2(
    id       integer auto_increment primary key ,
    username varchar(128) not null unique ,
    password varchar(256) not null
) replace select username , password from jack.kaven;
select * from jack.kaven2;

如果既不指定​​IGNORE​​​也不​​REPLACE​​指定，在出现重复的唯一键值会导致错误。

drop database if exists jack;
create database jack;
create table jack.kaven
(
    id       integer auto_increment primary key ,
    username varchar(128) not null,
    password varchar(256) not null
) engine 'innodb' character set 'utf8mb4';

insert into jack.kaven(username, password) values ('k', '123456'),('k', '1234567'),
       ('k', '1234567890'),('qsdd', '1234563210.'),
       ('qskd', '1234563210.');
create table jack.kaven2(
    id       integer auto_increment primary key ,
    username varchar(128) not null unique ,
    password varchar(256) not null
) select username , password from jack.kaven;
select * from jack.kaven2;

列数据类型和属性

每个表有​​4096​​列的硬限制，但给定表的最多有效列数可能会更少。

data_type

​​data_type​​​表示列定义的数据类型，如​​int​​​ 、​​varchar​​​、​​text​​​、​​decimal​​​、​​timestamp​​ 等。

drop database if exists jack;
create database jack;
create table jack.kaven(
    id int auto_increment primary key ,
    username varchar(128) not null unique ,
    profile text ,
    money decimal(10 , 2) not null default 0 ,
    create_time timestamp default current_timestamp ,
    update_time timestamp default current_timestamp on update current_timestamp
) engine 'innodb' character set 'utf8mb4';

以后有机会再详细介绍Mysql 8中的数据类型，不然篇幅会很长。

NOT NULL | NULL

如果既未指定​​NULL​​​也未指定​​NOT NULL​​​，则将该列视为已指定​​NULL​​。

在​​MySQL 8.0​​​中，只有​​InnoDB​​​， ​​MyISAM​​​和​​MEMORY​​​存储引擎支持在索引列可以有​​NULL​​​值。在其他情况下，必须将索引列声明为​​NOT NULL​​，不然可能会导致错误。

drop database if exists jack;
create database jack;
create table jack.kaven(
  id integer auto_increment ,
  username varchar(128) not null ,
  password varchar(256) not null ,
  code varchar(32) ,
  PRIMARY KEY PRIMARY_KEY(id) ,
  UNIQUE KEY UNI_USERNAME(username) ,
  INDEX CODE_INDEX(code)
)engine 'innodb' character set 'utf8mb4';

insert into jack.kaven(username, password , code) values ('k' , '123456' , '123') , ('j' , '1234567' , null) ;

create table jack.kaven2 as select * from jack.kaven;
select * from jack.kaven2;

DEFAULT

指定列的默认值，有些数据类型不允许指定默认值，要注意判别。

drop database if exists jack;
create database jack;
create table jack.kaven(
  id integer auto_increment ,
  username varchar(128) not null ,
  password varchar(256) not null ,
  code varchar(32) default 'code',
  PRIMARY KEY PRIMARY_KEY(id) ,
  UNIQUE KEY UNI_USERNAME(username) ,
  INDEX CODE_INDEX(code)
)engine 'innodb' character set 'utf8mb4';

insert into jack.kaven(username, password) values ('k' , '123456');

create table jack.kaven2 as select * from jack.kaven;
select * from jack.kaven2;

创建时的时间戳默认值，可以设置为当前的时间戳，更新时的时间戳默认值，也可以设置为当前的时间戳。

drop database if exists jack;
create database jack;
create table jack.kaven(
  id integer auto_increment ,
  username varchar(128) not null ,
  password varchar(256) not null ,
  code varchar(32) default 'code',
  create_time timestamp default current_timestamp ,
  update_time timestamp default current_timestamp on update current_timestamp ,
  PRIMARY KEY PRIMARY_KEY(id) ,
  UNIQUE KEY UNI_USERNAME(username) ,
  INDEX CODE_INDEX(code)
)engine 'innodb' character set 'utf8mb4';

insert into jack.kaven(username, password) values ('k' , '123456') , ('l' , '.0123456');

create table jack.kaven2 as select * from jack.kaven;
select * from jack.kaven2;

VISIBLE、INVISIBLE

指定列可见性。如果两个关键字都不存在，默认情况下是​​VISIBLE​​。一张表必须至少有一个可见列。尝试使所有列不可见会产生错误。

所有列不可见会产生错误（​​A table must have at least one visible column​​）：

drop database if exists jack;
create database jack;
create table jack.kaven(
  id integer auto_increment invisible ,
  username varchar(128) not null invisible ,
  password varchar(256) not null invisible ,
  code varchar(32) invisible ,
  PRIMARY KEY PRIMARY_KEY(id) ,
  UNIQUE KEY UNI_USERNAME(username) ,
  INDEX CODE_INDEX(code)
)engine 'innodb' character set 'utf8mb4';

指定​​password​​字段不可见。

drop database if exists jack;
create database jack;
create table jack.kaven(
  id integer auto_increment ,
  username varchar(128) not null ,
  password varchar(256) not null invisible ,
  code varchar(32)  ,
  PRIMARY KEY PRIMARY_KEY(id) ,
  UNIQUE KEY UNI_USERNAME(username) ,
  INDEX CODE_INDEX(code)
)engine 'innodb' character set 'utf8mb4';

insert into jack.kaven(username, password, code) values ('kaven' , '123' , '666') , ('jack' , '456' , '000') ,
                                                        ('king' , '789' , '555');
select * from jack.kaven;

​​VISIBLE​​​和​​INVISIBLE​​​关键字是从​​MySQL 8.0.23​​​才有的。在​​MySQL 8.0.23​​之前，所有列都是可见的。

AUTO_INCREMENT

整数或浮点数列可以具有附加属性​​AUTO_INCREMENT​​。

浮点数列其实不推荐使用附加属性​​AUTO_INCREMENT​​​，因为在未来的版本中会移除对​​FLOAT​​​和​​DOUBLE​​​关于附加属性​​AUTO_INCREMENT​​​的支持，执行下面的SQL语句会出现警告（博主的Mysql版本是​​8.0.25​​）。

drop database if exists jack;
create database jack;
create table jack.kaven(
  money double auto_increment  primary key ,
  username varchar(128) not null  unique ,
  password varchar(256) not null ,
  code varchar(32)  ,
  INDEX CODE_INDEX(code)
)engine 'innodb' character set 'utf8mb4';
insert into jack.kaven(username, password, code) value ('kaven' , '111' , '111');
insert into jack.kaven(money , username, password, code) values (10.9 , 'jack' , '222' , '222') , (11.6 , 'cookie' , '222' , '222');
insert into jack.kaven(username, password, code) value ('jojo' , '333' , '333');
insert into jack.kaven(money , username, password, code) value (13.4 , 'java' , '222' , '222');
insert into jack.kaven(username, password, code) values ('docker' , '333' , '333') , ('k8s' , '333' , '333');

select * from jack.kaven;

有如下警告：

[HY000][3856] AUTO_INCREMENT support for FLOAT/DOUBLE columns is deprecated and will be removed in a future release. Consider removing AUTO_INCREMENT from column ‘money’.

整数列使用附加属性​​AUTO_INCREMENT​​。

drop database if exists jack;
create database jack;
create table jack.kaven(
  id int auto_increment  primary key ,
  username varchar(128) not null  unique ,
  password varchar(256) not null ,
  code varchar(32)  ,
  INDEX CODE_INDEX(code)
)engine 'innodb' character set 'utf8mb4';
insert into jack.kaven(username, password, code) value ('kaven' , '111' , '111');
insert into jack.kaven(id , username, password, code) values (10 , 'jack' , '222' , '222') , (13 , 'cookie' , '222' , '222');
insert into jack.kaven(username, password, code) value ('jojo' , '333' , '333');
insert into jack.kaven(id , username, password, code) value (16 , 'java' , '222' , '222');
insert into jack.kaven(username, password, code) values ('docker' , '333' , '333') , ('k8s' , '333' , '333');

select * from jack.kaven;

每个表只能有一个​​AUTO_INCREMENT​​​列，它必须被索引，并且不能有​​DEFAULT​​值。

如果有多个​​AUTO_INCREMENT​​​列，会报错：​​[42000][1075] Incorrect table definition; there can be only one auto column and it must be defined as a key​​。

drop database if exists jack;
create database jack;
create table jack.kaven(
  id int auto_increment  primary key ,
  code int auto_increment,
  INDEX CODE_INDEX(code)
)engine 'innodb' character set 'utf8mb4';

如果​​AUTO_INCREMENT​​​列没有被索引，会报错：​​[42000][1075] Incorrect table definition; there can be only one auto column and it must be defined as a key​​。

drop database if exists jack;
create database jack;
create table jack.kaven(
  id int auto_increment ,
  code int,
  INDEX CODE_INDEX(code)
)engine 'innodb' character set 'utf8mb4';

如果​​AUTO_INCREMENT​​​列有​​DEFAULT​​​值，会报错：​​[42000][1067] Invalid default value for 'id'​​。

drop database if exists jack;
create database jack;
create table jack.kaven(
  code int default 1,
  id int auto_increment primary key default 1,
  INDEX CODE_INDEX(code)
)engine 'innodb' character set 'utf8mb4';

当将值​​NULL​​​（推荐）或 ​​0​​​插入 ​​AUTO_INCREMENT​​​列时，该列将设置为下一个序列值。通常是​​value+1​​​，其中​​value​​​是表中当前列的最大值， ​​AUTO_INCREMENT​​​序列以​​1​​开始（从上面演示的效果也可知道）。

drop database if exists jack;
create database jack;
create table jack.kaven(
  id int auto_increment  primary key ,
  username varchar(128) not null  unique ,
  password varchar(256) not null ,
  code varchar(32)  ,
  INDEX CODE_INDEX(code)
)engine 'innodb' character set 'utf8mb4';
insert into jack.kaven(username, password, code) value ('kaven' , '111' , '111');
insert into jack.kaven(id , username, password, code) values (null , 'jack' , '222' , '222') , (0 , 'cookie' , '222' , '222');
insert into jack.kaven(username, password, code) value ('jojo' , '333' , '333');
insert into jack.kaven(id , username, password, code) value (null , 'java' , '222' , '222');
insert into jack.kaven(username, password, code) values ('docker' , '333' , '333') , ('k8s' , '333' , '333');

select * from jack.kaven;

插入负数也不会意外出现​​AUTO_INCREMENT​​​列包含 ​​0​​​的情况。上面有说过，当将值​​NULL​​​或 ​​0​​​插入 ​​AUTO_INCREMENT​​​列时，该列将设置为下一个序列值。通常是​​value+1​​​，其中​​value​​​是表中当前列的最大值，所以可以知道这个最大值不会是负数，不然就有可能出现​​AUTO_INCREMENT​​​列包含 ​​0​​的情况，和下面的实验结果矛盾。

drop database if exists jack;
create database jack;
create table jack.kaven(
  id int auto_increment  primary key ,
  username varchar(128) not null  unique ,
  password varchar(256) not null ,
  code varchar(32)  ,
  INDEX CODE_INDEX(code)
)engine 'innodb' character set 'utf8mb4';
insert into jack.kaven(id , username, password, code) value (-1 , 'go' , '222' , '222') , (null , 'k8s' , '333' , '333');
insert into jack.kaven(username, password, code) value ('kaven' , '111' , '111');
insert into jack.kaven(id , username, password, code) values (-10 , 'jack' , '222' , '222') , (-0 , 'cookie' , '222' , '222');
insert into jack.kaven(username, password, code) value ('jojo' , '333' , '333');
insert into jack.kaven(id , username, password, code) value (-155 , 'java' , '222' , '222');
insert into jack.kaven(username, password, code) values ('docker' , '333' , '333');
select * from jack.kaven;

COMMENT

可以使用该​​COMMENT​​​选项指定列的注释 ，最长为​​1024​​​个字符。注释由​​SHOW CREATE TABLE​​​和 ​​SHOW FULL COLUMNS​​语句显示。

drop database if exists jack;
create database jack;
create table jack.kaven(
  id int auto_increment  primary key comment '这是id , 每行数据唯一',
  username varchar(128) not null  unique comment '这是用户的用户名，也是唯一的',
  password varchar(256) not null comment '这是用户的密码',
  code varchar(32)  comment '这是用户的身份码',
  INDEX CODE_INDEX(code) comment '建立索引 ，方便通过用户的身份码来查询用户信息'
)engine 'innodb' character set 'utf8mb4';
show create table jack.kaven;
show full columns from jack.kaven;

不常用的属性就不介绍了，以后有需求会再进行补充。

参考

• ​​Mysql 官网​​