mysql - 兼容 mysql 插入的 Perl 格式数组元素

Question

想要对 $ALL_Total_Attach_ENB_data[0] 执行以下操作

1. 从 $ALL_Total_Attach_ENB_data[0] 中存储的 CSV 数据中选择特定字符串。
2. 用单引号括起来
3. 将该数据作为输入传递给 mysql insert into 语句。

我已经完成了第 1 步，但不确定如何有效地完成第 2 步和第 3 步。可能一个衬垫会有所帮助。

Actual data $ALL_Total_Attach_ENB_data[0]= ",ITEM,=,ENB,INTERVAL,=,HOUR,DURATION,=,2013-10-16,11:00,~,2013-10-16,12:00,ENB,TYPE,ATTEMPT,ACCEPT,COMB_ACPT,CTXT_RSP,SUCCESS,HSS_FAIL,ERR_IMEI,ERR_MS,ERR_SVC,GW_FAIL,GW_TO,ENB_FAIL,ENB_TO,UE_FAIL,ESM_FAIL,SM_FAIL,SM_TO,S1_REL,RSC_FULL,SYS_FLT,HSS_TO,AUC_FAIL,AUC_TO,EIR_FAIL,EIR_TO,MS_TO,COLLIS,UNK_PLMN,APN_FAIL,ODB_BAR,DNS_FAIL,DNS_TO,CMP_TO,S1_REL1,ERR_ETC,CANCEL,DTCH,S_RATIO,C_RATIO,SET_TIME,AVG_CPS,PEAK_CPS,SS_RATIO,TOTAL,INIT,162797,150716,0,150134,149450,0,1,0,0,4458,0,10,0,525,245,0,0,39,0,1,0,51,0,0,0,314,18,0,5950,0,1025,0,605,2,2,0,80,92.58,91.80,583.20,45,48,97.25,COMB,17219,4601,0,4571,4560,0,0,0,0,1075,0,0,0,25,27,7,0,2,0,0,0,243,0,0,0,103,1,300,10691,0,163,0,7,0,0,0,12,26.72,26.48,584.81,5,6,93.72,EMG,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.00,0.00,0.00,0,0,0.00";

my @tmp = split(',', $ALL_Total_Attach_ENB_data[0]);
$val1=join(',' , @tmp[9,10,12,13,60 .. 103]);

$db ="4ghourly";
$user = "root";
$pass = "root";
$host="localhost";
$port="3307";

$dbh = DBI->connect("DBI:mysql:$db:$host:$port", $user, $pass);
$query = "INSERT INTO attach (startDate,startTime,endDate,endTime,type,attempt,accept,comb_acpt,ctxt_rsp,success,hss_fail,err_imei,err_ms,err_svc,gw_fail,gw_to,enb_fail,enb_to,ue_fail,esm_fail,sm_fail,sm_to,s1_rel,rsc_full,sys_flt,hss_to,auc_fail,auc_to,eir_fail,eir_to,ms_to,collis,unk_plmn,apn_fail,odb_bar,dns_fail,dns_to,cmp_to,s1_rel1,err_etc,cancel,dtch,s_ratio,c_ratio,set_time,avg_cps,peak_cps,ss_ratio) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

$sqlQuery  = $dbh->prepare($query) or die "Can't prepare $query: $dbh->errstr\n";

$rv = $sqlQuery->execute('2013-10-30','11:00','2013-10-30','12:00','INIT','162797','150716','0','150134','149450','0','1','0','0','4458','0','10','0','525','245','0','0','39','0','1','0','51','0','0','0','314','18','0','5950','0','1025','0','605','2','2','0','80','92.58','91.8','583.2','45','48','97.25') or die "can't execute the query: $sqlQuery->errstr";

Answer 1

取而代之的是:

$val1=join(',' , @tmp[9,10,12,13,60 .. 103]);

您可以将该数组切片直接传递给 execute():

$rv = $sqlQuery->execute( @tmp[9,10,12,13,60 .. 103] ) 
    or die "can't execute the query: $sqlQuery->errstr"

不需要用引号将它们括起来。占位符将负责正确引用内容。