【mysql】CSV文件导入mysql（命令）

最新推荐文章于 2024-06-15 16:09:53 发布

Gjiujiu

最新推荐文章于 2024-06-15 16:09:53 发布

阅读量5k

点赞数 2

文章标签： mysql 数据库 linux

本文链接：https://blog.csdn.net/Gjiujiu/article/details/129435759

版权

CSV文件导入mysql（命令）

查看本地是否支持导入：show variables like ‘%secure%’

mysql> show variables like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_file_priv         | NULL  |
+--------------------------+-------+
2 rows in set (0.00 sec)

secure_file_priv = NULL，表示限制不能导出导入

secure_file_priv = /tmp，表示限制在/tmp目录中执行导出导入

secure_file_priv = ，表示不限制在任意目录都可导出导入

secure_file_priv是一个只读变量，不能通过set global修改

mysql> set global secure_file_priv='';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable

mac版本没有my.cnf文件，需要自己写：

1、查找mysql文件所在

root1@guodeMacBook-Pro /etc % mdfind -name mysql

root1@guodeMacBook-Pro /etc % cd /usr/local      
root1@guodeMacBook-Pro local % ls
bin				go				mysql				mysql-8.0.30-macos12-arm64	mysql-8.0.32-macos13-arm64
root1@guodeMacBook-Pro local % ls -la
total 0
drwxr-xr-x   7 root  wheel  224  2 13 18:34 .
drwxr-xr-x@ 11 root  wheel  352  2 26  2022 ..
drwxr-xr-x  22 root  wheel  704 10 16 17:09 bin
drwxr-xr-x  17 root  wheel  544  8  2  2022 go
lrwxr-xr-x   1 root  wheel   26  2 13 18:34 mysql -> mysql-8.0.32-macos13-arm64  -- 映射
drwxr-xr-x   4 root  wheel  128  2 13 18:34 mysql-8.0.30-macos12-arm64
drwxr-xr-x  13 root  wheel  416  2 13 18:35 mysql-8.0.32-macos13-arm64
root1@guodeMacBook-Pro local % cd mysql-8.0.32-macos13-arm64 
root1@guodeMacBook-Pro mysql-8.0.32-macos13-arm64 % ls
LICENSE		bin		docs		keyring		man		support-files
README		data		include		lib		share
root1@guodeMacBook-Pro mysql-8.0.32-macos13-arm64 % pwd

2、创建my.cnf

root1@guodeMacBook-Pro mysql-8.0.32-macos13-arm64 % sudo touch my.cnf
root1@guodeMacBook-Pro mysql-8.0.32-macos13-arm64 % ls
LICENSE		bin		docs		keyring		man		share
README		data		include		lib		my.cnf		support-files
root1@guodeMacBook-Pro mysql-8.0.32-macos13-arm64 %

3、添加内容，保存退出

[client]

default-character-set=utf8

[mysqld_safe]

[mysqld]

secure_file_priv=""

character-set-server=utf8


 sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

 [mysql] 

 default-character-set=utf8

4、设置mysql配置文件

打开系统偏好设置

苹果系统偏好设置----->找到MySQL---->点击Configuration---->勾选configuration并将my.cnf路径添加上去---->重启mysql

5、查看本地是否可以导入数据

mysql> show variables like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_file_priv         |       |
+--------------------------+-------+
2 rows in set (0.01 sec)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aaJZAtLx-1678411162483)(/Users/root1/Library/Application Support/typora-user-images/image-20230307195059605.png)]

6、csv导入到mysql

load data local infile '/Users/root1/File/flask/myproject/111-main/res/newbook.csv' # 文件路径
into table books             # 表名
character set utf8                 # 编码
fields terminated by ','           # 分隔符
lines terminated by '\r\n'         # 换行符，windows下是\r\n
ignore 1 lines;                    # 忽略第一行，因为表头已建好

报错：Error Code: 3948. Loading local data is disabled; this must be enabled on both the client and server sides

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-53wlP822-1678411162483)(/Users/root1/Library/Application Support/typora-user-images/image-20230307195833324.png)]

解决方法：

forLoading local data被禁用；这必须在客户端和服务器端都启用

原因：我们的这种报错是属于mysql导入数据报错：local_infile服务器变量指示能否使用load data local infile命令。该变量为OFF时，禁用客户端的load data local infile命令，只要我们将该变量设置为ON时，报错才会消失。

设置local_infile=on：show variables like ‘%local_infile’

mysql> show variables like '%local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

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

mysql> show variables like '%local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

执行csv导入mysql插入语句

报错：Error Code: 2068. LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

原因：登录mysql时加上–local-infile参数即可：mysql -uroot -p123456 --local-infile
由于我用的mysql command line client，在登录时无法选择参数，因此可以切换用户，等同于重新登录。

root1@guodeMacBook-Pro ~ % mysql -uroot -p --local-infile
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

切换数据库

mysql> use Flask_book;					-- 切换数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------------+
| Tables_in_flask_book |
+----------------------+
| book_country_num     |
| book_people_title    |
| book_presstime_num   |
| book_publisher_num   |
| book_score_num       |
| books                |
+----------------------+
6 rows in set (0.00 sec)

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


-- csv导入mysql语句
mysql> load data local infile '/Users/root1/File/flask/myproject/111-main/res/newbook.csv' 
    -> replace into table books              
    -> fields terminated by ',' 
    -> optionally enclosed by '"' 
    -> lines terminated by '\n' 
    -> ignore 1 lines;
Query OK, 247 rows affected (0.01 sec)
Records: 247  Deleted: 0  Skipped: 0  Warnings: 0

-- 查看是否插入
mysql> select * from books limit 2;
+-----------+------------------------------------------+---------+--------------+-------------------------+-------------------------+------------+-------+------+-------+--------+---------------------------------------+
| title     | link                                     | country | author       | translator              | publisher               | press_time | price | star | score | people | comment                               |
+-----------+------------------------------------------+---------+--------------+-------------------------+-------------------------+------------+-------+------+-------+--------+---------------------------------------+
| 红楼梦    | https://book.douban.com/subject/1007305/ | 清      | 曹雪芹著     |  人民文学出版社         |  人民文学出版社         |       1996 | 59.70 |    5 |   9.6 | 395299 |             | 解其中味？
| 活着      | https://book.douban.com/subject/4913064/ | 中      | 余华         |                         |  作家出版社             |       2012 | 20.00 |  4.5 |   9.4 | 758324 |                       |
+-----------+------------------------------------------+---------+--------------+-------------------------+-------------------------+------------+-------+------+-------+--------+---------------------------------------+
2 rows in set (0.00 sec)