在shell命令行中创建mysql触发器 [英] create mysql trigger in shell command line

问题描述

我需要在命令行中创建mysql触发器

I need to create a mysql trigger in command line

此sql在mysql控制台中运行良好：

This sql working good in mysql console:

$sql = "
    USE DB1;
    DROP TRIGGER IF EXISTS my_trigger;
    DELIMITER $$
    CREATE TRIGGER my_trigger AFTER UPDATE ON tbl1
     FOR EACH ROW BEGIN 
        INSERT INTO DB2.tbl2 (column1)
        VALUES (1234);
    END$$
    DELIMITER ;
";

$cmd = 'mysql -sse "'.$sql.'";';

//put all in one line to avoid shell errors
$cmd = preg_replace('/\r\n|\r|\n|\t|\s+/m', " ", $cmd);

exec("($cmd) 2>&1", $output, $result);

这引发了mysql错误

This thrown an mysql error

第1行的错误1064（42000）：SQL语法有错误；
检查与您的MySQL服务器版本相对应的手册以获取
正确语法，以在第1行的\'END\'附近使用

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'END\' at line 1

我也尝试了 DELIMITER // ...相同的错误

I have also tried with DELIMITER // ... same error

推荐答案

解决方案：将 DELIMITER $$ 更改为 \d $$ （花了两天时间之后：（）

The solution: change DELIMITER $$ with \d $$ (after spending two days with this :( )

在php中必须使用 \\d $$

$sql = "
    USE DB1;
    DROP TRIGGER IF EXISTS my_trigger;
    \\d $$
    CREATE TRIGGER my_trigger AFTER UPDATE ON tbl1
     FOR EACH ROW BEGIN 
        INSERT INTO DB2.tbl2 (column1)
        VALUES (1234);
    END$$
    \\d ;
";

$cmd = 'mysql -sse "'.$sql.'";';

//put all in one line to avoid shell errors
$cmd = preg_replace('/\r\n|\r|\n|\t|\s+/m', " ", $cmd);

exec("($cmd) 2>&1", $output, $result);

似乎当我们将所有内容放在一行中时，只能使用定界符的简写 \d

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