MySQL 5.6 リファレンスマニュアル  /  ...  /  テーブルへのデータのロード

3.3.3 テーブルへのデータのロード

テーブルを作成したら、データを移入する必要があります。これには、LOAD DATA ステートメントと INSERT ステートメントが役立ちます。

ペットのレコードを次のように記述できると仮定します。(MySQL では、日付の形式は 'YYYY-MM-DD' と想定されています。これはユーザーが通常使用する形式とは異なる場合があります。)

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

空のテーブルから始めているため、データを移入する簡単な方法は、各ペットに対応する行を記述したテキストファイルを作成してから、1 つのステートメントでそのファイルの内容をテーブルにロードすることです。

たとえば、テキストファイル pet.txt を作成し、1 行に 1 レコードを記述します。値は、CREATE TABLE ステートメントに指定したカラムの順序に従い、タブで区切って指定します。性別が不明な場合やまだ生きているペットの死亡年月日など、不足している値には NULL 値を使用できます。テキストファイルでこれらを表現するには、\N (バックスラッシュと大文字の N) を使用します。たとえば、Whistler という鳥のレコードは次のようになります (値の間の空白は 1 つのタブ文字です)。

Whistler        Gwen    bird    \N      1997-12-09      \N

テキストファイル pet.txtpet テーブルにロードするには、次のステートメントを使用します。

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

このファイルを Windows で作成した場合、作成に使用したエディタで \r\n が行ターミネータとして使用されているときは、代わりに次のステートメントを使用します。

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

(OS X を実行している Apple マシンでは、LINES TERMINATED BY '\r' を使用するとよいでしょう。)

カラム値の区切り文字と行末マーカーは、必要に応じて LOAD DATA ステートメントで明示的に指定できますが、デフォルトではタブと改行です。ステートメントで pet.txt ファイルを正しく読み取るにはこれで十分です。

ステートメントが失敗する場合、使用している MySQL インストールではローカルファイル機能がデフォルトで有効になっていない可能性があります。これを変更する方法については、セクション6.1.6「LOAD DATA LOCAL のセキュリティーの問題」を参照してください。

新しいレコードを 1 つずつ追加する場合は、INSERT ステートメントが役立ちます。もっとも単純な形式では、CREATE TABLE ステートメントに指定したカラムの順序に従って、各カラムの値を入力します。Diane が、Puffball という名前の新しいハムスターを手に入れたとします。次のように、INSERT ステートメントを使用して新しいレコードを追加できます。

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

ここでは、文字値と日付値を、引用符付きの文字列で指定しています。また、INSERT では、不足している値を表す NULL を直接挿入することができます。LOAD DATA の場合のように \N を使用することはありません。

この例からわかるとおり、初期レコードをロードするために複数の INSERT ステートメントを使用すると、1 つの LOAD DATA ステートメントを使用する場合よりもかなり多くの入力が必要になります。


User Comments
  Posted by Doug Hall on February 17, 2003
With Apple OS X: Use the terminal's drag and drop capability to insert the full path of the import file. This cuts down on the amount of typing, if you don't want deal with adding the import file into MySQL's data folder.

example:
%mysql --local-infile -u <username> -p <DatabaseName>
Enter password:<password>
mysql>load data local infile '<drag input file here>' into table <TableName>;
  Posted by Brandon Stout on September 22, 2004
you can also drag windows files to the command window, but you'll need to change the backslashes to double-backslashes or forwardslashes, and remove the c: at the beginning. If you have quotes around the path, you'll need to delete them as well.
  Posted by tsaiching wong on December 3, 2004
mysql> LOAD DATA LOCAL INFILE '<dir>/pet.txt' INTO TABLE pet FIELDS terminated by '<delimiter>';

--> just in case anyone experienced some discomfort following above instructions.
  Posted by phil newcombe on December 23, 2004
I used the full path name 'c:/<path to file>' and it worked fine, but my defaults aren't THE defaults. :-)
  Posted by Mark Buchanan on August 8, 2012
When dragging file in Windows I found the quotes needed to be kept in.
  Posted by Mike Hearn on January 17, 2005
Doug Halls trick also works on Linux/BSD using the GNOME or KDE terminal emulator programs.
  Posted by Dennis Verbunt on February 27, 2005
I was having some problems getting this working in XP but got it working after checking my syntax multiple times and then ENABLING local infiles.

Also after draging the file into the command window I had to replace the windows style backslashes with linux style forward slashes.
mysql> LOAD DATA LOCAL INFILE "C:\Documents and Settings\Dennis\Desktop\menagerie\pet.txt" INTO TABL
E PET;
ERROR 2 (HY000): File 'C:Documents and SettingsDennisDesktopmenageriepet.txt' not found (Errcode: 2)

mysql> LOAD DATA LOCAL INFILE "C:/Documents and Settings/Dennis/Desktop/menagerie/pet.txt" INTO TABL
E PET;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 0
  Posted by MidnighToker on May 7, 2005
I'm thrilled and amazed about being able to drop files into a console to get the path, but

for people not using the mouse, you can put a path from your home directory using '~/' in the path.

Eg if your file is 'pets.txt' in your home directory

LOAD DATA LOCAL INFILE '~/pets.txt' INTO TABLE pet;

NOTE: This only works on *nix opperating systems and OSX (as far as i know).

NOTE: A blank line at the end of your infile gives wierd results:
| Slim     | Benny  | snake    | m    | 1996-04-29 | NULL       |
| | NULL | NULL | NULL | NULL | NULL |
| Puffball | Daine | hampster | f | 1999-03-30 | NULL |
Worth keeping an eye on.
  Posted by Mohamed Abdulla on August 17, 2005
I noticed that in case you want to use LOAD DATA LOCAL INFILE or the INSERT commands to fill your table fields with data, special care should be taken for INTEGER AUTO_INCREMENT fields. In the case of "LOAD DATA LOCAL INFILE" command I pressed TAB where was supposed to enter AUTO_INCREMENT INT value, and it was accepted. In the case of "INSERT INTO table VALUES (.." command I just entered the name of the column <without quotes> where was supposed to supply the value of that field, and it worked! Actually in all cases it worked also when entering any string value like: 'i' or 's'. It will alwys be converted to the required INT value. I thought this may help!
  Posted by Aaron Peterson on November 9, 2005
With a defalut installation from FreeBSD ports, I had to use the command line

mysql -u user -p --local-infile menagerie

to start the mysql monitor, else the LOAD DATA LOCAL command failed with an error like the following:

ERROR 1148 (42000): The used command is not allowed with this MySQL version

Also, the pet.txt downloaded from the mysql.com link (tgz archive) appears to have ended the lines with '\n' instead of '\r\n'.
  Posted by Jerry Krinock on July 8, 2006
If you are uploading to a remote server, check the file after uploading before using "LINES TERMINATED BY. For example, because Microsoft Excel on my Macintosh writes line endings as '\r' (0x0d), I used the clause LINES TERMINATED BY '\r' in my command. But this gave me a syntax error at the end of the first line. When I remove this clause, it worked! Further investigation showed that the file on the remote server had \n (0x0a) line endings. Apparently, my web hosting provider (DreamHost) automatically changes line endings of uploaded .txt files to the Unix standard.
  Posted by Linh Hoang on February 11, 2007
It seems like mysql ignore trailing whitespaces. Here is my input:

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

my tab token is ".TAB." (no whitespace in between). There are few instances where I had a tab and a whitespace and it works as normal. For the example, in the last line, there is a tab, a space and the date 1996-04-29 and it works okay. So mysql ignores white spaces between token.
  Posted by on March 15, 2007
if you get this

> mysql menagerie -u root -p < load_pet_tbl.sql
Enter password:
ERROR 1148 (42000) at line 5: The used command is not allowed with this MySQL version

you can use this to get around it

> mysql menagerie --local_infile=1 -u root -p < load_pet_tbl.sql
Enter password:

  Posted by Chris Cooper on April 11, 2007
OS = Slackware with 2.4.29 kernel
mySQL = 5.0.24a
connection = ssh
editor = vim 6.3.7

I was able to load a 4K row tab delimited text file with
"mysql>load data infile 'filepath/file' into table yourtable;"

I was NOT able to load the same file using
"mysql>load data local infile 'filepath/file' into table yourtable;"

I was receiving the following:
"ERROR 1148 (42000): The used command is not allowed with this MySQL version"

The tutorial above does not show an example of using the command without the word "local" in it.
  Posted by Pradeep Chakravarty on April 14, 2007
To ensure that even special characters in the text file are added to the table as records the complete command we use is

load data local infile 'file.txt' into table tablename fields terminated by '\t' optionally enclosed by '\\' escaped by '\\' lines terminated by '\n';

  Posted by Kristofer Krus on June 28, 2007
Note that a tab should be added after each element in the .txt version of the table, even after the last elements in each row.

I skipped the last tab at each row and ended up with a table with "0000-00-00" as death dates instead of NULL, the only death dates which where really correct was death dates for the dog that actually died, and the pet at the very last row.
  Posted by Phil Hystad on July 31, 2008
On Mac OS X, lines of a file are normally terminated by '\n' just like on any other Unix system. The line termination of '\r' is used on the previous Mac operating systems such as OS9. Also, if a file was created on Mac OS9 but used on a Mac OS X system then you need to use '\r' as well. Of course, this last comment goes for any file -- you need to be aware of where it was created and not so much as to where you are using it.
  Posted by Matthew DesVoigne on August 8, 2008
I am using Windows. I originally used the backslash to separate directories as in DOS

mysql> load data local infile 'C:\Users\Matt DesVoigne\Documents\pet.txt' into table pet
-> LINES TERMINATED BY '\r\n';

That did NOT work. I had to use forward slash to separate directories:

mysql> load data local infile 'C:/Users/Matt DesVoigne/Documents/pet.txt' into table pet
-> LINES TERMINATED BY '\r\n';

Thank you. Matt
  Posted by Angela Ray on September 2, 2008
I found on my computer (running Windows XP) that if I loaded the file without specifying "Lines terminated by '\r\n'" that the death dates that were supposed to be null were all '0000-00-00'.
I deleted the information and reinserted the file specifying the line terminator, and it inserted the NULLs correctly.
  Posted by John Best on October 11, 2008
On a Windows-based system, for the INFILE parameter if using backslashes for the filepath, you'll need to escape the backslashes with an additional backslash:

LOAD DATA LOCAL INFILE 'C:\\directory\\path\\to\\file\\pet.txt' into TABLE pet fields terminated by ',' lines terminated by '\n';

The alternative as others have already mentioned is to use forward slashes instead of backslashes.

Below is the input file that I used. In the above LOAD DATA command, I specified "fields terminated by ','" and "lines terminated by '\n'":

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,

Note, depending on how (e.g. text editor used) and/or where (system type) the text file was created, the line termination can be different:

- Line Feed (LF): \n
- Carriage Return (CR): \r
- Carriage Return and Line Feed (CR+LF): \r\n

Wikipedia has an article for 'newline' which goes into much greater detail on this:

http://en.wikipedia.org/wiki/Newline

  Posted by Mike Peterson on March 20, 2010
I used the following pet.txt example on windows vista using the Mysql version 5.1.45 Community:
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

* notice the whitespace in between each field is a tab and also **there is a tab following each line**, besides the last line.

I used the syntax:

mysql> LOAD DATA LOCAL INFILE 'C:/Databases/pet.txt' into table pet
-> LINES TERMINATED BY '\r\n';

* This allowed the file to load with no errors or warnings.
I had to piece parts together from a few different peoples posts on here in order to come to this conclusion and have everything working right.
  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.