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

￡神魔★判官ぃ 2022-10-13 12:49 116阅读 0赞

## 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();

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70]

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

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)
    );

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 1]

如果表存在，为了防止发生错误，可以使用`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';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 2]

## 临时表 ##

创建表时，可以使用`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);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 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`值应用于目标表。

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 4]  
**\[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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 5]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 6]  
进行复制，目标表的数据和复制时原始表的数据是一样的。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 7]  
进行复制，但目标表只需要原始表中满足`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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 8]

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

**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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 9]  
使用`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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 10]  
如果既不指定`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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 11]

## 列数据类型和属性 ##

每个表有`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';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 12]  
以后有机会再详细介绍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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 13]

**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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 14]  
创建时的时间戳默认值，可以设置为当前的时间戳，更新时的时间戳默认值，也可以设置为当前的时间戳。

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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 15]

**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';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 16]

指定`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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 17]  
`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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 18]  
有如下警告：

> \[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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 19]

每个表只能有一个`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';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 20]  
如果`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';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 21]  
如果`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';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 22]

当将值`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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 23]  
插入负数也不会意外出现`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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 24]

**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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 25]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 26]

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

**参考**

*  [Mysql 官网][Mysql]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70]: /images/20221005/de846f12b5724e4c8518862b951b3b34.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 1]: /images/20221005/8152c24ef9e743bd9bc8779de3442af5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 2]: /images/20221005/15328c78d7a6490184b49c95975ca093.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 3]: /images/20221005/a2bb4a8ab7d0410a929a9257e0ebc9b5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 4]: /images/20221005/cb6f80032242426fbb720464a046215d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 5]: /images/20221005/0725094cb4e04da7b78a59dda00cedb6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 6]: /images/20221005/b41926e2963d4d0ca32ca07088ac90b0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 7]: /images/20221005/cc49fa14dfaa4bfbafe6c6b6fa1a34e7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 8]: /images/20221005/bb84b05569f249f5b88f3cfffe496444.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 9]: /images/20221005/2abe81157d1b492bbafba51a05e32891.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 10]: /images/20221005/65bc7d426e52413cad61f550298a49b1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 11]: /images/20221005/3909d1e4b1d44ff2ba5e377211eb34a1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 12]: /images/20221005/b9231feec0924e468ffa47b925693eb7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 13]: /images/20221005/70f0e9295bc541d396bb7b29aeb75463.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 14]: /images/20221005/20389f12701149978f2ec2042389feb4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 15]: /images/20221005/1052a17d4dd249ce844911116f93e7d9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 16]: /images/20221005/4476a3ee9e824c8eb08c140044029b78.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 17]: /images/20221005/91f07d1c3c6b49b2800271ad9eca0f30.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 18]: /images/20221005/d8c664d05a974a109c3b6b8418953a1b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 19]: /images/20221005/4435afc9380a4d25abd4fe946883e705.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 20]: /images/20221005/f7abb987033c4cac922237030878ecc3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 21]: /images/20221005/50ee67eb30ea4178999a9f879a299146.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 22]: /images/20221005/57c9237259c84457b61ad949e628ffe7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 23]: /images/20221005/7dfcda97a6f1456d926f9de08d8e7f0f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 24]: /images/20221005/d35cab5912c14c92bb1bed92de72ec6d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 25]: /images/20221005/88dae84f1f754668877ebc95cc14de12.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 26]: /images/20221005/e6dbba8480f44bb09a8cd9fa41ffc062.png
[Mysql]: https://dev.mysql.com/doc/refman/8.0/en/create-table.html