MySQL8实用系列（三）常用DDL汇总：管理数据库、表和字段以及相关的新特性

目录

一、创建CREATE

创建数据库

三种方式创建数据表

约束条件

表的一些其他设置

复制数据表结构

复制数据表结构和数据

二、修改ALTER

修改数据表名

设置数据表备注

添加列 

修改列

删除列

MySQL8新特性之一：即时添加或删除列、添加列时评估行大小限制

MySQL8新特性之一：行版本 

三、删除DROP

删除数据库

 删除数据表

四、MySQL8新特性之一：原子DDL操作

DDL是数据定义语言，主要用于对数据库或表进行操作，如创建数据库、删除数据库，创建、删除、修改数据表，新增、删除、修改数据表中的列等。MySQL中如何使用SQL创建数据库？如何管理数据库和数据表？如何管理数据表中的列？MySQL8中有哪些DDL的新特性？我将在本文中详细介绍。

环境：MySQL 8.0.32

下文语法中[]包括的内容表示可选项

一、创建CREATE

创建数据库

CREATE DATABASE [IF NOT EXISTS] `库名` [CHARSET charset_name];

说明：IF NOT EXISTS表示如果数据库已经存在，则不执行创建操作。charset_name表示要使用的字符集名称，中文使用utf8mb4。

三种方式创建数据表

CREATE TABLE [IF NOT EXISTS] `表名`(
    `字段名1` 数据类型 [列级约束] [COMMENT '字段说明'],
    ...
    [CONSTRAINT `约束名1` 行级约束(`字段名1`),
    [CONSTRAINT `约束名2` FOREIGN KEY(`字段名2`) REFERENCES `关联表名`(`关联字段名`)，
    ...
    ]
)[表的一些其他设置];

说明：同一张表字段不能相同，最后一个字段或条件后不能加逗号。多个列级约束条件使用空格分隔。行级约束条件在字段后使用。

约束条件

NOT NULL：非空约束，字段的值不能为空。

DEFAULT：默认约束，字段为空时默认有值。用例：DEFAULT 0

UNIQUE：唯一约束，字段唯一，但可以为空。

PRIMARY KEY：主键约束，字段唯一且非空。

FOREIGN KEY：外键约束，即创建外键约束的为主表，主表中设置外键约束的字段与从表的字段关联，可通过外键连接两张表。外键关联的字段类型需一致，设置外键约束的字段不能为主键，被关联的字段为主键，主表中插入的字段在从表中必须存在，否则报错。

注意：

1. 列级约束：除外键约束外均支持，可在列级约束中使用。
2. 行级约束：除唯一和默认约束外均支持。约束条件中多个字段使用逗号分隔。外键约束必须用作行级约束，否则无效。

AUTO_INCREMENT：标识该字段（整数类型）的值自动增长。一个表中有且只能有一个自增长列，自增长列一般和主键搭配。初始默认值为1，自增值为1。可在列级中使用设置。

注意：

• MyISAM引擎的自增值保存在数据文件中。
• MySQL5.7及之前版本，InnoDB保存在内存中，数据库每次重启之后，会查询当前表中自增列的最大值作为当前值，如果表数据被清空之后，数据库重启了，自增列的值将从初始值开始 。MySQL8后才有了持久化的能力，重启的时候通过redo log恢复重启之前的自增值。

 自增主键不连续的原因：

1.  唯一索引冲突
2. 事务回滚（事务中执行insert会使自增主键+1，回滚后不会撤销）

表的一些其他设置

ENGINE=引擎 DEFAULT CHARSET=编码 COLLATE=排序规则

• ENGINE设置引擎类型，常用的有innoDB和myISAM引擎，默认使用InnoDB引擎。
• DEFAULT CHARSET设置表所用的字符集，建议使用utf8mb4，默认使用数据库的编码。可通过数据库变量查看。
• COLLATE设置排序规则，建议使用utf8_general_ci，默认使用数据库的排序规则。可通过数据库变量查看。

复制数据表结构

CREATE TABLE `表名` like `被复制的表名`;

复制数据表结构和数据

CREATE TABLE `表名` [AS] SELECT `字段``,... from `被复制的表名` [where 条件];

二、修改ALTER

修改数据表名

ALTER TABLE `表名` RENAME [TO] `新表名`;

设置数据表备注

ALTER TABLE `表名` comment '备注信息';

添加列 

ALTER TABLE `表名` ADD COLUMN `列名` 类型 [列级约束][,ADD COLUMN `列名` 类型 [列级约束]];

说明： 添加多列使用逗号间隔

修改列

ALTER TABLE `表名` MODIFY COLUMN `列名` 新类型 [列级约束];
ALTER TABLE `表名` CHANGE COLUMN `列名` `新列名` 新类型 [列级约束];

说明：两种方式区别：modify不能修改列名，change可以修改列名 

删除列

ALTER TABLE `表名` DROP COLUMN `列名`[,DROP COLUMN `列名`];

说明： 删除多列使用逗号间隔

MySQL8新特性之一：即时添加或删除列、添加列时评估行大小限制

MySQL8.0.29之前的版本，仅支持在表最后一列即时添加列，不支持在表任一位置即时添加列；也不支持即时删除列；添加列时也不会评估行大小限制，只在插入或更新表中的行的DML操作期间会检查行大小限制。

MySQL8.0.29之后的版本扩展了对ALGORITHM的支持：用户可以在表的任何位置即时添加列、即时删除列、添加列时评估行大小限制

ALTER TABLE `表名` ADD COLUMN `列名` 类型 [列级约束] AFTER `列名`,ALGORITHM=INSTANT;
ALTER TABLE `表名` DROP COLUMN `列名`,ALGORITHM=INSTANT;

MySQL8新特性之一：行版本 

SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0;

ALTER TABLE ... ALGORITHM=INSTANT在每次添加一或多列、删除一或多列或在同一操作中添加和删除一或多列的操作之后，都会创建一个新的行版本。

MySQL 8.0.29在INFORMATION_SCHEMA.INNODB_TABLES表中新添加了TOTAL_ROW_VERSIONS列来跟踪表的行版本数。每次立即添加或删除列时，该值都会增加。初始值为0。

三、删除DROP

删除数据库

DROP DATABASE [IF EXISTS] `库名`;

 删除数据表

DROP TABLE [IF EXISTS] `表名`;

 说明：IF EXISTS表示如果数据库存在再执行删除操作，不存在则不执行。

注意：

• 慎用DROP：DROP会彻底删除目标对象及其中所有的数据，且不可恢复！
• 备份数据：DROP
• 是否存在：使用IF EXISTS判断再执行删除操作

四、MySQL8新特性之一：原子DDL操作

原子DDL操作是指对数据库结构进行更改时，要么完全成功，要么在出错时完全回滚的DDL语句，是确保数据库结构更改一致性和完整性的重要机制。

在过去，执行DDL（数据定义语言）语句时，如果中途发生错误，可能会导致数据结构的不一致或无效状态。为解决这个问题，MySQL8.0的新特性开始支持原子DDL操作，其中与表相关的原子DDL只支持 InnoDB 存储引擎，以下是一些常见的DDL原子操作语句：

1. 创建表（CREATE TABLE）：当使用InnoDB存储引擎创建表时，该操作是原子的。
2. 修改表（ALTER TABLE）：许多ALTER TABLE操作在MySQL 8.0及更高版本中都是原子的，包括添加或删除列、修改列类型、添加或删除索引等。但是，不是所有的ALTER TABLE操作都是原子的，你应该查阅官方文档以了解哪些操作是原子的。
3. 删除表（DROP TABLE）：当删除使用InnoDB存储引擎的表时，该操作也是原子的。如果尝试删除多个表，并且所有这些表都使用支持原子DDL的存储引擎（如InnoDB），那么这些表的删除操作要么全部成功，要么全部回滚。
4. 创建或删除索引（CREATE INDEX, DROP INDEX）：与InnoDB表相关的创建或删除索引操作也是原子的。
5. 创建或删除视图（CREATE VIEW, DROP VIEW）：在某些情况下，视图的创建或删除也可以是原子的，但这通常取决于视图的定义和所使用的存储引擎。
6. 存储程序、触发器和函数的创建、修改和删除：这些操作也可以是原子的，特别是当它们与InnoDB表相关时。

原子DDL语句将数据字典更新、存储引擎操作和与DDL操作相关联的二进制日志写入组合成单个原子操作。即使服务器在操作过程中停止运行，操作也可以提交，将适用的更改保存到数据字典、存储引擎和二进制日志中，或者回滚。

以上为数据库、表、列操作的基本的DDL，其余部分会在后续系列中详解。如有问题和建议，可私信或评论，非常感谢。