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 variablemac版本没有my.cnf文件，需要自己写：
查找mysql文件所在
root1@guodeMacBook-Pro /etc % mdfind -name mysqlroot1@guodeMacBook-Pro /etc % cd /usr/local root1@guodeMacBook-Pro local % lsbingomysqlmysql-8.0.30-macos12-arm64mysql-8.0.32-macos13-arm64root1@guodeMacBook-Pro local % ls -latotal 0drwxr-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 bindrwxr-xr-x 17 root wheel 544 8 2 2022 golrwxr-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-arm64drwxr-xr-x 13 root wheel 416 2 13 18:35 mysql-8.0.32-macos13-arm64root1@guodeMacBook-Pro local % cd mysql-8.0.32-macos13-arm64 root1@guodeMacBook-Pro mysql-8.0.32-macos13-arm64 % lsLICENSEbindocskeyringmansupport-filesREADMEdataincludelibshareroot1@guodeMacBook-Pro mysql-8.0.32-macos13-arm64 % pwd创建my.cnf
root1@guodeMacBook-Pro mysql-8.0.32-macos13-arm64 % sudo touch my.cnfroot1@guodeMacBook-Pro mysql-8.0.32-macos13-arm64 % lsLICENSEbindocskeyringmanshareREADMEdataincludelibmy.cnfsupport-filesroot1@guodeMacBook-Pro mysql-8.0.32-macos13-arm64 % 添加内容，保存退出
[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 设置mysql配置文件
打开系统偏好设置
苹果系统偏好设置----->找到MySQL---->点击Configuration---->勾选configuration并将my.cnf路径添加上去---->重启mysql
查看本地是否可以导入数据
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)]
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\nignore 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-infileEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 14Server version: 8.0.32 MySQL Community Server - GPLCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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)来源地址：https://blog.csdn.net/Gjiujiu/article/details/129435759
![[命令行]Mysql 导入 excel 文件](/upload/202205/01/hgpmqvemj12.jpg)
