[MYSQL] Import data using load data

LOAD Data import Syntax:

LOAD DATA [Low_priority | CONCURRENT] [LOCAL] INFILE ' file_name '    [REPLACE | IGNORE] into    TABLE tbl_name    [PARTITION (Partition_name,...)]    [CHARACTER SET Charset_name]    [{fields | COLUMNS}        [TERMINATED by ' string ']--split character        [[[Optionally] enclosed by ' char ']        [escaped by ' char '    ]    [ LINES        [Starting by ' string ']-  -start character        [TERMINATED by ' string ']  --split character    ]    [IGNORE number { LINES | ROWS}]    [(Col_name_or_user_var,...)]    [SET col_name = expr,...]

　　

Fields and lines are optional, but if all are specified, fields must precede lines.

A pet table is used locally with the following structure:

+---------+-------------+------+-----+---------+-------+| Field   | Type        | Null | Key | Default | Extra |+---------+-------------+------+-----+---------+-------+| Name    | varchar (20) | YES  |     | NULL    |       | | owner   | varchar (20) | YES  |     | NULL    |       | | species | varchar (20) | YES  |     | NULL    |       | | sex     | char (1)     | YES  |     | NULL    |       | | birth   | date        | YES  |     | NULL    |       | | death   | date        | YES  |     | NULL    |       | +---------+-------------+------+-----+---------+-------+

　　

Data to import: Pet.txt

Fluffy,harold,cat,f,1993-02-04claws,gwen,cat,m,1994-03-17buffy,harold,dog,f,1989-05-13fang,benny,dog,m, 1990-08-27bowser,diane,dog,m,1979-08-31,1995-07-29chirpy,gwen,bird,f,1998-09-11whistler,gwen,bird,, 1997-12-09slim,benny,snake,m,1996-04-29

　　

Import command:

LOAD DATA LOCAL INFILE '/home/scott/mysql/pet.txt ' IGNORE to TABLE pet fields TERMINATED by ', ' lines TERMINATED by ' \ n ' ;

　　

Import Results:

Mysql> SELECT * from pet;+----------+--------+---------+------+------------+------------+| Name     | owner  | species | Sex  | birth      | death      |+----------+--------+---------+------+------------ +------------+| Fluffy   | Harold | Cat     | f    | 1993-02-04 | NULL       | | Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       | | Buffy    | Harold | Dog     | f    | 1989-05-13 | NULL       | | Fang     | Benny |  dog     | m    | 1990-08-27 | NULL       | | Bowser   | Diane |  dog     | m    | 1979-08-31 | 1995-07-29 | | Chirpy   | Gwen |   Bird    | f    | 1998-09-11 | NULL       | | Whistler | Gwen |   bird    |      | 1997-12-09 | NULL       | | Slim     | Benny |  snake   | m    | 1996-04-29 | NULL       |+----------+--------+---------+------+------------+------------+

　　

[MYSQL] Import data using load data