☰

bash shell 脚本中的 mysql 语句不起作用/语法错误

tag-icon • tag-icon command-line bash scripts mysql csv
bash shell 脚本中的 mysql 语句不起作用/语法错误

希望有人能帮助我！我不知道这个脚本（Bash / Ubuntu 12.04 / Mysql / XAMPP 1.8.3）出了什么问题。尝试读取带有 csv 文件名列表（449-R11、449-R12 ..）的输入文件，以便执行一些 sql 语句....

    #!/bin/bash

     OUTPath="/home/bono/RD/BV-OUT/tmp/distinct_"
     OUTPath1="/home/bono/RD/BV-OUT/tmp/load_"
     INPath="/home/bono/RD/BV-OUT/"

     while read line do
     RD="$line"
     bv_out_distinct=$OUTPath$RD
     bv_out_load_new_bvs=$OUTPath1$RD
     RDDATEI=$INPath$RD

     ###MYSQLs:
     /opt/lampp/bin/mysql --local-infile --user=root RD << EOF 
     LOAD DATA LOCAL INFILE $RDDATEI 
       INTO TABLE bv_tmp_all FIELDS TERMINATED BY ';';
     SELECT * FROM bv_tmp_all GROUP BY BAN 
       INTO OUTFILE $bv_out_distinct FIELDS TERMINATED BY ';' ;
     LOAD DATA LOCAL INFILE $bv_out_distinct 
       INTO TABLE bv_tmp_distinct FIELDS TERMINATED BY ';';
     UPDATE bv, bv_tmp_distinct 
       SET bv.RD=bv_tmp_distinct.RD,bv.LEV21=bv_tmp_distinct.LEV21 
       WHERE bv.BAN=bv_tmp_distinct.BAN;
     SELECT * FROM bv_tmp_distinct 
       WHERE BAN NOT IN (SELECT BAN FROM bv) 
       INTO OUTFILE $bv_out_load_new_bvs FIELDS TERMINATED BY ';';
     LOAD DATA LOCAL INFILE $bv_out_load_new_bvs 
       INTO TABLE bv FIELDS TERMINATED BY ';';
     TRUNCATE TABLE bv_tmp_distinct;
     TRUNCATE TABLE bv_tmp_all;
     EOF
     ##### EOSQL
     done < /home/bono/RD/BV-OUT/allto468

我尝试了不同的版本 :(，但都没有用...我得到了这个 mysql 输出：

bono@bono-S3210SH:~/RD/procs$ ./load_RD_out_tmp

ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; 
  check the manual that corresponds to your MyS INTO TABLE bv_tmp_all FIEL' 
  at line 1 to use near '"LOAD DATA LOCAL INFILE /home/bono/RD/BV-OUT/449-R11
ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; 
  check the manual that corresponds to your MyS INTO TABLE bv_tmp_all FIEL' 
  at line 1 to use near '"LOAD DATA LOCAL INFILE /home/bono/RD/BV-OUT/449-R12

答案1

您的文件名应该用引号引起来 - MySQL 将它们视为字符串，因此您的 MySQL 块应该是：

LOAD DATA LOCAL INFILE '$RDDATEI' 
  INTO TABLE bv_tmp_all FIELDS TERMINATED BY ';';
SELECT * FROM bv_tmp_all GROUP BY BAN 
  INTO OUTFILE '$bv_out_distinct' FIELDS TERMINATED BY ';';
LOAD DATA LOCAL INFILE '$bv_out_distinct'
  INTO TABLE bv_tmp_distinct FIELDS TERMINATED BY ';';
UPDATE bv, bv_tmp_distinct 
  SET bv.RD=bv_tmp_distinct.RD,bv.LEV21=bv_tmp_distinct.LEV21 
  WHERE bv.BAN=bv_tmp_distinct.BAN;
SELECT * FROM bv_tmp_distinct 
  WHERE BAN NOT IN (SELECT BAN FROM bv) 
  INTO OUTFILE '$bv_out_load_new_bvs' FIELDS TERMINATED BY ';';
LOAD DATA LOCAL INFILE '$bv_out_load_new_bvs' 
  INTO TABLE bv FIELDS TERMINATED BY ';';
TRUNCATE TABLE bv_tmp_distinct;
TRUNCATE TABLE bv_tmp_all;

相关内容