SELECT INTO OUTFILE,LOAD DATA

[yehlu]

http://giantdorks.org/alain/shell-scrip ... umn-names/

代碼: 選擇全部

#!/bin/bash
 
# Script to export all tables from a MySQL DB using the SELECT INTO 
# OUTFILE method and to add table column names to dump files that this 
# method otherwise doesn't allow. Requires superuser privileges to 
# copy export files out of the MySQL data dir.
 
DB=MyDB # change me!
export_dir="/path/to/dir" # change me!
myopts="--skip-column-names -Be" # add username/password if not using ~/.my.cnf
 
err_exit()
{
 echo -e 1>&2
 exit 1
}
 
mysql_data_dir=$(mysql $myopts "show variables like 'datadir'" | awk '{sub(/\/$/,"");print$NF}')
 
if ! (mysqlshow $DB 1>/dev/null); then
  echo ERROR: unable to access database
  exit 1
fi
 
if ! [ -w $export_dir ]; then
  echo ERROR: export dir is not writable
  exit 1 
fi
 
if ! (sudo touch $mysql_data_dir/$DB/test 2>/dev/null); then
  echo ERROR: this script will need sudo access to
  echo move exported files out of mysql data dir. 
  echo Come back when you get sudo, son.
  exit 1
else
  sudo rm $mysql_data_dir/$DB/test
fi
 
ExportTable()
{
 mysql $DB $myopts "
   SELECT * INTO OUTFILE '$T.psv' 
   FIELDS TERMINATED BY '|' 
   LINES TERMINATED BY '\n' 
   FROM $T
 "
}
 
AddColumnHeaders()
{
 cols=$(mysql $DB $myopts "desc $T" | awk '{print$1}' | tr '\n' '|' | sed 's/|$//')
 sed -i "1i \
 $cols
 " $export_dir/$T.psv
}
 
mysql $DB $myopts "show tables" | 
while read T; do 
  echo exporting $T
  ExportTable || err_exit
  sudo mv $mysql_data_dir/$DB/$T.psv $export_dir/ || err_exit
  AddColumnHeaders || err_exit
done

[yehlu]

http://stackoverflow.com/questions/2213 ... file-mysql

代碼: 選擇全部

#!/bin/bash
for f in *.csv
do
/opt/lampp/bin/mysql -e "use test" -e "
      LOAD DATA LOCAL INFILE '$f'
      INTO TABLE temp_table 
      FIELDS TERMINATED BY ',' 
      OPTIONALLY ENCLOSED BY '\"' 
      LINES TERMINATED BY '\n' 
      IGNORE 1 LINES 
      (DATE, TIME, SITE_NAME, SITE_IP, TOTAL_TALKTIME, 
           EDGE_UL_BYTES, EDGE_DL_BYTES);"
done