通过PHP创建mysql触发器？

发布时间：2020-12-13 14:09:53 所属栏目：PHP教程来源：网络整理

导读：我在 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 O

我在 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 triggersG
*************************** 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)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!