SyntaxLOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name' [REPLACE | IGNORE] INTO TABLE tbl_name [CHARACTER SET charset_name] [{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number LINES] [(col_name_or_user_var,...)] [SET col_name = expr,...] Description
Show
Reads rows from a text file into the designated table on the database at a very high speed. The file name must be given as a literal string. Files are written to disk using the SELECT INTO
OUTFILE statement. You can then read the files back into a table using the Executing this statement activates One must have the FILE privilege to be able to execute LOAD DATA. This is the ensure the normal users will not attempt to read system files. Note that MariaDB's systemd unit file restricts access to LOAD DATA LOCAL INFILEWhen you execute
the In the event that you don't want to permit this operation (such as for security reasons), you can disable the
If the The used command is not allowed with this MariaDB version Note that it is not entirely accurate to say that the MariaDB version does not support the command. It would be more accurate to say that the MariaDB configuration does not support the command. See MDEV-20500 for more information. From MariaDB 10.5.2, the error message is more accurate: The used command is not allowed because the MariaDB server or client has disabled the local infile capability REPLACE and IGNOREIn cases where you load data from a file into a table that already contains data and has a primary key, you may encounter issues where the statement attempts to insert a row with a primary key that
already exists. When this happens, the statement fails with Error 1064, protecting the data already on the table. In cases where you want MariaDB to overwrite duplicates, use the The This operation can cause a degradation in load speed by a factor of 20 or more if the part that has already been loaded is larger than the capacity of the InnoDB Buffer Pool. This happens because it causes a lot of turnaround in the buffer pool. Use the Character-setsWhen the statement opens the file, it attempts to read the contents using the default character-set, as defined by the character_set_database system variable. In the cases where the file was written using a character-set other than the default, you can specify the character-set to use with the The statement interprets all fields in the file as having the same character-set, regardless of the column data type. To properly interpret file contents, you must ensure that it was written with the correct character-set. If you write a data file with
mysqldump -T or with the SELECT INTO OUTFILE statement with the mysql client, be sure to use the When using mixed character sets, use the The character_set_filesystem system variable controls the interpretation of the filename. It is currently not possible to load data files that use the Preprocessing Inputscol_name_or_user_var can be a column name, or a user variable. In the case of a variable, the SET statement can be used to preprocess the value before loading into the table. Priority and ConcurrencyIn storage engines that perform table-level locking (MyISAM, MEMORY and MERGE), using the LOW_PRIORITY keyword, MariaDB delays insertions until no other clients are reading from the table. Alternatively, when using the MyISAM storage engine, you can use the CONCURRENT keyword to perform concurrent insertion. The Progress ReportingThe Using mariadb-import/mysqlimportMariaDB ships with a separate utility for loading data from files: mariadb-import (or Using mariadb-import/mysqlimport you can compress the file using the IndexingIn cases where the storage engine supports ALTER TABLE... DISABLE KEYS statements (MyISAM and Aria), the ExamplesYou have a file with this content (note the the separator is ',', not tab, which is the default): 2,2 3,3 4,4 5,5 6,8 CREATE TABLE t1 (a int, b int, c int, d int); LOAD DATA LOCAL INFILE '/tmp/loaddata7.dat' into table t1 fields terminated by ',' (a,b) set c=a+b; SELECT * FROM t1; +------+------+------+ | a | b | c | +------+------+------+ | 2 | 2 | 4 | | 3 | 3 | 6 | | 4 | 4 | 8 | | 5 | 5 | 10 | | 6 | 8 | 14 | +------+------+------+ Another example, given the following data (the separator is a tab): 1 a 2 b The value of the first column is doubled before loading: LOAD DATA INFILE 'ld.txt' INTO TABLE ld (@i,v) SET i=@i*2; SELECT * FROM ld; +------+------+ | i | v | +------+------+ | 2 | a | | 4 | b | +------+------+ See Also
How do I load data into MySQL?Apart from the LOAD DATA statement to load data from file to table in MySQL, you can use the INSERT statement to insert new rows into an existing table. You can use the INSERT statement to load data from file to table in MySQL in the following forms: INSERT… VALUES. INSERT…VALUES ROWS()
How import MySQL command line?Command line MySQL import. Type: mysql -u username -p database_name < file.sql.. The username refers to your MySQL username.. database_name refers to the database you want to import.. file. sql is your file name.. If you've assigned a password, type it now and press Enter.. What is load data infile in MySQL?The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. If the LOCAL keyword is specified, the file is read from the client host. If LOCAL is not specified, the file must be located on the server. ( LOCAL is available in MySQL 3.22.
How do I import a CSV file into MySQL using terminal?Import CSV File Using Command Line. Step 1: Access MySQL Shell. Access your terminal window and log into MySQL using the following command: mysql –u username –p. ... . Step 2: Create MySQL Table for CSV Import. ... . Step 3: Import CSV into MySQL Table.. |