如何创建一个mysql触发器的条件和没有分隔符的laravel迁移？

b4qexyjb 于 2021-06-23 发布在 Mysql

关注(0)|答案(1)|浏览(351)

我有一个laravel项目，它已经有了一个带有sql脚本的数据库，我正在尝试将mysql脚本移植到eloquent中。我的数据库有使用 DELIMITER $$ 根据这个问题，看起来我不能用 DELIMITER $$ 因为它是一个mysql客户机命令，mysql pdo不能用php访问它。
有办法避免吗 DELIMITER $$ 但mysql触发器中仍有条件通过laravel数据库迁移使其可用吗？
迁移.php：

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Log;

class RunInitScriptSql extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
     public function up()
     {
        $sql_dump = File::get(base_path("database/billing_system_stable.sql"));
        $sql_dump_insert = File::get(base_path("database/billing_system_insertions.sql"));
        $sql_dump_trigger = File::get(base_path("/database/billing_system_triggers.sql"));

        DB::connection('mysql')->getPdo()->exec("CREATE DATABASE IF NOT EXISTS billing_system;");
        DB::connection('mysql')->getPdo()->exec(
            $sql_dump .
            $sql_dump_insert .
            $sql_dump_trigger
        );
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {

    }
}

示例触发器：

DROP TRIGGER IF EXISTS before_update_typesoumission;
DELIMITER $$
CREATE TRIGGER before_update_typesoumission
  BEFORE UPDATE
  ON typesoumission
  FOR EACH ROW
    IF NEW.typeSoumission = 'Résidentiel'
    THEN
      SET NEW.ratio = 1;
    ELSE
      SET NEW.ratio =
      NEW.tauxHoraire / (SELECT tauxHoraire
                         FROM typesoumission
                         WHERE typeSoumission = 'Résidentiel');
    END IF;
$$
DELIMITER ;

mysql php laravel laravel-migrations

来源：https://stackoverflow.com/questions/51814303/how-to-create-a-mysql-trigger-with-condition-and-without-delimiter-for-laravel-m

1条答案

按热度按时间

ax6ht2ek1#

注意：虽然这个解决方案可以很好地与mysql配合使用，但op使用的是mariadb，它似乎并不喜欢。
你应该能把 CREATE TRIGGER 进入查询。在我的情况下，我使用 $link （结果来自 $link = new PDO("mysql:host=$server;dbname=$db;charset=utf8", $user, $pass, $options); )作为我的联系。我正在运行php7和mysql5.6：

$link->exec("DROP TRIGGER IF EXISTS before_update_typesoumission");
$link->exec("CREATE TRIGGER before_update_typesoumission
  BEFORE UPDATE
  ON typesoumission
  FOR EACH ROW
    IF NEW.typeSoumission = 'Résidentiel'
    THEN
      SET NEW.ratio = 1;
    ELSE
      SET NEW.ratio =
      NEW.tauxHoraire / (SELECT tauxHoraire
                         FROM typesoumission
                         WHERE typeSoumission = 'Résidentiel');
    END IF;");
$result = $link->query("SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='before_update_typesoumission'");
print_r($result->fetch());

输出：

Array
(
    [TRIGGER_CATALOG] => def
    [TRIGGER_SCHEMA] => test
    [TRIGGER_NAME] => before_update_typesoumission
    [EVENT_MANIPULATION] => UPDATE
    [EVENT_OBJECT_CATALOG] => def
    [EVENT_OBJECT_SCHEMA] => test
    [EVENT_OBJECT_TABLE] => typesoumission
    [ACTION_ORDER] => 0
    [ACTION_CONDITION] => 
    [ACTION_STATEMENT] => IF NEW.typeSoumission = 'Résidentiel'
    THEN
      SET NEW.ratio = 1;
    ELSE
      SET NEW.ratio =
      NEW.tauxHoraire / (SELECT tauxHoraire
                         FROM typesoumission
                         WHERE typeSoumission = 'Résidentiel');
    END IF
    [ACTION_ORIENTATION] => ROW
    [ACTION_TIMING] => BEFORE
    [ACTION_REFERENCE_OLD_TABLE] => 
    [ACTION_REFERENCE_NEW_TABLE] => 
    [ACTION_REFERENCE_OLD_ROW] => OLD
    [ACTION_REFERENCE_NEW_ROW] => NEW
    [CREATED] => 
    [SQL_MODE] => STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    [DEFINER] => test@10.1.10.0/255.255.255.0
    [CHARACTER_SET_CLIENT] => utf8
    [COLLATION_CONNECTION] => utf8_general_ci
    [DATABASE_COLLATION] => utf8_general_ci
)

赞(0）回复(0）举报 2021-06-23

我来回答

如何创建一个mysql触发器的条件和没有分隔符的laravel迁移？

1条答案

相关问题

热门标签

最新问答