MySQL/MariaDB中如何按JSON字段createdOn范围查询及删除数据？

你之前的SQL错误在于不能把LIKE和BETWEEN这样混用，而且直接用字符串匹配JSON字段非常不可靠（比如JSON键值顺序变化就会失效）。正确的做法是利用数据库的JSON解析函数，提取createdOn字段后再做范围判断。

下面分主流数据库给出实现方案：

一、查询符合条件的记录 #

MySQL（5.7+支持JSON类型） #

SELECT * 
FROM data 
WHERE JSON_UNQUOTE(JSON_EXTRACT(body, '$.createdOn')) BETWEEN '2018-10-05T00:00:00.000+0000' AND '2018-10-10T00:00:00.000+0000';

或者用更简洁的语法：

SELECT * 
FROM data 
WHERE body->>'$.createdOn' BETWEEN '2018-10-05T00:00:00.000+0000' AND '2018-10-10T00:00:00.000+0000';

PostgreSQL（9.3+支持JSON） #

SELECT * 
FROM data 
WHERE (body->>'createdOn')::timestamp WITH TIME ZONE BETWEEN '2018-10-05T00:00:00.000+0000' AND '2018-10-10T00:00:00.000+0000';

SQL Server（2016+支持JSON） #

SELECT * 
FROM data 
WHERE JSON_VALUE(body, '$.createdOn') BETWEEN '2018-10-05T00:00:00.000+0000' AND '2018-10-10T00:00:00.000+0000';

二、删除符合条件的记录 #

直接把上面的SELECT替换成DELETE即可，操作前务必先执行SELECT确认目标数据，避免误删：

MySQL #

DELETE FROM data 
WHERE body->>'$.createdOn' BETWEEN '2018-10-05T00:00:00.000+0000' AND '2018-10-10T00:00:00.000+0000';

PostgreSQL #

DELETE FROM data 
WHERE (body->>'createdOn')::timestamp WITH TIME ZONE BETWEEN '2018-10-05T00:00:00.000+0000' AND '2018-10-10T00:00:00.000+0000';

SQL Server #

DELETE FROM data 
WHERE JSON_VALUE(body, '$.createdOn') BETWEEN '2018-10-05T00:00:00.000+0000' AND '2018-10-10T00:00:00.000+0000';

额外说明 #

• 如果你的body列是普通字符串类型（不是数据库原生JSON类型），上面的函数依然可以正常解析，只要字符串格式是合法JSON。
• 若需要先导出符合条件的记录再删除，可以先将SELECT结果导出为文件，再执行DELETE语句。

内容的提问来源于stack exchange，提问作者nee nee