通过SQL语句导入数据
LOAD DATE [LOW_PRIORTY][LOCAL] INFILE 'file_name'
INTO TABLE tbl_name
[CHARACTER SET gbk] # 用于指定字符集
[FIELDS TERMINATED BY '\t'] # 字段之间的分隔符
[ENCLOSED BY ""]
[ESCAPED BY '\'] # 转义字符
[LINES TERMINATED BY '\n'] # 用于指定行之间的分隔符
[IGNORE n LINES] # 用于跳过指定行数
下面以导入二手房数据为例:
LOAD DATE INFILE 'D:/buildings.csv'
INTO TABLE buildings
FIELDS TERMINATED BY ',' # 指定字段之间分隔符为英文逗号
LINES TERMINATED BY '\n' # 指定记录行之间分隔符为换行符'\n'
IGNORE 1 ROWS; # 原始数据第一行为表头,所以要忽略掉
注意:导入前必须先在数据库种建立一个空表用于存储数据,否则报错。