02｜建库建表

上一篇装好了 MySQL，能连上了。但里面是空的——没有库、没有表，存不了数据。

要把数据存进去，得先建库（database）、再建表（table）。库是容器，表是实际存数据的地方。建表时做的决策——字段类型、索引、约束——会跟着这张表一辈子，后面排查慢查询、锁争用时经常要回头看。本篇先讲最基础的：怎么建库、怎么建表、怎么改表结构。

一、连进来

上一篇装好 MySQL 后，本机连：

mysql -uroot -p

输密码进去，看到 mysql> 提示符。后续的 SQL 都在这个提示符下敲。

二、库

MySQL 里数据按库（database）分。一个 MySQL 实例可以有很多个库，库之间互相独立。先看看现在有哪些：

SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

前面几个（information_schema、mysql、performance_schema、sys）是系统库，存 MySQL 自己的元数据、权限、性能数据，不要往里写业务数据。

建一个业务库：

CREATE DATABASE ops_demo
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_0900_ai_ci;

字符集用 utf8mb4（上一篇讲过，utf8 是残缺的存不了 emoji）。COLLATE 是排序规则，utf8mb4_0900_ai_ci 是 8.0+ 的默认——ai 表示不区分重音，ci 表示不区分大小写。

切到这个库：

USE ops_demo;
SELECT DATABASE();     -- 确认当前在哪个库

不 USE 也行，可以用 库名.表名 全限定访问。但日常操作一个库时 USE 进去方便。

三、建第一张表

库建好了，里面没表。建一张记录服务器的表：

CREATE TABLE servers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  hostname VARCHAR(128) NOT NULL,
  ip_addr VARCHAR(45) NOT NULL,
  env ENUM('dev','test','prod') NOT NULL DEFAULT 'test',
  status TINYINT NOT NULL DEFAULT 1,
  remark VARCHAR(255) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_ip_addr (ip_addr),
  KEY idx_env_status (env, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

先别被这堆字段吓到，拆开看。

字段定义

每个字段一行，格式是 字段名 类型 约束：

字段	类型	说明
id	BIGINT UNSIGNED	主键，自增
hostname	VARCHAR(128)	主机名，变长字符串
ip_addr	VARCHAR(45)	IP 地址（IPv6 最长 45 字符）
env	ENUM(...)	环境枚举，只能取指定值
status	TINYINT	状态码，小整数
remark	VARCHAR(255)	备注，可为空
created_at	DATETIME	创建时间，默认当前时间
updated_at	DATETIME	更新时间，改时自动更新

NOT NULL 表示不能为空，DEFAULT 给默认值。AUTO_INCREMENT 让 id 自动加 1。DEFAULT CURRENT_TIMESTAMP 让时间默认取当前，ON UPDATE CURRENT_TIMESTAMP 让记录被修改时这个字段自动更新成当前时间——这两个时间戳字段是建表标配。

索引和约束

表定义最后几行是索引：

PRIMARY KEY (id),              -- 主键，每行唯一标识
UNIQUE KEY uk_ip_addr (ip_addr),  -- 唯一约束，ip 不能重复
KEY idx_env_status (env, status)  -- 普通索引，加速按 env+status 查询

PRIMARY KEY 每张表都要有，定位一行数据靠它。UNIQUE KEY 保证某列值不重复（这里 IP 不能重复）。KEY 是普通索引，让查询快。索引怎么用、什么时候失效，第 6 篇专门讲。

ENGINE 和 CHARSET

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

ENGINE=InnoDB 用 InnoDB 存储引擎——支持事务、行锁、崩溃恢复，是生产标配，基本都用它。CHARSET=utf8mb4 字符集。这两个一般整张表统一。

四、看表结构

建完表，确认长什么样：

SHOW CREATE TABLE servers\G

\G 让输出竖排，字段多时比表格好读：

*************************** 1. row ***************************
       Table: servers
Create Table: CREATE TABLE `servers` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `hostname` varchar(128) NOT NULL,
  ...

这是 MySQL 实际存的表定义，能看出来字段类型、约束、索引的完整情况。排查"为什么这个字段存不进去""索引建没建对"时，先看这个。

看字段简表：

DESC servers;

+------------+-----------------+------+-----+-------------------+
| Field      | Type            | Null | Key | Default           |
+------------+-----------------+------+-----+-------------------+
| id         | bigint unsigned | NO   | PRI | NULL              |
| hostname   | varchar(128)    | NO   |     | NULL              |
...

DESC 比 SHOW CREATE TABLE 简洁，快速看字段名和类型时用。

五、改表结构

表建好用了一段时间，发现要加个字段。ALTER TABLE：

-- 加字段
ALTER TABLE servers ADD COLUMN owner VARCHAR(64) DEFAULT NULL;

-- 删字段
ALTER TABLE servers DROP COLUMN remark;

-- 改字段类型
ALTER TABLE servers MODIFY COLUMN hostname VARCHAR(256) NOT NULL;

但 ALTER TABLE 在大表上是重操作——它可能要重建整张表（拷贝所有数据）。一张几千万行的表加字段，可能锁表几分钟甚至更久，期间业务写不进去。

8.0+ 很多 ALTER 操作支持 INSTANT 算法（加字段在末尾、加索引等），瞬间完成不锁表：

ALTER TABLE servers ADD COLUMN owner VARCHAR(64) DEFAULT NULL, ALGORITHM=INSTANT;

但不能保证所有改动都 INSTANT。大表改结构前先查这个改动支不支持 INSTANT，不支持的要在低峰期或用 pt-online-schema-change 这类在线改表工具。

六、删表和删库

DROP TABLE servers;
DROP DATABASE ops_demo;

DROP 是不可逆的——表和库直接没了，数据全没。生产环境敲 DROP 前必须三思，最好先备份。MySQL 没有"回收站"，删了就是删了。

建好表之后

库建了、表建了、字段类型和约束定了。但表里还是空的——没有数据。下一篇讲数据类型和约束怎么选（建表时的决策细节），再后面讲怎么往表里增删改查数据。