ЗАГРУЗИТЬ ДАННЫЕ из файла CSV, где двойная кавычка использовалась в качестве escape-символа
У меня есть куча данных CSV, которые мне нужно загрузить в базу данных MySQL. Ну, возможно, CSV. (отредактируйте: фактически, это похоже на материал, описанный в RFC 4180)
Каждая строка представляет собой список строк в двойных кавычках, разделенных запятыми. Чтобы избежать двойных кавычек, которые появляются в значении столбца, используются двойные кавычки. Обратные слеши могут представлять себя.
Например, строка:
"", "\wave\", ""hello,"" said the vicar", "what are ""scare-quotes"" good for?", "I'm reading ""Bossypants"""
если разобрать в JSON должно быть:
[ "", "\\wave\\", "\"hello,\" said the vicar", "what are \"scare-quotes\" good for?", "I'm reading \"Bossypants\"" ]
Я пытаюсь использовать LOAD DATA читать CSV, но у меня странное поведение.
В качестве примера рассмотрим, если у меня есть простая таблица из двух столбцов
shell% mysql exampledb -e "describe person"
+-------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| ID | int(11) | YES | | NULL | |
| UID | char(255) | YES | | NULL | |
+-------+-----------+------+-----+---------+-------+
shell%
Если первая строка без заголовка моего входного файла заканчивается "":
shell% cat temp-1.csv
"ID","UID"
"9",""
"0","Steve the Pirate"
"1","\Alpha"
"2","Hoban ""Wash"" Washburne"
"3","Pastor Veal"
"4","Tucker"
"10",""
"5","Simon"
"6","Sonny"
"7","Wat\"
Я могу загрузить каждую строку без заголовка, но первую:
mysql> DELETE FROM person;
Query OK, 0 rows affected (0.00 sec)
mysql> LOAD DATA
LOCAL INFILE 'temp-1.csv'
INTO TABLE person
FIELDS
TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '"'
LINES
TERMINATED BY '\n'
IGNORE 1 LINES
;
Query OK, 9 rows affected (0.00 sec)
Records: 9 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT * FROM person;
+------+------------------------+
| ID | UID |
+------+------------------------+
| 0 | Steve the Pirate |
| 10 | |
| 1 | \Alpha |
| 2 | Hoban "Wash" Washburne |
| 3 | Pastor Veal |
| 4 | Tucker |
| 5 | Simon |
| 6 | Sonny |
| 7 | Wat\ |
+------+------------------------+
9 rows in set (0.00 sec)
Или я могу загрузить все строки, включая заголовок:
mysql> DELETE FROM person;
Query OK, 9 rows affected (0.00 sec)
mysql> LOAD DATA
LOCAL INFILE 'temp-1.csv'
INTO TABLE person
FIELDS
TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '"'
LINES
TERMINATED BY '\n'
IGNORE 0 LINES
;
Query OK, 11 rows affected, 1 warning (0.01 sec)
Records: 11 Deleted: 0 Skipped: 0 Warnings: 1
mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'ID' for column 'ID' at row 1 |
+---------+------+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM person;
+------+------------------------+
| ID | UID |
+------+------------------------+
| 0 | UID |
| 9 | |
| 0 | Steve the Pirate |
| 10 | |
| 1 | \Alpha |
| 2 | Hoban "Wash" Washburne |
| 3 | Pastor Veal |
| 4 | Tucker |
| 5 | Simon |
| 6 | Sonny |
| 7 | Wat\ |
+------+------------------------+
11 rows in set (0.00 sec)
Если строки моего входного файла не заканчиваются на "":
shell% cat temp-2.csv
"ID","UID"
"0","Steve the Pirate"
"1","\Alpha"
"2","Hoban ""Wash"" Washburne"
"3","Pastor Veal"
"4","Tucker"
"5","Simon"
"6","Sonny"
"7","Wat\"
тогда я не могу загрузить ни строки:
mysql> DELETE FROM person;
Query OK, 11 rows affected (0.00 sec)
mysql> LOAD DATA
LOCAL INFILE 'temp-2.csv'
INTO TABLE person
FIELDS
TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '"'
LINES
TERMINATED BY '\n'
IGNORE 1 LINES
;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT * FROM person;
Empty set (0.00 sec)
Или я могу загрузить все строки, включая заголовок:
mysql> DELETE FROM person;
Query OK, 0 rows affected (0.00 sec)
mysql> LOAD DATA
LOCAL INFILE 'temp-2.csv'
INTO TABLE person
FIELDS
TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '"'
LINES
TERMINATED BY '\n'
IGNORE 0 LINES
;
Query OK, 9 rows affected, 1 warning (0.03 sec)
Records: 9 Deleted: 0 Skipped: 0 Warnings: 1
mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'ID' for column 'ID' at row 1 |
+---------+------+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM person;
+------+------------------------+
| ID | UID |
+------+------------------------+
| 0 | UID |
| 0 | Steve the Pirate |
| 1 | \Alpha |
| 2 | Hoban "Wash" Washburne |
| 3 | Pastor Veal |
| 4 | Tucker |
| 5 | Simon |
| 6 | Sonny |
| 7 | Wat\ |
+------+------------------------+
9 rows in set (0.00 sec)
Итак, теперь, когда я обнаружил много способов сделать это неправильно, как я могу использовать LOAD DATA импортировать данные из этих файлов в мою базу данных?
1 ответ
Согласно документации для LOAD DATA обработка двойных двойных кавычек как двойной кавычки используется по умолчанию:
Если поле начинается с символа ENCLOSED BY, экземпляры этого символа распознаются как завершающие значение поля, только если за ними следует поле или строка последовательности TERMINATED BY. Чтобы избежать неоднозначности, вхождения символа ENCLOSED BY в значение поля могут быть удвоены и интерпретироваться как один экземпляр символа. Например, если указано ENCLOSED BY '"', кавычки обрабатываются, как показано здесь:
"The ""BIG"" boss" -> The "BIG" boss The "BIG" boss -> The "BIG" boss The ""BIG"" boss -> The ""BIG"" boss
Так что все, что мне нужно сделать, это отключить перевод \ как побег, используя ESCAPED BY '',
LOAD DATA
LOCAL INFILE 'temp-1.csv'
INTO TABLE person
FIELDS
TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY ''
LINES
TERMINATED BY '\n'
IGNORE 1 LINES
;