使用PHP加载包含分隔符的SQL文件


Loading SQL files which contain deliminators using PHP

我成功地使用下面的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("/''';/", '&#59', $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("/&#59/", ';', $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);
    }