首先贴一段json数据
[{"image":"/Public/upload/manager/product/5ccffa865df0e.jpg","title":"发生的水电费膏 产后","point":"5","number":"1","g_id":"1104455699976421377","gb_id":"1104455699976421376","c_id":"45","e_id":"1104419134373888000"}][{"image":"/Public/upload/manager/product/5cce8c9cc073a.jpg","title":"秒儿舒 ","point":"5","number":"1","g_id":"1104062843994505217","gb_id":"1104062843994505216","c_id":"39","e_id":"1104057638015991808"}][{"image":"\/Public\/upload\/manager\/product\/5ccfe8c20e7fd.jpg","title":"万色份膏 备孕","point":"5","number":"1","c_id":"45","g_id":"1104436622687993856","gb_id":"1104436622683799552"}]
mysql也有正则函数regexp
假设需求就是匹配title字段带“膏”的数据
刚开始我以为跟php或者js正则一样的,写了个正则:
("title":")*膏.*?(","point":)
在网上正则测试通过,这样确实是可以满足,突然感觉自己闲的慌吧!mysql我只用匹配出一条数据就够了
我觉得应该也可以,发现居然mysql没有返回数据。蜜汁尴尬
百度了一下mysql 正则发现可使用的操作符并没有那么多
mysql数据如下
现在查询title包含“膏”的
结合数据前有title标题后有point
所以给出的
goods_image regexp '["title":].*膏.*[","point":]'
完整的:
select * from 表名 where json字段 regexp '[前面固定的内容].*匹配的内容.*[后面固定格式的内容]'