MySQL CRUD 高频操作速查表

本文面向日常开发中的高频 MySQL 数据操作。示例基于 MySQL 8.0，使用 users 和 orders 两张表，既可以按章节学习，也可以直接复制 SQL 模板后修改。

CRUD 是 Create、Read、Update、Delete 的缩写，分别对应新增、查询、更新和删除。

1. 示例表和测试数据

1.1 创建数据库

CREATE DATABASE IF NOT EXISTS nloop_demo
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

USE nloop_demo;

• utf8mb4 可以完整存储中文、Emoji 等 Unicode 字符。
• utf8mb4_0900_ai_ci 是 MySQL 8.0 常用排序规则；ai 表示忽略重音，ci 表示比较时不区分大小写。

1.2 创建用户表

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(255) NOT NULL,
  age TINYINT UNSIGNED NULL,
  status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1=正常，0=禁用',
  deleted_at DATETIME NULL COMMENT '软删除时间',
  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_users_email (email),
  KEY idx_users_status_created_at (status, created_at)
) ENGINE = InnoDB;

1.3 创建订单表

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  order_no VARCHAR(32) NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  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_orders_order_no (order_no),
  KEY idx_orders_user_id_created_at (user_id, created_at),
  CONSTRAINT fk_orders_user_id
    FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE = InnoDB;

金额使用 DECIMAL，不要使用 FLOAT 或 DOUBLE。后两者是近似值类型，可能产生不适合金额计算的精度误差。

1.4 插入测试数据

INSERT INTO users (username, email, age, status)
VALUES
  ('张三', 'zhangsan@example.com', 25, 1),
  ('李四', 'lisi@example.com', 30, 1),
  ('王五', 'wangwu@example.com', NULL, 0);

INSERT INTO orders (user_id, order_no, amount, status)
VALUES
  (1, 'ORD202608020001', 99.00, 'paid'),
  (1, 'ORD202608020002', 199.50, 'pending'),
  (2, 'ORD202608020003', 50.00, 'paid');

2. 常规数据类型

选择类型时主要考虑：数据的业务含义、最大范围，以及是否需要参与查询、排序、计算或索引。类型应留有合理余量，但不是越大越好。

2.1 整数类型

age TINYINT UNSIGNED NULL,
stock INT UNSIGNED NOT NULL DEFAULT 0,
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT

• UNSIGNED 表示无符号，适合 ID、年龄、库存等不应为负数的数据。
• AUTO_INCREMENT 常用于整数主键，由数据库生成递增值。
• INT(11) 中的 11 过去表示显示宽度，不表示能存储 11 位数字。该写法已在 MySQL 8.0 中废弃。
• 不要只因为“以后可能很大”就把所有数字都设为 BIGINT。

JavaScript number 最大只能安全表示 2^53 - 1，小于 MySQL BIGINT 的上限。大型 ID 超出安全范围时，应在 Node.js 中把它作为字符串传递，或者明确使用 JavaScript bigint。注意 JSON 不能直接序列化 bigint。

console.log(Number.MAX_SAFE_INTEGER); // 9007199254740991

2.2 精确小数和浮点数

DECIMAL(M, D) 用于金额、费率等必须精确存储的小数：

amount DECIMAL(10, 2) NOT NULL,
tax_rate DECIMAL(5, 4) NOT NULL

• M 是总位数，D 是小数位数。
• DECIMAL(10, 2) 最多包含 8 位整数和 2 位小数，例如 99999999.99。
• DECIMAL(5, 4) 可以存储类似 0.1350 的费率。

FLOAT 和 DOUBLE 是近似值类型，适合测量数据和科学计算等允许微小误差的场景：

temperature DOUBLE NULL

金额不要使用 FLOAT 或 DOUBLE。应用层同样要注意 JavaScript 中 0.1 + 0.2 !== 0.3，金额可以用十进制字符串或高精度方案传递和计算。

2.3 字符串类型

country_code CHAR(2) NOT NULL,
username VARCHAR(50) NOT NULL,
email VARCHAR(255) NOT NULL,
description TEXT NULL

• CHAR(n) 适合长度始终一致的值。
• VARCHAR(n) 适合长度变化的字符串，是业务字段的常见选择。
• VARCHAR(255) 中的 255 通常表示字符数，不是字节数；实际字节数受字符集影响。
• 短文本和需要频繁索引的字段通常优先使用 VARCHAR；正文、备注等长内容使用 TEXT。
• 不要无依据地把所有字段都定义为超长 VARCHAR，长度上限也在表达业务约束。

TEXT 建立索引时可能需要前缀索引：

CREATE INDEX idx_articles_content_prefix
ON articles (content(100));

它只索引前 100 个字符，可以减小索引体积，但无法区分前缀完全相同的内容。

2.4 日期和时间类型

birthday DATE NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  ON UPDATE CURRENT_TIMESTAMP

• DATETIME 保存写入的日期时间值，不会因为连接时区变化而自动转换，支持的范围也更大。
• TIMESTAMP 写入时从会话时区转换为 UTC，读取时再转换为会话时区。
• 两者都不保存 Asia/Shanghai 这样的时区名称。

本地单时区业务常使用 DATETIME；跨时区系统可以统一使用 UTC。最重要的是数据库连接、Node.js 应用和业务约定保持一致。

推荐使用左闭右开的时间范围：

WHERE created_at >= '2026-08-01 00:00:00'
  AND created_at < '2026-09-01 00:00:00'

这种写法不会遗漏月底最后一秒之后的微秒值，也通常有利于使用索引。

2.5 布尔值和枚举

MySQL 的 BOOLEAN、BOOL 是 TINYINT(1) 的同义写法，通常用 0 表示假、1 表示真：

is_admin BOOLEAN NOT NULL DEFAULT FALSE

如果字段有待支付、已支付、已取消等多个状态，就不应使用布尔值。状态集合很小且极少变化时可以使用 ENUM：

status ENUM('pending', 'paid', 'cancelled')
  NOT NULL DEFAULT 'pending'

ENUM 能限制取值，但增加成员需要修改表结构。状态经常变化时，可以使用 VARCHAR 配合 CHECK：

status VARCHAR(20) NOT NULL DEFAULT 'pending',
CONSTRAINT chk_orders_status
  CHECK (status IN ('pending', 'paid', 'cancelled'))

• 状态固定、希望数据库直接限制取值：可以使用 ENUM。
• 状态可能扩展、强调迁移灵活性：使用 VARCHAR、CHECK 或状态字典表。
• 不要使用缺少说明的 1、2、3 表示复杂状态，否则 SQL 结果难以直接理解。

2.6 JSON 类型

preferences JSON NULL

写入和读取 JSON：

UPDATE users
SET preferences = JSON_OBJECT('theme', 'dark', 'language', 'zh-CN')
WHERE id = 1;

SELECT id, preferences->>'$.theme' AS theme
FROM users
WHERE id = 1;

• -> 返回 JSON 值。
• ->> 返回去掉 JSON 引号后的文本值。
• JSON_SET(preferences, '$.theme', 'light') 可以更新其中一个属性。

JSON 适合结构可能变化、不是核心查询条件的扩展属性，但不能代替关系表设计。邮箱、金额和关联 ID 等核心数据应使用明确的列，以便添加约束和索引。

经常按 JSON 属性查询时，可以创建生成列并建立索引：

ALTER TABLE users
ADD COLUMN preference_theme VARCHAR(20)
  GENERATED ALWAYS AS (
    JSON_UNQUOTE(JSON_EXTRACT(preferences, '$.theme'))
  ) STORED,
ADD INDEX idx_users_preference_theme (preference_theme);

2.7 二进制类型

大型图片、视频和附件通常更适合放在对象存储或文件系统中，数据库只保存 URL 和元数据：

file_url VARCHAR(2048) NOT NULL,
file_size BIGINT UNSIGNED NOT NULL,
mime_type VARCHAR(100) NOT NULL

把大文件保存到 BLOB 会增加数据库、备份和复制的成本，只有确实需要事务一致性等能力时才应采用。

2.8 高频字段选型

手机号、身份证号和订单号虽然可能由数字组成，但它们是“标识符”，不用于数学计算，应使用字符串类型。这样可以保留前导零，也不会受到整数范围限制。

2.9 NULL、NOT NULL 和默认值

username VARCHAR(50) NOT NULL,
age TINYINT UNSIGNED NULL,
status TINYINT UNSIGNED NOT NULL DEFAULT 1

• 每条记录都必须存在的值使用 NOT NULL。
• 确实允许“未知”或“尚未填写”的值使用 NULL。
• 默认值应具有明确的业务含义，不要只为避免处理 NULL 而随意填写 0 或空字符串。
• NULL、数字 0 和空字符串 '' 具有不同的业务语义。

3. Create：新增数据

3.1 新增一条数据

INSERT INTO users (username, email, age)
VALUES ('赵六', 'zhaoliu@example.com', 28);

推荐明确写出字段名。这样即使以后表结构或字段顺序发生变化，SQL 的含义仍然清楚。

3.2 批量新增

INSERT INTO users (username, email, age)
VALUES
  ('用户A', 'user-a@example.com', 20),
  ('用户B', 'user-b@example.com', 21),
  ('用户C', 'user-c@example.com', 22);

一次批量插入通常比循环执行多次单条 INSERT 更高效，但单批数据也不宜无限增大。

3.3 插入查询结果

INSERT INTO disabled_user_backup (user_id, username, email)
SELECT id, username, email
FROM users
WHERE status = 0;

目标表必须已经存在，并且查询结果的字段数量、顺序和类型要与目标字段兼容。

3.4 唯一键冲突时更新

INSERT INTO users (username, email, age)
VALUES ('新名字', 'zhangsan@example.com', 26) AS new
ON DUPLICATE KEY UPDATE
  username = new.username,
  age = new.age;

当主键或唯一索引冲突时执行更新，常用于“存在就更新，不存在就新增”的场景。示例使用 MySQL 8.0.19 及之后推荐的行别名写法，避免使用已经废弃的 VALUES(column) 写法。

3.5 忽略唯一键冲突

INSERT IGNORE INTO users (username, email, age)
VALUES ('重复邮箱用户', 'zhangsan@example.com', 20);

INSERT IGNORE 会把部分错误降级为警告，可能掩盖数据问题。只应在明确允许跳过冲突数据时使用，并检查警告：

SHOW WARNINGS;

3.6 获取自增 ID

在当前数据库连接中执行：

SELECT LAST_INSERT_ID();

应用程序一般直接读取数据库驱动返回的 insertId，不需要再发起一次查询。

4. Read：查询数据

4.1 查询指定字段

SELECT id, username, email
FROM users;

业务代码中优先明确字段，而不是使用 SELECT *：

• 返回的数据更少；
• 字段含义更清晰；
• 表新增字段后，不会意外改变接口返回内容。

4.2 字段别名

SELECT
  id AS user_id,
  username AS user_name
FROM users;

4.3 常用条件

-- 等于、不等于和比较
SELECT id, username, age
FROM users
WHERE status = 1 AND age >= 18;

-- 多个可选值
SELECT id, order_no, status
FROM orders
WHERE status IN ('pending', 'paid');

-- 闭区间，包含 100 和 500
SELECT id, order_no, amount
FROM orders
WHERE amount BETWEEN 100 AND 500;

-- 排除条件
SELECT id, username
FROM users
WHERE status <> 0;

4.4 AND、OR 和括号

AND 的优先级高于 OR。混用时建议始终加括号表达业务含义：

SELECT id, username, age, status
FROM users
WHERE status = 1
  AND (age < 20 OR age >= 60);

4.5 模糊查询

-- 以“张”开头
SELECT id, username
FROM users
WHERE username LIKE '张%';

-- 任意位置包含“用户”
SELECT id, username
FROM users
WHERE username LIKE '%用户%';

• % 匹配任意长度的字符。
• _ 匹配一个字符。
• LIKE '张%' 有机会使用普通 B-Tree 索引。
• LIKE '%张%' 通常无法利用普通索引快速定位，数据量大时要关注性能。

4.6 判断 NULL

SELECT id, username
FROM users
WHERE age IS NULL;

SELECT id, username
FROM users
WHERE age IS NOT NULL;

不能写 age = NULL。NULL 表示未知值，必须使用 IS NULL 或 IS NOT NULL。

4.7 去重

SELECT DISTINCT status
FROM orders;

DISTINCT 针对所选字段的整个组合去重：

SELECT DISTINCT user_id, status
FROM orders;

4.8 排序

SELECT id, username, created_at
FROM users
ORDER BY created_at DESC, id DESC;

• ASC：升序，也是默认值。
• DESC：降序。
• 增加 id 作为第二排序字段，可以让相同时间的数据顺序保持稳定。

4.9 分页

-- 第 1 页，每页 20 条
SELECT id, username, created_at
FROM users
WHERE deleted_at IS NULL
ORDER BY id DESC
LIMIT 20 OFFSET 0;

-- 第 3 页，每页 20 条，offset = (3 - 1) * 20
SELECT id, username, created_at
FROM users
WHERE deleted_at IS NULL
ORDER BY id DESC
LIMIT 20 OFFSET 40;

MySQL 也支持 LIMIT 40, 20，其中第一个数字是偏移量，第二个数字是返回条数。LIMIT 20 OFFSET 40 更容易读懂。

深分页会扫描并丢弃大量前置记录。连续向后翻页时，可以使用游标分页：

SELECT id, username, created_at
FROM users
WHERE deleted_at IS NULL
  AND id < 10000
ORDER BY id DESC
LIMIT 20;

这里的 10000 是上一页最后一条记录的 id。

4.10 聚合统计

SELECT
  COUNT(*) AS order_count,
  SUM(amount) AS total_amount,
  AVG(amount) AS average_amount,
  MAX(amount) AS maximum_amount,
  MIN(amount) AS minimum_amount
FROM orders
WHERE status = 'paid';

• COUNT(*) 统计符合条件的行数。
• COUNT(age) 只统计 age 不为 NULL 的行数。
• 没有匹配行时，COUNT(*) 返回 0，而 SUM() 等函数可能返回 NULL。

需要保证合计值为数字时，可以使用：

SELECT COALESCE(SUM(amount), 0) AS total_amount
FROM orders
WHERE status = 'cancelled';

4.11 分组和分组过滤

SELECT
  user_id,
  COUNT(*) AS order_count,
  SUM(amount) AS total_amount
FROM orders
WHERE status = 'paid'
GROUP BY user_id
HAVING COUNT(*) >= 2
ORDER BY total_amount DESC;

• WHERE 在分组前过滤原始行。
• HAVING 在分组后过滤聚合结果。
• 能放在 WHERE 中的普通条件应优先放在 WHERE，减少参与分组的数据量。

4.12 内连接

只返回两张表中能够匹配的数据：

SELECT
  o.id,
  o.order_no,
  o.amount,
  u.username
FROM orders AS o
INNER JOIN users AS u ON u.id = o.user_id
WHERE o.status = 'paid';

4.13 左连接

返回所有用户，即使用户还没有订单：

SELECT
  u.id,
  u.username,
  COUNT(o.id) AS paid_order_count
FROM users AS u
LEFT JOIN orders AS o
  ON o.user_id = u.id
  AND o.status = 'paid'
WHERE u.deleted_at IS NULL
GROUP BY u.id, u.username;

这里把订单状态条件放在 ON 中非常重要。如果写成 WHERE o.status = 'paid'，没有订单的用户对应的 o.status 是 NULL，会被过滤掉，效果接近 INNER JOIN。

4.14 判断关联记录是否存在

SELECT
  u.id,
  u.username,
  EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.user_id = u.id
      AND o.status = 'pending'
  ) AS has_pending_order
FROM users AS u;

只需要判断“有没有”时，EXISTS 通常比先统计总数更符合语义。

4.15 CASE WHEN

SELECT
  order_no,
  amount,
  CASE
    WHEN amount >= 500 THEN '大额订单'
    WHEN amount >= 100 THEN '普通订单'
    ELSE '小额订单'
  END AS amount_level
FROM orders;

4.16 高频日期查询

-- 查询今天创建的订单
SELECT id, order_no, created_at
FROM orders
WHERE created_at >= CURRENT_DATE
  AND created_at < CURRENT_DATE + INTERVAL 1 DAY;

-- 查询指定月份，使用左闭右开区间
SELECT id, order_no, created_at
FROM orders
WHERE created_at >= '2026-08-01 00:00:00'
  AND created_at < '2026-09-01 00:00:00';

-- 按天统计
SELECT
  DATE(created_at) AS order_date,
  COUNT(*) AS order_count,
  SUM(amount) AS total_amount
FROM orders
WHERE created_at >= '2026-08-01 00:00:00'
  AND created_at < '2026-09-01 00:00:00'
GROUP BY DATE(created_at)
ORDER BY order_date;

过滤时优先写 created_at >= ... AND created_at < ...，而不是 DATE(created_at) = ...。不要对索引字段套函数，通常更有利于使用索引。

5. Update：更新数据

5.1 根据主键更新

UPDATE users
SET username = '张三（已更新）',
    age = 26
WHERE id = 1;

5.2 根据条件批量更新

UPDATE users
SET status = 0
WHERE deleted_at IS NOT NULL;

5.3 字段值自增或自减

UPDATE inventory
SET stock = stock - 1
WHERE product_id = 1001
  AND stock > 0;

把 stock > 0 放进同一条 SQL，可以避免先查询库存、再扣减时产生的并发竞争。应用程序还应检查受影响行数：如果是 0，说明商品不存在或库存不足。

5.4 批量更新为不同值

UPDATE users
SET status = CASE id
  WHEN 1 THEN 1
  WHEN 2 THEN 0
  WHEN 3 THEN 1
END
WHERE id IN (1, 2, 3);

WHERE id IN (...) 不可省略，否则其他行可能被更新成 NULL 或意外值。

5.5 关联更新

UPDATE users AS u
INNER JOIN orders AS o ON o.user_id = u.id
SET u.status = 1
WHERE o.status = 'paid';

一名用户可能匹配多个订单。执行关联更新前，应先用相同的 JOIN 和 WHERE 写成 SELECT DISTINCT u.id，确认目标范围。

5.6 安全更新流程

先查询：

SELECT id, username, status
FROM users
WHERE status = 0;

确认后再更新：

UPDATE users
SET status = 1
WHERE status = 0;

最后确认受影响数据：

SELECT ROW_COUNT() AS affected_rows;

UPDATE 和 DELETE 遗漏 WHERE 会影响整张表。在生产环境执行批量操作前，建议使用事务并先运行同条件的 SELECT。

6. Delete：删除数据

6.1 根据主键物理删除

DELETE FROM orders
WHERE id = 1;

6.2 根据条件批量删除

DELETE FROM orders
WHERE status = 'cancelled'
  AND created_at < CURRENT_DATE - INTERVAL 1 YEAR;

6.3 关联删除

DELETE o
FROM orders AS o
INNER JOIN users AS u ON u.id = o.user_id
WHERE u.deleted_at IS NOT NULL;

执行前先确认：

SELECT o.id, o.order_no
FROM orders AS o
INNER JOIN users AS u ON u.id = o.user_id
WHERE u.deleted_at IS NOT NULL;

6.4 软删除

UPDATE users
SET deleted_at = CURRENT_TIMESTAMP
WHERE id = 3
  AND deleted_at IS NULL;

查询正常用户时统一增加：

SELECT id, username, email
FROM users
WHERE deleted_at IS NULL;

恢复软删除数据：

UPDATE users
SET deleted_at = NULL
WHERE id = 3;

软删除保留了数据，便于审计和恢复，但所有正常业务查询都必须正确过滤。软删除也不会自动释放唯一键，例如已删除用户的邮箱仍然可能占用唯一索引。

6.5 DELETE、TRUNCATE、DROP 的区别

TRUNCATE 和 DROP 是 DDL 操作，风险很高，不应把它们当作普通业务删除命令。

7. 事务

事务适用于“多个操作必须全部成功或全部失败”的业务，例如创建订单同时扣减库存。

START TRANSACTION;

UPDATE inventory
SET stock = stock - 1
WHERE product_id = 1001
  AND stock > 0;

-- 应用程序必须检查上一步受影响行数是否为 1。
INSERT INTO orders (user_id, order_no, amount, status)
VALUES (1, 'ORD202608020004', 299.00, 'pending');

COMMIT;

发生错误或业务条件不满足时：

ROLLBACK;

事务的 ACID 特性：

• 原子性（Atomicity）：事务中的操作全部成功或全部失败。
• 一致性（Consistency）：事务前后数据约束保持有效。
• 隔离性（Isolation）：并发事务之间按照隔离级别控制相互影响。
• 持久性（Durability）：提交后的结果能够持久保存。

注意：

• COMMIT 或 ROLLBACK 之前不要把数据库连接归还连接池。
• 事务内应尽量只做必要的数据库操作，不要执行耗时的网络请求。
• MySQL 默认开启自动提交，每条普通 SQL 都会作为独立事务提交。
• CREATE TABLE、ALTER TABLE 等 DDL 可能触发隐式提交，不要随意混入业务事务。

8. 索引和查询性能

8.1 创建索引

CREATE INDEX idx_orders_status_created_at
ON orders (status, created_at);

创建唯一索引：

CREATE UNIQUE INDEX uk_users_username
ON users (username);

删除索引：

DROP INDEX idx_orders_status_created_at ON orders;

索引可以加快查询，但会占用空间，并增加 INSERT、UPDATE、DELETE 维护索引的成本，不应为每个字段都创建索引。

8.2 联合索引和最左前缀

索引：

KEY idx_orders_user_id_created_at (user_id, created_at)

通常适合：

WHERE user_id = 1

WHERE user_id = 1
  AND created_at >= '2026-08-01 00:00:00'

通常不能仅依靠该索引高效定位：

WHERE created_at >= '2026-08-01 00:00:00'

联合索引从最左边的字段开始匹配。索引顺序应根据实际的过滤、排序和分组方式设计。

8.3 使用 EXPLAIN

EXPLAIN
SELECT id, order_no, amount
FROM orders
WHERE user_id = 1
ORDER BY created_at DESC
LIMIT 20;

初学阶段重点观察：

• key：实际使用的索引；为 NULL 时表示没有选用索引。
• type：访问方式，常见的 const、ref、range 通常优于 ALL。
• rows：优化器预计需要检查的行数。
• Extra：额外信息，例如 Using filesort、Using temporary。

不能只看“是否使用索引”，还要结合扫描行数、返回行数和真实执行时间判断。

8.4 常见索引使用问题

-- 对索引字段使用函数
WHERE DATE(created_at) = '2026-08-02'

-- 前置通配符
WHERE username LIKE '%三'

-- 字段和参数类型不匹配，可能发生隐式类型转换
WHERE order_no = 202608020001

-- 联合索引没有从最左字段开始使用
WHERE created_at >= '2026-08-01'

这些写法不代表索引一定百分之百失效，最终选择由优化器决定，但它们经常让普通 B-Tree 索引难以发挥作用，应通过 EXPLAIN 验证。

9. 高频业务场景

9.1 判断记录是否存在

SELECT EXISTS (
  SELECT 1
  FROM users
  WHERE email = 'zhangsan@example.com'
) AS user_exists;

9.2 查询最新一条数据

SELECT id, order_no, amount, created_at
FROM orders
WHERE user_id = 1
ORDER BY created_at DESC, id DESC
LIMIT 1;

9.3 查询每位用户的最新订单

MySQL 8.0 可以使用窗口函数：

SELECT id, user_id, order_no, amount, created_at
FROM (
  SELECT
    o.*,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY created_at DESC, id DESC
    ) AS row_num
  FROM orders AS o
) AS ranked_orders
WHERE row_num = 1;

PARTITION BY user_id 表示每位用户单独编号，row_num = 1 就是每组最新的一条。

9.4 分页列表和总数

列表查询：

SELECT id, username, email, created_at
FROM users
WHERE status = 1
  AND deleted_at IS NULL
ORDER BY id DESC
LIMIT 20 OFFSET 0;

总数查询：

SELECT COUNT(*) AS total
FROM users
WHERE status = 1
  AND deleted_at IS NULL;

两条 SQL 的筛选条件必须保持一致。高并发下它们不是同一时刻的快照，因此总数与当前页数据可能存在短暂差异，普通列表通常可以接受。

9.5 按月统计

SELECT
  DATE_FORMAT(created_at, '%Y-%m') AS order_month,
  COUNT(*) AS order_count,
  SUM(amount) AS total_amount
FROM orders
WHERE status = 'paid'
  AND created_at >= '2026-01-01 00:00:00'
  AND created_at < '2027-01-01 00:00:00'
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY order_month;

9.6 查找重复值

SELECT email, COUNT(*) AS duplicate_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

真正需要禁止重复的数据，应使用唯一索引约束，而不是只依赖应用代码先查询再插入。

10. Node.js + TypeScript 参数化查询

本项目目前没有安装 MySQL 驱动。需要实际连接 MySQL 时，可以安装：

npm install mysql2

mysql2 是运行时依赖，提供 MySQL 协议、连接池和 Promise API。安装它不会改变当前项目的 CommonJS 模块输出方式；在现有 tsconfig.json 中仍然可以使用 import，并通过 ts-node server.ts 运行。

10.1 创建连接池

import mysql, { type PoolOptions } from "mysql2/promise";

const poolOptions: PoolOptions = {
  host: "127.0.0.1",
  port: 3306,
  user: "app_user",
  password: "请通过环境变量读取密码",
  database: "nloop_demo",
  connectionLimit: 10,
  charset: "utf8mb4",
};

export const pool = mysql.createPool(poolOptions);

实际项目不要把密码写进源码：

const databasePassword: string | undefined = process.env.DB_PASSWORD;

if (databasePassword === undefined) {
  throw new Error("缺少 DB_PASSWORD 环境变量");
}

10.2 类型安全地查询列表

import type { RowDataPacket } from "mysql2";
import { pool } from "./database";

interface UserRow extends RowDataPacket {
  id: number;
  username: string;
  email: string;
  age: number | null;
}

async function findActiveUsers(limit: number): Promise<UserRow[]> {
  const [rows] = await pool.execute<UserRow[]>(
    `SELECT id, username, email, age
     FROM users
     WHERE status = ? AND deleted_at IS NULL
     ORDER BY id DESC
     LIMIT ?`,
    [1, limit],
  );

  return rows;
}

? 是参数占位符。数据库驱动负责正确转义参数，避免把用户输入直接当成 SQL 结构执行。

10.3 查询单条数据

async function findUserById(id: number): Promise<UserRow | null> {
  const [rows] = await pool.execute<UserRow[]>(
    `SELECT id, username, email, age
     FROM users
     WHERE id = ? AND deleted_at IS NULL
     LIMIT 1`,
    [id],
  );

  return rows[0] ?? null;
}

当前项目开启了 noUncheckedIndexedAccess，因此 rows[0] 的类型包含 undefined。使用 ?? null 可以明确表达“没有查询到用户”。

10.4 新增并获取 ID

import type { ResultSetHeader } from "mysql2";

interface CreateUserInput {
  username: string;
  email: string;
  age: number | null;
}

async function createUser(input: CreateUserInput): Promise<number> {
  const [result] = await pool.execute<ResultSetHeader>(
    `INSERT INTO users (username, email, age)
     VALUES (?, ?, ?)`,
    [input.username, input.email, input.age],
  );

  return result.insertId;
}

10.5 更新并检查结果

async function disableUser(id: number): Promise<boolean> {
  const [result] = await pool.execute<ResultSetHeader>(
    `UPDATE users
     SET status = 0
     WHERE id = ? AND deleted_at IS NULL`,
    [id],
  );

  return result.affectedRows === 1;
}

affectedRows === 0 可能表示记录不存在、条件不匹配，或者没有产生数据库认定的受影响行。具体含义要结合 SQL 和驱动配置判断。

10.6 软删除

async function softDeleteUser(id: number): Promise<boolean> {
  const [result] = await pool.execute<ResultSetHeader>(
    `UPDATE users
     SET deleted_at = CURRENT_TIMESTAMP
     WHERE id = ? AND deleted_at IS NULL`,
    [id],
  );

  return result.affectedRows === 1;
}

10.7 事务示例

async function createOrder(
  userId: number,
  productId: number,
  orderNo: string,
  amount: string,
): Promise<number> {
  const connection = await pool.getConnection();

  try {
    await connection.beginTransaction();

    const [stockResult] = await connection.execute<ResultSetHeader>(
      `UPDATE inventory
       SET stock = stock - 1
       WHERE product_id = ? AND stock > 0`,
      [productId],
    );

    if (stockResult.affectedRows !== 1) {
      throw new Error("库存不足或商品不存在");
    }

    const [orderResult] = await connection.execute<ResultSetHeader>(
      `INSERT INTO orders (user_id, order_no, amount, status)
       VALUES (?, ?, ?, 'pending')`,
      [userId, orderNo, amount],
    );

    await connection.commit();
    return orderResult.insertId;
  } catch (error: unknown) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

关键点：

• 事务中的 SQL 必须使用同一个 connection，不能改用 pool.execute()。
• 成功时 commit()，失败时 rollback()。
• 无论成功失败，都在 finally 中释放连接。
• DECIMAL 金额可以用字符串在应用层传递，避免 JavaScript 浮点数精度问题。

10.8 不安全的字符串拼接

不要这样写：

const sql = `SELECT id, username FROM users WHERE email = '${email}'`;

如果 email 来自用户输入，它可能改变 SQL 的原始结构，造成 SQL 注入。应该使用参数化查询：

const [rows] = await pool.execute<UserRow[]>(
  "SELECT id, username, email, age FROM users WHERE email = ?",
  [email],
);

参数占位符只能代表“值”，不能代表表名、字段名或 ASC、DESC 等 SQL 结构。动态排序字段应使用代码白名单：

const allowedSortFields = {
  id: "id",
  createdAt: "created_at",
} as const;

type SortField = keyof typeof allowedSortFields;

function getOrderBy(sortField: SortField): string {
  return allowedSortFields[sortField];
}

11. 常见问题和易错点

11.1 NULL 不等于空字符串

• NULL：未知、缺失或没有值。
• ''：已知值，只是字符串长度为零。

WHERE age IS NULL
WHERE username = ''

11.2 COUNT(*) 和 COUNT(column)

SELECT
  COUNT(*) AS total_rows,
  COUNT(age) AS rows_with_age
FROM users;

COUNT(age) 不统计 age IS NULL 的行。

11.3 WHERE 和 HAVING

SELECT user_id, COUNT(*) AS order_count
FROM orders
WHERE status = 'paid'
GROUP BY user_id
HAVING COUNT(*) >= 2;

先通过 WHERE 筛选已支付订单，再通过 HAVING 筛选订单数不少于 2 的用户分组。

11.4 LEFT JOIN 条件的位置

-- 保留没有已支付订单的用户
LEFT JOIN orders AS o
  ON o.user_id = u.id
  AND o.status = 'paid'

-- 写在 WHERE 中会过滤掉 o 为 NULL 的行
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.status = 'paid'

11.5 时区

数据库、Node.js 进程和服务器操作系统可能使用不同的时区。项目开始时应统一约定：

• 数据库存 UTC，展示时转换为用户时区；或
• 全部明确使用同一个业务时区，例如 Asia/Shanghai。

查看当前 MySQL 时区：

SELECT @@global.time_zone, @@session.time_zone;

11.6 不要依赖“先查询再插入”防重复

下面的流程存在并发竞争：

查询邮箱不存在 → 另一个请求也查询到不存在 → 两个请求同时插入

正确做法是建立唯一索引，让数据库作为最后一道约束，并在应用层处理重复键错误。

12. 一页式 CRUD 模板

-- 新增一条
INSERT INTO users (username, email, age)
VALUES (?, ?, ?);

-- 批量新增
INSERT INTO users (username, email, age)
VALUES (?, ?, ?), (?, ?, ?);

-- 按主键查询
SELECT id, username, email, age
FROM users
WHERE id = ? AND deleted_at IS NULL
LIMIT 1;

-- 条件列表 + 排序 + 分页
SELECT id, username, email, created_at
FROM users
WHERE status = ? AND deleted_at IS NULL
ORDER BY id DESC
LIMIT ? OFFSET ?;

-- 统计总数
SELECT COUNT(*) AS total
FROM users
WHERE status = ? AND deleted_at IS NULL;

-- 更新
UPDATE users
SET username = ?, age = ?
WHERE id = ? AND deleted_at IS NULL;

-- 软删除
UPDATE users
SET deleted_at = CURRENT_TIMESTAMP
WHERE id = ? AND deleted_at IS NULL;

-- 物理删除
DELETE FROM users
WHERE id = ?;

-- 分组统计
SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
WHERE status = ?
GROUP BY user_id
HAVING COUNT(*) >= ?;

-- 关联查询
SELECT o.id, o.order_no, o.amount, u.username
FROM orders AS o
INNER JOIN users AS u ON u.id = o.user_id
WHERE o.status = ?;

-- 存在则更新，不存在则新增（MySQL 8.0.19+）
INSERT INTO users (username, email, age)
VALUES (?, ?, ?) AS new
ON DUPLICATE KEY UPDATE
  username = new.username,
  age = new.age;

13. 执行更新和删除前的检查清单

• 是否先用相同条件执行过 SELECT？
• WHERE 条件是否足够精确？
• AND 和 OR 是否加了正确的括号？
• 预计影响多少行，实际影响多少行？
• 是否需要事务，以便发生错误时回滚？
• 是否涉及外键、唯一索引或软删除数据？
• 应用代码是否使用参数化查询，而不是拼接 SQL？
• 批量操作是否应该分批执行，避免长事务和大范围锁定？

对于生产环境中的大批量修改，除了以上检查，还应准备备份、回滚方案，并在低峰期执行。