mysql命令导入导出csv文件

• 准备csv文件
  

• 创建表
  

• 使用ssh工具登录虚拟机服务器，将user.csv上传到服务器，导入mysql

[root@master data]# pwd
/home/data
[root@master data]# ls
user.csv

# 开始导入
[root@master data]# mysqlimport -h127.0.0.1 -P3306 -uroot -p123456 db_test --fields-terminated-by=',' user.csv --columns='id,name,sex,birthday,grades,address' --ignore-lines=1 --local
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
db_test.user: Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

• 查看导入的数据
  

• 导入时报错

[root@master data]# mysqlimport -h127.0.0.1 -P3306 -uroot -p123456 db_test --fields-terminated-by=',' user.csv --columns='id,name,sex,birthday,grades,address' --local
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
mysqlimport: Error: 3948, Loading local data is disabled; this must be enabled on both the client and server sides, when using table: user

# 解决方案：登录mysql，将local_infile设置为NO
[root@master data]# mysql -u root -p
Enter password: 
mysql> show global variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set global local_infile=1;
Query OK, 0 rows affected (0.00 sec)

• 使用ssh工具登录虚拟机服务器，登录mysql，导出为csv

# 报错如下，指定目录没有权限
mysql> select * from user into outfile '/home/data/user.csv' FIELDS TERMINATED BY ',';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

# 查看有权限的目录
mysql> show global variables like '%secure_file_priv%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)

# 再次测试，成功
mysql> select * from student into outfile '/var/lib/mysql-files/student.csv' FIELDS TERMINATED BY ',';
Query OK, 4 rows affected (0.00 sec)

• 本地安装mysql，使用本地mysql.exe将本地csv文件导入远程mysql

# 远程表中没有数据

# 本地进入user.csv所在文件夹，右键打开Git Bash
ychen@DESKTOP-49HGBQ1 MINGW64 /c/work
$ pwd
/c/work
ychen@DESKTOP-49HGBQ1 MINGW64 /c/work
$ ls
 user.csv

# 执行如下代码，导入成功
ychen@DESKTOP-49HGBQ1 MINGW64 /c/work
$ mysqlimport -h192.168.128.78 -P3306 -uroot -p123456 db_test --fields-terminated-by=',' user.csv --columns='id,name,sex,birthday,grades,address' --ignore-lines=1 --local
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
db_test.user: Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

• 使用cmd会报错

查看详情

# 报错
C:\work>mysqlimport -h192.168.128.78 -P3306 -uroot -p123456 db_test --fields-terminated-by=',' user.csv --columns='id,username,sex,birthday,grades,address' --ignore-lines=1 --local
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
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 ''id,username,sex,birthday,grades,address')' at line 1, when using table: user

# 测试1
C:\work>mysqlimport -h192.168.128.78 -P3306 -uroot -p123456 db_test --fields-terminated-by=',' user.csv --columns=id,name,sex,birthday,grades,address --ignore-lines=1 --local
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
db_test.user: Records: 6  Deleted: 0  Skipped: 0  Warnings: 36

# 测试2
C:\work>mysqlimport -h192.168.128.78 -P3306 -uroot -p123456 db_test --fields-terminated-by=',' user.csv --columns=id --columns=sex --ignore-lines=1 --local
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
db_test.user: Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

• LOAD DATA LOCAL导入csv

# 创建表，表中没有数据
CREATE TABLE `user`  (
  `id` int NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `birthday` timestamp NULL DEFAULT NULL,
  `grades` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

# 将user.csv文件上传到服务器的/var/lib/mysql-files/目录
# 使用ssh工具登录到虚拟机的服务器，登录mysql
[root@master mysql-files]# mysql -u root -p --local-infile
Enter password: 

# 切换库
mysql> use db_test;

# 导入成功
mysql> LOAD DATA LOCAL INFILE '/var/lib/mysql-files/user.csv'
    -> INTO TABLE user
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 ROWS;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

# 复制如下
LOAD DATA LOCAL INFILE '/var/lib/mysql-files/user.csv'
INTO TABLE user
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

• 导入时报错

# ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides.
# local_infile设置为ON
show global variables like 'local_infile';
set global local_infile=1;

# ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
# 登录时加上--local-infile
mysql -u root -p --local-infile

• 使用本地mysql.exe将本地csv文件导入远程mysql

# 远程表中没有数据
# 打开cmd，登录mysql
C:\work>mysql -h 192.168.128.78 -P 3306 -u root -p --local-infile
Enter password: ******

# 切换库
mysql> use db_test
Database changed

# 执行导入
mysql> LOAD DATA LOCAL INFILE 'C:\\work\\user.csv'
    -> INTO TABLE user
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 ROWS;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

# 复制代码
LOAD DATA LOCAL INFILE 'C:\\work\\user.csv'
INTO TABLE user
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;