โหลดข้อมูลเข้า MySQL ด้วย LOAD DATA INFILE

บ่อยครั้งที่เราได้ข้อมูลเป็นไฟล์ธรรมดาแบบ text แล้วเราต้องการนำข้อมูลนี้ใส่เข้าไปในฐานข้อมูลเช่น MySQL มีหลายวิธีในการเขียนโปรแกรม เพื่อจะโหลดข้อมูลเข้า MySQL ได้

ในบทความนี้ขอแนะนำวิธีการใช้คำสั่ง LOAD DATA INFILE ซึ่งเป็นคำสั่งใน MySQL เพื่อทำหน้าที่นี้โดยเฉพาะ และมีประสิทธิภาพ ความเร็วในการโหลดข้อมูลลงฐานข้อมูลสูงมาก

ในที่นี้จะเปรียบเทียบความเร็วการเขียนโปรแกรมแบบเปิดไฟล์แล้ววนลูปทีละบรรทัดเพื่อโหลดข้อมูล กับการใช้คำสั่ง LOAD DATA INFILE ทีเดียวเลย

ตัวอย่างข้อมูลที่ทดสอบ

ในการทดสอบ เราจะสร้าง table ขึ้นมาแบบง่ายๆ มี 3 column ดังนี้

CREATE TABLE test_load (
 id     INTEGER UNSIGNED AUTO_INCREMENT,
 dat1   INTEGER UNSIGNED,
 dat2   INTEGER UNSIGNED,
 PRIMARY KEY(id)
);

ส่วนไฟล์ข้อมูลที่เราจะโหลดทั้งหมด 10,000 บรรทัด มีสองฟิลด์คั่นด้วยเครื่องหมาย comma “,” ตัวอย่างไฟล์เป็นดังนี้

[user@db-server ~]$ cat data.txt
2436,156
1732,9161
3016,49
1833,963
5430,434
2092,3929
5430,444
4123,120
...

เปิดไฟล์ วนลูป แล้ว INSERT ข้อมูล

ตัวอย่างโปรแกรม PHP ที่เปิดไฟล์  วนลูปทีละบรรทัด แล้ว INSERT ข้อมูลลง MySQL

<?php

// connect database
mysql_connect("localhost", "db_user", "db_pass");
mysql_select_db("test");

$fname  = $argv[1];

// open file
$fh = fopen($fname, "r");

while ( $fline = fgetcsv($fh, "4096", ",") ) {
    $_dat1 = $fline[0];
    $_dat2 = $fline[1];

    // loop insert data
    $db_sql = "INSERT INTO test_load ( dat1, dat2 )  VALUES ( '$_dat1', '$_dat2' );";
    $db_res = mysql_query($db_sql) or die($db_sql);

}

fclose($fh);
?>

รันโปรแกรม และใช้คำสั่ง time เพื่อจับเวลาที่ใช้ในการรัน

[user@db-server ~]$ time php insert-data.php data.txt

real    0m0.898s
user    0m0.100s
sys     0m0.564s

ใช้คำสั่ง LOAD DATA INFILE

รูปแบบการใช้คำสั่ง LOAD DATA INFILE มีดังนี้

LOAD 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,...]

คำอธิบายการใช้คำสั่งโดยย่อ

• INFILE ‘file_name’ ระบุว่าโหลดจากไฟล์ไหน 
• INTO TABLE tbl_name  ระบุชื่อ table ที่จะโหลดข้อมูลลง
• FIELDS TERMINATED BY ‘string’  แต่ละฟิลด์คั่นด้วยเครื่องหมายอะไร
• (col_name_or_user_var,…) ในกรณีที่โหลดไม่ครบทุกฟิลด์ตามโครงสร้างของ table ต้องมีการระบุด้วยว่าโหลดใส่ชื่อฟิลด์อะไรบ้างตามลำดับ

ตัวอย่างการเขียนโปรแกรม เพื่อโหลดข้อมูลจากไฟล์ ลง table ชื่อ test_load แต่ละฟิลด์คั่นด้วยเครื่องหมาย “,” และโหลดเฉพาะฟิลด์ที่ชื่อ dat1, dat2 ตามลำดับ

<?php

// connect database
mysql_connect("localhost", "db_user", "db_pass");
mysql_select_db("test");

$fname  = $argv[1];

// load data
$db_sql = "LOAD DATA LOCAL INFILE '$fname' INTO TABLE test_load
             FIELDS TERMINATED BY ',' ( dat1, dat2 );";
$db_res = mysql_query($db_sql) or die($db_sql);

?>

รันโปรแกรมเพื่อโหลดข้อมูลลง MySQL ใช้คำสั่ง time เพื่อจับเวลาที่ใช้ในการรันโปรแกรม

[user@db-server ~]$ time php load-data.php data.txt

real    0m0.117s
user    0m0.054s
sys     0m0.023s

สังเกตว่าเวลาที่ใช้ในการรันเพื่อโหลดข้อมูลด้วย LOAD DATA INFILE เร็วกว่าการ เปิดไฟล์และวนลูปมาก

ข้อมูลอ้างอิง

• MySQL 5.1 Reference Manual :: 12.2.6 LOAD DATA INFILE Syntax