DDL — 数据定义语言

本节内容

数据库操作：创建、查看、切换、删除。
表操作：创建、查看结构、修改、删除、清空。
MySQL 9.6.0 常用数据类型概览与选用原则。
DDL 操作的不可逆提醒。

概念

DDL（Data Definition Language）操作的是数据库对象的结构，不是数据本身。

DDL 的典型特点是：一条 DDL 执行后通常会隐式提交当前事务，即 MySQL 会先自动 COMMIT 再执行 DDL。因此不要在未提交事务的中途执行 DDL，否则之前的 DML 修改会被一起提交。

数据库操作

创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名
  DEFAULT CHARACTER SET 字符集
  DEFAULT COLLATE 排序规则;

示例：

CREATE DATABASE IF NOT EXISTS mysql_basic
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_0900_ai_ci;

查看数据库

-- 列出所有数据库
SHOW DATABASES;

-- 查看创建语句
SHOW CREATE DATABASE mysql_basic;

SHOW CREATE DATABASE 输出的是可以重新执行的完整建库语句，迁移或排查问题时很有用。

切换数据库

USE mysql_basic;

查看当前数据库

SELECT DATABASE();

删除数据库

DROP DATABASE [IF EXISTS] 数据库名;

删除数据库会同时删除库内所有表和数据，不可恢复。

表操作

创建表

CREATE TABLE [IF NOT EXISTS] 表名 (
  字段名 数据类型 [约束] [COMMENT '注释'],
  字段名 数据类型 [约束] [COMMENT '注释'],
  ...
) ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8mb4
  DEFAULT COLLATE = utf8mb4_0900_ai_ci
  COMMENT = '表注释';

示例——创建后续 DML/DQL 使用的示例表：

CREATE TABLE IF NOT EXISTS tb_user (
  id       BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
  username VARCHAR(50)  NOT NULL UNIQUE COMMENT '用户名',
  age      TINYINT UNSIGNED COMMENT '年龄',
  gender   ENUM('male', 'female', 'other') COMMENT '性别',
  balance  DECIMAL(10, 2) NOT NULL DEFAULT 0.00 COMMENT '余额',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8mb4
  DEFAULT COLLATE = utf8mb4_0900_ai_ci
  COMMENT = '用户表';

要点说明：

BIGINT PRIMARY KEY AUTO_INCREMENT：自增主键。
VARCHAR(50)：可变长度字符串，最大 50 字符。
ENUM：限定取值范围的字符串枚举。
DECIMAL(10,2)：精确小数，10 位总长度、2 位小数，适合金额。
DEFAULT CURRENT_TIMESTAMP：插入行时自动填入当前时间。
ENGINE = InnoDB：MySQL 9.6.0 默认引擎，支持事务、行锁、外键。
COMMENT：表注释和字段注释，方便后续维护。

查看表结构

-- 查看表有哪些字段
DESC tb_user;

-- 查看完整建表语句
SHOW CREATE TABLE tb_user;

修改表

ALTER TABLE 最常用的三种操作：

-- 添加字段
ALTER TABLE tb_user ADD COLUMN phone VARCHAR(20) COMMENT '手机号';

-- 修改字段类型
ALTER TABLE tb_user MODIFY COLUMN username VARCHAR(100) NOT NULL;

-- 重命名字段（同时要给出新类型）
ALTER TABLE tb_user CHANGE COLUMN username user_name VARCHAR(100) NOT NULL;

MODIFY 和 CHANGE 的区别：

操作	用法	能否改名
`MODIFY`	只改字段定义（类型、约束、注释）	否
`CHANGE`	改字段名 + 字段定义	是

注意：CHANGE 即使只改名也必须写出完整的新字段定义。

删除表

DROP TABLE [IF EXISTS] tb_user;

删除表会清空结构和数据，不可恢复。

清空表

TRUNCATE TABLE tb_user;

TRUNCATE 和 DELETE FROM tb_user（不加 WHERE）的区别：

	TRUNCATE	DELETE
归类	DDL	DML
速度	快（直接释放数据页）	慢（逐行删除，写日志）
自增计数器	重置	不重置
可回滚	不支持（隐式提交）	支持（在事务内）
WHERE 条件	不支持	支持

常用数据类型

数值类型

类型	说明	示例
`TINYINT`	1 字节整数，-128~127	年龄、状态码
`SMALLINT`	2 字节整数	年份
`INT` / `INTEGER`	4 字节整数	普通 ID
`BIGINT`	8 字节整数	主键、大 ID
`TINYINT UNSIGNED`	无符号，0~255	年龄
`DECIMAL(M,D)`	精确定点小数	金额、价格
`FLOAT` / `DOUBLE`	近似浮点数	统计、科学计算

金额字段必须用 DECIMAL，不能用 FLOAT 或 DOUBLE：

-- 正确
balance DECIMAL(10, 2)

-- 错误：0.1 + 0.2 可能不等于 0.3
balance FLOAT

字符串类型

类型	说明	适用场景
`CHAR(N)`	定长字符串，0~255	固定长度的编码、MD5
`VARCHAR(N)`	变长字符串，0~65535	用户名、邮箱、标题
`TEXT`	长文本，最大 65535	文章内容、简介
`ENUM('a','b')`	枚举，只允许列出的值	性别、状态
`SET`	集合，可多选	标签（极少用）

CHAR vs VARCHAR：

CHAR(10) 存 'Hi' 会占用 10 字符空间（右侧填空格补齐）。
VARCHAR(10) 存 'Hi' 只占用 2 字符空间 + 1 字节长度前缀。
对固定长度的数据（如 MD5 32 位），CHAR 比 VARCHAR 快。
对变长数据，用 VARCHAR 省空间。

日期时间类型

类型	格式	范围
`DATE`	`YYYY-MM-DD`	1000-01-01 ~ 9999-12-31
`TIME`	`HH:MM:SS`	-838:59:59 ~ 838:59:59
`DATETIME`	`YYYY-MM-DD HH:MM:SS`	1000-01-01 ~ 9999-12-31
`TIMESTAMP`	`YYYY-MM-DD HH:MM:SS`	1970-01-01 ~ 2038-01-19

DATETIME vs TIMESTAMP：

DATETIME 保存的是字面时间，不受时区影响。
TIMESTAMP 保存的是 UTC 时间戳，显示时自动转换到当前会话时区。
TIMESTAMP 有 2038 年问题，新项目建议用 DATETIME。
记录写入时间用 DEFAULT CURRENT_TIMESTAMP，建议配合 DATETIME。

易错点与注意

DDL 会隐式提交：先提交当前事务再执行 DDL，不要在事务中途执行。
DROP 操作不可回滚：DROP DATABASE 和 DROP TABLE 不走事务日志。
金额用 DECIMAL：永远不用 FLOAT 或 DOUBLE。
macOS 表名大小写：取决于文件系统和 lower_case_table_names 配置，统一用小写最安全。
MODIFY vs CHANGE：只改属性用 MODIFY，改名用 CHANGE。用 CHANGE 改名时必须带上完整的字段定义。

MySQL-basic：DDL 数据定义语言