O'Reilly logo

MySQL in a Nutshell, 2nd Edition by Russell J.T. Dyer

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Name

LOAD DATA INFILE

Synopsis

LOAD DATA [LOW_PRIORITY|CONCURRENT] [LOCAL] INFILE '/path/file'
  [REPLACE|IGNORE] INTO TABLE table
  [CHARACTER SET character_set]
  [FIELDS [TERMINATED BY 'character']
  [[OPTIONALLY] ENCLOSED BY 'character'] [ESCAPED BY 'character']]

  [LINES [STARTING BY 'string'] [TERMINATED BY 'string']]

  [IGNORE count LINES]

  [(column,...)]
  
  [SET column = expression,...]

You can use this statement to import organized data from a text file into a table in MySQL. The file can be either on the server or on the client.

For a file on the server, if you use a bare filename (such as input.txt) or a relative path (such as ../), the file is found relative to the directory of the database into which the data is to be imported. If the file is not located in the directory’s database, the file permissions must be set so it can be read for all filesystem users.

For a file on the client, the LOCAL keyword must be given. This feature must be enabled on both the client and the server by using the startup option of --local-infile=1. See Chapter 15 for more information on server and client settings.

If a data text file contains rows of data duplicating some of the rows in the table into which it’s being imported, an error will occur and the import may end without importing the remaining data. Duplicate rows are those that have the same values for key columns or other unique columns. To instruct the server to ignore any errors encountered and to continue loading other rows, use the IGNORE keyword. ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required