mysql 操作JSON (5.7以上) JSON_EXTRACT

select JSON_EXTRACT('{"time":"2024-01-02 08:08:08","confirmStatus":0,"name":"zizhen"}', '$.time') as a

JSON_EXTRACT

SELECT
	KeyId,
	TradeTime,
	Remark,
	CustomerCostCenterCode
FROM
	` Tab`
WHERE  JSON_VALID(Remark)  AND
	`KeyId` IN (
		'180606020227654185',
		'191001211524654198',
		'20160512144219375328'
	)
 AND JSON_EXTRACT (CustomerCostCenterCode, '$.name') REGEXP 'Alice|zizhen' 

AND JSON_EXTRACT (Remark, '$.name') REGEXP 'Alice|zizhen'  


;

JSON_VALID

SELECT
        KeyId,
        TradeTime,
        Remark,
        CustomerCostCenterCode,
        JSON_EXTRACT (
                CustomerCostCenterCode,
                '$.time'
        ) AS  确认时间
FROM
        `Tab`
WHERE
        JSON_VALID (CustomerCostCenterCode)
AND `KeyId` IN (
        '180606020227654185',
        '191001211524654198',
        '20160512144219375328'
)
AND JSON_EXTRACT (
        CustomerCostCenterCode,
        '$.name'
) REGEXP 'zizhen'
AND JSON_EXTRACT (
        CustomerCostCenterCode,
        '$.confirmStatus'
) REGEXP '0'
AND JSON_EXTRACT (
        CustomerCostCenterCode,
        '$.time'
) > '2024-01-01 08:08:08'
AND JSON_EXTRACT (
        CustomerCostCenterCode,
        '$.time'
) < '2024-01-03 08:08:08'