sql: JSON and XML Data Handling 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 JSON / XML 半结构化数据处理专题
-- 主题：国际化珠宝集团 OA/ERP/CRM/HRM/MES/SCM/WMS/KMS 八域 JSON/XML
--       + JSON 存储/查询/索引/校验/拆解/修改 + XML 处理边界（MySQL 无原生 XML）
--       + 万亿高并发 + 国际时区 + 多币种结算
-- 运行前提：MySQL 9.0.x，库 JewelryAnalytics 自动建/用；本脚本完全幂等。
--   方式A（命令行，推荐，避开 Workbench 30s 读超时）：
--     mysql -uroot -p --default-character-set=utf8mb4 < MySQL9_JSON_XML_JewelryDemo.sql
--   方式B（Workbench）：整段执行。
-- 幂等说明：每次先 DROP 全部 js_* 实验表/过程再重建并造数，重复运行结果一致。
-- 度量口径：用 performance_schema.session_status 的 INNODB_ROWS_READ 增量，
--           量化「引擎真正读了多少行」，比 EXPLAIN rows 更接近真实代价。
-- ============================================================================
 
-- §0.1  MySQL JSON/XML 能力速览（与其它数据库的术语映射）
-- ----------------------------------------------------------------------------
-- SQL Server / 通用概念        MySQL 9.0 等价 / 真相
-- ---------------------------  -------------------------------------------------
-- JSON 列类型                  JSON 原生类型（校验、二进制存储、内联部分更新）
-- column->'$.path'             JSON_EXTRACT 简写；->> 自动去引号
-- JSON_VALUE(col,'$.path')     col->>'$.path'
-- JSON_QUERY(col,'$.arr')      col->'$.arr'（保留 JSON，不拆引号）
-- 多值数组索引                  CREATE INDEX ... ((CAST(col->'$.arr' AS .. ARRAY))) 多值索引
-- OPENJSON / JSON_TABLE        JSON_TABLE(col, '$.path[*]' COLUMNS(...)) 炸行
-- ISJSON(col)                  JSON_VALID(col)
-- JSON 校验(Schema)            JSON_SCHEMA_VALID(schema, doc)（8.0.17+）
-- JSON_MODIFY                  JSON_SET / JSON_INSERT / JSON_REPLACE / JSON_REMOVE
-- XML 类型 / XMLTABLE          ❌ MySQL 9.0 无 XML 类型、无 XMLTABLE（实测 1064 不存在）
-- EXTRACTVALUE(XPath标量)       ✅ 本机 9.0.1 实测仍在（XPath 1.0 子集，仅抽单值，无集合/无索引）
--                             （XMLTABLE 没有；EXTRACTVALUE 有但功能弱）
-- ============================================================================
 
-- §0.2  反模式速查表（本机已实测）
-- ----------------------------------------------------------------------------
-- 反模式                                  后果                                  正确做法
-- 在 JSON 列上直接建二级索引              报错（只能对生成列/标量建索引）     抽生成列再建索引
-- 热路径用 CAST(specs->>'$.x')=v 过滤     全 JSON 扫描（万亿级灾难）          生成列 + 二级索引
-- 数组包含查询用 JSON_SEARCH 全扫         不可走索引，O(n)                   多值索引 + MEMBER OF
-- 误以为 XMLTABLE 可用                    实测 1064 不存在，EXTRACTVALUE 仅抽单值   XML 存 TEXT，ETL 转 JSON/关系化
-- 用 EXTRACTVALUE 抽数组/多值               仅返回首个匹配，无集合语义           需要集合用 JSON_TABLE 转 JSON 再炸行
-- 用 JSON 存高频聚合字段                  行膨胀、Buffer Pool 浪费           抽成关系列或侧表
-- JSON 不校验直接入库                      脏数据（缺字段/类型错）             JSON_SCHEMA_VALID 入库前校验
-- CONVERT_TZ(ts,'UTC',name)              本机 mysql.time_zone_name=0 → NULL  用 TIMESTAMPADD(MINUTE,偏移,ts)
-- 跨币种 SUM(amount_orig)                 不同币种相加无意义                  只 SUM(price_usd)
-- ============================================================================
 
-- §01  度量框架（js_demo_*）：量化引擎真实读行，证明取舍
DROP TABLE IF EXISTS js_demo_metric;
CREATE TABLE js_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 js_demo_result;
CREATE TABLE js_demo_result(
  check_id VARCHAR(30) PRIMARY KEY,
  passed TINYINT NOT NULL,
  detail VARCHAR(400)
);
 
DROP TABLE IF EXISTS js_demo_probe;
CREATE TABLE js_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 js_snap;
DELIMITER //
CREATE PROCEDURE js_snap()
BEGIN
  SELECT VARIABLE_VALUE INTO @js_snap_ir FROM performance_schema.session_status WHERE VARIABLE_NAME='INNODB_ROWS_READ';
  SELECT VARIABLE_VALUE INTO @js_snap_rn FROM performance_schema.session_status WHERE VARIABLE_NAME='HANDLER_READ_RND_NEXT';
  SELECT VARIABLE_VALUE INTO @js_snap_rd FROM performance_schema.session_status WHERE VARIABLE_NAME='HANDLER_READ_RND';
  SET @js_snap_ts = UNIX_TIMESTAMP(NOW(6));
END//
DELIMITER ;
 
DROP PROCEDURE IF EXISTS js_measure;
DELIMITER //
CREATE PROCEDURE js_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)) - @js_snap_ts)*1000);
  INSERT INTO js_demo_metric(tag,innodb_rows,rnd_next,rnd,ms) VALUES(p_tag, v_ir-@js_snap_ir, v_rn-@js_snap_rn, v_rd-@js_snap_rd, v_ms)
    ON DUPLICATE KEY UPDATE innodb_rows=v_ir-@js_snap_ir, rnd_next=v_rn-@js_snap_rn, rnd=v_rd-@js_snap_rd, ms=v_ms;
END//
DELIMITER ;
 
DROP PROCEDURE IF EXISTS js_v;
DELIMITER //
CREATE PROCEDURE js_v(IN p_check VARCHAR(30), IN p_pass TINYINT, IN p_detail VARCHAR(400))
BEGIN
  INSERT INTO js_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 js_err_probe;
DELIMITER //
CREATE PROCEDURE js_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 @js_probe_sql = p_sql;
  PREPARE js_pstmt FROM @js_probe_sql;
  IF v_no = 0 THEN
    EXECUTE js_pstmt;
    DEALLOCATE PREPARE js_pstmt;
  END IF;
  INSERT INTO js_demo_probe(label,errno,sql_state,msg) VALUES(p_label, v_no, v_st, v_msg);
END//
DELIMITER ;
 
-- §02  实验台：维度表 + 八域 JSON 仓 + 造数（国际化珠宝集团）
-- 命名统一 js_* 前缀，避免与同库其他主题 demo_*/fts_*/samp_*/gl_*/dim_* 表冲突。
 
DROP TABLE IF EXISTS js_dim_currency;
CREATE TABLE js_dim_currency(code CHAR(3) PRIMARY KEY, name VARCHAR(40), symbol VARCHAR(8), usd_rate DECIMAL(12,6));
INSERT INTO js_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 js_dim_country;
CREATE TABLE js_dim_country(code CHAR(2) PRIMARY KEY, name VARCHAR(40), tz_offset_min INT, currency CHAR(3));
INSERT INTO js_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');
 
-- 八域统一商品/物料仓：ERP(商品规格) / MES(工艺参数) / SCM(供货条款) / WMS(批次属性)
-- 均用 JSON 存半结构化规格；生成列抽热点标量建索引；tags 数组用多值索引。
DROP TABLE IF EXISTS js_product;
CREATE TABLE js_product(
  id              BIGINT PRIMARY KEY,
  system_code     CHAR(4) NOT NULL,          -- OA/ERP/CRM/HRM/MES/SCM/WMS/KMS
  sku             VARCHAR(20) NOT NULL,
  name            VARCHAR(240) NOT NULL,
  specs           JSON NOT NULL,             -- 半结构化规格
  price_orig      DECIMAL(18,2),
  currency_code   CHAR(3),
  price_usd       DECIMAL(18,2),             -- 已按汇率归一，结算只用它
  country_code    CHAR(2),
  created_ts      DATETIME NOT NULL,         -- 统一存 UTC
  create_offset_min SMALLINT,                -- 时区偏移（分钟），冗余存储以便生成列建索引
  create_local_date DATE                     -- 生成列：本地营业日，用于带索引的时区过滤
    GENERATED ALWAYS AS (DATE(TIMESTAMPADD(MINUTE, create_offset_min, created_ts))) STORED,
  -- 生成列：从 JSON 抽热点标量，可建二级索引（JSON 列本身不能直接建索引）
  gen_carat       DECIMAL(6,3) GENERATED ALWAYS AS (CAST(specs->>'$.carat' AS DECIMAL(6,3))) STORED,
  gen_cert        VARCHAR(12) GENERATED ALWAYS AS (specs->>'$.cert') STORED,
  KEY ix_system (system_code),
  KEY ix_carat (gen_carat),
  KEY ix_cert (gen_cert),
  KEY ix_local_date (create_local_date),
  KEY ix_usd (price_usd)
) ENGINE=InnoDB;
 
-- 造 100,000 行（250×400），八域、多国、多币种、JSON 规格
INSERT INTO js_product(id,system_code,sku,name,specs,price_orig,currency_code,price_usd,country_code,created_ts,create_offset_min)
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)
SELECT
  id,
  ELT(1+id%8,'OA','ERP','CRM','HRM','MES','SCM','WMS','KMS'),
  CONCAT('SKU',LPAD(id,8,'0')),
  CONCAT(ELT(1+id%10,'黄金','铂金','钻石','红宝石','蓝宝石','翡翠','珍珠','银','18K金','白金'),
         ELT(1+id%8,'戒指','项链','手链','耳环','吊坠','胸针','手镯','脚链')),
  JSON_OBJECT(
    'carat',   ROUND(0.3+(id%50)/10.0,2),
    'color',   ELT(1+id%6,'D','E','F','G','H','I'),
    'clarity', ELT(1+id%7,'IF','VVS1','VVS2','VS1','VS2','SI1','SI2'),
    'cert',    ELT(1+id%3,'GIA','IGI','NGTC'),
    'origin',  ELT(1+id%5,'南非','澳大利亚','加拿大','俄罗斯','中国'),
    'tags',    JSON_ARRAY(
                ELT(1+id%6,'婚戒','送礼','经典','时尚','收藏','投资'),
                ELT(1+(id+1)%6,'婚戒','送礼','经典','时尚','收藏','投资'),
                ELT(1+(id+2)%6,'婚戒','送礼','经典','时尚','收藏','投资')
               )
  ),
  ROUND(100 + (id%9000)/10.0, 2),
  ELT(1+id%8,'CNY','USD','EUR','JPY','HKD','GBP','EUR','USD'),
  ROUND((100 + (id%9000)/10.0) * CASE 1+id%8
        WHEN 1 THEN 1/7.20 WHEN 2 THEN 1.0 WHEN 3 THEN 1.08
        WHEN 4 THEN 0.0067 WHEN 5 THEN 1/7.80 WHEN 6 THEN 1.27
        WHEN 7 THEN 1.08 WHEN 8 THEN 1.0 END, 2),
  ELT(1+id%8,'CN','US','FR','JP','HK','GB','DE','SG'),
  DATE_ADD('2024-01-01 00:00:00', INTERVAL (id % 365) DAY),
  ELT(1+id%8,480,-300,60,540,480,0,60,480)
FROM base;
 
-- 多值索引（8.0.17+）：对 JSON 数组 tags 建索引，支持 MEMBER OF / JSON_CONTAINS / JSON_OVERLAPS 走索引
ALTER TABLE js_product ADD INDEX ix_tags ( (CAST(specs->'$.tags' AS CHAR(32) ARRAY)) );
 
-- CRM/HRM 客户档案：用 JSON 存偏好/渠道/等级（典型半结构化）
DROP TABLE IF EXISTS js_customer;
CREATE TABLE js_customer(
  id BIGINT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  profile JSON NOT NULL
) ENGINE=InnoDB;
INSERT INTO js_customer(id,name,profile)
WITH RECURSIVE a(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM a WHERE n<200)
SELECT
  n,
  CONCAT('客户', LPAD(n,6,'0')),
  JSON_OBJECT(
    'tier',   ELT(1+n%4,'普通','银卡','金卡','VIP'),
    'channels', JSON_ARRAY(ELT(1+n%3,'email','sms','wechat'),
                           ELT(1+(n+1)%3,'email','sms','wechat'),
                           ELT(1+(n+2)%3,'email','sms','wechat')),
    'prefs',  JSON_OBJECT('metal', ELT(1+n%5,'铂金','18K金','黄金','白银','钛'),
                          'budget', 1000 + (n%20)*500),
    'loyalty', 100 + n*7
  )
FROM a;
 
-- KMS/OA 知识库文章：用 JSON 存标签数组；部分遗留文档以 XML 文本存储（见 §09）
DROP TABLE IF EXISTS js_article;
CREATE TABLE js_article(
  id BIGINT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  body_json JSON NOT NULL,
  KEY ix_article_tags ( (CAST(body_json->'$.tags' AS CHAR(32) ARRAY)) )
) ENGINE=InnoDB;
INSERT INTO js_article(id,title,body_json)
WITH RECURSIVE a(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM a WHERE n<100)
SELECT
  n,
  CONCAT('知识库文章-', n),
  JSON_OBJECT(
    'category', ELT(1+n%8,'OA','ERP','CRM','HRM','MES','SCM','WMS','KMS'),
    'tags', JSON_ARRAY(ELT(1+n%6,'入库','质检','审批','培训','排产','盘点'),
                       ELT(1+(n+1)%6,'入库','质检','审批','培训','排产','盘点')),
    'views', 50 + n*3
  )
FROM a;
 
-- XML 遗留数据：MySQL 无 XML 类型，只能存为 TEXT/LONGTEXT，解析靠应用层或 ETL
DROP TABLE IF EXISTS js_legacy_xml;
CREATE TABLE js_legacy_xml(
  id BIGINT PRIMARY KEY,
  domain CHAR(4),
  xml_doc LONGTEXT NOT NULL,
  created_ts DATETIME NOT NULL,
  create_offset_min SMALLINT,
  create_local_date DATE GENERATED ALWAYS AS (DATE(TIMESTAMPADD(MINUTE, create_offset_min, created_ts))) STORED,
  KEY ix_local (create_local_date)
) ENGINE=InnoDB;
INSERT INTO js_legacy_xml(id,domain,xml_doc,created_ts,create_offset_min) VALUES
 (1,'OA','<workflow><id>1</id><title>采购审批</title><amount>5000</amount><approver>张三</approver></workflow>','2024-03-01 09:00:00',480),
 (2,'ERP','<invoice><no>INV-2024-001</no><item>钻石戒指</item><qty>12</qty><total>96000</total></invoice>','2024-03-02 10:30:00',-300),
 (3,'WMS','<lot><sku>SKU00000001</sku><qty>200</qty><loc>A-01-03</loc><expiry>2026-12-31</expiry></lot>','2024-03-03 14:15:00',60);
 
SET @total_rows = 100000;
 
-- ============================================================================
-- §03  JSON 存储与类型校验
-- ============================================================================
SELECT '§03  JSON 存储与类型校验（JSON_TYPE / JSON_VALID）' AS `-- 第3章 --`;
SELECT JSON_TYPE(specs) AS root_type, JSON_TYPE(specs->'$.carat') AS carat_type,
       JSON_TYPE(specs->'$.tags') AS tags_type, JSON_VALID(specs) AS is_valid
FROM js_product WHERE id=1;
 
-- ============================================================================
-- §04  JSON 查询：->> / JSON_EXTRACT / JSON_KEYS / JSON_CONTAINS / JSON_SEARCH
-- ============================================================================
SELECT '§04  JSON 查询（->> / JSON_EXTRACT / JSON_KEYS / JSON_CONTAINS / JSON_SEARCH）' AS `-- 第4章 --`;
-- 4.1  ->> 去引号提取标量
SELECT id, name, specs->>'$.color' AS color, specs->>'$.clarity' AS clarity, specs->>'$.cert' AS cert
FROM js_product WHERE id BETWEEN 1 AND 5;
-- 4.2  JSON_KEYS 看顶层键；JSON_LENGTH 看数组长度
SELECT specs->>'$.cert' AS cert, JSON_KEYS(specs) AS keys_arr, JSON_LENGTH(specs->'$.tags') AS tags_len
FROM js_product WHERE id=1;
-- 4.3  JSON_CONTAINS 判断对象包含；JSON_SEARCH 定位值路径
SELECT id, name FROM js_product
WHERE JSON_CONTAINS(specs, JSON_OBJECT('origin','南非')) LIMIT 5;
SELECT JSON_SEARCH(specs, 'one', 'GIA') AS path_to_gia FROM js_product WHERE id=1;
 
-- ============================================================================
-- §05  JSON 索引：生成列 + 二级索引（标量）vs 多值索引（数组）
-- ============================================================================
SELECT '§05  JSON 索引：生成列二级索引（标量）vs 多值索引（数组 tags）' AS `-- 第5章 --`;
-- 5.1 生成列索引：高净度大克拉热路径查询（走 ix_carat，只读命中行）
SELECT id, name, gen_carat, gen_cert FROM js_product WHERE gen_carat > 4.8 LIMIT 5;
-- 5.2 多值索引：数组包含（走 ix_tags，避免全 JSON 扫描）
SELECT id, name, specs->>'$.tags' AS tags FROM js_product
WHERE '婚戒' MEMBER OF (specs->'$.tags') LIMIT 5;
 
-- ============================================================================
-- §06  JSON Schema 校验（JSON_SCHEMA_VALID，8.0.17+）：脏数据拦截
-- ============================================================================
SELECT '§06  JSON Schema 校验（JSON_SCHEMA_VALID）：入库前拦截脏数据' AS `-- 第6章 --`;
SET @product_schema = '{"type":"object","properties":{"carat":{"type":"number"},"cert":{"type":"string"},"tags":{"type":"array"}},"required":["carat","cert"]}';
-- 合法文档（carat 是数字、cert 是字符串）→ 1
SELECT JSON_SCHEMA_VALID(@product_schema, JSON_OBJECT('carat',1.2,'cert','GIA','tags',JSON_ARRAY('x'))) AS valid_ok;
-- 非法文档（carat 是字符串）→ 0
SELECT JSON_SCHEMA_VALID(@product_schema, JSON_OBJECT('carat','bad','cert','GIA')) AS valid_bad;
 
-- ============================================================================
-- §07  JSON 拆解：JSON_TABLE 把 tags 数组炸成关系行（半结构化 → 关系）
-- ============================================================================
SELECT '§07  JSON 拆解：JSON_TABLE 把 tags 数组炸成行（半结构化→关系）' AS `-- 第7章 --`;
SELECT j.system_code, t.tag, COUNT(*) AS cnt
FROM js_product j,
     JSON_TABLE(j.specs, '$.tags[*]' COLUMNS (tag VARCHAR(32) PATH '$')) AS t
GROUP BY j.system_code, t.tag
ORDER BY cnt DESC
LIMIT 12;
 
-- ============================================================================
-- §08  JSON 原地修改：JSON_SET / JSON_INSERT / JSON_REPLACE / JSON_REMOVE
-- ============================================================================
SELECT '§08  JSON 修改：JSON_SET / JSON_INSERT / JSON_REPLACE / JSON_REMOVE' AS `-- 第8章 --`;
DROP TABLE IF EXISTS js_tmp_edit;
CREATE TABLE js_tmp_edit(id INT PRIMARY KEY, specs JSON);
INSERT INTO js_tmp_edit VALUES (1, JSON_OBJECT('a',1,'b',2));
UPDATE js_tmp_edit SET specs = JSON_SET(specs,'$.c',3) WHERE id=1;        -- 新增/覆盖 c=3
UPDATE js_tmp_edit SET specs = JSON_INSERT(specs,'$.d',4) WHERE id=1;     -- 仅当不存在才插入
UPDATE js_tmp_edit SET specs = JSON_REMOVE(specs,'$.b') WHERE id=1;       -- 删除 b
SELECT id, specs FROM js_tmp_edit;
 
-- ============================================================================
-- §09  XML 处理边界：MySQL 9.0 无 XML 类型 / 无 XMLTABLE，但 EXTRACTVALUE(XPath) 仍在
-- ============================================================================
SELECT '§09  XML 处理边界：MySQL 9.0 无 XML 类型/无 XMLTABLE，但 EXTRACTVALUE(XPath) 仍可用' AS `-- 第9章 --`;
-- 9.1 探针：EXTRACTVALUE 在 9.0.1 实测【仍在】（XPath 1.0 子集，仅抽单值）；XMLTABLE【已不存在 → 1064】
CALL js_err_probe('EXTRACTVALUE仍在', 'SELECT EXTRACTVALUE(xml_doc,''/workflow/title'') FROM js_legacy_xml');
CALL js_err_probe('XMLTABLE不存在', 'SELECT * FROM XMLTABLE(''/x'' COLUMNS a INT PATH ''/x/a'') AS x');
-- 9.2 实测 EXTRACTVALUE 能抽 XML 单值（轻量级可用，但仅首个匹配、无集合语义、无 XML 索引）
SELECT id, domain,
       EXTRACTVALUE(xml_doc,'/workflow/title')   AS wf_title,
       EXTRACTVALUE(xml_doc,'/invoice/item')      AS inv_item
FROM js_legacy_xml;
-- 9.3 字符串函数也可抽（不依赖 XML 解析器，适合固定模板）
SELECT id,
       SUBSTRING_INDEX(SUBSTRING_INDEX(xml_doc,'<approver>',-1),'</approver>',1) AS approver_when_has
FROM js_legacy_xml;
-- 9.4 正解：万亿级 XML 应 ETL 转 JSON（入库 JSON 列，享 JSON_TABLE/多值索引）或关系化到侧表；
--       EXTRACTVALUE 只适合偶发、单值抽取，别承载高频 XML 解析。
 
-- ============================================================================
-- §10  时区 + 多币种：本地营业日生成列过滤；只 SUM(price_usd)
-- ============================================================================
SELECT '§10  国际时区 + 多币种结算（生成列本地日；只 SUM(price_usd)）' AS `-- 第10章 --`;
-- 10.1 按本地营业日过滤（带索引，时区无关 UTC 存储）
SELECT create_local_date, COUNT(*) AS cnt
FROM js_product
WHERE create_local_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY create_local_date ORDER BY create_local_date LIMIT 5;
-- 10.2 跨域、跨币种只汇总 USD 归一值（绝不可 SUM(price_orig)）
SELECT system_code, COUNT(*) AS orders, SUM(price_usd) AS gmv_usd
FROM js_product GROUP BY system_code ORDER BY gmv_usd DESC;
 
-- ============================================================================
-- §11  万亿/高并发：索引取舍实测（生成列索引 vs 全 JSON 扫描；多值索引 vs 全扫）
-- ============================================================================
SELECT '§11  万亿/高并发性能感知：JSON 索引实测（INNODB_ROWS_READ 增量）' AS `-- 第11章 --`;
-- 11.1 生成列索引 vs 全 JSON 扫描（标量过滤）
--  注意：MySQL 会对「与生成列定义逐字相同的表达式」做生成列替换而误用索引，
--  故「全扫描」版本改用 JSON_EXTRACT(...)，表达式文本不同，强制真全扫。
CALL js_snap();
SELECT COUNT(*) INTO @gc_idx FROM js_product WHERE gen_carat > 4.8;
CALL js_measure('GENCOL_index');
CALL js_snap();
SELECT COUNT(*) INTO @gc_full FROM js_product WHERE CAST(JSON_EXTRACT(specs,'$.carat') AS DECIMAL(6,3)) > 4.8;
CALL js_measure('GENCOL_fullscan');
 
-- 11.2 多值索引 vs JSON_SEARCH 全扫（数组包含）
CALL js_snap();
SELECT COUNT(*) INTO @mv_idx FROM js_product WHERE '婚戒' MEMBER OF (specs->'$.tags');
CALL js_measure('MV_index');
CALL js_snap();
SELECT COUNT(*) INTO @mv_full FROM js_product WHERE JSON_SEARCH(specs,'all','婚戒') IS NOT NULL;
CALL js_measure('MV_fullscan');
 
SELECT m.tag, m.innodb_rows,
       CASE WHEN m.tag LIKE 'GENCOL%' THEN '生成列索引 vs 全JSON扫描'
            WHEN m.tag LIKE 'MV%' THEN '多值索引 vs JSON_SEARCH全扫' END AS 对比
FROM js_demo_metric m WHERE m.tag IN ('GENCOL_index','GENCOL_fullscan','MV_index','MV_fullscan')
ORDER BY m.tag;
 
-- ============================================================================
-- §12  V1–V12 自检（每轮运行结果一致即为幂等）
-- ============================================================================
SELECT '§12  V1–V12 自检（连跑两次应完全一致）' AS `-- 第12章 --`;
 
-- V1 框架表存在
SELECT COUNT(*) INTO @v1 FROM information_schema.tables
WHERE table_schema='JewelryAnalytics' AND table_name IN ('js_demo_metric','js_demo_result','js_demo_probe');
CALL js_v('V1_框架表存在', IF(@v1=3,1,0), CONCAT('框架表数=',@v1));
 
-- V2 JSON 相关索引存在（ix_carat/ix_cert/ix_tags）
SELECT COUNT(*) INTO @v2 FROM information_schema.statistics
WHERE table_schema='JewelryAnalytics' AND table_name='js_product'
  AND index_name IN ('ix_carat','ix_cert','ix_tags');
CALL js_v('V2_JSON索引存在', IF(@v2=3,1,0), CONCAT('JSON索引数=',@v2));
 
-- V3 ->> 查询返回 > 0
SELECT COUNT(*) INTO @v3 FROM js_product WHERE specs->>'$.color' = 'D';
CALL js_v('V3_箭头查询>0', IF(@v3>0,1,0), CONCAT('color=D命中=',@v3));
 
-- V4 生成列索引生效：命中行数相等且生成列读行 << 全 JSON 扫描读行
SELECT IFNULL((SELECT innodb_rows FROM js_demo_metric WHERE tag='GENCOL_index'),0) INTO @ir_gci;
SELECT IFNULL((SELECT innodb_rows FROM js_demo_metric WHERE tag='GENCOL_fullscan'),0) INTO @ir_gcf;
CALL js_v('V4_生成列索引生效', IF(@gc_idx=@gc_full AND @ir_gci>0 AND @ir_gci < @ir_gcf,1,0),
          CONCAT('命中=',@gc_idx,' 索引读行=',@ir_gci,' 全扫读行=',@ir_gcf));
 
-- V5 多值索引生效：命中相等且多值索引读行 < 全扫读行
SELECT IFNULL((SELECT innodb_rows FROM js_demo_metric WHERE tag='MV_index'),0) INTO @ir_mvi;
SELECT IFNULL((SELECT innodb_rows FROM js_demo_metric WHERE tag='MV_fullscan'),0) INTO @ir_mvf;
CALL js_v('V5_多值索引生效', IF(@mv_idx=@mv_full AND @ir_mvi>0 AND @ir_mvi < @ir_mvf,1,0),
          CONCAT('命中=',@mv_idx,' 索引读行=',@ir_mvi,' 全扫读行=',@ir_mvf));
 
-- V6 JSON Schema 校验正确区分合法/非法
SET @s6 = '{"type":"object","properties":{"carat":{"type":"number"},"cert":{"type":"string"}},"required":["carat","cert"]}';
SELECT JSON_SCHEMA_VALID(@s6, JSON_OBJECT('carat',1.2,'cert','GIA')) INTO @v6a;
SELECT JSON_SCHEMA_VALID(@s6, JSON_OBJECT('carat','bad','cert','GIA')) INTO @v6b;
CALL js_v('V6_Schema校验正确', IF(@v6a=1 AND @v6b=0,1,0), CONCAT('合法=',@v6a,' 非法=',@v6b));
 
-- V7 JSON_TABLE 炸行 > 0
SELECT COUNT(*) INTO @v7 FROM (
  SELECT j.id, t.tag FROM js_product j,
    JSON_TABLE(j.specs, '$.tags[*]' COLUMNS (tag VARCHAR(32) PATH '$')) AS t
) x;
CALL js_v('V7_JSON_TABLE炸行>0', IF(@v7>0,1,0), CONCAT('炸出行数=',@v7));
 
-- V8 JSON 修改生效（SET 新增、REMOVE 删除）
SELECT JSON_CONTAINS_PATH(specs,'one','$.c') INTO @v8a FROM js_tmp_edit WHERE id=1;
SELECT JSON_CONTAINS_PATH(specs,'one','$.b') INTO @v8b FROM js_tmp_edit WHERE id=1;
CALL js_v('V8_JSON修改生效', IF(@v8a=1 AND @v8b=0,1,0), CONCAT('含c=',@v8a,' 含b=',@v8b));
 
-- V9 XML 支持边界：EXTRACTVALUE 仍在(errno=0) 但 XMLTABLE 不存在(errno=1064)
SELECT errno INTO @v9a FROM js_demo_probe WHERE label='EXTRACTVALUE仍在' LIMIT 1;
SELECT errno INTO @v9b FROM js_demo_probe WHERE label='XMLTABLE不存在' LIMIT 1;
CALL js_v('V9_XML支持边界', IF(@v9a=0 AND @v9b=1064,1,0), CONCAT('EXTRACTVALUE errno=',@v9a,' XMLTABLE errno=',@v9b));
 
-- V10 时区生成列可用 + CONVERT_TZ 本机失效（执行成功 errno=0 但返回 NULL）
SELECT COUNT(*) INTO @v10 FROM js_product WHERE create_local_date >= '2024-01-01';
CALL js_err_probe('CONVERT_TZ本机失效', 'SELECT CONVERT_TZ(created_ts,''UTC'',''Asia/Shanghai'') FROM js_product LIMIT 1');
SELECT errno INTO @v10b FROM js_demo_probe WHERE label='CONVERT_TZ本机失效' LIMIT 1;
SELECT CONVERT_TZ(created_ts,'UTC','Asia/Shanghai') INTO @ctz_test FROM js_product LIMIT 1;
CALL js_v('V10_时区生成列可用', IF(@v10>0 AND @v10b=0 AND @ctz_test IS NULL,1,0),
          CONCAT('本地日命中=',@v10,' CONVERT_TZ errno=',@v10b,' 结果=',IF(@ctz_test IS NULL,'NULL','非NULL')));
 
-- V11 多币种 USD 归一一致（price_usd == price_orig*rate 求和）
SELECT SUM(price_usd) INTO @v11a FROM js_product;
SELECT SUM(ROUND(price_orig * CASE currency_code
        WHEN 'CNY' THEN 1/7.20 WHEN 'USD' THEN 1.0 WHEN 'EUR' THEN 1.08
        WHEN 'JPY' THEN 0.0067 WHEN 'HKD' THEN 1/7.80 WHEN 'GBP' THEN 1.27 END, 2)) INTO @v11b FROM js_product;
CALL js_v('V11_多币种USD归一一致', IF(ABS(@v11a-@v11b) < 1,1,0), CONCAT('sum_usd=',@v11a,' sum_orig*rate=',@v11b));
 
-- V12 数据量稳定（幂等）
SELECT COUNT(*) INTO @v12 FROM js_product;
CALL js_v('V12_数据量稳定100000', IF(@v12=@total_rows,1,0), CONCAT('产品行数=',@v12));
 
-- 汇总
SELECT check_id, passed, detail FROM js_demo_result ORDER BY check_id;
 
-- ============================================================================
-- §13  结论：MySQL 9.0 JSON/XML 半结构化处理要点
-- ============================================================================
SELECT '§13  JSON/XML 半结构化处理结论' AS `-- 第13章 --`;
SELECT '1) JSON 是原生类型，支持 ->> / JSON_EXTRACT / JSON_TABLE / JSON_SET 等完整函数族。' AS `结论1`;
SELECT '2) JSON 列不能直接建二级索引；抽热点标量到「生成列」再建索引，热路径才不会全 JSON 扫描。' AS `结论2`;
SELECT '3) JSON 数组包含查询用「多值索引」+ MEMBER OF，可走索引；JSON_SEARCH 是全扫描，万亿级禁用。' AS `结论3`;
SELECT '4) JSON_SCHEMA_VALID 在入库前校验结构/类型，拦截脏数据（carat 数字/ cert 字符串）。' AS `结论4`;
SELECT '5) JSON_TABLE 把半结构化数组炸成关系行，是「JSON→关系」分析的正道（替代应用层循环）。' AS `结论5`;
SELECT '6) ⚠️ MySQL 9.0 无 XML 类型、无 XMLTABLE(1064)；但 EXTRACTVALUE(XPath) 仍在，仅抽单值、无集合/无索引；XML 重负载仍应 ETL 转 JSON/关系化。' AS `结论6`;
SELECT '7) 万亿级：高频聚合字段不要塞 JSON；抽生成列或侧表，JSON 只存真正半结构化、低频访问的部分。' AS `结论7`;
SELECT '8) 时区用 TIMESTAMPADD(偏移) 生成列（CONVERT_TZ 本机失效）；多币种只 SUM(price_usd) 归一结算。' AS `结论8`;
 
SELECT '① JSON 存半结构化；② 热点抽生成列+索引；③ 数组用多值索引；④ 入库前 JSON_SCHEMA_VALID；⑤ XML 存TEXT走ETL；' AS `清单`;