一个对字符串处理的SQL_MySQL

bitsCN.com

一个对字符串处理的sql

 

某表中有一个json格式的字段 如props里有以下字符 

 

{"1":36,"2":54,"3":160,"4":8,"5":767680,"6":12,"7":1,"8":8, "13":"14,17,21,20,23,22,25,24,29,28,31,30,34,35,32,33,39,96,42,43,108,40,41,107,44,116,115,114,112,125,124","14":{"15":"0","13":"3","14":"0","11":"3","12":"3","3":"56","2":"38","1":"56","10":"4","7":"40","6":"55","5":"57","4":"37", "9":"6","8":"13"},"15":[{"8":[-1,288516249174946623,-1,288516249174945177,288516249174978057,288516249174977954,-1,288516249174938096,-1,7,0]},{"9":[0,-1,-1,288516249174938096,288516249174946623,-1,-1,-1,0,3,-1]},{"11":[-1,288516249174938096,0,-1,-1,0,-1,-1,-1,1,-1]}, {"10":[-1,288516249174938096,-1,-1,0,-1,-1,0,-1,1,-1]},{"12":[-1,288516249174938096,-1,0,-1,0,-1,-1,-1,1,-1]}, {"13":[288516249174938096,-1,0,-1,-1,-1,-1,-1,0,0,-1]}],"16":160,"18":{"0":1381766801601,"1":1381823994910}, "19":8,"20":767671,"21":[1030990,2,-1,2,0,3,0,4,0,5,0,6,0,3,0,0], "22":{"9012":"1001,1381762836328,1,1,0,false,[false;0;0]","9011":"1001,1381707248623,1,1,0,false, [false;0;0]","2021":"1001,1379343644952,1,1,0,false, [false;0;0]","2020":"1001,1379317060249,1,1,0,false,[false;0;0]","9010":"1001,1381634617324,1,1,0,false, [false;0;0]","2022":"1001,1379469893656,1,1,0,false,[false;0;0]","1008":"1001,1379516486264,1,1,0,false, [false;0;0]","1111":"1001,1380020062745,1,1,0,false,[false;0;0]","1010":"1001,1379765171241,1,1,0,false, [false;0;0]","1011":"1001,1379853879986,1,1,0,false,[false;0;0]","9000":"1001,1381543622820,1,1,0,false,[false;0;0]"}, "23":{"3":"[288516249174954963,288516249174942369,288516249174955163,288516249174954795,288516249174947676]","1":"1"},"24":{"0":1030990,"1":2,"2":0,"3":0},"25":[180,35,3,0,1,8,5,4,4,3,10],"26":402,"27":{"0":1381782823890,"1":[]}, "28":{"2":[49,37,25,18,2,1],"1":4,"0":1381767129747},"29":15,"30":8,"31":"","32":"","33":0,"34":[10020,1], "35":1,"36":[0,0,1030977,""],"37":0,"38":0,"39":[[288516249174945651,288516249174942150,288516249174942809, 288516249174954842,288516249174953399,288516249174944948,288516249174946420,288516249174953771,288516249174950819,288516249174943615]],"40":[],"41":0,"42":0,"43":0,"44":0,"45":0,"46":[22,1,1,0,[[1,7,0,[[[1,1381795409928,false,true,3], 81063479]]],[2,7,0,[[[5,1381763877328,false,true,1],3072]]],[4,4,1,[[[3,1381767432593,false,true,1],0]]],[5,7,0,[[[2,1381 795422562,false,false,2],994861454]]]],[5]],"47":[10,0,true],"48":0,"49":[-1,0,0,0],"50":0,"51":[] "52":[16,2,0],"53":7,"54":2,"55":[],"56":50400,"57":0,"58":"","60":[0],"61":[68,69,70,71],"62":{"throwSeq":1},"63":[],"64":{"1":{}, "2":{}},"65":[],"66":[0],"67":{"1":[],"3":0,"4":0,"5":0,"2":[]},"68":0,"69":50,"70":1381762800263,"71":[[],50,1381821984680]} 

 

 

现在要查出第56个属性的值 

 

即有如何 SQL： 

 

select passportId,level, cast(substring(props,position(',"56":' in props)+6,position(',"57":' in props)-position(',"56":' in props)-6) as signed integer) as num,cast(substring(props,position(',"56":' in props)+6,position(',"57":' in props)-position(',"56":' in props)-6) as signed integer)/1200 h_count from t_character_info where  cast(substring(props,position(',"56":' in props)+6,position(',"57":' in props)-position(',"56":' in props)-6) as signed integer) >0; 如果在shell中分布试查出各个数据库的内容 并把内容邮件形式发出: 即把以上sql 存入到sql文件里 程序如下: #! /bin/sh last_date=$(date -d last-day +%Y_%m_%d); cat /dev/null > $last_date.data for s_ip in $(awk -F= '{print $0}' /root/action_shell/kr_ip.txt); do         echo ===============================start to do task===================================================;         s=$(echo $s_ip|awk -F= '{print $1}');         ip=$(echo $s_ip|awk -F= '{print $2}');         echo '====================='$s'.cqkr.huayugame.com==============================================' >>/root/action_shell/$last_date.data         out1=$(mysql -h$ip -uxxx -pxxx -Dgen -e 'source query_hounter.sql' >> /root/action_shell/$last_date.data);        echo $out1;         sleep 10         echo ===============================send mail successfully===================================================; done /bin/mail -s 'from games.asia team tech '_$last_date  mingming.wang@renren-inc.com   games.asia<p> </p><br>						bitsCN.com