通过PHP创建mysql触发器? [英] Create mysql trigger via PHP?

问题描述

我正在PHP5.3中执行以下操作:

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);

未创建"photo_fulltext_update"触发器.该语句确实在phpMyAdmin中运行(并创建所有触发器).我在某处在线阅读了MySQL服务器完全不支持DELIMITER语句的信息，因此我正在寻找一种方法来重写此多步骤CREATE TRIGGER语句，以便mysqli :: multi_query可以将其发送到MySQL .

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.

谢谢！

推荐答案

虽然mysqli在multi-query语句中对DELIMITER不执行任何操作，但实际上对任何分隔符不执行任何操作常规查询，因此只需一一推开触发器:

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 
<?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)

这篇关于通过PHP创建mysql触发器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！