MySQL导入文本数据

参考官网地址

1. Creating and Selecting a Database

mysql> CREATE DATABASE menagerie;
mysql> USE menagerie
Database changed

2. Creating a Table

Use a CREATE TABLE statement to specify the layout of your table:

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
       species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

Once you have created a table, SHOW TABLES should produce some output:

mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| pet                 |
+---------------------+

To verify that your table was created the way you expected, use a DESCRIBE statement:

mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| 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    |       |
+---------+-------------+------+-----+---------+-------+

3. Loading Data into a Table

After creating your table, you need to populate it. The LOAD DATA and INSERT statements are useful for this.

pet.txt

Fluffy   Harold  cat    f       1993-02-04      \N
Claws    Gwen    cat    m       1994-03-17      \N
Buffy    Harold  dog    f       1989-05-13      \N
Fang     Benny   dog    m       1990-08-27      \N
Bowser   Diane   dog    m       1979-08-31      1995-07-29
Chirpy   Gwen    bird   f       1998-09-11      \N
Whistler Gwen    bird   \N      1997-12-09      \N
Slim     Benny   snake  m       1996-04-29      \N

To load the text file pet.txt into the pet table, use this statement:

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

When you want to add new records one at a time, the INSERT statement is useful.

mysql> INSERT INTO pet
       VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

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