我有一个txt文件,看起来像:
Col1, Col2, Col3
1, foo0, bar0
1, foo1, bar1
2, foo2, bar2
3, foo3, bar3
我想使用类似的查询将txt文件导入数据库:
LOAD DATA INFILE '/tmp/sample.txt'
INTO TABLE sample
FIELDS TERMINATED BY ','
LINES TERMINATED BY 'n'
IGNORE 1 LINES;
但只需要txt文件中的记录,其中Col1的值为1,我只想在示例表中使用Col1和Col2。如何使用WHERE子句创建查询以确定Col1的值为1,然后只将txt中的前两列放入示例表?
查看mysql加载数据的文件语法
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]
它似乎不支持where子句。之后,您将不得不删除那些不需要的记录,或者进行某种形式的命令行过滤,然后将过滤后的数据加载到mysql中。