MySql语句备忘 JSON截取

INSERT INTO DONGZ_TMP_AuditCreditAmount (OrderId, AuditCreditAmount,UpdateTime) SELECT SUBSTRING(OrderDetail,
LOCATE('"OrderId":',OrderDetail)+11,

LOCATE(',',OrderDetail,LOCATE('"OrderId":',OrderDetail))-1-(LOCATE('"OrderId":',OrderDetail)+11)) AS OrderId
,
SUBSTRING(OrderDetail,LOCATE('"AuditCreditAmount":',OrderDetail)+20,IF(LOCATE(',',OrderDetail,11) =0,LOCATE('}',OrderDetail,2),LOCATE(',',OrderDetail,LOCATE('"AuditCreditAmount":',OrderDetail)))-(LOCATE('"AuditCreditAmount":',OrderDetail)+20)) AS AuditCreditAmount
,SUBSTRING(OrderDetail,
LOCATE('"UpdateTime":',OrderDetail)+14,

LOCATE(',',OrderDetail,LOCATE('"UpdateTime":',OrderDetail))-1-(LOCATE('"UpdateTime":',OrderDetail)+20)) AS UpdateTime
FROM tbs where FlowName ='总部初审'

转载于:https://www.cnblogs.com/dongzhou/p/8464376.html