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

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文件，需要自己写：

查找mysql文件所在

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

root1@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