(mysql.info.gz) Loading tables
Info Catalog
(mysql.info.gz) Creating tables
(mysql.info.gz) Database use
(mysql.info.gz) Retrieving data
3.3.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.
Suppose that your pet records can be described as shown here. (Observe
that MySQL expects dates in `'YYYY-MM-DD'' format; this may be
different from what you are used to.)
*name* *owner* *species**sex**birth* *death*
Fluffy Harold cat f 1993-02-04
Claws Gwen cat m 1994-03-17
Buffy Harold dog f 1989-05-13
Fang Benny dog m 1990-08-27
Bowser Diane dog m 1979-08-31 1995-07-29
Chirpy Gwen bird f 1998-09-11
WhistlerGwen bird 1997-12-09
Slim Benny snake m 1996-04-29
Because you are beginning with an empty table, an easy way to populate
it is to create a text file containing a row for each of your animals,
then load the contents of the file into the table with a single
statement.
You could create a text file `pet.txt' containing one record per line,
with values separated by tabs, and given in the order in which the
columns were listed in the `CREATE TABLE' statement. For missing
values (such as unknown sexes or death dates for animals that are still
living), you can use `NULL' values. To represent these in your text
file, use `\N' (backslash, capital-N). For example, the record for
Whistler the bird would look like this (where the whitespace between
values is a single tab character):
*name* *owner* *species**sex**birth* *death*
`Whistler'`Gwen' `bird' `\N' `1997-12-09' `\N'
To load the text file `pet.txt' into the `pet' table, use this command:
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
Note that if you created the file on Windows with an editor that uses
`\r\n' as a line terminator, you should use:
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
-> LINES TERMINATED BY '\r\n';
You can specify the column value separator and end of line marker
explicitly in the `LOAD DATA' statement if you wish, but the defaults
are tab and linefeed. These are sufficient for the statement to read
the file `pet.txt' properly.
If the statement fails, it is likely that your MySQL installation does
not have local file capability enabled by default. See `LOAD
DATA LOCAL' LOAD DATA LOCAL. for information on how to change this.
When you want to add new records one at a time, the `INSERT' statement
is useful. In its simplest form, you supply values for each column, in
the order in which the columns were listed in the `CREATE TABLE'
statement. Suppose that Diane gets a new hamster named Puffball. You
could add a new record using an `INSERT' statement like this:
mysql> INSERT INTO pet
-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Note that string and date values are specified as quoted strings here.
Also, with `INSERT', you can insert `NULL' directly to represent a
missing value. You do not use `\N' like you do with `LOAD DATA'.
From this example, you should be able to see that there would be a lot
more typing involved to load your records initially using several
`INSERT' statements rather than a single `LOAD DATA' statement.
Info Catalog
(mysql.info.gz) Creating tables
(mysql.info.gz) Database use
(mysql.info.gz) Retrieving data
automatically generated byinfo2html