mysql - 使用 mysqlimport 命令从命令行将 csv 文件导入 mysql

Question

如何使用 mysqlimport 命令从命令行将 csv 文件导入 mysql，这样就不用进入 mysql。？

我正在尝试使用这个答案 here

从命令行导入csv文件到mysql

root@678cf3cd1587:/home# mysqlimport --columns='head -n 1 discounts.csv' --ignore-lines=1 temp discounts.csv -u root -p
Enter password:
mysqlimport: Error: 1064, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-n 1 discounts.csv)' at line 1, when using table: discounts
root@678cf3cd1587:/home#

但我无法让它工作 谁能告诉我做错了什么？

这是我的文件的样子

root@678cf3cd1587:/var/lib/mysql-files# cat discounts.csv
id,title,expired_date,amount
1,"1Spring Break ",20140401,20
2,"2Spring Break ",20140401,20
3,"3Spring Break ",20140401,20
4,"3Spring Break ",20140401,20
5,"3Spring Break ",20140401,20
6,"3Spring Break ",20140401,20
7,"3Spring Break ",20140401,20

这就是我的 mysql 的样子:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| temp               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use temp
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql>

注意 我在 docker 容器中运行 mysql，如果需要我可以提供更多详细信息这个问题有重复，但我认为我的问题很具体。

编辑1

我尝试了不同的单引号 ' 双引号 "和反引号 `。尽管反引号给出了不同的错误。这有帮助吗？

root@678cf3cd1587:/home# mysqlimport --columns='head -n 1 discounts.csv' --ignore-lines=1 temp discounts.csv -u root -p
Enter password:
mysqlimport: Error: 1064, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-n 1 discounts.csv)' at line 1, when using table: discounts
root@678cf3cd1587:/home# mysqlimport --columns="head -n 1 discounts.csv" --ignore-lines=1 temp discounts.csv -u root -p
Enter password:
mysqlimport: Error: 1064, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-n 1 discounts.csv)' at line 1, when using table: discounts
root@678cf3cd1587:/home# mysqlimport --columns=`head -n 1 discounts.csv` --ignore-lines=1 temp discounts.csv -u root -p
Enter password:
mysqlimport: Error: 1049 Unknown database 'date,amount'

EDIT2

如果我在没有创建表的情况下执行此操作，我会得到 Error: 1146, Table 'temp.discounts' doesn't exist, when using table: discounts

root@678cf3cd1587:/var/lib/mysql-files# mysqlimport temp /var/lib/mysql-files/discounts.csv -u root -p --columns=`head -n 1 /var/lib/mysql-files/discounts.csv` --ignore-lines=1
Enter password:
mysqlimport: Error: 1146, Table 'temp.discounts' doesn't exist, when using table: discounts

如果我然后创建表然后再试一次

mysql> CREATE TABLE discounts (
    ->   id INT NOT NULL AUTO_INCREMENT,
    ->   title VARCHAR(255) NOT NULL,
    ->   expired_date DATE NOT NULL,
    ->   amount DECIMAL(10,2) NULL,
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql>
mysql>
mysql> show tables;
+----------------+
| Tables_in_temp |
+----------------+
| discounts      |
+----------------+
1 row in set (0.00 sec)

mysql> select * from discounts;
Empty set (0.00 sec)

我收到以下错误 Error: 1265, Data truncated for column 'id' at row 1, when using table: discounts

root@678cf3cd1587:/var/lib/mysql-files# mysqlimport temp /var/lib/mysql-files/discounts.csv -u root -p --columns=`head -n 1 /var/lib/mysql-files/discounts.csv` --ignore-lines=1
Enter password:
mysqlimport: Error: 1265, Data truncated for column 'id' at row 1, when using table: discounts
root@678cf3cd1587:/var/lib/mysql-files#

我正在使用 /var/lib/mysql-files/discounts.csv明确是因为，如果我理解正确的话，这是我可以使用 secure_file_priv 从中导入文件的唯一目录。

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.01 sec)

mysql>

EDIT3

我让这个工作 mysqlimport --columns="`head -n 1 /var/lib/mysql-files/discounts.csv`" --ignore-lines=1 temp /var/lib/mysql-files/discounts.csv -u root -p --fields-terminated-by=',' --fields-optionally-enclosed-by='"'

root@678cf3cd1587:/home# mysqlimport --columns="`head -n 1 /var/lib/mysql-files/discounts.csv`" --ignore-lines=1 temp /var/lib/mysql-files/discounts.csv -u root -p --fields-terminated-by=','  --fields-optionally-enclosed-by='"'
Enter password:
temp.discounts: Records: 7  Deleted: 0  Skipped: 0  Warnings: 0
root@678cf3cd1587:/home#
root@678cf3cd1587:/home# cat /var/lib/mysql-files/discounts.csv
id,title,expired_date,amount
1,"1Spring Break ",20140401,20
2,"2Spring Break ",20140401,20
3,"3Spring Break ",20140401,20
4,"3Spring Break ",20140401,20
5,"3Spring Break ",20140401,20
6,"3Spring Break ",20140401,20
7,"3Spring Break ",20140401,20

但我必须先在我的 sql 中创建表，如上所述。

CREATE TABLE discounts (
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  expired_date DATE NOT NULL,
  amount DECIMAL(10,2) NULL,
  PRIMARY KEY (id)
);

我还必须使用这个文件 /var/lib/mysql-files/discounts.csv作为secure_file_priv据我所知，已设置为只能从此目录加载文件。

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.01 sec)

Answer 1

看起来“过期日期”中的空格可能让您感到困惑。在命令行上，您需要引用 --columns 参数，同时保留反引号。

mysqlimport --columns="`head -n 1 discounts.csv`" \
--ignore-lines=1 temp discounts.csv -u root -p

表格列名实际上是带空格的“过期日期”吗？如果是这样，您需要在带有反引号的 csv 文件中说明这一点。

id,title,`expired date`,amount

此外，如果文件格式与默认格式不同，请确保指定文件格式

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\' LINES TERMINATED BY '\n' STARTING BY ''

在你的情况下，

--fields-terminated-by=','  --fields-optionally-enclosed-by='"'