MySQL
How can we import data in MySQL from CSV file
Run this SQL script either from a mysql console or from your favorite scripting language (PHP, PERL…)
LOAD DATA LOCAL INFILE ‘/path-to-file/dump_file.csv’
INTO TABLE table_name
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’
(Field_1, Filed_2, Field_3);
I believe the syntax is more than clear so I won’t bother explaining too much.
How can we export MySQL data as CSV
IN PHP:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | $table2dump = 'table_name'; $file_content = ''; $sql = "SHOW COLUMNS FROM " . $table2dump ; $res = mysql_query($sql); while ($row = mysql_fetch_assoc($res)) { $file_content .= $row['Field'].','; } $file_content = substr($file_content, 0, -1)."\n"; $sql = "SELECT * FROM " . $table2dump ; $res = mysql_query($sql); while ($row = mysql_fetch_assoc($res)) { $file_content .= implode(',', $row)."\n"; } header("Content-type: application/vnd.ms-excel"); header("Content-disposition: csv; filename=" . date("Y-m-d") . "_" . $table2dump . ".csv; size=".strlen($file_content)); echo $file_content; exit; |
How can you import(restore) a MySQL database from an SQL file
From the shell command prompt type:
mysql -p -h SERVER_NAME DATABASE_NAME < BACKUP_FILE.sql
where:
- SERVER_NAME – is the database server name (usually localhost)
- DATABASE_NAME – is the name of the database to be restored
- BACKUP_FILE – is the name of the file (usualy the name of the database plus the backup date)
How can you dump a MySQL database
from the shell command prompt type:
mysqldump -h SERVER_NAME DATABASE_NAME > BACKUP_FILE.sql
where:
- SERVER_NAME – is the database server name (usually localhost)
- DATABASE_NAME – is the name of the database to be dumped
- BACKUP_FILE – is the name of the file (usualy the name of the database plus the backup date)