MYSQL 替换字段里JSON字符串的图片属性为另一个字段的值-优快云博客

-- 订单详情表增加商品主图字段
ALTER TABLE t_ord_detail ADD COLUMN prod_img_path varchar(1000) COMMENT '商品主图';

-- 根据订单详情表商品ID关联图片表里的主图，把先有的主图URL赋给订单详情表商品主图字段
UPDATE t_ord_detail ordDetail INNER JOIN(SELECT * FROM (
SELECT detail.prod_id,pic.path from t_ord_detail detail INNER JOIN t_pd_picture pic ON detail.prod_id = pic.product_id
where pic.picture_pd_type = 0)subTemplate)newTemplate on ordDetail.prod_id = newTemplate.prod_id
SET ordDetail.prod_img_path = newTemplate.path;

-- 替换prod_info里面的picPath对应的值为prod_img_path字段的值
UPDATE t_ord_detail SET prod_info = REPLACE(prod_info,SUBSTRING(
           prod_info,
           LOCATE('"picPath":', prod_info),
           LOCATE(',"contacts":', prod_info) - LOCATE('"picPath":', prod_info)
       ),CONCAT('"picPath":"',prod_img_path,'"'));

-- 订单详情表删除商品主图字段
alter table t_ord_detail drop column prod_img_path;

转载于:https://my.oschina.net/u/3155476/blog/3081446