php mysql 触发器_通过PHP创建mysql触发器？

I'm executing the following in PHP5.3:

$sql = "

CREATE TRIGGER `_photo_fulltext_insert` AFTER INSERT ON `photo` FOR EACH ROW INSERT INTO `_photo_fulltext` SELECT * FROM `photo` WHERE `id`=NEW.`id`;

CREATE TRIGGER `_photo_fulltext_delete` AFTER DELETE ON `photo` FOR EACH ROW DELETE FROM `_photo_fulltext` WHERE `id`=OLD.`id`;

DELIMITER |

CREATE TRIGGER `_photo_fulltext_update` AFTER UPDATE ON `photo`

FOR EACH ROW BEGIN

DELETE FROM `_photo_fulltext` WHERE `id`=NEW.`id`;

INSERT INTO `_photo_fulltext` SELECT * FROM `photo` WHERE `id`=NEW.`id`;

END;

|

DELIMITER ;

";

$mysqli->multi_query($sql);

The "photo_fulltext_update" trigger isn't getting created. This statement does run (and creates all the triggers) in phpMyAdmin. I've read online somewhere that the MySQL server doesn't support the DELIMITER statement at all, so I'm looking for a way to re-write this multi-step CREATE TRIGGER statement so that mysqli::multi_query can send it to MySQL.

Thanks!

解决方案

While the mysqli doesn't to anything with DELIMITER in multi-query statements, it actually doesn't do anything with any delimiters at all in normal queries, so just shove your triggers in one by one:

$ cat i.php

$mysqli = new mysqli('localhost', 'test', '', 'test');

$sql = "

CREATE TRIGGER `_foo_fulltext_update` AFTER UPDATE ON `foo`

FOR EACH ROW BEGIN

DELETE FROM `bar` WHERE `bar`=NEW.`bar`;

INSERT INTO `bar` (bar) SELECT bar FROM `foo` WHERE `bar`=NEW.`bar`;

END;

";

$mysqli->query($sql);

var_dump($mysqli->error);

$ php i.php

string(0) ""

$ mysql

mysql> use test;

Database changed

mysql> show triggers\G

*************************** 1. row ***************************

Trigger: _foo_fulltext_update

Event: UPDATE

Table: foo

Statement: BEGIN

DELETE FROM `bar` WHERE `bar`=NEW.`bar`;

INSERT INTO `bar` (bar) SELECT bar FROM `foo` WHERE `bar`=NEW.`bar`;

END

Timing: AFTER

Created: NULL

sql_mode:

Definer: root@localhost

character_set_client: latin1

collation_connection: latin1_swedish_ci

Database Collation: utf8_general_ci

1 row in set (0.00 sec)