mysql 命令行创建触发器,在shell命令行中创建mysql触发器

I need to create a mysql trigger in command line

This sql working good in mysql console:

$sql = "

USE DB1;

DROP TRIGGER IF EXISTS my_trigger;

DELIMITER $$

CREATE TRIGGER my_trigger AFTER UPDATE ON tbl1

FOR EACH ROW BEGIN

INSERT INTO DB2.tbl2 (column1)

VALUES (1234);

END$$

DELIMITER ;

";

$cmd = 'mysql -sse "'.$sql.'";';

//put all in one line to avoid shell errors

$cmd = preg_replace('/\r\n|\r|\n|\t|\s+/m', " ", $cmd);

exec("($cmd) 2>&1", $output, $result);

This thrown an mysql error

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax;

check the manual that corresponds to your MySQL server version for the

right syntax to use near \'END\' at line 1

I have also tried with DELIMITER // ... same error

解决方案

The solution: change DELIMITER $$ with \d $$ (after spending two days with this :( )

In php must use \\d $$

$sql = "

USE DB1;

DROP TRIGGER IF EXISTS my_trigger;

\\d $$

CREATE TRIGGER my_trigger AFTER UPDATE ON tbl1

FOR EACH ROW BEGIN

INSERT INTO DB2.tbl2 (column1)

VALUES (1234);

END$$

\\d ;

";

$cmd = 'mysql -sse "'.$sql.'";';

//put all in one line to avoid shell errors

$cmd = preg_replace('/\r\n|\r|\n|\t|\s+/m', " ", $cmd);

exec("($cmd) 2>&1", $output, $result);

It seem that when we put all in one line only work the shorthand of delimiter \d