sql: Implementing Full-Text Search in SQL using mysql 9.0
CREATE DATABASE IF NOT EXISTS JewelryAnalytics DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE JewelryAnalytics;
SET SESSION cte_max_recursion_depth=1000000;
SET SESSION group_concat_max_len=1000000;
SET SESSION optimizer_switch='index_condition_pushdown=on,mrr=on,derived_merge=on,skip_scan=on';
SET SESSION transaction_isolation='REPEATABLE-READ';
SET SESSION sql_safe_updates=0;
SET SESSION innodb_lock_wait_timeout=50;
SET SESSION time_zone='+00:00';
SET SESSION max_execution_time=0;
-- ============================================================================
-- §0.0 运行方式必读（务必先读）
-- ============================================================================
-- 本脚本：MySQL 9.0 全文检索（FULLTEXT）专题
-- 主题：国际化珠宝集团 OA/ERP/CRM/HRM/MES/SCM/WMS/KMS 八域全文搜索
-- + CONTAINS/FREETEXT 等价实现 + 词干(stemming) + 停用词(stopwords)
-- + 性能感知查询设计 + 万亿高并发 + 国际时区 + 多币种结算
-- 运行前提：MySQL 9.0.x，库 JewelryAnalytics 自动建/用；本脚本完全幂等。
-- 方式A（命令行，推荐，避开 Workbench 30s 读超时）：
-- mysql -uroot -p --default-character-set=utf8mb4 < MySQL9_FullTextSearch_JewelryDemo.sql
-- 方式B（Workbench）：整段执行；若报 1175(safe update) 见 §0.2。
-- 幂等说明：每次先 DROP 全部 fts_* 实验表/过程再重建并造数，重复运行结果一致。
-- 度量口径：用 performance_schema.session_status 的 INNODB_ROWS_READ 增量，
-- 量化「引擎真正读了多少行」，比 EXPLAIN rows 更接近真实代价。
-- ============================================================================
-- §0.1 MySQL 全文检索能力速览（与其它数据库的术语映射）
-- ----------------------------------------------------------------------------
-- SQL Server 概念 MySQL 9.0 等价实现
-- ---------------------- -----------------------------------------------------
-- CONTAINS(col,'x') MATCH(col) AGAINST('+x' IN BOOLEAN MODE) —— 布尔算子
-- CONTAINS(col,'"短语"') MATCH(col) AGAINST('"短语"' IN BOOLEAN MODE) —— 双引号短语
-- CONTAINS(col,'x*') MATCH(col) AGAINST('+x*' IN BOOLEAN MODE) —— * 前缀算子
-- FREETEXT(col,'x') MATCH(col) AGAINST('x' IN NATURAL LANGUAGE MODE) —— 自然语言
-- FREETEXT 扩展召回 MATCH(col) AGAINST('x' WITH QUERY EXPANSION) —— 查询扩展
-- 相关性分值 MATCH(...) AGAINST(...) 返回浮点 relevance，可 ORDER BY
-- 停用词 mysql.innodb_ft_default_stopword / 自定义停用词表
-- 词干(stemming) MySQL 无健壮词干器；用 ngram 子串重叠 或 * 前缀 近似，真词干靠应用层/ES
-- 解析器 默认(按空白/标点分词) + ngram(中日韩必需) + mecab(日语)
-- ============================================================================
-- §0.2 反模式速查表（本机已实测）
-- ----------------------------------------------------------------------------
-- 反模式 后果 正确做法
-- 中文用默认解析器建 FULLTEXT 中文整句无法分词，命中极少 WITH PARSER ngram
-- MATCH(a) 但 FULLTEXT 索引是 (a,b,c) 列集合不一致 → ERROR 1191 MATCH 列须与索引列完全一致
-- 分区表上建 FULLTEXT 报错（MySQL 不支持）→ 建表失败 分片建 FULLTEXT，或 ES 承担全文
-- 搜纯停用词('the') 索引构建即剔除 → NLM/BOOLEAN 均 0 行 自定义停用词表增删 + 重建索引
-- 误以为 MySQL 有词干 搜索 'jewelry' 不匹配 'jewel' 用 * 前缀 / ngram 子串 / 应用层词干
-- LIKE '%钻石%' 前导通配 全表扫描，深数据灾难 FULLTEXT 或改前缀 '钻石%'
-- CONVERT_TZ(ts,'UTC','Asia/Shanghai') 本机 mysql.time_zone_name=0 → NULL 用 TIMESTAMPADD(MINUTE,偏移,ts)
-- 跨币种 SUM(amount_orig) 不同币种相加无意义 只 SUM(amount_usd)
-- ============================================================================
-- §01 度量框架（fts_demo_*）：量化引擎真实读行，证明取舍
DROP TABLE IF EXISTS fts_demo_metric;
CREATE TABLE fts_demo_metric(
tag VARCHAR(40) PRIMARY KEY,
innodb_rows BIGINT NOT NULL DEFAULT 0,
rnd_next BIGINT NOT NULL DEFAULT 0,
rnd BIGINT NOT NULL DEFAULT 0,
ms INT NOT NULL DEFAULT 0,
note VARCHAR(255)
);
DROP TABLE IF EXISTS fts_demo_result;
CREATE TABLE fts_demo_result(
check_id VARCHAR(30) PRIMARY KEY,
passed TINYINT NOT NULL,
detail VARCHAR(400)
);
DROP TABLE IF EXISTS fts_demo_probe;
CREATE TABLE fts_demo_probe(
id INT AUTO_INCREMENT PRIMARY KEY,
label VARCHAR(60),
errno INT,
sql_state VARCHAR(10),
msg VARCHAR(300),
caught_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
DROP PROCEDURE IF EXISTS fts_snap;
DELIMITER //
CREATE PROCEDURE fts_snap()
BEGIN
SELECT VARIABLE_VALUE INTO @fts_snap_ir FROM performance_schema.session_status WHERE VARIABLE_NAME='INNODB_ROWS_READ';
SELECT VARIABLE_VALUE INTO @fts_snap_rn FROM performance_schema.session_status WHERE VARIABLE_NAME='HANDLER_READ_RND_NEXT';
SELECT VARIABLE_VALUE INTO @fts_snap_rd FROM performance_schema.session_status WHERE VARIABLE_NAME='HANDLER_READ_RND';
SET @fts_snap_ts = UNIX_TIMESTAMP(NOW(6));
END//
DELIMITER ;
DROP PROCEDURE IF EXISTS fts_measure;
DELIMITER //
CREATE PROCEDURE fts_measure(IN p_tag VARCHAR(40))
BEGIN
DECLARE v_ir BIGINT; DECLARE v_rn BIGINT; DECLARE v_rd BIGINT; DECLARE v_ms INT;
SELECT VARIABLE_VALUE INTO v_ir FROM performance_schema.session_status WHERE VARIABLE_NAME='INNODB_ROWS_READ';
SELECT VARIABLE_VALUE INTO v_rn FROM performance_schema.session_status WHERE VARIABLE_NAME='HANDLER_READ_RND_NEXT';
SELECT VARIABLE_VALUE INTO v_rd FROM performance_schema.session_status WHERE VARIABLE_NAME='HANDLER_READ_RND';
SET v_ms = ROUND((UNIX_TIMESTAMP(NOW(6)) - @fts_snap_ts)*1000);
INSERT INTO fts_demo_metric(tag,innodb_rows,rnd_next,rnd,ms) VALUES(p_tag, v_ir-@fts_snap_ir, v_rn-@fts_snap_rn, v_rd-@fts_snap_rd, v_ms)
ON DUPLICATE KEY UPDATE innodb_rows=v_ir-@fts_snap_ir, rnd_next=v_rn-@fts_snap_rn, rnd=v_rd-@fts_snap_rd, ms=v_ms;
END//
DELIMITER ;
DROP PROCEDURE IF EXISTS fts_v;
DELIMITER //
CREATE PROCEDURE fts_v(IN p_check VARCHAR(30), IN p_pass TINYINT, IN p_detail VARCHAR(400))
BEGIN
INSERT INTO fts_demo_result(check_id,passed,detail) VALUES(p_check,p_pass,p_detail)
ON DUPLICATE KEY UPDATE passed=p_pass, detail=p_detail;
END//
DELIMITER ;
DROP PROCEDURE IF EXISTS fts_err_probe;
DELIMITER //
CREATE PROCEDURE fts_err_probe(IN p_label VARCHAR(60), IN p_sql TEXT)
BEGIN
DECLARE v_no INT DEFAULT 0; DECLARE v_st VARCHAR(10) DEFAULT '00000'; DECLARE v_msg VARCHAR(300) DEFAULT '';
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 v_no = MYSQL_ERRNO, v_st = RETURNED_SQLSTATE, v_msg = MESSAGE_TEXT;
END;
SET @fts_probe_sql = p_sql;
PREPARE fts_pstmt FROM @fts_probe_sql;
IF v_no = 0 THEN
EXECUTE fts_pstmt;
DEALLOCATE PREPARE fts_pstmt;
END IF;
INSERT INTO fts_demo_probe(label,errno,sql_state,msg) VALUES(p_label, v_no, v_st, v_msg);
END//
DELIMITER ;
-- §02 实验台：维度表 + 八域统一文档仓 + 造数（国际化珠宝集团）
-- 命名统一 fts_* 前缀，避免与同库其他主题 demo_*/srch_*/gl_*/dim_* 表冲突。
DROP TABLE IF EXISTS fts_dim_currency;
CREATE TABLE fts_dim_currency(code CHAR(3) PRIMARY KEY, name VARCHAR(40), symbol VARCHAR(8), usd_rate DECIMAL(12,6));
INSERT INTO fts_dim_currency VALUES
('CNY','人民币','¥',1/7.20),('USD','美元','$',1.0),('EUR','欧元','€',1.08),
('JPY','日元','¥',0.0067),('HKD','港元','HK$',1/7.80),('GBP','英镑','£',1.27);
DROP TABLE IF EXISTS fts_dim_country;
CREATE TABLE fts_dim_country(code CHAR(2) PRIMARY KEY, name VARCHAR(40), tz_offset_min INT, currency CHAR(3));
INSERT INTO fts_dim_country VALUES
('CN','中国',480,'CNY'),('US','美国',-300,'USD'),('FR','法国',60,'EUR'),
('JP','日本',540,'JPY'),('HK','中国香港',480,'HKD'),('GB','英国',0,'GBP'),
('DE','德国',60,'EUR'),('SG','新加坡',480,'USD');
-- 八域统一文档仓：一张表覆盖 OA/ERP/CRM/HRM/MES/SCM/WMS/KMS，FULLTEXT 跨域检索 + system_code 过滤。
DROP TABLE IF EXISTS fts_document;
CREATE TABLE fts_document(
doc_id BIGINT PRIMARY KEY,
system_code CHAR(4) NOT NULL, -- OA/ERP/CRM/HRM/MES/SCM/WMS/KMS
doc_type VARCHAR(30),
title VARCHAR(240) NOT NULL,
body TEXT NOT NULL,
tags VARCHAR(200), -- 空格分隔关键词，供布尔模式演示
author VARCHAR(80),
country_code CHAR(2),
currency_code CHAR(3),
amount_orig DECIMAL(18,2),
amount_usd DECIMAL(18,2), -- 已按汇率归一，结算只用它
create_offset_min SMALLINT, -- 时区偏移（分钟），冗余存储以便生成列建索引
created_ts DATETIME NOT NULL, -- 统一存 UTC
create_local_date DATE -- 生成列：本地营业日，用于带索引的时区过滤
GENERATED ALWAYS AS (DATE(TIMESTAMPADD(MINUTE, create_offset_min, created_ts))) STORED,
KEY ix_system (system_code),
KEY ix_country (country_code),
KEY ix_local_date (create_local_date),
KEY ix_usd (amount_usd)
) ENGINE=InnoDB;
-- 造 100,000 行（250×400），八域、多国、多币种、中英混排
INSERT INTO fts_document(doc_id,system_code,doc_type,title,body,tags,author,country_code,currency_code,amount_orig,amount_usd,create_offset_min,created_ts)
WITH RECURSIVE a(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM a WHERE n<250),
b(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM b WHERE n<400),
base AS (
SELECT a.n*400 + b.n - 400 AS id
FROM a JOIN b
),
enriched AS (
SELECT
id,
ELT(1+id%8,'OA','ERP','CRM','HRM','MES','SCM','WMS','KMS') AS sys,
ELT(1+id%10,'黄金','铂金','钻石','红宝石','蓝宝石','翡翠','珍珠','银','18K金','白金') AS mat,
ELT(1+id%8,'戒指','项链','手链','耳环','吊坠','胸针','手镯','脚链') AS cat,
ELT(1+id%6,'经典','现代','复古','奢华','简约','定制') AS style,
ELT(1+id%8,'CN','US','FR','JP','HK','GB','DE','SG') AS ctry,
ELT(1+id%4,'区域经理','财务','总部','质检') AS auditor,
ELT(1+id%5,'意大利','日本','法国','中国','瑞士') AS craft,
ELT(1+id%6,'采购','报销','合同','工单','运单','知识') AS dtype,
ROUND(100 + (id%9000)/10.0, 2) AS amt
FROM base
)
SELECT
id,
sys,
dtype,
CONCAT(ELT(FIELD(sys,'OA','ERP','CRM','HRM','MES','SCM','WMS','KMS'),
'OA办公','ERP企业','CRM客户','HRM人力','MES制造','SCM供应链','WMS仓储','KMS知识'),
'｜', mat, cat, '｜', style) AS title,
CONCAT(
ELT(FIELD(sys,'OA','ERP','CRM','HRM','MES','SCM','WMS','KMS'),
'OA办公自动化文档：','ERP企业资源计划单：','CRM客户关系记录：','HRM人力资源档案：',
'MES制造执行工单：','SCM供应链协同单：','WMS仓储管理记录：','KMS知识库文章：'),
mat, cat, '，', style, '风格。采用', craft, '工艺，涉及',
ELT(FIELD(ctry,'CN','US','FR','JP','HK','GB','DE','SG'),
'中国','美国','法国','日本','中国香港','英国','德国','新加坡'),
'市场，需', auditor, '审核。',
'Diamond ', mat, ' ', cat, ' Luxury jewelry collection. ',
'The quick brown fox and the cat is on the table of goods. ',
'We sell jewels and jewel sets for the wedding. '
) AS body,
CONCAT(mat, ' ', cat, ' ', style) AS tags,
CONCAT(ELT(1+id%8,'张','李','王','刘','陈','杨','黄','周'),
ELT(1+id%6,'伟','芳','娜','强','磊','敏')) AS author,
ctry,
ELT(FIELD(ctry,'CN','US','FR','JP','HK','GB','DE','SG'),
'CNY','USD','EUR','JPY','HKD','GBP','EUR','USD') AS cur,
amt,
ROUND(amt * ELT(FIELD(ctry,'CN','US','FR','JP','HK','GB','DE','SG'),
1/7.20, 1.0, 1.08, 0.0067, 1/7.80, 1.27, 1.08, 1.0), 2) AS amt_usd,
ELT(FIELD(ctry,'CN','US','FR','JP','HK','GB','DE','SG'),
480,-300,60,540,480,0,60,480) AS off,
DATE_ADD('2025-09-01 00:00:00', INTERVAL (id % 365) DAY) + INTERVAL (id % 86400) SECOND AS cts
FROM enriched;
-- 加载后再建全文索引（比边插边建更快；中文必须 ngram）
-- 多列索引 (title,body,tags)：MATCH 必须精确引用这三列
ALTER TABLE fts_document ADD FULLTEXT ft_doc (title, body, tags) WITH PARSER ngram;
-- 单列索引 (body)：便于演示「只搜 body」以及触发 1191 列集合不一致的对照
ALTER TABLE fts_document ADD FULLTEXT ft_doc_body (body) WITH PARSER ngram;
-- 八域分表示例（与统一仓互为补充；各域有专属字段，但全文检索思路一致）
DROP TABLE IF EXISTS fts_kms_article;
CREATE TABLE fts_kms_article(article_id BIGINT PRIMARY KEY, sys_code CHAR(4), title VARCHAR(240), content TEXT, author VARCHAR(80), created_ts DATETIME);
INSERT INTO fts_kms_article(article_id,sys_code,title,content,author,created_ts)
SELECT doc_id,'KMS',title,body,author,created_ts FROM fts_document WHERE system_code='KMS';
ALTER TABLE fts_kms_article ADD FULLTEXT ft_kms (title, content) WITH PARSER ngram;
DROP TABLE IF EXISTS fts_wms_log;
CREATE TABLE fts_wms_log(log_id BIGINT PRIMARY KEY, sys_code CHAR(4), loc VARCHAR(60), note TEXT, created_ts DATETIME);
INSERT INTO fts_wms_log(log_id,sys_code,loc,note,created_ts)
SELECT doc_id,'WMS',CONCAT('仓',doc_id%50,'-位',doc_id%200),body,created_ts FROM fts_document WHERE system_code='WMS';
ALTER TABLE fts_wms_log ADD FULLTEXT ft_wms (note) WITH PARSER ngram;
-- §03 建全文索引的三种解析器（演示 + 信息.schema 自检）
-- 默认解析器（按空白/标点分词，适合拉丁语；中文几乎无效）
DROP TABLE IF EXISTS fts_sw_demo;
CREATE TABLE fts_sw_demo(id INT PRIMARY KEY, txt TEXT, FULLTEXT INDEX ft_txt (txt));
INSERT INTO fts_sw_demo VALUES
(1,'The diamond ring is a luxury jewelry item for wedding'),
(2,'Gold necklace and bracelet are popular jewelry products'),
(3,'The sapphire earring and the ruby pendant are on sale'),
(4,'We sell jewel sets and jewels for the anniversary'),
(5,'Silver bracelet is the classic choice of the season');
-- ngram 解析器（中日韩必需；也把拉丁按 n 元切，可做子串重叠）
DROP TABLE IF EXISTS fts_ngram_demo;
CREATE TABLE fts_ngram_demo(id INT PRIMARY KEY, txt TEXT, FULLTEXT(txt) WITH PARSER ngram);
INSERT INTO fts_ngram_demo VALUES
(1,'钻石戒指经典款采用意大利工艺'),
(2,'铂金项链奢华风格日本工艺'),
(3,'红宝石耳环复古风格法国工艺'),
(4,'蓝宝石吊坠简约风格中国工艺'),
(5,'翡翠手镯定制风格瑞士工艺');
-- 信息.schema：确认主表 FULLTEXT 索引已建（ngram）
SELECT '§03 已建 FULLTEXT 索引清单' AS `-- 第3章 --`;
SELECT table_name, index_name, index_type, comment
FROM information_schema.statistics
WHERE table_schema='JewelryAnalytics' AND table_name='fts_document' AND index_type='FULLTEXT';
-- §04 CONTAINS 等价 → BOOLEAN MODE（含 + - "短语" * > < 算子）
SELECT '§04 CONTAINS 等价：BOOLEAN MODE 布尔算子' AS `-- 第4章 --`;
-- 4.1 +钻石 -银 ：必须含「钻石」且不含「银」（CONTAINS(col,'钻石 AND NOT 银')）
CALL fts_snap();
SELECT COUNT(*) INTO @c_contains FROM fts_document
WHERE MATCH(title,body,tags) AGAINST('+钻石 -银' IN BOOLEAN MODE);
CALL fts_measure('BOOL_contains');
-- 4.2 "铂金戒指" 双引号短语（CONTAINS(col,'"铂金戒指"')）
SELECT COUNT(*) INTO @c_phrase FROM fts_document
WHERE MATCH(title,body,tags) AGAINST('"铂金戒指"' IN BOOLEAN MODE);
-- 4.3 珍珠* 前缀算子（CONTAINS(col,'珍珠*')，匹配 珍珠/珍珠链...）
SELECT COUNT(*) INTO @c_prefix FROM fts_document
WHERE MATCH(title,body,tags) AGAINST('+珍珠*' IN BOOLEAN MODE);
-- 4.4 加权 >红宝石 <蓝宝石 ：红宝石权重高于蓝宝石
SELECT doc_id, title,
MATCH(title,body,tags) AGAINST('>红宝石 <蓝宝石' IN BOOLEAN MODE) AS rel
FROM fts_document
WHERE MATCH(title,body,tags) AGAINST('>红宝石 <蓝宝石' IN BOOLEAN MODE)
ORDER BY rel DESC LIMIT 5;
-- §05 FREETEXT 等价 → NATURAL LANGUAGE MODE + 查询扩展
SELECT '§05 FREETEXT 等价：NATURAL LANGUAGE MODE + QUERY EXPANSION' AS `-- 第5章 --`;
-- 5.1 自然语言模式（返回相关性分值，可排序）
SELECT doc_id, title,
MATCH(title,body,tags) AGAINST('钻石 戒指 经典' IN NATURAL LANGUAGE MODE) AS score
FROM fts_document
WHERE MATCH(title,body,tags) AGAINST('钻石 戒指 经典' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC LIMIT 5;
-- 5.2 查询扩展（WITH QUERY EXPANSION）：先按词检索，再用首轮命中词扩展召回
SELECT doc_id, title,
MATCH(title,body,tags) AGAINST('钻石 订婚' WITH QUERY EXPANSION) AS score
FROM fts_document
WHERE MATCH(title,body,tags) AGAINST('钻石 订婚' WITH QUERY EXPANSION)
ORDER BY score DESC LIMIT 5;
-- 5.3 断言：扩展召回 >= 自然语言召回（扩展会带回更多相关文档）
SELECT COUNT(*) INTO @n_nlm FROM fts_document
WHERE MATCH(title,body,tags) AGAINST('钻石 订婚' IN NATURAL LANGUAGE MODE);
SELECT COUNT(*) INTO @n_qe FROM fts_document
WHERE MATCH(title,body,tags) AGAINST('钻石 订婚' WITH QUERY EXPANSION);
-- §06 词干(stemming)：MySQL 真相 + ngram 子串 / * 前缀 近似
SELECT '§06 词干(stemming)：MySQL 无健壮词干器，用 ngram 子串 / * 前缀 近似' AS `-- 第6章 --`;
-- 6.1 ngram 子串重叠：搜 'jewel'（2-gram: je/ew/we/el）会命中含 'jewelry'/'jewels' 的文档
SELECT COUNT(*) INTO @s_ngram FROM fts_document
WHERE MATCH(body) AGAINST('+jewel' IN BOOLEAN MODE);
-- 6.2 * 前缀算子（更干净）：jewel* 同时匹配 jewel/jewels/jewelry
SELECT COUNT(*) INTO @s_prefix FROM fts_document
WHERE MATCH(body) AGAINST('+jewel*' IN BOOLEAN MODE);
-- 6.3 反例：默认解析器下，'jewelry' 不会自动匹配 'jewel'（无形态归一）—— 说明需要 * 或应用层词干
SELECT COUNT(*) INTO @s_exact FROM fts_sw_demo
WHERE MATCH(txt) AGAINST('+jewelry' IN BOOLEAN MODE);
SELECT COUNT(*) INTO @s_no_stem FROM fts_sw_demo
WHERE MATCH(txt) AGAINST('+jewel' IN BOOLEAN MODE);
-- §07 停用词(stopwords)：默认表 + 自然语言过滤 + 自定义停用词表
SELECT '§07 停用词(stopwords)：默认表 / NLM 过滤 / 自定义表' AS `-- 第7章 --`;
-- 7.1 默认停用词表（information_schema，可直接查）
SELECT COUNT(*) INTO @sw_default FROM information_schema.innodb_ft_default_stopword;
SELECT '默认停用词示例（前 8 个）' AS `-- 默认停用词 --`;
SELECT value FROM information_schema.innodb_ft_default_stopword LIMIT 8;
-- 7.2 关键真相：停用词在【索引构建时】就被剔除，因此 NLM 与 BOOLEAN 都搜不到停用词
-- 'the' 是默认停用词 → 两种模式都返回 0；'diamond' 非停用词 → 两种模式都可搜到
SELECT COUNT(*) INTO @sw_the_nlm FROM fts_sw_demo
WHERE MATCH(txt) AGAINST('the' IN NATURAL LANGUAGE MODE); -- 期望 0（索引未收录）
SELECT COUNT(*) INTO @sw_the_bool FROM fts_sw_demo
WHERE MATCH(txt) AGAINST('+the' IN BOOLEAN MODE); -- 期望 0（同样未收录）
SELECT COUNT(*) INTO @sw_diamond FROM fts_sw_demo
WHERE MATCH(txt) AGAINST('diamond' IN NATURAL LANGUAGE MODE); -- 期望 >0（非停用词）
-- 7.3 自定义停用词表：增删停用词必须重建索引才生效
DROP TABLE IF EXISTS fts_custom_stopword;
CREATE TABLE fts_custom_stopword(value VARCHAR(18) NOT NULL) ENGINE=InnoDB;
INSERT INTO fts_custom_stopword VALUES ('diamond'),('jewelry'),('the'),('and');
-- 会话级自定义停用词表（动态变量，会话作用域；指定后默认停用词表不再生效）
CALL fts_err_probe('设自定义停用词表', 'SET SESSION innodb_ft_user_stopword_table=''JewelryAnalytics/fts_custom_stopword''');
-- 重建 fts_sw_demo 全文索引以应用自定义停用词（'diamond' 现在变为停用词）
ALTER TABLE fts_sw_demo DROP INDEX ft_txt;
ALTER TABLE fts_sw_demo ADD FULLTEXT ft_txt(txt);
-- 验证：重建后 'diamond' 已被剔除 → 返回 0（演示自定义停用词生效）
SELECT COUNT(*) INTO @sw_custom_diamond FROM fts_sw_demo
WHERE MATCH(txt) AGAINST('diamond' IN BOOLEAN MODE);
-- §08 性能感知查询设计（FULLTEXT vs LIKE 前导通配；组合过滤；覆盖）
SELECT '§08 性能感知：FULLTEXT 远胜 LIKE 前导通配；带索引组合过滤' AS `-- 第8章 --`;
-- 8.1 FULLTEXT 检索读行
CALL fts_snap();
SELECT COUNT(*) INTO @p_ft FROM fts_document
WHERE MATCH(title,body,tags) AGAINST('+钻石' IN BOOLEAN MODE) LIMIT 1000;
CALL fts_measure('FT_ngram');
-- 8.2 LIKE 前导通配（同样语义）全表扫描
CALL fts_snap();
SELECT COUNT(*) INTO @p_like FROM fts_document
WHERE body LIKE '%钻石%' LIMIT 1000;
CALL fts_measure('LIKE_lead_wc');
-- 注解度量：FULLTEXT 由辅助索引直接给出 doc_id，不回聚集表；LIKE 前导通配须全表扫描回表
UPDATE fts_demo_metric SET note='fts辅助索引直接给doc_id，不回聚集表' WHERE tag='FT_ngram';
UPDATE fts_demo_metric SET note='前导通配全表扫描，回表10万行' WHERE tag='LIKE_lead_wc';
-- 8.3 性能感知组合：全文 + 选择性过滤（域/本地日/金额）并 LIMIT，缩小结果集
SELECT doc_id, system_code, create_local_date, amount_usd,
MATCH(title,body,tags) AGAINST('+钻石' IN BOOLEAN MODE) AS rel
FROM fts_document
WHERE MATCH(title,body,tags) AGAINST('+钻石' IN BOOLEAN MODE)
AND system_code='ERP'
AND create_local_date >= '2025-10-01'
AND amount_usd > 500
ORDER BY rel DESC LIMIT 10;
-- 8.4 万亿级分区陷阱：分区表上建 FULLTEXT → 报错（MySQL 不支持）
CALL fts_err_probe('分区表建FULLTEXT',
'CREATE TABLE fts_part_ft (id INT, body TEXT, FULLTEXT(body)) PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN MAXVALUE)');
-- 8.5 正确做法示意：分区表「不含 FULLTEXT」，全文下沉分片或 ES（仅演示语法正确性）
DROP TABLE IF EXISTS fts_part_noft;
CREATE TABLE fts_part_noft(
id BIGINT, system_code CHAR(4), body TEXT, created_ts DATETIME,
KEY ix_sys (system_code)
) PARTITION BY RANGE (TO_DAYS(created_ts)) (
PARTITION p2025q3 VALUES LESS THAN (TO_DAYS('2025-10-01')),
PARTITION p2025q4 VALUES LESS THAN (TO_DAYS('2026-01-01')),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
-- §09 国际时区 + 多币种搜索（与全文组合）
SELECT '§09 国际时区 + 多币种：UTC 存储 + 生成列本地日 + 只 SUM(amount_usd)' AS `-- 第9章 --`;
-- 9.1 时区：用生成列 create_local_date（带索引）按「本地营业日」过滤 + 全文
SELECT COUNT(*) INTO @tz_cnt FROM fts_document
WHERE create_local_date = '2025-12-25'
AND MATCH(title,body,tags) AGAINST('+戒指' IN BOOLEAN MODE);
-- 反例：对列套函数 TIMESTAMPADD 会使索引失效（全扫）；生成列才走索引
SELECT TIMESTAMPADD(MINUTE,480,'2026-01-01 00:00:00') INTO @tz_chk;
SELECT CONVERT_TZ('2026-01-01 00:00:00','UTC','Asia/Shanghai') INTO @tz_null;
-- 9.2 多币种：同一语义下，按归一化 amount_usd 结算；跨币种 SUM(amount_orig) 无意义
SELECT system_code,
COUNT(*) AS docs,
ROUND(SUM(amount_usd),2) AS settle_usd, -- 正确：统一美元
ROUND(SUM(amount_orig),2) AS wrong_orig_sum -- 错误示范：混合币种相加
FROM fts_document
WHERE MATCH(title,body,tags) AGAINST('+钻石' IN BOOLEAN MODE)
AND create_local_date >= '2025-10-01'
GROUP BY system_code
ORDER BY settle_usd DESC;
-- 9.3 各域全文命中量（证明八域覆盖）
SELECT system_code, COUNT(*) AS hit_docs
FROM fts_document
WHERE MATCH(title,body,tags) AGAINST('+工艺' IN BOOLEAN MODE)
GROUP BY system_code ORDER BY system_code;
-- §10 自检（V1..V12）全部通过才算交付
-- V1 框架表存在
SELECT COUNT(*) INTO @v1 FROM information_schema.tables WHERE table_schema='JewelryAnalytics' AND table_name IN ('fts_demo_metric','fts_demo_result','fts_demo_probe');
CALL fts_v('V1_框架表存在', IF(@v1=3,1,0), CONCAT('框架表数=',@v1));
-- V2 主表 ngram FULLTEXT 索引存在（含多列与单列两个）
SELECT COUNT(*) INTO @v2 FROM information_schema.statistics WHERE table_schema='JewelryAnalytics' AND table_name='fts_document' AND index_type='FULLTEXT';
CALL fts_v('V2_全文索引ngram存在', IF(@v2>=2,1,0), CONCAT('FULLTEXT索引数=',@v2));
-- V3 自然语言相关性返回 > 0
SELECT COUNT(*) INTO @v3 FROM fts_document WHERE MATCH(title,body,tags) AGAINST('钻石 戒指' IN NATURAL LANGUAGE MODE);
CALL fts_v('V3_自然语言返回>0', IF(@v3>0,1,0), CONCAT('命中=',@v3));
-- V4 CONTAINS 等价（+钻石 -银）返回 > 0
CALL fts_v('V4_CONTAINS等价>0', IF(@c_contains>0,1,0), CONCAT('命中=',@c_contains));
-- V5 查询扩展召回 >= 自然语言召回
CALL fts_v('V5_查询扩展>=自然语言', IF(@n_qe >= @n_nlm,1,0), CONCAT('QE=',@n_qe,' NLM=',@n_nlm));
-- V6 词干近似：ngram 子串(jewel→jewelry) 与 * 前缀 均 > 0
CALL fts_v('V6_词干近似>0', IF(@s_ngram>0 AND @s_prefix>0,1,0), CONCAT('ngram=',@s_ngram,' prefix=',@s_prefix));
-- V7 停用词：'the' 在 NLM/BOOLEAN 均=0（索引时剔除）；'diamond' 可搜到；默认表非空
CALL fts_v('V7_停用词索引时剔除', IF(@sw_the_nlm=0 AND @sw_the_bool=0 AND @sw_diamond>0 AND @sw_default>0,1,0), CONCAT('the_NLM=',@sw_the_nlm,' the_BOOL=',@sw_the_bool,' diamond=',@sw_diamond,' 默认表=',@sw_default));
-- V8 时区：生成列非 NULL；TIMESTAMPADD 可用；CONVERT_TZ 本机为 NULL（已知）
CALL fts_v('V8_时区生成列可用', IF(@tz_chk IS NOT NULL AND @tz_null IS NULL,1,0), CONCAT('TSadd非NULL=',IF(@tz_chk IS NULL,'N','Y'),' CONVERT_TZ=',IF(@tz_null IS NULL,'NULL(已知)','有值')));
-- V9 多币种：amount_usd 与 amount_orig*rate 全部一致
SELECT COUNT(*) INTO @v9 FROM fts_document
WHERE ABS(amount_usd - ROUND(amount_orig * CASE currency_code WHEN 'CNY' THEN 1/7.20 WHEN 'USD' THEN 1 WHEN 'EUR' THEN 1.08 WHEN 'JPY' THEN 0.0067 WHEN 'HKD' THEN 1/7.80 WHEN 'GBP' THEN 1.27 END,2)) < 0.02;
SELECT COUNT(*) INTO @v9tot FROM fts_document;
CALL fts_v('V9_多币种USD归一一致', IF(@v9=@v9tot,1,0), CONCAT('一致=',@v9,'/',@v9tot));
-- V10 性能：FULLTEXT 引擎读行 远少于 LIKE 前导通配
SELECT IFNULL((SELECT innodb_rows FROM fts_demo_metric WHERE tag='FT_ngram'),0) INTO @m_ft;
SELECT IFNULL((SELECT innodb_rows FROM fts_demo_metric WHERE tag='LIKE_lead_wc'),1) INTO @m_lk;
CALL fts_v('V10_全文读行少于LIKE', IF(@m_ft < @m_lk,1,0), CONCAT('FT=',@m_ft,' LIKE=',@m_lk));
-- V11 错误探针：①MATCH 列集合与索引不一致 → 1191；②分区+FULLTEXT → 报错
SELECT IFNULL((SELECT errno FROM fts_demo_probe WHERE label='FT列集合不一致' ORDER BY id DESC LIMIT 1),0) INTO @v11a;
CALL fts_err_probe('FT列集合不一致', 'SELECT COUNT(*) FROM fts_document WHERE MATCH(title) AGAINST(''钻石'' IN NATURAL LANGUAGE MODE)');
SELECT IFNULL((SELECT errno FROM fts_demo_probe WHERE label='FT列集合不一致' ORDER BY id DESC LIMIT 1),0) INTO @v11b;
SELECT IFNULL((SELECT errno FROM fts_demo_probe WHERE label='分区表建FULLTEXT' ORDER BY id DESC LIMIT 1),0) INTO @v11c;
CALL fts_v('V11_错误探针捕获', IF(@v11b=1191 AND @v11c>0,1,0), CONCAT('列集合errno=',@v11b,' 分区errno=',@v11c));
-- V12 幂等：文档量稳定 = 100000
SELECT COUNT(*) INTO @v12 FROM fts_document;
CALL fts_v('V12_数据量稳定100000', IF(@v12=100000,1,0), CONCAT('doc行数=',@v12));
SELECT '==== 自检汇总：以下应全部 passed=1 ====' AS `-- 自检 --`;
SELECT check_id, passed, detail FROM fts_demo_result ORDER BY check_id;
-- §11 实测总表与结论
SELECT '§11.1 引擎真实读行对照（INNODB_ROWS_READ 增量）' AS `-- 第11章 --`;
SELECT tag, innodb_rows AS 引擎读行, rnd_next AS 回表_next, rnd AS 随机回表, ms AS 耗时ms, note
FROM fts_demo_metric ORDER BY tag;
SELECT '§11.2 错误探针（预期捕获，非致命）' AS `-- 第11章 --`;
SELECT label, errno, msg FROM fts_demo_probe ORDER BY id;
SELECT '§11.3 自检结果（全部 passed=1 为通过）' AS `-- 第11章 --`;
SELECT check_id, passed, detail FROM fts_demo_result ORDER BY check_id;
SELECT '§11.4 关键结论' AS `-- 第11章 --`;
SELECT '1) CONTAINS→BOOLEAN MODE(+词 -词 "短语" *前缀 ><权重)；FREETEXT→NATURAL LANGUAGE / WITH QUERY EXPANSION。' AS `结论1`;
SELECT '2) 中文全文必须 WITH PARSER ngram；MATCH 列集合须与 FULLTEXT 索引列完全一致（否则 1191）。' AS `结论2`;
SELECT '3) MySQL 无健壮词干器：用 ngram 子串重叠 或 * 前缀 近似；真词干靠应用层或 Elasticsearch。' AS `结论3`;
SELECT '4) 停用词在【索引构建时】即剔除，NLM 与 BOOLEAN 都搜不到停用词；增删停用词用自定义表+重建索引。' AS `结论4`;
SELECT '5) 全文读行远少于 LIKE 前导通配（高并发下差距随数据量放大），热路径严禁 LIKE ''%x%''。' AS `结论5`;
SELECT '6) 分区表不能建 FULLTEXT（MySQL 限制）；万亿级全文须按 system_code/地区/时间 分片建 FULLTEXT，或下沉 ES。' AS `结论6`;
SELECT '7) 时区存 UTC，展示用生成列 create_local_date（带索引）做本地日过滤；本机 CONVERT_TZ 失效用 TIMESTAMPADD。' AS `结论7`;
SELECT '8) 多币种只 SUM(amount_usd) 归一结算，绝不可跨币种 SUM(amount_orig)。' AS `结论8`;
SELECT '§11.5 八条方法清单（落到建表/查询）' AS `-- 第11章 --`;
SELECT '① 中文列建 ngram FULLTEXT；② MATCH 列与索引列严格一致；③ CONTAINS→布尔、FREETEXT→自然语言/扩展；' AS `清单`;
SELECT '④ 词干用 * 前缀/ngram，真词干下沉 ES；⑤ 停用词用自定义表+重建索引；⑥ 时区用生成列本地日+索引；' AS `清单`;
SELECT '⑦ 金额归一 amount_usd；⑧ 万亿级分片建 FULLTEXT 或 ES 承担全文、MySQL 做结构化过滤+取详情（CQRS）。' AS `清单`;
SELECT '§11.6 脚本结构' AS `-- 第11章 --`;
SELECT '_s00 头部/速览/反模式；_s01 度量框架；_s02 八域建表造数；_s03 解析器；' AS `结构`;
SELECT '_s04 CONTAINS等价；_s05 FREETEXT等价；_s06 词干；_s07 停用词；_s08 性能/万亿；_s09 时区/多币种；_s10 自检；_s11 总结。' AS `结构`;
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文）
浙公网安备 33010602011771号