MySQL导入csv数据

sdhgf

在工作中经常遇到需要往MySQL数据库中导入开发人员提供的数据，其中一种是csv格式的，导入方式如下：
创建相应的表格

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

mysql> use kevin; Database changed mysql> CREATE TABLE GeoPC_Places (     -> ISO varchar(2) NOT NULL,     -> Country varchar(50) NOT NULL,     -> Language varchar(2) NOT NULL,     -> ID bigint(20) NOT NULL,     -> Region1 varchar(80),     -> Region2 varchar(80),     -> Region3 varchar(80),     -> Region4 varchar(80),     -> Locality varchar(80),     -> Postcode varchar(15),     -> Suburb varchar(80),     -> Latitude double,     -> Longitude double,     -> Elevation integer,     -> ISO2 varchar(10),     -> FIPS varchar(10),     -> NUTS varchar(12),     -> HASC varchar(12),     -> STAT varchar(20),     -> Timezone varchar(30),     -> UTC varchar(10),     -> DST varchar(10),     -> PRIMARY KEY (Language, ID)     -> ) ENGINE=Innodb DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.08 sec)

通过load data命令导入csv数据，load data语法如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.csv'     [REPLACE | IGNORE]     INTO TABLE tbl_name     [FIELDS         [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,...)]

导入数据：

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> load data local infile '/dump/GeoPC_AU_Places.csv' into table GeoPC_Places     ->      fields terminated by ';'     ->      lines terminated by '\n'     ->      ignore 1 lines; Query OK, 15828 rows affected (1.18 sec) Records: 15828  Deleted: 0  Skipped: 0  Warnings: 0 mysql> select count(*) from GeoPC_Places; +----------+ | count(*) | +----------+ |    15828 | +----------+ 1 row in set (0.10 sec)

导入成功
相对应的有导入，肯定有导出，这里不再详细说明，附上select导出文件语法共参阅：
SELECT语法

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

SELECT     [ALL | DISTINCT | DISTINCTROW ]       [HIGH_PRIORITY]       [STRAIGHT_JOIN]       [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]       [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]     select_expr, ...     [INTO OUTFILE 'file_name' export_options       | INTO DUMPFILE 'file_name']     [FROM table_references     [WHERE where_definition]     [GROUP BY {col_name | expr | position}       [ASC | DESC], ... [WITH ROLLUP]]     [HAVING where_definition]     [ORDER BY {col_name | expr | position}       [ASC | DESC] , ...]     [LIMIT {[offset,] row_count | row_count OFFSET offset}]     [PROCEDURE procedure_name(argument_list)]     [FOR UPDATE | LOCK IN SHARE MODE]]