mysql全局替换脚本_MYSQL 替换字段值数据SQL脚本

在MYSQL数据中，往往一些表字段的值需要手工的去替换维护。如果要替换某一个字段里面的值，假设字段是prod_no_info，值为

{"prodSpecId":22637,"prodId":23197,"prodNo":"010606","prodName":"Double a A4／80G 复印纸 500张／包 5包／箱 (单位:箱) 白色","brandName":"DOUBLE A","itemId":"22636","areaCode":null,"specName":"A4/80G","specValueName":"[{\"parentSpecNo\":172,\"parentSpecName\":\"model\",\"specNo\":22637,\"specName\":\"A4/80G\",\"specIndex\":null}]","oldPrice":250.00,"nowPrice":155.00,"untaxOriginalPrice":215.517241,"untaxPresentPrice":133.620690,"supplierPrice":155.00,"catalog":"办公用品/钟表>办公用品>办公用纸","catalogCode":"1031","quantity":7,"picPath":"http://20.76.5.34\pic\product\20191301548784819323.jpg","contacts":[{"id":null,"supplierId":23,"contactsName":"下默默","contactsPhone":"020-2828286","contactsMail":"moyangkun@richinfo.cn"}],"stock":99835,"rate":0.1600,"status":1,"serviceProd":0,"productUUID":"9eb8a030a0e348c9a3b531ca346ee96f","supplierSubOrderNo":null,"supplierSubOrderStatus":null,"serviceProducts":null,"pmsContractVo":{"contractNo":"CSN-MYG-1******0041","pmsContractNo":"402881f067ee76da0167ee97de1001fc","contractName":"电子商城采*****协议","contractType":null,"contarctAmountRmb":10000000.00,"targetId":"402881f067ee76da0167ee97e8d20215","contractId":null,"classifyNo":"1451006","targetName":null,"untaxPrice":0.000000,"rate":0.130,"untaxAmount":0.000000,"quantity":0,"supplierNo":"9133020025438442XD","supplierOracleNo":null,"taxPrice":51.100,"taxAmount":0.000},"prodTotalPrice":1085.00,"sku":"deli_100031378PCS","prodTotalUntaxPrice":935.34}

假设我要替换

"picPath":"http://20.76.5.34\pic\product\20191301548784819323.jpg" 图片路径中的\转移字符为/，那么SQL脚本如下，这里where条件我是根据我这边的需求去更新，具体的按需调整。。

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)

),replace(SUBSTRING(

prod_info,

LOCATE('"picPath":', prod_info),

LOCATE(',"contacts":', prod_info) - LOCATE('"picPath":', prod_info)

),'\\','/')) where prod_info like '%colipu%';