一、MYSQL解析JSON字符串
1、5.7版本以上:使用 JSON_EXTRACT和REPLACE 内置函数。
实例:解析出JSON字符串{"contactName":"段XX","contactJobTitle":"待确认","contactMobile":"131XXXXXXX"}中contactName字段的属性值。
SQL:
SELECTREPLACE(
JSON_EXTRACT (
'{"contactName":"段XX","contactJobTitle":"待确认","contactMobile":"131XXXXXXX"}',
'$.contactName'
),
'"',
''
)
FROM
DUAL ;
2、5.7版本以下:使用REPLACE、SUBSTRING和LOCATE内置函数。
实例:解析出 nns_buy_order 数据表前10条数据, nns_request_data 字段中 nns_product_id 的属性值。
如:nns_request_data 属性值:{"contactName":"段XX","nns_product_id":"mgtv_yb","contactMobile":"131XXXXXXX"}
SQL:
SELECT
REPLACE(
SUBSTRING(
REPLACE(nns_request_data, '}', ','),
LOCATE(
'nns_product_id":',
REPLACE(nns_request_data, '}', ',')
) + CHAR_LENGTH('nns_product_id":'),
LOCATE(
',',
REPLACE(nns_request_data, '}', ','),
LOCATE(
'nns_product_id":',
REPLACE(nns_request_data, '}', ',')
) + CHAR_LENGTH('nns_product_id":')
) - (
LOCATE(
'nns_product_id":',
REPLACE(nns_request_data, '}', ',')
) + CHAR_LENGTH('nns_product_id":')
)
),
'"',
''
) AS nns_product_id
FROM
nns_buy_order
WHERE 1=1 LIMIT 10;
二、联表UPDATE操作
1、不同表:实现从A表中某字段的属性值 更新至B数据表某字段。( 思路:联接A、B表)
格式:UPDATE B b JOIN A a ON a.`id` = b.`a_id` SET b.`xxx` = a.`yyy` WHERE 1=1 ;
实例:更细nns_product_fee表中的nns_product_fee_id字段属性值到nns_buy_order表的nns_product_fee_id字段,nns_product_fee和nns_buy_order关联字段为nns_product_id。
SQL:
UPDATE
nns_buy_order o
JOIN nns_product_fee f
ON f.`nns_product_id` = o.`nns_product_id` SET o.`nns_product_fee_id` = f.`nns_product_fee_id`
WHERE 1=1;
2、相同表:更新A表中x字段的属性值到A表的y字段,且x字段的属性值为JSON字符串,需要更新JSON字符串中z参数值给y。(思路:构建临时表T + 联接A、临时表)
格式:
UPDATE
A a
JOIN
(SELECT
id,
REPLACE(
SUBSTRING(
REPLACE(y, '}', ','),
LOCATE(
'z":',
REPLACE(y, '}', ',')
) + CHAR_LENGTH('z":'),
LOCATE(
',',
REPLACE(y, '}', ','),
LOCATE(
'z":',
REPLACE(y, '}', ',')
) + CHAR_LENGTH('z":')
) - (
LOCATE(
'z":',
REPLACE(y, '}', ',')
) + CHAR_LENGTH('z":')
)
),
'"',
''
) AS z
FROM
A
WHERE 1=1) T
ON T.id = a.id SET a.x = T.z
WHERE 1=1;
实例:更细nns_buy_order表中nns_request_data字段的属性值 nns_product_id 到nns_buy_order表的nns_product_id字段。
如:nns_request_data 属性值:{"contactName":"段XX","nns_product_id":"mgtv_yb","contactMobile":"131XXXXXXX"}
UPDATE
nns_buy_order o
JOIN
(SELECT
nns_id,
REPLACE(
SUBSTRING(
REPLACE(nns_request_data, '}', ','),
LOCATE(
'nns_product_id":',
REPLACE(nns_request_data, '}', ',')
) + CHAR_LENGTH('nns_product_id":'),
LOCATE(
',',
REPLACE(nns_request_data, '}', ','),
LOCATE(
'nns_product_id":',
REPLACE(nns_request_data, '}', ',')
) + CHAR_LENGTH('nns_product_id":')
) - (
LOCATE(
'nns_product_id":',
REPLACE(nns_request_data, '}', ',')
) + CHAR_LENGTH('nns_product_id":')
)
),
'"',
''
) AS nns_product_id
FROM
nns_buy_order
WHERE nns_product_id = ''
OR nns_product_id IS NULL) T
ON T.nns_id = o.nns_id SET o.`nns_product_id` = T.nns_product_id
WHERE o.nns_product_id = ''
OR o.nns_product_id IS NULL
3、同时更新多个关联数据表的字段属性值。(思路:联接A、B表 + 分别更新)
格式:UPDATE B b JOIN A a ON a.`id` = b.`a_id` SET b.`b_xxx` = '***',a.`a_yyy` = '***' WHERE 1=1 ;
实例:更细nns_product_fee表中的nns_product_fee_id字段属性值为 111且更新nns_buy_order表的nns_product_fee_id字段为222,nns_product_fee和nns_buy_order关联字段为nns_product_id。
SQL:
UPDATE
nns_buy_order o
JOIN nns_product_fee f
ON f.`nns_product_id` = o.`nns_product_id` SET o.`nns_product_fee_id` = '111',
f.`nns_product_fee_id` = '222'
WHERE 1 = 1 ;