mysql查询结果，需要解析json字符串。

Question

mysql数据库表中有一字段soldInfo，存储如下格式的信息：
[{"optime":"2012-08-31 09:30:38","uid":"4028818a3a0ba9cc013a0bbb42d0000f","rid":"4028818a3a0ba9cc013a0bb9e284000a","controltype":"in","location":"","mfname":"温江百事可乐公司","uname":"库管","rname":"库管"},{"optime":"2012-09-31 09:31:16","uid":"4028818a3a0ba9cc013a0bbb42d0000f","rid":"4028818a3a0ba9cc013a0bb9e284000a","controltype":"out","location":"","mfname":"温江百事可乐公司","uname":"库管","rname":"库管"},{"optime":"2012-10-31 09:31:49","uid":"4028818a3a0ba9cc013a0bbb42d0000f","rid":"4028818a3a0ba9cc013a0bba348c000c","controltype":"in","location":"","mfname":"温江百事可乐公司","uname":"库管","rname":"贴码员"}]
现在，传入参数uid,rid,controltype进行查询，我想让查询结果如以下格式：
{"optime":"2012-08-31 09:30:38","uid":"4028818a3a0ba9cc013a0bbb42d0000f","rid":"4028818a3a0ba9cc013a0bb9e284000a","controltype":"in","location":"","mfname":"温江百事可乐公司","uname":"库管","rname":"库管"}
{"optime":"2012-09-31 09:31:16","uid":"4028818a3a0ba9cc013a0bbb42d0000f","rid":"4028818a3a0ba9cc013a0bb9e284000a","controltype":"out","location":"","mfname":"温江百事可乐公司","uname":"库管","rname":"库管"}
请问下各位大侠sql语句应该怎么写呢？跪求！

Answer 1

详细说明，格式是否固定SET @AA='[{"optime":"2012-08-31 09:30:38","uid":"4028818a3a0ba9cc013a0bbb42d0000f","rid":"4028818a3a0ba9cc013a0bb9e284000a","controltype":"in","location":"","mfname":"温江百事可乐公司","uname":"库管","rname":"库管"},{"optime":"2012-09-31 09:31:16","uid":"4028818a3a0ba9cc013a0bbb42d0000f","rid":"4028818a3a0ba9cc013a0bb9e284000a","controltype":"out","location":"","mfname":"温江百事可乐公司","uname":"库管","rname":"库管"},{"optime":"2012-10-31 09:31:49","uid":"4028818a3a0ba9cc013a0bbb42d0000f","rid":"4028818a3a0ba9cc013a0bba348c000c","controltype":"in","location":"","mfname":"温江百事可乐公司","uname":"库管","rname":"贴码员"}]
';
SET @AQ=SUBSTRING_INDEX(@AA,'},',1);
SELECT @AQ;
SET @AQ=SUBSTRING_INDEX(SUBSTRING_INDEX(@AA,'},',2),'},',-1);
SELECT @AQ;

Answer 2

每一个“{}”之间的格式是固定的，我的意思是要根据掺入的rid，uid来查找json数据中相同的数据，而不是仅仅取前2个。
有没有比如正则表达之类的办法，可以查到数据？

Answer 3

SET @mysubstr:=CONCAT('"uid":"',uid,'","rid":"',rid,'","controltype":"',controltype,'"');
SELECT CONCAT(RIGHT(LEFT(soldInfo,LOCATE(@mysubstr,soldInfo)-1),LOCATE('{',REVERSE(LEFT(soldInfo,LOCATE(@mysubstr,soldInfo)-1)))),LEFT(SUBSTR(soldInfo,LOCATE(@mysubstr,soldInfo)),LOCATE('}',SUBSTR(soldInfo,LOCATE(@mysubstr,soldInfo))))) 
FROM `tablename` 
WHERE LOCATE(@mysubstr,soldInfo)>0; 
把红色字段替换成你的试试。

Answer 4

rid和uid是传入的参数，对应json数据中rid及uid相应的值，我是想要将这个字符串进行解析，解析出和传入参数条件相对应的字符串。
比如我传入uid是4028818a3a0ba9cc013a0bbb42d0000f，rid是4028818a3a0ba9cc013a0bb9e284000a，那么我就想要解析出以下两条数据：
{"optime":"2012-08-31 09:30:38","uid":"4028818a3a0ba9cc013a0bbb42d0000f","rid":"4028818a3a0ba9cc013a0bb9e284000a","controltype":"in","location":"","mfname":"温江百事可乐公司","uname":"库管","rname":"库管"}
{"optime":"2012-09-31 09:31:16","uid":"4028818a3a0ba9cc013a0bbb42d0000f","rid":"4028818a3a0ba9cc013a0bb9e284000a","controltype":"out","location":"","mfname":"温江百事可乐公司","uname":"库管","rname":"库管"}
同样，如果我传入了controltype参数值为in，那么就只解析出第一条数据。
我试了试你给的答案，是只能解析出一条数据。但是去掉controltype参数，仍然只能解析出一条数据，有什么办法能实现我上面说的需求吗？

Answer 5

这个要拆分字符串才行：LSB1：字段ID，值1、2、310000SELECT * FROM (
SELECT SUBSTRING_INDEX(
SUBSTRING_INDEX(a1.bb,'}',b.id),'}',-1) AS bb
 FROM t2 a1 LEFT JOIN lsb1 b
ON (LENGTH(a1.bb)-LENGTH(REPLACE(a1.bb,'}','')))+1>=b.id) aa
WHERE INSTR(bb,'4028818a3a0ba9cc013a0bbb42d0000f')>0 AND INSTR(bb,'4028818a3a0ba9cc013a0bb9e284000a')>0
AND INSTR(bb,'"controltype":"in"')>0

Answer 6

sigh, 如果你这是postgres数据库，那么一切就显得是那么简单。iihero=# create extension hstore;
CREATE EXTENSION
iihero=#
iihero=# select 'a=>1,b=>2'::hstore;
       hstore
--------------------
 "a"=>"1", "b"=>"2"
(1 row)iihero=# insert into tjson values(1, 'a=>1, b=>2, c=>3'::hstore);
INSERT 0 1
iihero=# insert into tjson values(2, 'a=>2, b=>3, c=>4'::hstore);
INSERT 0 1
iihero=# select * from tjson where col2 @>'a=>1';
 id |             col2
----+------------------------------
  1 | "a"=>"1", "b"=>"2", "c"=>"3"
(1 row)iihero=# select * from tjson where col2 @>'a=>1' and col2 @>'b=>2';
 id |             col2
----+------------------------------
  1 | "a"=>"1", "b"=>"2", "c"=>"3"
(1 row)所不同的，只是语法表现形式的不同。还可以充分利用索引。

Answer 7

可惜它是mysql，而且。您写的，我真没看懂，不好意思了。数据库菜鸟，还请大家多多帮忙！

Answer 8

你所要做的，是要实现一个存储过程，对这个类json串进行解析再匹配。