mysql导入csv表

mysql一系列操作可以参考这里

show databases;
+--------------------+
| Database           |
+--------------------+
| 15                 |
| comments           |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
8 rows in set (2.12 sec)
mysql> use comments;
Database changed
mysql> use comments;
Database changed
mysql> load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/appInfoAll20190125.csv'
    ->  into table pinglun1
    ->  fields terminated by ','
    -> optionally enclosed by '"' escaped by '"'
    -> lines terminated by '\r\n';

如果出现下面的错误

是因为你的文件存放位置不对，mysql有保护文件的路径，也就是工作路径。

mysql> show variables like "%char%";
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | gbk                                                     |
| character_set_connection | gbk                                                     |
| character_set_database   | utf8mb4                                                 |
| character_set_filesystem | binary                                                  |
| character_set_results    | gbk                                                     |
| character_set_server     | utf8mb4                                                 |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.02 sec)

两种方法

• 把文件移到工作路径
• 在my.ini文件里做修改（如果是win10，ProgramData会被隐藏）：
  -如果想忽略任何一行导入只需加上

ignore 1 lines;