SQL 基础与表管理

MySQL 运维里日常会接触建库建表、增删改查，以及字段类型、约束和字符集这些表结构相关的选择。表结构设计时做的决定，后面排查慢查询和锁争用的时候会反复遇到。

一、库和表的查看

mysql -h 127.0.0.1 -P 3306 -uroot -p

进去后先看有哪些库：

SHOW DATABASES;

建库时指定字符集和排序规则：

CREATE DATABASE ops_demo
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_0900_ai_ci;

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

看库下面有哪些表：

SHOW TABLES;

看某张表是怎么建出来的：

SHOW CREATE TABLE servers\G

\G 把结果竖排显示，字段多的时候比横向表格好读得多。

二、字段类型的选择

字段类型定下来之后，存储空间、索引效果、查询行为、比较逻辑都跟着定了。改字段类型在线上是个重操作（大表可能要拷贝整表），所以建表的时候尽量选对。

常用类型：

类型	什么时候用	注意什么
INT / BIGINT	整数 ID、计数	INT UNSIGNED 空间翻倍，ID 一般不用负数
DECIMAL	金额、需要精确小数的地方	DECIMAL(10,2) 总共 10 位，小数 2 位，避免浮点误差
VARCHAR	变长字符串	要按实际业务上限估长度，别全写 VARCHAR(255)
TEXT	长文本	不能有默认值，索引需要前缀
DATETIME	日期时间	不带时区，存什么就取什么
TIMESTAMP	时间戳	受时区影响，存入和取出可能不一样
JSON	扩展属性	不适合频繁查询的字段，适合做补充信息

NULL 需要特别注意。NULL 不是空字符串，不是 0，它表示"未知"。在唯一约束里，多个 NULL 不算冲突（NULL != NULL 在 SQL 的语境中判断为"未知"，而不是"相等"）；在统计和排序里，NULL 值的处理也跟普通值不同。字段如果确实需要表达"没有值"这个含义，用 NULL 是合理的；否则加上 NOT NULL 并给一个明确的默认值，省掉代码里处理三值逻辑（TRUE/FALSE/NULL）的麻烦。

金额和 IP 地址的例子：

price DECIMAL(10,2) NOT NULL
hostname VARCHAR(128) NOT NULL
ip_addr  VARCHAR(45)  NOT NULL    -- IPv6 最长 45 字符

三、建表

一张服务器的管理表，尽量把约束想清楚再建：

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;

建表语句里每一处决定都有后续影响：

配置	为什么这样写	后续影响
BIGINT UNSIGNED	ID 空间足够大	主键大，二级索引也一起变大
NOT NULL + DEFAULT	明确的值不存在歧义	应用代码不用判断 NULL
ENUM	值固定在少数几个	后续加选项要 ALTER TABLE
PRIMARY KEY (id)	InnoDB 聚簇索引	决定了数据在磁盘的物理顺序
UNIQUE KEY uk_ip_addr	IP 不能重复	插入重复值时报错，查询走 const 级别
KEY idx_env_status	按环境和状态筛选	写入时多维护一个索引
ENGINE=InnoDB	事务引擎	支持事务、行锁、crash recovery
utf8mb4	完整 Unicode	MySQL 的 utf8 只支持基本多语言面

InnoDB 表要有显式主键。不指定的话，InnoDB 会找一个非空唯一索引当聚簇索引，找不到就生成隐藏的 row id。隐藏 row id 在复制、数据归档和排查问题时不方便，所以生产表一般都显式给主键。

四、INSERT

单行插入——明确写出字段名：

INSERT INTO servers (hostname, ip_addr, env, status, remark)
VALUES ('web-01', '10.0.0.11', 'prod', 1, 'nginx node');

多行一起插：

INSERT INTO servers (hostname, ip_addr, env, status)
VALUES
  ('web-02', '10.0.0.12', 'prod', 1),
  ('db-01',  '10.0.0.21', 'prod', 1),
  ('test-01','10.0.1.11', 'test', 1);

不写字段名的插入方式有隐患：

INSERT INTO servers VALUES (...);   -- 不写字段名，依赖字段顺序

表结构一改（比如中间加了一列），字段位置偏移，这种 SQL 就会把数据塞到错的列里。写清楚字段名是简单但重要的防护。

五、SELECT 查询

指定需要的字段，不取全表：

SELECT id, hostname, ip_addr, env
FROM servers
WHERE env = 'prod'
ORDER BY id DESC
LIMIT 10;

等值条件组合查询：

SELECT id, hostname
FROM servers
WHERE env = 'prod'
  AND status = 1;

前缀模糊匹配：

SELECT id, hostname
FROM servers
WHERE hostname LIKE 'web-%';

LIKE 'web-%' 可以走索引（前缀固定），LIKE '%web%' 不行——后者的匹配位置在字符串任意处，BTree 索引没办法定位。

分组统计：

SELECT env, COUNT(*) AS cnt
FROM servers
GROUP BY env
ORDER BY cnt DESC;

日常脚本里少用 SELECT *。取回的字段多了，网络传输变大，而且表结构后续调整了程序解析也可能受影响。写清楚需要哪些字段，也是给读代码的人一个信号：这次查询关心的是这些数据。

六、UPDATE 和 DELETE——先查再改

更新前，先用 SELECT 确认将要影响的范围：

SELECT id, hostname, status
FROM servers
WHERE hostname = 'web-01';

确认范围正确，再执行更新：

UPDATE servers
SET status = 0
WHERE hostname = 'web-01';

删除也一样，先查后删：

SELECT id, hostname, ip_addr
FROM servers
WHERE env = 'test';

DELETE FROM servers
WHERE env = 'test';

没有 WHERE 条件的 UPDATE/DELETE 全表生效，这类操作在生产上基本就是事故。InnoDB 在有索引的 WHERE 条件下只锁符合条件的行；如果没有索引（或 WHERE 条件用不上索引），可能会扫描并锁住更多行，造成范围更大的锁等待。

可以打开安全更新模式做一层保护：

SET SQL_SAFE_UPDATES = 1;

开启之后，没有 key 条件或没有 LIMIT 的 UPDATE/DELETE 会被拒绝。这层保护有局限性（比如用不上索引的 WHERE 条件可能漏过去），但交互操作时能兜住一部分误操作。

大批量修改（几十万行起）不适合一条 UPDATE 全干完。拆成小批执行（每次几千行），控制锁持有时间和事务大小，同时观察连接数和主从延迟。

七、ALTER TABLE——修改表结构

加字段：

ALTER TABLE servers
ADD COLUMN owner VARCHAR(64) DEFAULT NULL COMMENT '负责人';

改字段类型或长度：

ALTER TABLE servers
MODIFY COLUMN remark VARCHAR(512) DEFAULT NULL;

加索引和删索引：

ALTER TABLE servers ADD KEY idx_hostname (hostname);
ALTER TABLE servers DROP INDEX idx_hostname;

MySQL 8.0 支持更多 online DDL 和 instant DDL（添加字段可以瞬间完成），但不是所有 DDL 都无锁、无拷贝。大表改字段前要查清楚当前版本的 DDL 行为、表大小、业务写入量，判断是否适合直接在线上做。

看表大小：

SELECT
  table_schema,
  table_name,
  ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'ops_demo'
  AND table_name = 'servers';

八、约束——数据库兜底

约束是数据库自身强制执行的规则。应用层校验负责尽早拦截错误输入，数据库约束负责兜底——防止绕过应用的直接操作、或者应用代码的校验漏洞导致脏数据入库。

约束	干什么	违反时的表现
PRIMARY KEY	唯一 + 非空	插入重复或空值时报错
UNIQUE	唯一，可以有多个 NULL	ERROR 1062: Duplicate entry
NOT NULL	不能为空	插入 NULL 时报错
DEFAULT	不提供值时自动填入	静默填充
FOREIGN KEY	引用完整性	删除被引用行时阻止或级联

外键在高并发场景下要看具体情况。它能保证引用关系完整，但也可能引入锁等待、增加删除限制、加大迁移复杂度。很多高并发业务把引用关系校验放在应用层，数据库只保留最必要的唯一约束。没有绝对对错，看业务容忍度和架构取舍。

加唯一约束：

ALTER TABLE servers ADD UNIQUE KEY uk_hostname (hostname);

九、几个常用函数

时间相关：

SELECT NOW(), CURRENT_DATE(), UNIX_TIMESTAMP();

字符串拼接：

SELECT CONCAT(hostname, ':', ip_addr) AS node FROM servers;

处理 NULL 值——IFNULL 遇到 NULL 时返回指定替代值：

SELECT hostname, IFNULL(remark, '') AS remark FROM servers;

条件转换：

SELECT
  hostname,
  CASE status
    WHEN 1 THEN 'online'
    ELSE 'offline'
  END AS status_name
FROM servers;

JSON 取值：

SELECT JSON_EXTRACT('{"role":"web"}', '$.role') AS role;

JSON 字段适合放结构不固定的扩展信息。频繁在 JSON 字段里按某个 key 的值来查询或过滤的话，把这个 key 单独拆成一个列会更利于加索引。

十、事务基础

MySQL 的 InnoDB 支持事务。一组操作要么全部成功、要么全部失败：

START TRANSACTION;

UPDATE servers
SET status = 0
WHERE hostname = 'web-01';

SELECT id, hostname, status
FROM servers
WHERE hostname = 'web-01';

COMMIT;

如果发现改错了，可以在 COMMIT 之前撤销：

START TRANSACTION;

DELETE FROM servers
WHERE hostname = 'web-02';

ROLLBACK;     -- 回到事务开始前的状态

事务给人一次"看一眼再确认"的机会。但有一个重要限制：DDL 操作（ALTER TABLE、CREATE TABLE 等）在 MySQL 里通常会引起隐式提交——在执行 DDL 之前，当前事务里的未提交变更会被自动提交。所以 ROLLBACK 覆盖不了 DDL。

打开事务的时间越长，锁持有的时间越长、undo 日志更难清理、从库回放也可能被延迟。交互式操作适合短事务——改几条、看一看、提交。长时间开着事务不做任何操作（比如在事务里查完数据去干别的事），是常见的性能隐患。