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

zhu6100441 2012-11-01 11:17:47

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语句应该怎么写呢？跪求！

...全文

19751 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

zhu6100441 2012-11-01

打赏
举报

[Quote=引用 1 楼的回复:]

详细说明，格式是否固定

SET @AA='[{"optime":"2012-08-31 09:30:38","uid":"4028818a3a0ba9cc013a0bbb42d0000f","rid":"4028818a3a0ba9cc013a0bb9e284000a","controltype":"in","location":"","mfname":"温江百事可乐公司","uname"……
[/Quote]
每一个“{}”之间的格式是固定的，我的意思是要根据掺入的rid，uid来查找json数据中相同的数据，而不是仅仅取前2个。
有没有比如正则表达之类的办法，可以查到数据？

WWWWA 2012-11-01

打赏
举报

详细说明，格式是否固定

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;

iihero 2012-11-01

打赏
举报

[Quote=引用 9 楼的回复:]

引用 8 楼的回复:

sigh, 如果你这是postgres数据库，那么一切就显得是那么简单。

iihero=# create extension hstore;
CREATE EXTENSION
iihero=#
iihero=# select 'a=>1,b=>2'::hstore;
hstore
--------------------
"a"=>"1", "b……
[/Quote]

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

zhu6100441 2012-11-01

打赏
举报

[Quote=引用 8 楼的回复:]

sigh, 如果你这是postgres数据库，那么一切就显得是那么简单。

iihero=# create extension hstore;
CREATE EXTENSION
iihero=#
iihero=# select 'a=>1,b=>2'::hstore;
hstore
--------------------
"a"=>"1", "b"=>"2"
……
[/Quote]
可惜它是mysql，而且。您写的，我真没看懂，不好意思了。数据库菜鸟，还请大家多多帮忙！

iihero 2012-11-01

打赏
举报

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)

所不同的，只是语法表现形式的不同。还可以充分利用索引。

wwwwb 2012-11-01

打赏
举报

这个要拆分字符串才行：

LSB1：字段ID，值1、2、3。。。10000

SELECT * 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

zhu6100441 2012-11-01

打赏
举报

[Quote=引用 5 楼的回复:]

字体颜色没加上。。。
我说的红色的就是字体颜色标签的部分。。。引用 4 楼的回复:
SQL code

SET @mysubstr:=CONCAT('"uid":"',uid,'","rid":"',rid,'","controltype":"',controltype,'"');
SELECT CONCAT(RIGHT(LEFT(sol……
[/Quote]
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参数，仍然只能解析出一条数据，有什么办法能实现我上面说的需求吗？

mysdzlt2007 2012-11-01

打赏
举报

字体颜色没加上。。。
我说的红色的就是字体颜色标签的部分。。。[Quote=引用 4 楼的回复:]
SQL code

SET @mysubstr:=CONCAT('"uid":"',uid,'","rid":"',rid,'","controltype":"',controltype,'"');
SELECT CONCAT(RIGHT(LEFT(sol……
[/Quote]

mysdzlt2007 2012-11-01

打赏
举报

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;

把红色字段替换成你的试试。

WWWWA 2012-11-01