mysql - 批处理脚本将 CSV 文件循环到 MySQL 中

Question

如何循环遍历目录中的文件(它们都是CSV文件)，然后使用mysql -u root登录mysql(为了简单起见，没有密码)并将它们全部添加到 mysql 表？这是快速将文件加载到 mysql 的查询:

LOAD DATA LOCAL INFILE "C:/prog/mydb_load_Cards_chunks.dump" INTO TABLE vcc2.tmp_cards CHARACTER SET latin1
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;

如何在登录 mysql 时对目录中的每个文件执行此操作？

Answer 1

尝试:

文件:load_csv.sh(-r-x--------用户用户load_csv.sh*)

#!/bin/bash

for current_csv in *.csv
do
mysql --login-path=myconnection -e "USE test" -e "
  LOAD DATA LOCAL INFILE '$current_csv'
  INTO TABLE temp_table
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '\"'
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES
  (DATE, TIME);"
done

参见4.6.6 mysql_config_editor — MySQL Configuration Utility .

文件:0.csv

DATE,TIME
"2000-01-01","00:00:00"
"2001-01-01","12:00:00"
"2002-01-01","23:00:00"

文件:1.csv

DATE,TIME
"2003-01-01","23:00:00"
"2004-01-01","00:00:00"
"2005-01-01","12:00:00"

文件:2.csv

DATE,TIME
"2006-01-01","12:00:00"
"2007-01-01","23:00:00"
"2008-01-01","00:00:00"

MySQL 命令行:

mysql> CREATE DATABASE IF NOT EXISTS `test`;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> USE `test`;
Database changed

mysql> DROP TABLE IF EXISTS `temp_table`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `temp_table` (
    ->   `id` SERIAL,
    ->   `DATE` DATE,
    ->   `TIME` TIME
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> \! bash
$ ./load_csv.sh
$ exit
exit

mysql> SELECT `id`, `DATE`, `TIME`
    -> FROM `temp_table`;
+----+------------+----------+
| id | DATE       | TIME     |
+----+------------+----------+
|  1 | 2000-01-01 | 00:00:00 |
|  2 | 2001-01-01 | 12:00:00 |
|  3 | 2002-01-01 | 23:00:00 |
|  4 | 2003-01-01 | 23:00:00 |
|  5 | 2004-01-01 | 00:00:00 |
|  6 | 2005-01-01 | 12:00:00 |
|  7 | 2006-01-01 | 12:00:00 |
|  8 | 2007-01-01 | 23:00:00 |
|  9 | 2008-01-01 | 00:00:00 |
+----+------------+----------+
9 rows in set (0.00 sec)