安全提取JSON值需先验证合法性：MySQL用JSON_VALID()过滤，PostgreSQL用json_col::jsonb IS NOT NULL；入库前须清洗控制字符、修复引号格式；Python中注意bytes解码；索引需与查询条件严格匹配，高频字段宜冗余为普通列。
SQL 中 JSON 字段怎么安全提取值？
直接用 JSON_EXTRACT 或 -> 操作符看似简单，但一遇到 null、格式错误或嵌套缺失字段就崩——MySQL 5.7+ 和 PostgreSQL 的 ->> 行为还不一样。关键不是“能不能取”，而是“取不到时要不要报错”。
- MySQL 默认对非法 JSON 返回 NULL，但若字段本身是 TEXT 类型存了乱码，
JSON_EXTRACT 会静默失败（不报错也不返回值），得先用 JSON_VALID() 过滤
- PostgreSQL 更严格：
-> 遇到 key 不存在返回 NULL，->> 强制转字符串，但若整个字段不是合法 JSON，查询直接报错 invalid input syntax for type json
- 实操建议：在 WHERE 条件里加
JSON_VALID(json_col)（MySQL）或用 json_col::jsonb IS NOT NULL（PostgreSQL）兜底，别等 SELECT 里炸
SELECT id, JSON_EXTRACT(data, '$.user.name') AS name
FROM events
WHERE JSON_VALID(data) AND data != ''; -- 空字符串也通不过 JSON_VALID
JSON 数据入库前怎么批量清洗？
原始数据常含控制字符（\u0000）、多余空格、单引号冒充双引号、甚至 HTML 实体，直接 INSERT 进 JSON 字段，轻则解析失败，重则被截断或注入。
- 不要用应用层 string replace 处理——比如把单引号全换双引号，可能误伤 JSON 内容里的合法单引号（如 "desc": "it's ok"）
- MySQL 8.0+ 可用
JSON_REPLACE + JSON_SET 修字段，但更稳的是用 JSON_OBJECT 重建结构；PostgreSQL 推荐先转 jsonb，它自动去重键、标准化空格、丢弃重复 key
- 常见雷区：前端传来的
"{name: 'Alice'}"（key 没双引号）不是合法 JSON，数据库不会自动修复，必须在入库前由应用或触发器拦截
JSON_EXTRACT 会静默失败（不报错也不返回值），得先用 JSON_VALID() 过滤-> 遇到 key 不存在返回 NULL，->> 强制转字符串，但若整个字段不是合法 JSON，查询直接报错 invalid input syntax for type json
JSON_VALID(json_col)（MySQL）或用 json_col::jsonb IS NOT NULL（PostgreSQL）兜底，别等 SELECT 里炸INSERT 进 JSON 字段，轻则解析失败，重则被截断或注入。
- 不要用应用层 string replace 处理——比如把单引号全换双引号，可能误伤 JSON 内容里的合法单引号（如 "desc": "it's ok"）
- MySQL 8.0+ 可用
JSON_REPLACE+JSON_SET修字段，但更稳的是用JSON_OBJECT重建结构；PostgreSQL 推荐先转jsonb，它自动去重键、标准化空格、丢弃重复 key - 常见雷区：前端传来的
"{name: 'Alice'}"（key 没双引号）不是合法 JSON，数据库不会自动修复，必须在入库前由应用或触发器拦截
示例（MySQL）：入库前用函数预检并标准化
INSERT INTO logs (data) SELECT JSON_OBJECT( 'ts', COALESCE(JSON_UNQUOTE(JSON_EXTRACT(raw, '$.ts')), NOW()), 'user_id', CAST(JSON_EXTRACT(raw, '$.userId') AS UNSIGNED) ) FROM raw_input WHERE JSON_VALID(raw);
用 Python 处理 SQL JSON 字段时，json.loads() 为什么总报错？
从数据库 fetch 出来的 data 看似是字符串，但可能是 bytes（尤其用 PyMySQL 或 psycopg2），也可能是数据库自动转成 dict（如某些 ORM 启用了 JSON 解析），混用导致 json.loads() 报 TypeError: expected string or bytes-like object。
- 先确认类型：
type(row['data']) —— 如果是 bytes，必须 decode：json.loads(row['data'].decode('utf-8'))
- 如果用 SQLAlchemy，注意
JSON 类型默认不解析，需手动 json.loads()；而 JSONB（PostgreSQL）配合 psycopg2.extras.Json 可能已转 dict
- 性能提示：高频场景别每次
json.loads()，考虑用 ujson 替代标准库，快 3–5 倍，且对 \uXXXX 转义更鲁棒
JSON 字段建索引后查询还是慢？
加了 JSON_EXTRACT(col, '$.id') 的函数索引，或 PostgreSQL 的 jsonb_path_ops，结果 EXPLAIN 显示没走索引——大概率是查询写法和索引定义不匹配。
- MySQL 要求 WHERE 条件必须和函数索引完全一致：
WHERE JSON_EXTRACT(data, '$.id') = 123 才能命中，写成 = '123' 或包一层 CAST(... AS UNSIGNED) 就失效
- PostgreSQL 的
jsonb_path_ops 索引只加速存在性查询（@>、?），不加速等值查询（->>'id' = '123'），后者得用 jsonb_ops 或单独表达式索引
- 容易漏的点：JSON 字段默认无字符集校验，如果存了 GBK 编码的乱码，索引构建阶段就出错，查不到数据还找不到原因
type(row['data']) —— 如果是 bytes，必须 decode：json.loads(row['data'].decode('utf-8'))
JSON 类型默认不解析，需手动 json.loads()；而 JSONB（PostgreSQL）配合 psycopg2.extras.Json 可能已转 dictjson.loads()，考虑用 ujson 替代标准库，快 3–5 倍，且对 \uXXXX 转义更鲁棒JSON_EXTRACT(col, '$.id') 的函数索引，或 PostgreSQL 的 jsonb_path_ops，结果 EXPLAIN 显示没走索引——大概率是查询写法和索引定义不匹配。
- MySQL 要求 WHERE 条件必须和函数索引完全一致：
WHERE JSON_EXTRACT(data, '$.id') = 123才能命中，写成= '123'或包一层CAST(... AS UNSIGNED)就失效 - PostgreSQL 的
jsonb_path_ops索引只加速存在性查询（@>、?），不加速等值查询（->>'id' = '123'），后者得用jsonb_ops或单独表达式索引 - 容易漏的点：JSON 字段默认无字符集校验，如果存了 GBK 编码的乱码，索引构建阶段就出错，查不到数据还找不到原因
真正要查得快，优先把高频查询字段冗余成普通列（如 user_id INT），JSON 字段只存非结构化扩展内容——索引不是万能解药，设计时就得想好哪些值真需要查。
