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，在登录时无法选择参数，因此可以切换用户，等同于重新登录。文章来源：https://www.toymoban.com/news/detail-480163.html
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>
切换数据库文章来源地址https://www.toymoban.com/news/detail-480163.html
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)
到了这里，关于【mysql】CSV文件导入mysql（命令）的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
