我在
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);
未创建“photo_fulltext_update”触发器.此语句在PHPMyAdmin中运行(并创建所有触发器).我在网上看过MysqL服务器根本不支持DELIMITER语句,所以我正在寻找一种方法来重写这个多步CREATE TRIGGER语句,以便MysqLi :: multi_query可以将它发送到MysqL .
谢谢！
虽然MysqLi在多查询语句中没有任何与DELIMITER相关的东西,但它在普通查询中根本不对任何分隔符做任何事情,所以只需逐个推送你的触发器：
$cat i.PHP
<?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)