加载没有双引号的数据

Question

我正在加载如下所示的数据(URL删除)

"shortUrl"|"url"|"date"|"ipAddr"|"userAgent"|"referrer"
"http://clk2.it/uHamBX"|"http://..."|"2017-05-21T16:14:49.590Z"|"97.33.66.11"|"Mozilla/5.0 (iPhone; CPU iPhone OS 6_0_1 like Mac OS X) AppleWebKit/536.26 (KHTML, like Gecko) Version/6.0 Mobile/10A523 Safari/8536.25"|""

就像这样

mysql -h <host> -u <user> --password=<password> --local-infile <db> -e "LOAD DATA LOCAL INFILE '/data/<file>.csv' INTO TABLE <table> FIELDS TERMINATED BY '|' IGNORE 1 LINES"

它工作得很好，但问题是数据是用像so这样的双引号加载的(URL删除了)

shortUrl, url, date, ipAddr, userAgent, referrer
"http://clk2.it/CPJK2c", "http://...", "2017-05-18T17:12:22.278Z", "96.8.170.62", "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_1) AppleWebKit/601.2.4 (KHTML, like Gecko) Version/9.0.1 Safari/601.2.4 facebookexternalhit/1.1 Facebot Twitterbot/1.0", ""
"http://clk2.it/CPJK2c", "http://...", "2017-05-18T17:12:45.500Z", "96.8.170.62", "Mozilla/5.0 (iPhone; CPU iPhone OS 10_3_1 like Mac OS X) AppleWebKit/603.1.30 (KHTML, like Gecko) Version/10.0 Mobile/14E304 Safari/602.1", ""
"http://clk2.it/CPJK2c", "http://...", "2017-05-18T17:13:44.015Z", "96.8.170.62", "Mozilla/5.0 (iPhone; CPU iPhone OS 10_3_1 like Mac OS X) AppleWebKit/603.1.30 (KHTML, like Gecko) Version/10.0 Mobile/14E304 Safari/602.1", ""

有没有一种不用双引号来加载数据的方法？

Answer 1

这对我有用。注意在'\"'中进行筛选。

cat >data.csv

"shortUrl"|"url"|"date"|"ipAddr"|"userAgent"|"referrer"
"http://clk2.it/uHamBX"|"http://..."|"2017-05-21T16:14:49.590Z"|"97.33.66.11"|"Mozilla/5.0 (iPhone; CPU iPhone OS 6_0_1 like Mac OS X) AppleWebKit/536.26 (KHTML, like Gecko) Version/6.0 Mobile/10A523 Safari/8536.25"|""
^D

mysql -u <user> --password=<password> --local-infile <db> -e "LOAD DATA LOCAL INFILE '/path/to/data.csv' INTO TABLE t1 FIELDS TERMINATED BY '|' enclosed by '\"' IGNORE 1 LINES"

mysql:在命令行界面上使用密码进行警告可能是不安全的。

mysql -u <user> -p <db> -e 'select * from t1'

| shorturl              | url        | date1                    | ip          | useragent                                                                                                                               | referrer |
+-----------------------+------------+--------------------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------+----------+
| http://clk2.it/uHamBX | http://... | 2017-05-21T16:14:49.590Z | 97.33.66.11 | Mozilla/5.0 (iPhone; CPU iPhone OS 6_0_1 like Mac OS X) AppleWebKit/536.26 (KHTML, like Gecko) Version/6.0 Mobile/10A523 Safari/8536.25 |          |
+-----------------------+------------+--------------------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------+----------+

Answer 2

用围住的，用的

FIELDS
    TERMINATED BY '|'
    ENCLOSED BY '"'
    ESCAPED BY ''