挖掘机驾驶宝典

Appearance

SQL 语法入门 ​

MySQL SQL 入门的重点不是列出所有语句，而是建立类型、约束、NULL、事务和批量修改的正确默认习惯。示例以 MySQL 8.4 与 InnoDB 为参照。

建表 ​

sql
CREATE TABLE app_user (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username    VARCHAR(100) NOT NULL,
    enabled     BOOLEAN NOT NULL DEFAULT TRUE,
    balance     DECIMAL(18, 2) NOT NULL DEFAULT 0,
    profile     JSON,
    created_at  DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at  DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                            ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uk_app_user_username (username),
    CONSTRAINT ck_app_user_balance CHECK (balance >= 0)
) ENGINE = InnoDB
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;
选择说明
BIGINT UNSIGNED主键容量较大，但跨数据库迁移需注意 Unsigned 支持
DECIMAL精确金额，避免 Float/Double 二进制误差
BOOLEANMySQL 中是 TINYINT(1) 的同义形式，不是独立物理类型
DATETIME保存日历时间，不自动进行时区转换
TIMESTAMP按 UTC 存储并根据 Session Time Zone 转换，范围与语义不同
JSONBinary JSON，常用路径可通过 Generated/Functional Index 支持
utf8mb4完整 Unicode；旧 utf8 名称不是完整四字节 UTF-8

Collation 决定字符串相等、排序和唯一约束语义，不能只看字符集。迁移或升级 Collation 前应检查唯一键是否产生冲突。

插入与 Upsert ​

sql
INSERT INTO app_user (username, enabled, balance, profile)
VALUES ('alice', TRUE, 100.00, JSON_OBJECT('level', 'vip'));

SELECT LAST_INSERT_ID();
sql
INSERT INTO app_user (username, enabled)
VALUES ('alice', TRUE) AS incoming
ON DUPLICATE KEY UPDATE
    enabled = incoming.enabled,
    updated_at = CURRENT_TIMESTAMP(6);

Upsert 可能由任意 Unique Key 冲突触发，不应在存在多个唯一约束时假设一定由指定业务键触发。批量 Upsert 还应检查影响行数与自增值语义。

查询与 NULL ​

sql
SELECT id, username, created_at
FROM app_user
WHERE enabled = TRUE
  AND username LIKE 'a%'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;
需求写法
判断 NULLIS NULL / IS NOT NULL
NULL 安全相等<=>
取第一个非 NULLCOALESCE(a, b, c)
条件表达式CASE WHEN ... THEN ... ELSE ... END
去重SELECT DISTINCT，先确认是否掩盖错误 JOIN

NULL = NULL 的结果不是 TRUE。NOT IN 子查询中只要出现 NULL 就可能使结果与直觉不同，反连接通常更适合使用 NOT EXISTS 并明确关联条件。

关联修改 ​

sql
UPDATE app_user AS u
JOIN user_blacklist AS b ON b.username = u.username
SET u.enabled = FALSE,
    u.updated_at = CURRENT_TIMESTAMP(6)
WHERE b.active = TRUE;
sql
DELETE u
FROM app_user AS u
JOIN deleted_account AS d ON d.user_id = u.id;

执行关联 UPDATE/DELETE 前，先用相同 JOIN 写 SELECT 检查目标主键和数量。来源关系应通过唯一约束保证一个目标行不会被多条来源记录不确定地匹配。

事务 ​

sql
START TRANSACTION;

SELECT balance
FROM account
WHERE id = 1
FOR UPDATE;

UPDATE account
SET balance = balance - 100
WHERE id = 1 AND balance >= 100;

COMMIT;

应用必须检查 UPDATE 的 Affected Rows。余额判断放在 UPDATE Predicate 中，可以减少先读后写的竞态，但完整转账仍需要事务、目标账户更新和失败重试。

DDL 可能隐式提交事务，不能假设所有 Schema 语句都像普通 DML 一样回滚。事务中也不应调用耗时外部接口或等待人工确认。

分页 ​

sql
SELECT id, username, created_at
FROM app_user
WHERE (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 20;

大 OFFSET 会扫描并丢弃前序记录。稳定连续翻页使用由排序键构成的 Keyset Pagination，并建立匹配的复合索引。排序键必须能唯一确定顺序，因此示例同时使用 created_at 和 id。

安全默认习惯 ​

  • 所有写语句显式写列名和 WHERE 条件。
  • 应用使用 Prepared Statement 绑定值，不拼接用户输入。
  • 金额、计数和时间类型在 Driver 层明确映射。
  • 生产变更先在事务或影子数据上确认影响范围，但注意 DDL 的隐式提交。
  • 批量任务限制批次大小，记录进度，并设计可重试和幂等机制。
  • 不依赖无 ORDER BY 的自然返回顺序。

参考资料 ​