这篇文章上次修改于 766 天前，可能其部分内容已经发生变化，如有疑问可询问作者。

1. 导入 csv 文件

1.1. 设置 root 密码

cd /usr/local/mysql/bin/
sudo ./mysqld_safe --skip-grant-tables
alter user 'root'@'localhost' identified by '密码';
FLUSH PRIVILEGES;

1.2. 修改 my.cnf

在 [mysqld] 下添加 secure-file-priv = "" 后重启 mysql

[mysqld]

...

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
secure-file-priv = ""

1.3. 连接 mysql

 mysql --local-infile=1 -u root -p

1.4. 导入数据

LOAD DATA local INFILE '/Users/test/Documents/test.csv'
INTO TABLE train_ad
FIELDS TERMINATED BY ',' #分隔符
ENCLOSED BY '"' #结尾符
LINES TERMINATED BY '\n'#换行
IGNORE 1 ROWS;

2. 导出数据到 csv 文件

2.1. 查看导出文件的目录

show variables like 'datadir';

显示：

+---------------+-------------------------------------------------+
| Variable_name | Value                                           |
+---------------+-------------------------------------------------+
| datadir       | /usr/local/mysql/data/                          |
+---------------+-------------------------------------------------+
1 row in set (0.04 sec)

2.2. 导出数据

select * into outfile '/usr/local/mysql-8.0.20-macos10.15-x86_64/data/train.csv' 
fields terminated by ',' 
ENCLOSED BY '"'
lines terminated by '\n' 
from (select 'user_id','time', 'creative_id', 'click_times', 'ad_id', 'product_id', 
        'product_category', 'advertiser_id', 'industry', 'age', 'gender'  
      union 
      select user_id, time, creative_id, click_times, ad_id, product_id, 
        product_category, advertiser_id, industry, age, gender from train_tmp2 
      where product_id is not NULL 
      and industry is not NULL) b;