Invalid JSON text in argument 1 to function json_extract: “The document is empty.“

Mysql 5.7 Data truncation: Invalid JSON text in argument 1 to function json_extract: "The document is empty." at position 0

Fordring_

9450人浏览 · 2022-04-13 10:16:43

Fordring_ · 2022-04-13 10:16:43 发布

Mysql 5.7 刷数据遇见Data truncation: Invalid JSON text in argument 1 to function json_extract: "The document is empty." at position 0

需要将表字段json串中的某个值刷到新列时，用了以下sql:

update order a, 
(SELECT id, extend_info ->> '$.orgId' as orgId
   FROM order where extend_info is not null and extend_info != '') b
set a.org_id = b.orgId
WHERE a.id = b.id
  and b.orgId != '';

由于有历史数据为空，单独执行子查询正常，放入update语句报错，改为下面语句，子查询增加limit之后正常执行

update order a,

(SELECT id, extend_info ->> '$.orgId' as orgId FROM order

where extend_info is not null and extend_info != '' limit 50000) b

set a.org_id = b.orgId

WHERE a.id = b.id

and b.orgId != '';

没有limit分页会默认采用join的方式，直接从update开始执行；加limit后强制增加临时表，执行计划更改，优先执行子查询存放到临时表中，再执行update