Home » MySQL
MySQL LOAD DATA Syntax
Submitted by Igor on January 2, 2007 – 8:33 am3 Comments
Using LOAD DATA LOCAL INFILE is a much faster way to insert lots of data into a table than using INSERT. The only preliminary step is to format the input file with unique field separators.
In most cases MySQL by default will not allow LOAD DATA LOCAL operations. This feature needs to be enabled during MySQL source code compilation, mysqld daemon startup, or mysql CLI startup. Start mysql CLI like so:
|
1 |
mysql --local-infile=1 -u<username> -p<passwd> <db_name> |
In the following example, "list2" is a file containing one entry per line. The "biglist" table has three comma-separated columns: word_id (autoincrement), word1 , and word2 .
|
1 2 3 |
LOAD DATA LOCAL INFILE '/wordlists/incoming/list2' INTO TABLE biglist \ FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' \ (word1, word2); |
Not very complicated. You can do the same from inside a shell script:
|
1 2 3 4 5 6 7 |
#!/bin/ksh INFILE=/wordlists/incoming/list2 mysql --local-infile=1 -u<username> -p<password> <db_name> << EOF LOAD DATA LOCAL INFILE '$INFILE' INTO TABLE biglist \ FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' \ (word1, word2); EOF |
-
rndmaktn
-
Hayden
-
Lucas H
