🗄️ 第03章 · 库表操作与数据类型

CREATE DATABASE/TABLE 常见类型

库表操作与数据类型¶

库（database，MySQL 里也叫 schema）和表（table）是数据的“容器”。把数据结构设计好，后面查询、索引、备份都省心；设计得拍脑袋，后期全是坑。本章用这台服务器上真实的 bangdream 库为例，讲建库、建表和最常见的数据类型与约束。

库与表的关系¶

一个 MySQL 实例下可以有很多库，每个库是逻辑隔离的命名空间，库名直接对应数据文件目录（如 /var/lib/mysql/bangdream/）。表则是库内的一张张二维表：行（record）是记录、列（column）是字段。设计表时，字段类型选对、约束给全是重中之重。

创建与管理数据库¶

-- 创建数据库，显式指定字符集为 utf8mb4（能存 emoji 和所有汉字）
CREATE DATABASE bangdream
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- 查看当前有哪些库
SHOW DATABASES;

-- 切换到某个库
USE bangdream;

-- 查看当前库
SELECT DATABASE();

-- 删除库（危险！连数据一起删，没有回收站）
DROP DATABASE bangdream;

安全提示：DROP DATABASE / DROP TABLE 是真正的“一键毁尸灭迹”，没有回收站、没有任何确认，删了立刻从磁盘消失。执行前务必确认库名拼写无误，最好先备份或先在测试库演练。

创建表与常见数据类型¶

创建表是最常见的 DDL。这里以 bands（乐队）表为例，它覆盖了主键、自增、字符、时间戳等最常见元素。

CREATE TABLE bands (
  id         INT NOT NULL AUTO_INCREMENT COMMENT '主键，自增',
  name       VARCHAR(100) NOT NULL COMMENT '乐队名',
  cls        VARCHAR(50)  NOT NULL COMMENT '分类标识',
  icon       VARCHAR(10)  DEFAULT '' COMMENT '图标',
  color      VARCHAR(50)  DEFAULT '' COMMENT '主题色',
  created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

选择数据类型时，记住一句总原则：够用就好，别贪大。id 用 INT，日期用 DATETIME/TIMESTAMP，金额用 DECIMAL 而不是 FLOAT。

类型	字节/范围	典型用途	示例
`INT`	4 字节，±21 亿	主键、计数	`id INT AUTO_INCREMENT`
`BIGINT`	8 字节	超大计数、时间戳	`views BIGINT`
`VARCHAR(n)`	变长，最多 n 字符	名称、文本（n≤65535）	`name VARCHAR(100)`
`CHAR(n)`	定长	固定长度编码、国家码	`code CHAR(2)`
`DECIMAL(p,s)`	精确小数	金额、评分	`price DECIMAL(10,2)`
`FLOAT/DOUBLE`	4/8 字节	科学计算（有精度损失）	`lat DOUBLE`
`DATE`	3 字节	生日、日期	`birthday DATE`
`DATETIME`	8 字节	具体时刻	`created_at DATETIME`
`TIMESTAMP`	4 字节	时区敏感的时间戳	`updated_at TIMESTAMP`
`JSON`	变长	半结构化数据	`attrs JSON`
`TEXT`	变长	长文本	`bio TEXT`

易错点：金额千万别用 FLOAT/DOUBLE，浮点运算有精度误差（0.1+0.2≠0.3）；钱要用 DECIMAL。TIMESTAMP 受时区影响且上限到 2038 年；DATETIME 没有时区概念、更不易踩坑。

约束：让数据说话算话¶

约束是表结构里最有价值的部分，它在数据库层面拦住脏数据，而不是靠应用自觉。常用的五个：

约束	作用	示例
`PRIMARY KEY`	主键，唯一标识，非空	`PRIMARY KEY (id)`
`AUTO_INCREMENT`	自动递增，常配主键	`id INT AUTO_INCREMENT`
`UNIQUE`	字段值唯一	`UNIQUE KEY uk_slug (slug)`
`NOT NULL`	不允许为空	`name VARCHAR(100) NOT NULL`
`DEFAULT`	没填时的默认值	`status INT DEFAULT 0`
`FOREIGN KEY`	外键，引用另一张表	`FOREIGN KEY (band_id) REFERENCES bands(id)`

外键（FOREIGN KEY）用来维护表之间的引用完整性，比如 stories 的 band_id 必须指向真实存在的 bands.id。这是真实 stories 表的定义：

CREATE TABLE stories (
  id         INT NOT NULL AUTO_INCREMENT,
  band_id    INT NOT NULL COMMENT '所属乐队',
  title      VARCHAR(200) DEFAULT '',
  messages   JSON DEFAULT NULL COMMENT '剧情消息，JSON 存储',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY band_id (band_id),                                  -- 普通索引，加速按乐队查询
  CONSTRAINT stories_ibfk_1 FOREIGN KEY (band_id)
    REFERENCES bands (id) ON DELETE CASCADE               -- 删乐队时级联删剧情
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ON DELETE CASCADE 表示：删掉某个乐队时，它底下的剧情一并删除——这能避免“孤儿数据”，但也意味着误删会连带丢失大片数据，务必谨慎。

修改表结构¶

业务在变，表结构也要跟着变。ALTER TABLE 改结构不会丢已有数据（但大表会长时间锁表，生产要趁低谷窗口）：

-- 新增一列
ALTER TABLE bands ADD COLUMN founded_year SMALLINT DEFAULT NULL;

-- 修改列的类型/注释
ALTER TABLE bands MODIFY COLUMN color VARCHAR(80) DEFAULT '' COMMENT '主题色(可含渐变)';

-- 修改列名（注意 CHANGE 需要同时给出新类型）
ALTER TABLE bands CHANGE icon icon_path VARCHAR(100) DEFAULT '';

-- 删除一列
ALTER TABLE bands DROP COLUMN founded_year;

-- 查看表结构（\G 竖排显示更清晰）
DESC bands;
SHOW CREATE TABLE bands\G

易错点：对大表做 ALTER TABLE（尤其加列、加索引、改类型）会在 MySQL 8 里触发在线 DDL，但仍可能长时间占用资源甚至锁表。等到深夜低谷再做，或用 gh-ost 之类的在线变更工具，开盘前先看 information_schema 判断表大小。

案例场景：从零建一个业务库¶

拿这台服务器上 lovelive 库的 characters 为例，重建一个精简版：

-- 建库（带字符集）
CREATE DATABASE lovelive CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE lovelive;

-- 建分组表
CREATE TABLE idol_groups (
  id   INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 建角色表（关联分组，含唯一约束防重)
CREATE TABLE characters (
  id        INT NOT NULL AUTO_INCREMENT,
  group_id  INT NOT NULL,
  name_cn   VARCHAR(100) NOT NULL,
  name_jp   VARCHAR(100) NOT NULL,
  birthday  DATE DEFAULT NULL,
  bio       TEXT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_name (name_cn),
  CONSTRAINT fk_char_group FOREIGN KEY (group_id)
    REFERENCES idol_groups (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;