我成功地使用下面的run_sql_script()函数使用PHP在文件中执行SQL。
static public function run_sql_script($script)
{
// Load and explode the sql file
$f = fopen($script,"r+");
$sqlFile = fread($f,filesize($script));
$sqlFile=preg_replace("/''';/", ';', $sqlFile); //replace semicolons with ascii
$sqlArray = explode(';',$sqlFile);
//Process the sql file by statements
foreach ($sqlArray as $stmt)
{
if (strlen($stmt)>8)
{
//Used to prevent blank lines at end of sql script from executing
$stmt=preg_replace("/;/", ';', $stmt); //replace ascii with semicolons
try {db::db()->exec($stmt);}
catch(PDOException $e){library::sql_error($e,$stmt);}
}
}
return;
}
我使用MySQL Workbench生成SQL,最近,我也通过MySQL Workbench添加了一个触发器。它在文件中添加了以下SQL:
DELIMITER $$
CREATE TRIGGER tg_contacts_upd AFTER UPDATE ON contacts
FOR EACH ROW
....
BEGIN
END$$
DELIMITER ;
通过run_sql_script()函数运行新文件后,现在得到以下错误:
查询出错:DELIMITER $$ CREATE TRIGGER tg_contacts_upd AFTERSQLSTATE[42000]:语法错误或访问冲突:1064您的SQL中有错误语法;检查MySQL服务器版本对应的手册在'DELIMITER $$ CREATE TRIGGER '附近使用正确的语法tg_contacts_upd AFTER UPDATE ON contacts FOR EACH ' at line 1
有什么建议如何修复
似乎太简单了,但是下面的代码似乎可以工作。
static public function run_sql_script($script)
{
$sql = file_get_contents($script);
db::db()->exec($sql);
}