Networking

Unix and Linux network configuration. Multiple network interfaces. Bridged NICs. High-availability network configurations.

Applications

Reviews of latest Unix and Linux software. Helpful tips for application support admins. Automating application support.

Data

Disk partitioning, filesystems, directories, and files. Volume management, logical volumes, HA filesystems. Backups and disaster recovery.

Monitoring

Distributed server monitoring. Server performance and capacity planning. Monitoring applications, network status and user activity.

Commands & Shells

Cool Unix shell commands and options. Command-line tools and application. Things every Unix sysadmin needs to know.

Home » MySQL

MySQL LOAD DATA Syntax

Submitted by on January 2, 2007 – 8:33 am 3 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:

mysql --local-infile=1 -u -p 

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 .

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:

#!/bin/ksh
INFILE=/wordlists/incoming/list2
mysql --local-infile=1 -u -p  << EOF
LOAD DATA LOCAL INFILE '$INFILE' INTO TABLE biglist 
FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' 
(word1, word2);
EOF

Print Friendly, PDF & Email

3 Comments »

Leave a Reply

%d bloggers like this: