共计 7406 个字符，预计需要花费 19 分钟才能阅读完成。

JSON 与半结构化数据（JSON_TABLE / 生成列 / 索引）
全文检索与模糊匹配（FULLTEXT / 正则）
地理空间（GIS）与附近检索（POINT / SRID / 距离）
触发器、事件调度与存储过程（何时用 & 何时不用）
安全与权限（角色 / 视图脱敏 / 最小权限）
批量导入与幂等 Upsert（LOAD DATA / 分层落地 / 合并）
迁移与版本化（变更即代码）
小型实战：关键词 + JSON 过滤 + 附近门店
练习题（含参考答案）

环境：MySQL 8.0。延续 shop 库。

1) JSON 与半结构化数据

1.1 JSON 类型与常用函数

-- 商品附加属性（颜色 / 尺寸 / 标签等）ALTER TABLE products ADD COLUMN attrs JSON NULL;

-- 写入 / 更新 JSON 字段
UPDATE products
SET attrs = JSON_OBJECT('color','black','tags', JSON_ARRAY('ergonomic','bluetooth'))
WHERE product_id = 2;

-- 读取
SELECT product_id,
       JSON_EXTRACT(attrs,'$.color') AS color,
       JSON_CONTAINS(attrs, '"bluetooth"', '$.tags') AS has_bt
FROM products;

1.2 JSON_TABLE：把 JSON 拆成行列

-- 假设订单扩展字段 extra 中有 items 数组
ALTER TABLE orders ADD COLUMN extra JSON NULL;
UPDATE orders SET extra = JSON_OBJECT('items', JSON_ARRAY(JSON_OBJECT('sku','KB001','qty',1), JSON_OBJECT('sku','MS002','qty',2)
)) WHERE order_id = 1;

SELECT o.order_id, jt.sku, jt.qty
FROM orders o,
JSON_TABLE(o.extra, '$.items[*]'
  COLUMNS (sku VARCHAR(32) PATH '$.sku',
    qty INT PATH '$.qty'
  )
) AS jt;

1.3 给 JSON 建索引（生成列 + 索引）

-- 从 attrs 中提取颜色为生成列，并索引
ALTER TABLE products
  ADD COLUMN color VARCHAR(32)
    GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(attrs,'$.color'))) STORED,
  ADD INDEX idx_products_color(color);

-- 按颜色过滤现在可走索引
SELECT product_id, title FROM products WHERE color='black';

原则：查询条件要可索引化（SARGable）。对 JSON 先“投影”为生成列再建索引。

2) 全文检索与模糊匹配

2.1 FULLTEXT（InnoDB 支持）

ALTER TABLE products ADD FULLTEXT ft_title(title);

-- 自然语言模式
SELECT product_id, title
FROM products
WHERE MATCH(title) AGAINST('人体工学 键盘');

-- 布尔模式（+ 必须，- 排除，* 前缀）SELECT product_id, title
FROM products
WHERE MATCH(title) AGAINST('+ 键盘 - 机械 人体 *' IN BOOLEAN MODE)
ORDER BY MATCH(title) AGAINST('+ 键盘 - 机械 人体 *' IN BOOLEAN MODE) DESC
LIMIT 10;

2.2 LIKE/REGEXP 的取舍

LIKE 'abc%' 可走前缀索引；LIKE '%abc%' 通常不能走（考虑全文索引 / 外部搜索）。
REGEXP 强大但常扫表；谨慎用于在线高流量查询。

3) 地理空间（GIS）与附近检索

3.1 建表与插入（经纬度用 SRID=4326）

CREATE TABLE stores (
  store_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name     VARCHAR(200) NOT NULL,
  loc      POINT NOT NULL SRID 4326,
  SPATIAL INDEX(spx_loc)
) ENGINE=InnoDB;

-- 插入（经度在前，纬度在后）INSERT INTO stores(name, loc)
VALUES ('总部', ST_SRID(POINT(121.4737, 31.2304), 4326));  -- 上海

3.2 附近 N 公里（两种写法）

-- A) 有 ST_Distance_Sphere 时（8.0+ 部分版本提供）SELECT store_id, name
FROM stores
WHERE ST_Distance_Sphere(loc, ST_SRID(POINT(121.5,31.2),4326)) <= 2000; -- 2km

-- B) 通用：Haversine 近似（适度精度）SELECT store_id, name,
  6371000 * ACOS(COS(RADIANS(:lat))*COS(ST_Y(loc))*COS(RADIANS(:lon)-ST_X(loc)) +
    SIN(RADIANS(:lat))*SIN(ST_Y(loc))
  ) AS meters
FROM stores
HAVING meters <= 2000
ORDER BY meters
LIMIT 20;

生产建议：先做经纬度的粗 Bounding Box 过滤（经纬度范围）再计算球面距离。

4) 触发器、事件调度与存储过程

4.1 触发器（审计 / 缓存小表，不做重逻辑）

DELIMITER $$
CREATE TRIGGER trg_orders_ai
AFTER INSERT ON orders FOR EACH ROW
BEGIN
  INSERT INTO order_audit(order_id, user_id, amount, created_at)
  VALUES(NEW.order_id, NEW.user_id, NEW.amount, NEW.created_at);
END$$
DELIMITER ;

原则：短小且幂等。复杂业务逻辑放应用层 / 作业编排。

4.2 事件调度器（每日汇总）

SET GLOBAL event_scheduler = ON;
DELIMITER $$
CREATE EVENT IF NOT EXISTS ev_daily_metrics
ON SCHEDULE EVERY 1 DAY STARTS CURRENT_DATE + INTERVAL 1 DAY
DO BEGIN
  INSERT INTO metrics_daily(d, gmv)
  SELECT CURDATE()-INTERVAL 1 DAY,
         COALESCE(SUM(amount),0)
  FROM orders WHERE DATE(created_at)=CURDATE()-INTERVAL 1 DAY;
END$$
DELIMITER ;

4.3 存储过程（演示 Upsert 汇总）

DELIMITER $$
CREATE PROCEDURE sp_upsert_user_rev(IN p_user BIGINT)
BEGIN
  INSERT INTO user_rev(user_id, revenue)
  SELECT p_user, COALESCE(SUM(amount),0)
  FROM orders WHERE user_id=p_user AND status='paid'
  ON DUPLICATE KEY UPDATE revenue=VALUES(revenue);
END$$
DELIMITER ;

CALL sp_upsert_user_rev(1);

5) 安全与权限

5.1 角色与最小权限

CREATE ROLE analyst, app_rw;
GRANT SELECT ON shop.* TO analyst;
GRANT SELECT,INSERT,UPDATE,DELETE ON shop.* TO app_rw;

CREATE USER 'bi'@'%' IDENTIFIED BY 'Bi@123';
GRANT analyst TO 'bi'@'%';
SET DEFAULT ROLE analyst TO 'bi'@'%';

5.2 视图脱敏（SQL SECURITY DEFINER）

CREATE TABLE users_private AS SELECT user_id, email FROM users; -- 演示

CREATE OR REPLACE VIEW v_users_masked
SQL SECURITY DEFINER
AS
SELECT user_id,
       CONCAT(LEFT(email,3),'***@',SUBSTRING_INDEX(email,'@',-1)) AS email_mask
FROM users_private;

GRANT SELECT ON shop.v_users_masked TO 'bi'@'%';

使用 DEFINER + 视图 提供行 / 列级访问切片。更细粒度需求请评估专用网关 / 列级加密。

5.3 防注入：参数化与预编译

客户端示例（Python）

cur.execute("SELECT * FROM users WHERE email=%s", (email,))

服务端动态 SQL（仅内部工具）

SET @sql = 'SELECT * FROM products WHERE color=?';
-- 建议在应用层做预编译与白名单校验

6) 批量导入与幂等 Upsert

6.1 快速导入 CSV

-- 允许 LOCAL 必须在客户端与服务端配置允许（注意安全）LOAD DATA LOCAL INFILE '/path/new_products.csv'
INTO TABLE products
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'LINES TERMINATED BY'\n'
IGNORE 1 LINES
(title, price, stock)
SET created_at = NOW();

6.2 分层落地（Staging → 合并）

CREATE TABLE IF NOT EXISTS staging_products LIKE products;
-- 1) 先导入到 staging
-- 2) 清洗 / 去重
DELETE s1 FROM staging_products s1
JOIN staging_products s2
ON s1.product_id=s2.product_id AND s1._rowid > s2._rowid; -- 伪代码：示意
-- 3) 合并到正式表
INSERT INTO products(product_id,title,price,stock)
SELECT product_id,title,price,stock FROM staging_products
ON DUPLICATE KEY UPDATE
  title=VALUES(title), price=VALUES(price), stock=VALUES(stock);
TRUNCATE TABLE staging_products;

6.3 Upsert 模式

INSERT INTO inventory(sku,qty)
VALUES('KB001',10)
ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty);

导入大批量时可临时 SET FOREIGN_KEY_CHECKS=0;、SET UNIQUE_CHECKS=0; 并单事务提交；结束后立即恢复并做校验。

7) 迁移与版本化

7.1 轻量规则

每次变更一份独立脚本：V2025_10_11_01_add_user_email_lower.sql
脚本幂等（IF NOT EXISTS / CREATE OR REPLACE）。
建一张表记录版本：

CREATE TABLE IF NOT EXISTS schema_migrations (version VARCHAR(64) PRIMARY KEY,
  applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

执行器：Flyway/Liquibase（推荐），或自写小工具读取目录按顺序执行并记录到 schema_migrations。

8) 小型实战：关键词 + JSON 过滤 + 附近门店

需求：

输入：q（关键词），color（可选），lon/lat（可选），radius_km（可选）。
输出：匹配的商品，按距离升序（若提供坐标）。

SQL 组合拳

-- 预置：FULLTEXT(ft_title)、生成列 color、门店 stores(loc, SRID 4326)

-- 1) 关键词匹配（优先用全文）WITH kw AS (
  SELECT p.product_id, p.title, p.price, p.color
  FROM products p
  WHERE (:q IS NULL)
     OR MATCH(p.title) AGAINST(:q IN BOOLEAN MODE)
),
-- 2) JSON/ 生成列过滤
flt AS (
  SELECT * FROM kw
  WHERE (:color IS NULL OR color = :color)
),
-- 3) 距离（可选）：返回最近门店距离
nearest AS (
  SELECT f.*, MIN(
    6371 * ACOS(COS(RADIANS(:lat))*COS(ST_Y(s.loc))*COS(RADIANS(:lon)-ST_X(s.loc)) +
      SIN(RADIANS(:lat))*SIN(ST_Y(s.loc))
    )
  ) AS km
  FROM flt f JOIN stores s ON 1=1
  GROUP BY f.product_id, f.title, f.price, f.color
)
SELECT * FROM nearest
WHERE (:radius_km IS NULL OR km <= :radius_km)
ORDER BY COALESCE(km, 1e9) ASC, price ASC
LIMIT 50;

实战要点：先窄后广（关键词 / 过滤先于距离），必要时将“最近门店距离”预计算入物化表。

9) 练习题（含参考答案）

练习 1（JSON）：在 products.attrs 里加入 warranty_months，新建生成列 warranty 并建索引；写出“保修 ≥ 12 个月”的查询。

ALTER TABLE products
  ADD COLUMN warranty INT GENERATED ALWAYS AS (CAST(JSON_EXTRACT(attrs,'$.warranty_months') AS UNSIGNED)
  ) STORED,
  ADD INDEX idx_products_warranty(warranty);
SELECT product_id, title FROM products WHERE warranty >= 12;

练习 2（FULLTEXT）：建立 ft_title 并用布尔模式写一条“必须包含‘键盘’，排除‘机械’，可含‘人体工学’”的查询。

ALTER TABLE products ADD FULLTEXT ft_title(title);
SELECT product_id, title
FROM products
WHERE MATCH(title) AGAINST('+ 键盘 - 机械 人体工学' IN BOOLEAN MODE);

练习 3（GIS）：创建 stores 表并查找给定坐标 5km 内的门店，按距离排序返回前 10。

-- 建表见 §3.1
SELECT store_id, name,
  6371 * ACOS(COS(RADIANS(:lat))*COS(ST_Y(loc))*COS(RADIANS(:lon)-ST_X(loc)) +
    SIN(RADIANS(:lat))*SIN(ST_Y(loc))
  ) AS km
FROM stores
HAVING km <= 5
ORDER BY km
LIMIT 10;

练习 4（事件 & 存储过程）：创建每日 01:00 运行的事件，调用 sp_upsert_user_rev 为活跃用户批量更新收入汇总。

DELIMITER $$
CREATE EVENT ev_upsert_all
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY) + INTERVAL 1 HOUR
DO BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE uid BIGINT;
  DECLARE cur CURSOR FOR SELECT user_id FROM users WHERE is_active=1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO uid; IF done=1 THEN LEAVE read_loop; END IF;
    CALL sp_upsert_user_rev(uid);
  END LOOP;
  CLOSE cur;
END$$
DELIMITER ;

练习 5（安全）：创建只读角色 analyst 并授予给用户 bi@%，默认启用该角色；创建视图 v_users_masked 返回已脱敏邮箱并授权给 bi@%。

CREATE ROLE analyst; GRANT SELECT ON shop.* TO analyst;
CREATE USER 'bi'@'%' IDENTIFIED BY 'Bi@123';
GRANT analyst TO 'bi'@'%';
SET DEFAULT ROLE analyst TO 'bi'@'%';

CREATE OR REPLACE VIEW v_users_masked AS
SELECT user_id, CONCAT(LEFT(email,3),'***@',SUBSTRING_INDEX(email,'@',-1)) AS email_mask
FROM users;
GRANT SELECT ON shop.v_users_masked TO 'bi'@'%';

小结

JSON → 生成列 → 索引：让半结构化也跑得快。
搜索先上 FULLTEXT，模糊再评估外部引擎。
GIS 用 SRID=4326 + 近似过滤 + 精确距离。
触发器 / 事件 / 存储过程只做“短小刚需”，复杂逻辑在应用层。
权限以角色为中心，配合视图做脱敏与行列裁剪。
导入走 staging 与 Upsert，保证幂等与可回滚。
架构变更版本化，让数据库也“像代码一样管理”。

打赏赞

MySQL 入门教程 · 第四课

目录