linux下:
LOAD DATA INFILE '/home/test/dump/ip_location.csv'
INTO TABLE ip_location
CHARACTER SET utf8
FIELDS TERMINATED BY ',' ENCLOSED BY '"';
--CHARACTER SET :mysql字符集,一定要加上,免去乱码麻烦
--INTO TABLE :导入到哪个表
--FIELDS TERMINATED BY :以什么作为分割符
-- ENCLOSED BY :被什么包围
windows下:
LOAD DATA INFILE "d:/insert_data.csv"
REPLACE INTO TABLE DEMO
CHARACTER SET gb2312
FIELDS TERMINATED BY "," ENCLOSED BY ""
LINES TERMINATED BY " ";
--LINES TERMINATED BY:这个与linux不同,以什么作为一行的结尾。
-------导入本地csv文件
LOAD DATA LOCAL INFILE
'C:\Users\Administrator\Desktop\a.csv'
INTO TABLE -----想导入到哪个表
`ydtf`.`area_info`
FIELDS ESCAPED BY '\' -----字段中使用的转义符为\
TERMINATED BY ',' -------字段间以,号分隔
ENCLOSED BY '"' ---------字段用"号括起
LINES TERMINATED BY ' ' -------行以 结束
(`AREA_NAME`, `AREA_CODE`, `CITY_ID`, `PROVINCE_NAME`, `PROVINCE_CODE`); -------csv文件中数据按此顺序插入表中
编号,名称,说明
1,测试数据1,"测试CSV文件中,有逗号"
2,测试数据2,"测试CSV文件中有""双引号"""
3,测试数据3,"测试CSV文件中,有逗号和""双引号"""
4,测试数据4,普通数据
mysql>
CREATE
TABLE
Test_Book1 (
-> id
int
,
->
name
VARCHAR
(10),
-> data
VARCHAR
(100)
-> );
Query OK, 0
rows
affected (0.05 sec)
下面的 lines terminated
by
'
'
是 要求换行符号,为 windows的换行
下面的
ignore
1 lines是 忽略第一行的标题行。
mysql>
LOAD
DATA INFILE
'f:/Book1.csv'
->
INTO
TABLE
Test_Book1
-> FIELDS TERMINATED
BY
','
-> OPTIONALLY ENCLOSED
BY
'"'
-> lines terminated
by
'
'
->
ignore
1 lines
-> (id,
name
, data);
Query OK, 4
rows
affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
mysql>
select
*
from
test_book1;
+
------+-----------+--------------------------------+
| id |
name
| data |
+
------+-----------+--------------------------------+
| 1 | 测试数据1 | 测试CSV文件中,有逗号 |
| 2 | 测试数据2 | 测试CSV文件中有"双引号
" |
| 3 | 测试数据3 | 测试CSV文件中,有逗号和"
双引号" |
| 4 | 测试数据4 | 普通数据 |
+
------+-----------+--------------------------------+
4
rows
in
set
(0.00 sec)