菜鸟perl，教你写程序解析mysql中的json

#!C:/Dwimperl/perl -w

#use strict;

#use warnings;

use DBI;

use JSON;

use Data::Dumper;

use Encode;

## 注意事项：

##1、这个pl写的还是有问题，不是很智能。

##2、json里面有几个key，对应要调整 38-60行，之前 @nr? 的个数

##3、需要在mysql中创建对应的表，对应的列才行，不是直接使用。先要把表结构创建好。

##4、values的值如果也是数级，有几组就不用考虑了，循环时已经智能了。

##5、要修改对应的SQL语句，对应相应的字段。



my $user = "abcde";

my $passwd = "abcdefgh";

my $dsn="DBI:mysql:test:xxx.xxx.xxx.xxx:3306";

my $pid = 99;

my $id = 99;

my $drug = "denver";



my $dbh=DBI->connect($dsn, $user, $passwd) or die "can't connect to database";

$dbh->do("SET character_set_client='utf8'");

$dbh->do("SET character_set_connection='utf8'");

$dbh->do("SET character_set_results='utf8'");



#my $sth = $dbh->prepare("select id,pid,drug from test.tets_json where  drug not like '%null%';");

my $sth = $dbh->prepare("select id,pid,drug from test.tets_json where id=30330;");

$sth->execute;



$sth->bind_columns(\$id,\$pid, \$drug);

print "The results are: OK！\n\n";

while ( $sth->fetch() ) {

	my $json=new JSON;

	my $str = $drug;

	my $obj = $json->decode($str); ###将json的unicode解码 decode，并给对象$obj

	my %hash_json;

	my @keys = keys  %{$obj};  ## 取出 hash中已经解码的keys值

	my $rn=0;



	my @nr0=@{$obj->{$keys[0]}};  ## 取出hash对应的keys值对应的values  

	my @nr1=@{$obj->{$keys[1]}};

	my @nr2=@{$obj->{$keys[2]}};

	my @nr3=@{$obj->{$keys[3]}};

	my @nr4=@{$obj->{$keys[4]}};

	my @nr5=@{$obj->{$keys[5]}};

	my @nr6=@{$obj->{$keys[6]}};

	my @nr7=@{$obj->{$keys[7]}};  ## 一共有几个@nrx这要取决于json里面有几个key值，

	for ($rn .. $#nr0) {       ##这个是循环每一个keys对应的values（这个json里面,values对应的值又是一个数据），循环取出第一个值

	my $str00=$nr0[$rn];

	my $str10=$nr1[$rn];

	my $str20=$nr2[$rn];

	my $str30=$nr3[$rn];

	my $str40=$nr4[$rn];

	my $str50=$nr5[$rn];

	my $str60=$nr6[$rn];

	my $str70=$nr7[$rn];



  	my $sql = qq{INSERT INTO test.tets_json02 

  		(id,pid,$keys[0],$keys[1],$keys[2],$keys[3],$keys[4],$keys[5],$keys[6],$keys[7]) 

  		VALUES(?,?,?,?,?,?,?,?,?,?)};    ### 取要注意这里的SQL，这里面$keys[0],$keys[1] 等，是对应的json里面的key有多少个，不是固定的

  	my $sth1 = $dbh->prepare($sql);  

  	$sth1->execute($id,$pid,$str00,$str10,$str20,$str30,$str40,$str50,$str60,$str70); 

  	$rn +=1; 

	}

}

$sth->finish();



$dbh->disconnect;

有问题请留言：

给你搞几行测试数据：

29444	62509	{"common_name":[""],"domestic":["0"],"dosage":[""],"unit":["0"],"frequency":["0"],"day":[""],"day_describe":[""],"way":["0"]}

29546	62531	{"common_name":["\u5229\u59a5\u6614","GA101","Adcetris(SGN-35)","ofatumumab","zavalin","\u4f9d\u9c81\u66ff\u5c3c","\u5176\u5b83\u5355\u6297"],"domestic":["0","0","0","0","0","0","0"],"dosage":["600","","","","","",""],"unit":["2","0","0","0","0","0","0"],"frequency":["0","0","0","0","0","0","0"],"day":["1","","","","","",""],"day_describe":["1","","","","","",""],"way":["6","0","0","0","0","0","0"]}

30333	62674	{"common_name":["\u5730\u585e\u7c73\u677e","\u5f02\u73af\u78f7\u9170\u80fa","\u987a\u94c2","\u4f9d\u6258\u6cca\u82f7"],"domestic":["0","0","0","0"],"dosage":["30","200","30","100"],"unit":["2","2","2","2"],"frequency":["5","5","5","5"],"day":["4","2","3","2"],"day_describe":["1-4","1-2","1-3","1-2"],"way":["2","7","7","7"]}

30334	62674	{"common_name":["MTX","Arc","\u5730\u585e\u7c73\u677e"],"domestic":["0","0","0"],"dosage":["10","","5"],"unit":["2","0","2"],"frequency":["0","0","0"],"day":["1","","1"],"day_describe":["","",""],"way":["8","0","8"]}