php 通过PHP创建mysql触发器？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3762880/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create mysql trigger via PHP?
提问by brianjcohen
I'm executing the following in PHP5.3:
我在 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.
“photo_fulltext_update”触发器没有被创建。该语句确实在 phpMyAdmin 中运行（并创建了所有触发器）。我在网上某处读到 MySQL 服务器根本不支持 DELIMITER 语句，所以我正在寻找一种方法来重新编写这个多步 CREATE TRIGGER 语句，以便 mysqli::multi_query 可以将它发送到 MySQL .
Thanks!
谢谢！
回答by Wrikken
While the mysqlidoesn't to anything with DELIMITERin multi-querystatements, it actually doesn't do anything with any delimiters at all in normal queries, so just shove your triggers in one by one:
虽然in语句mysqli没有任何作用，但它实际上在正常查询中根本不使用任何分隔符，因此只需将触发器一个一个地推入：DELIMITERmulti-query
$ 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)
回答by Daniel Lenkes
回答by Martin Altmann
this might help as well: the following creates a trigger on selectes tables. change the code and it might do what you want. https://github.com/junicom/mysqltriggerscript
这也可能有所帮助：以下内容在选择表上创建触发器。更改代码，它可能会做你想要的。 https://github.com/junicom/mysqltriggerscript
