Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.9Mb
PDF (A4) - 38.0Mb
PDF (RPM) - 36.5Mb
HTML Download (TGZ) - 9.9Mb
HTML Download (Zip) - 9.9Mb
HTML Download (RPM) - 8.7Mb
Man Pages (TGZ) - 210.0Kb
Man Pages (Zip) - 319.1Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Loading Data into a Table

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
Whistler Gwen 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):

Whistler        Gwen    bird    \N      1997-12-09      \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;

If you created the file on Windows with an editor that uses \r\n as a line terminator, you should use this statement instead:

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
    -> LINES TERMINATED BY '\r\n';

(On an Apple machine running OS X, you would likely want to use LINES TERMINATED BY '\r'.)

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 Section 6.1.6, “Security Issues with 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);

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.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Mark Buchanan on August 8, 2012
When dragging file in Windows I found the quotes needed to be kept in.
  Posted by burak sarac on February 22, 2011
if you are creating source file via loop use character code for tab or "\t" (in example C# i have used "\t" instead of pressing TAB on string line).
Also when you are creating file with null date value (\N) then system showing as 0000-00-00 but when you use NULL for insert command system creating NULL value instead of 0000-00-00. then dba will have extra work to analyze null data isnt it?

Thank you
Burak

  Posted by Tom Spin on May 12, 2011
Hello, here is my solution, took me some time to figure out what was the catch in my case..

OS: WinXP
MySQL: 5.5.8 (included in WAMP server distibution)
INFILE: Made in Notepad, Win XP

Catch was pet.txt file, had to use TAB spacer between, even at last row... Then it still made some NULL rows, so i have positioned my mouse cursor at last row (Slim), behind last NULL word, pressed TAB once, then pressing DELETE so i can knock off those empty spaces..
etc: 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

It worked, and finally no more NULL rows.

Second, my Load line was:
LOAD DATA LOCAL INFILE 'c:/MySQL/pet.txt' INTO TABLE pet;

No TERMINATION line..

Cheers!
  Posted by Shannon Ploppa on July 14, 2011
Apperently, because of a security issue, the client is set up with local-infile=0. Read here:http://dev.mysql.com/doc/refman/5.1/en/load-data-local.html

Attempting to load data local infile from the client as stated in the tutorial will result in the following error : ERROR 1148: The used command is not allowed with this MySQL version.

In order to use load data local infile as in the tutorial, use the load-infile option when invoking the client.

From command line, run your client with the load-infile option set to true in order to be able to use load data local infile as in tutorial:
%> mysql --local-infile=1 -p

The instructions for loading data from a local file from the command line work as stated: ie from the shell prompt where the pwd contains the file cr_event_tbl.sql--
%> mysql menagerie < cr_event_tbl.sql
will still work.

  Posted by billy bob thorton on April 24, 2012
Like most admins in unix/linux world we use the terminal, and when using the terminal this usually means, you are local to the box - ie., sitting at the physical keyboard of the mysql server OR you are using ssh to connect via port 22. For this issue of load data local infile, if given the error - ERROR 1148 (42000): The used command is not allowed with this MySQL version, remember, you are remotely connected, and file you wish to import is local already, THUS, simply remove the word LOCAL, as it is already local, and the error message might be a bit misleading.

Here is the correct syntax for a flat.txt file that is already on the mysql server in a /path/file.txt and you are ssh'd into the box from say a laptop or other networked computer.

mysql> load data infile '/path/to/file/pet.txt' into table pet fields terminated by ',';

Query OK, 76 rows affected, 17 warnings (0.00 sec)
Records: 76 Deleted: 0 Skipped: 0 Warnings: 17

*** terminated by is a good option, and there was no need to specify a '\N'.

*** also if you made the mistake of having leading ',' like I did, you can issue a truncate table foo ; to simply remove current table data, and LOAD DATA INFILE again.

oly562 aka BBT
  Posted by Marlon Arenas on November 15, 2013
Newbie quesetion here:

I have this output:

+----------+--------+---------+------+------------+------------+
| 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 |
+----------+--------+---------+------+------------+------------+

I just wanted to change all records with DEATH value of '0000-00-00' to NULL, i did the following commands:

mysql> UPDATE pet
-> SET death=NULL
-> WHERE death='0000-00-00'
-> ;

now it displays:

+----------+--------+---------+------+------------+------------+
| 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 |
+----------+--------+---------+------+------------+------------+

is there a better way? the '0000-00-00' came from an imported CSV which I created using Google Spreadsheet. How come it didn't import as NULL off the bat (except the last record)? NULL and '0000-00-00' were from cells with blank data.

Is '0000-00-00' equals to NULL in a DATE field?

thanks,
Marlon
  Posted by Romina Chirre on March 27, 2014
This is a reply to Marlon Arenas' question. Hi Marlon. I am a newbie too and I was trying to figure out why when I was uploading the pet.txt file, the "death" date field was showing up as 0000-00-00 instead of as NULL. I realized I needed to add one more tab after the \N at the end of the line. Like this:

Fluffy -Tab Space- Harold -Tab Space- cat -Tab Space- f -Tab Space- 1993-02-04 -Tab Space- \N -Tab Space-

This is the way it should look once you load the file with this format:

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 | 1968-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| PusffBall | Diane | hamster | f | 1999-03-30 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
+-----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)

I hope this helps.
Sign Up Login You must be logged in to post a comment.