MySQL查询在Phpmyadmin应用时有效，但在通过PHP应用时却不行吗？

Question

I am trying to apply a trigger to an existing table and column through PHP. When executing the PHP code, no error is thrown, but the trigger is not created.

My PHP code is:

//file: migrations.php

// created_at column already defined in a previous migration
//...

$migrations[3]['message'] = "added created_at, updated_at columns on rate table";
$migrations[3]['created'] = "2015-09-05 08:28:00";
$migrations[3]['sql']     = "
DELIMITER |
CREATE TRIGGER rate_created_at BEFORE INSERT ON rate
FOR EACH ROW
    BEGIN
        SET NEW.created_at = CURRENT_TIMESTAMP;
    END |
    DELIMITER ;
";

// apply migration
foreach ($migrations as $key => $value) {
    // run the migration SQL for the current iteration
    $sth = $dbh->prepare( $value['sql'] );
    $sth->execute();
}

heres what does work

• Copying and pasting the trigger creating SQL into Phpmyadmin
• getting rid of the DELIMITER and BEGIN END statements e.g. if I reduce the SQL to

$migrations[3]['sql']     = "
CREATE TRIGGER rate_created_at BEFORE INSERT ON rate
FOR EACH ROW
        SET NEW.created_at = CURRENT_TIMESTAMP;
";

It does run from PHP, problem is I dont believe I can run multiline statements without DELIMITER and BEGIN END.
I have tried

• escaping the delimiter character
• using different delimiter characters

What can I do to get the PHP to run the SQL with the DELIMITER and BEGIN END statements?

Answer 1

yes, you will have to get rid of those DELIMITER, just have the below code

$migrations[3]['sql'] = "
CREATE TRIGGER rate_created_at BEFORE INSERT ON rate
FOR EACH ROW
    BEGIN
        SET NEW.created_at = CURRENT_TIMESTAMP;
    END;";

See this another post saying same thing PHP: multiple SQL queries in one mysql_query statement