...
- Create the table properly (with index)
- Flush table
No Format |
---|
$ mysqladmin flush-tables -u binga_db_user -p
|
- Removes all use of indexes for the table
No Format |
---|
$ sudo myisamchk --keys-used=0 -rq /var/lib/mysql/dbName/tblName
|
- LOAD DATA LOCAL INFILE. Adjust the parameters corresponding to your csv file.
No Format |
---|
$ mysql -u your_user_name -p
mysql> use your_db_name;
mysql> LOAD DATA LOCAL INFILE 'your_data_file.csv'
INTO TABLE `your_tbl_name`
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
|
- Re-create the indexes
No Format |
---|
$ sudo myisamchk --key_buffer_size=1024M --sort_buffer_size=1024M -rq /var/lib/mysql/dbName/tblName
|
- Flush table
No Format |
---|
$ mysqladmin flush-tables -u binga_db_user -p
|
LOAD DATA example
Code Block |
---|
|
LOAD DATA LOCAL INFILE '/home/xxx/data.csv'
INTO TABLE mydb.mytable -- make sure the table name is correct!
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
(@id, field_a, field_b, @flag, @filetime)
SET
id = @id+10000,
filetime = FROM_UNIXTIME(@filetime),
flag=IFNULL(@flag,0);
|
Refer to mysql.com and syntax for more details.
Panel |
---|
|
Ubuntu 10.10 Mysql server 5.1 |