So I have an SQL dump file that needs to be loaded using mysql_query(). Unfortunately, it's not possible to execute multiple queries with it.
-> It cannot be assumed that the mysql command-line client (mysql --help) is installed -- for loading the SQL file directly
-> It cannot be assumed that the mysqli extension is installed
/* contents of dump.sql, including comments */
DELETE FROM t3 WHERE body = 'some text; with semicolons; scattered; throughout';
DELETE FROM t2 WHERE name = 'hello';
DELETE FROM t1 WHERE id = 1;
The explode() below won't work because some of the dump content's values contain semicolons.
$sql = explode(';', file_get_contents('dump.sql'));
foreach ($sql as $key => $val) {
mysql_query($val);
}
What's the best way to load the SQL without modifying the dump file?
解决方案
You have more problem cases than just semicolons within strings.
Script builtin commands that cannot be executed by mysql_query(), like USE.
Statements that are not terminated with a semicolon, like DELIMITER.
Statements that contain semicolons, but not inside quotes, like CREATE PROCEDURE.
I don't know of an easy way to handle this task, without shelling out to the mysql command-line client. I realize you said you can't rely on that client being present, but without that client, you need a large amount of PHP code to parse the script and execute statements appropriately.
You may be able to find such code inside the phpMyAdmin product. However, that product is licensed under the GPL, so if you use any of the code, you must also license your own project under the GPL.
See also my answers to these related questions: