IMPORT csv

LOAD DATA INFILE 'test.csv' INTO TABLE test_table(col_name);

error1 1290

```
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
```

* MAC OSX
1. `cd /Library/LaunchDaemons`
2. `sudo vim com.oracle.oss.mysql.mysqld.plist`
*  在 programArgument 的地方 加上
`<string>--secure-file-priv=/</string>`
> 可以成功匯入了

* UBUNTU
1. 不改設定檔，將準備匯入的檔案放在預設目錄下，查看預設目錄：`SHOW VARIABLES LIKE "secure_file_priv"; `
![](https://ithelp.ithome.com.tw/upload/images/20210530/20102155TBXBFrFoZL.png)
2. `sudo cp test.csv /var/lib/mysql-files/`
> 可以成功匯入了

error2 1064

```
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ....
```
錯誤原因是我有個欄位名稱使用到了保留字 `group` 原：`LOAD DATA INFILE 'tan_list.csv' INTO TABLE tan_list(area,group,tan);` 。將保留字加反引號，修正如下：
```
LOAD DATA INFILE 'sarawak_tan_list.csv' INTO TABLE tan_list(area,`group`,tan);
```就可以了

error3

```
ERROR 1261 (01000): Row 1 doesn't contain data for all columns
```
1. `show variables like 'sql_mode';`
![https://ithelp.ithome.com.tw/upload/images/20210707/201021559VxGouidVm.png](https://ithelp.ithome.com.tw/upload/images/20210707/201021559VxGouidVm.png)
2. `set sql_mode='';`

成功匯入後，結果是亂碼

1. 看編碼字符集 show variables like 'character%';
   
2. ALTER DATABASE testdb CHARACTER SET utf8 COLLATE utf8_general_ci;
3. ALTER TABLE testtable CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

匯入 sql

• 匯入data
  mysql -u sarah -p00000 testdb < /Users/sarahcheng/Documents/ren_i.sql

ERROR 1273 (HY000) at line 25: Unknown collation: ‘utf8mb4_0900_ai_ci’

• 直接修改要匯入的 sql文件

1. 將utf8mb4_0900_ai_ci取代為utf8_general_ci（vim指令 :s/utf8mb4_0900_ai_ci/utf8_general_ci）
2. 將utf8mb4取代為utf8（vim指令:s/utf8mb4/urf8）

匯出 data

1. 在 mysql 外面 mysqldump -u sarah -p00000 tainanRen_i tan_list > /Users/sarahcheng/Documents/ren_i_tans.sql
2. 在 mysql 裡面下指令

SELECT * FROM counters WHERE created_at <'2021-06-02 21:00:00'
INTO OUTFILE '/var/lib/mysql-files/outputFile.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ''
LINES TERMINATED BY 'n'