SQL _MySQL for string processing

SQL bitsCN.com for string processing

An SQL statement for string processing

A table has a json field. for example, props contains the following characters:

{"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]} 

Now we need to check the values of the 56th attributes.

SQL statement:

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": 'I N props)-position (', "56":' in props)-6) as signed integer)> 0; if you try to find the content of each database in shell distribution and send the content in the form of an email: The above SQL statement is saved to the SQL file. The program is as follows :#! /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 </root/action_shell/$ last_date.data run as follows: ===============================s1.cqkr.huayugame.com ===================== ============= passportId level num h_count 102833976 78 2400 2.0000 102835789 84 4800 4.0000 72 102837173 12000 10.0000 102837399 51600 43.0000 102837452 3600 3.0000 102839736 48000 40.0000 102775934 60 50400 42.0000 ============================ s2.cqkr.huayugame.com ================ ========================== passportId level num h_count 102928945 76 2400 2.0000 102966633 63 72000 60.0000 64 102968267 12000 10.0000 67 102923416 1.0000 102970589 57 48000 40.0000 102971607 28800 24.0000 102885000 69 12000 10.0000 102975125 64 24000 20.0000 102915079 69 10800 9.0000 102977473 42000 65 35.0000 ============ ======= s3.cqkr.huayugame.com ================================== = passportId level num h_count 103214239 65 6000 5.0000 103048907 1200 1.0000 103166121 61 2400 2.0000 102906791 64 1200 1.0000 103259853 1200 1.0000 103268837 1200 1.0000 103270689 3600 3.0000

BitsCN.com