iT邦幫忙

1

MYSQL IMPORT 亂碼 ON OSX,LINUX

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'

尚未有邦友留言

立即登入留言