在真實場景中，遇到一種狀況，就是假設你的訂單詳情表裏的商品詳情字段，裏面的合同信息須要調整，而項目經理給了一份正確excel文檔給你，裏面指明瞭哪一些訂單號對應哪一些合同編號，訂單詳情表是沒有合同編號字段的，只有ID，訂單號，商品詳情，購買數量，單價，商品ID等字段，而具體的商品詳情字段值爲sql
{"prodSpecId":2323211,"prodId":23232,"prodNo":"010641","prodName":"格力A","brandName":"格力","itemId":"18139","areaCode":"0","specName":"1匹","specValueName":"[{\"parentSpecNo\":163,\"parentSpecName\":\"規格\",\"specNo\":2,\"specName\":\"1匹\",\"specIndex\":0}]","oldPrice":1000.00,"nowPrice":1109.38,"untaxOriginalPrice":884.955752,"untaxPresentPrice":981.752212,"supplierPrice":1109.38,"catalog":"裝卸/通信/安防/通用/空調製冷/廣播影像/檢測維修設備>空調/製冷設備>分體空調","catalogCode":"1144","quantity":1,"picPath":"http://30.33.56.67//pic/product//20192191550565720074.png","contacts":[{"id":null,"supplierId":28,"contactsName":"空調","contactsPhone":"03189898989","contactsMail":"zhanglei@cs.com"}],"stock":9999,"rate":0.1300,"status":1,"serviceProd":-1,"productUUID":"5c9ecec3c58b464c92016e75a4e9f1d2","supplierSubOrderNo":null,"supplierSubOrderStatus":null,"serviceProducts":[{"prodId":7335,"productNo":"007212","catalogId":1144,"supplierId":28,"productName":"不鏽鋼支架 5P 海爾專用 （全國可供）","quantity":1,"imgPath":"http://pic.colipu.com/pmspic/ItemPicture\\5\\49\\348\\322952\\Original\\322952_1029088.jpg","specName":"[{\"parentSpecNo\":172,\"parentSpecName\":\"model\",\"specNo\":2,\"specName\":\"5P\",\"specIndex\":0}]","nowPrice":56.88,"price":88.90,"untaxOriginalPrice":78.672566,"untaxPresentPrice":50.336283,"randomNumber":"15990b07b3864d94b7e27bb738447618","specOptId":7290,"rate":0.1300},{"prodId":7337,"productNo":"007214","catalogId":1144,"supplierId":28,"productName":"pvc水管 海爾專用 （全國可供）","quantity":1,"imgPath":"http://pic.colipu.com/pmspic/ItemPicture\\5\\49\\348\\322950\\Original\\322950_1029086.jpg","specName":"[{\"parentSpecNo\":172,\"parentSpecName\":\"model\",\"specNo\":2,\"specName\":\"model\",\"specIndex\":0}]","nowPrice":52.50,"price":52.50,"untaxOriginalPrice":46.460177,"untaxPresentPrice":46.460177,"randomNumber":"b76a0e1cf2b244ac861902b0c047c190","specOptId":7292,"rate":0.1300}],"pmsContractVo":{"contractNo":"CSN-MYG-1******3013","pmsContractNo":"402881eb67826581016782979fa88765","contractName":"空調供貨、安*****務框架合做協議","contractType":"空調及製冷設備","contarctAmountRmb":10000000.00,"targetId":"402881eb6782658101678297a0f80b89","contractId":null,"classifyNo":"1081001","targetName":null,"untaxPrice":0.000000,"rate":0.130,"untaxAmount":0.000000,"quantity":0,"supplierNo":"913702127837350374","supplierOracleNo":null,"taxPrice":1980.000,"taxAmount":0.000},"prodTotalPrice":1109.38,"sku":null,"prodTotalUntaxPrice":981.76}
這時候你該如何處理？
第一步：把excel表導入到新建的臨時表（A）裏面。
第二步：訂單詳情表增長合同編號字段。
第三步：訂單詳情表跟臨時表A關聯更新。框架
UPDATE t_ord_detail detail INNER JOIN A temp ON detail.order_no = temp.order_no SET detail.contract_no = temp.contract_no;
第四步：替換訂單詳情表裏的合同編號，條件是contract_no不爲空
dom
UPDATE t_ord_detail SET prod_info = REPLACE(prod_info,SUBSTRING(
prod_info,
LOCATE('"contractNo":', prod_info),
LOCATE(',"pmsContractNo":', prod_info) - LOCATE('"contractNo":', prod_info)
),CONCAT('"contractNo":"',contract_no,'"')) where contract_no is not null;