mysql - 在加载数据INFILE中设置

Question

好吧，我以为我可以自己解决，但我似乎没有看到它。我的目标是通过以下方式填充表 MODELOS:

+--------+----------+---------+
| ID_MOD | ID_MARCA | MODELO  |
+--------+----------+---------+
|      1 |        1 | C2      |
|      2 |        1 | C3      |
|      3 |        1 | C4      |
|      4 |        1 | PICASSA |
|      5 |        2 | MONDEO  |
|      6 |        2 | S-MAX   |
|      7 |        3 | ACCORD  |
|      8 |        3 | CIVIC   |
|      9 |        4 | CLS     |
|     10 |        5 | 900     |
|     11 |        6 | IBIZA   |
|     12 |        6 | LEON    |
|     13 |        7 | 307     |
|     14 |        7 | 308     |
|     15 |        7 | 407     |
|     16 |        7 | 408     |
|     17 |        8 | MEGANE  |
|     18 |        9 | GOLF    |
|     19 |        9 | PASSAT  |
|     20 |        9 | TOUAREG |
+--------+----------+---------+

我有这张表:

mysql> select * from MARCAS;
+----------+------------+
| ID_MARCA | MARCA      |
+----------+------------+
|        1 | CITROEN    |
|        2 | FORD       |
|        3 | HONDA      |
|        4 | MERCEDES   |
|        7 | PEUGEOT    |
|        8 | RENAULT    |
|        5 | SAAB       |
|        6 | SEAT       |
|        9 | VOLKSWAGEN |
+----------+------------+

以及要使用的以下文件:

"MARCA"#"MODELO"#"MATRICULA"#PRECIO
"CITROEN"#"PICASSA"#"CPG-2044"#12000
"CITROEN"#"PICASSA"#"CPR-1762"#12500
"CITROEN"#"C4"#"FPP-1464"#13500
"CITROEN"#"C4"#"FDR-4563"#13000
"CITROEN"#"C3"#"BDF-8856"#8000
"CITROEN"#"C3"#"BPZ-7878"#7500
"CITROEN"#"C2"#"CDR-1515"#5000
"CITROEN"#"C2"#"BCC-3434"#4500
"FORD"#"MONDEO"#"BTG-3267"#8000
"FORD"#"MONDEO"#"BPP-6792"#8200
"FORD"#"S-MAX"#"FDR-1564"#20000
"FORD"#"S-MAX"#"FCE-9327"#21000
"HONDA"#"CIVIC"#"FCC-7764"#16000
"HONDA"#"CIVIC"#"FBC-4567"#14000
"HONDA"#"ACCORD"#"FFC-6768"#22000
"HONDA"#"ACCORD"#"FPB-2231"#23000
"MERCEDES"#"CLS"#"FDR-2265"#31000
"SAAB"#"900"#"FPG-1165"#21000
"SEAT"#"LEON"#"DVB-1119"#14500
"SEAT"#"LEON"#"DCR-5634"#13500
"SEAT"#"IBIZA"#"DPR-3434"#9500
"SEAT"#"IBIZA"#"DPP-8756"#10000
"PEUGEOT"#"307"#"DGX-4598"#5500

等等

现在，我所取得的成就是:

mysql> select * from MODELOS;
+--------+----------+---------+
| ID_MOD | ID_MARCA | MODELO  |
+--------+----------+---------+
|      1 |     NULL | MODELO  |
|      2 |     NULL | PICASSA |
|      3 |     NULL | C4      |
|      4 |     NULL | C3      |
|      5 |     NULL | C2      |
|      6 |     NULL | MONDEO  |
|      7 |     NULL | S-MAX   |
|      8 |     NULL | CIVIC   |
|      9 |     NULL | ACCORD  |
|     10 |     NULL | CLS     |
|     11 |     NULL | 900     |
|     12 |     NULL | LEON    |
|     13 |     NULL | IBIZA   |
|     14 |     NULL | 307     |
|     15 |     NULL | 308     |
|     16 |     NULL | 407     |
|     17 |     NULL | 408     |
|     18 |     NULL | MEGANE  |
|     19 |     NULL | PASSAT  |
|     20 |     NULL | GOLF    |
|     21 |     NULL | TOUAREG |
+--------+----------+---------+

使用这些命令:

ALTER TABLE MODELOS ADD UNIQUE(MODELO);
LOAD DATA LOCAL INFILE myfile.txt
IGNORE INTO TABLE MODELOS
FIELDS TERMINATED BY '#' ENCLOSED BY '"'
LINES ENDED BY '\n'
IGNORE 1 LINES
(@ignore1, MODELO, @ignore2, @ignore3);

如何才能创建所需的输出？我怀疑最简单的方法是在 LOAD DATA INFILE 中使用 SET 语句，但我只是不知道怎么做？

Answer 1

您可以将查询放入 SET 子句中。

LOAD DATA LOCAL INFILE myfile.txt
IGNORE INTO TABLE MODELOS
FIELDS TERMINATED BY '#' ENCLOSED BY '"'
LINES ENDED BY '\n'
IGNORE 1 LINES
(@marca, modelo, @ignore2, @ignore3)
SET ID_MARCA = (SELECT ID_MARCA FROM MARCAS WHERE MARCA = @marca)

这使用文件中的第一列在 MARCAS 表中查找相应的 ID。