mysql - 阻止字段更新的触发器不起作用，行为怪异

Question

我有以下迁移:

public function up() {
    Schema::create('configurations', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name')->unique();
        $table->json('config');
        $table->timestamps();
        $table->timestamp('deleted_at')->nullable();
    });

    DB::unprepared('
        CREATE TRIGGER configuration_no_update_name BEFORE UPDATE ON configurations 
        FOR EACH ROW BEGIN
            IF OLD.name <=> NEW.name THEN
                SIGNAL SQLSTATE \'45000\'
                    SET MESSAGE_TEXT = \'Cannot update Configuration name\';
            END IF;
        END;
    ');
}

public function down() {
    Schema::dropIfExists('connection_configs');
    DB::unprepared('DROP TRIGGER IF EXISTS configuration_no_update_name');
}

播种者:

public function run() {
    $data = [
        [
            'name' => 'System A',
            'config' => json_encode([
                'host' => '127.0.0.1:80',
                'grant_type' => 'password',
                'token' => '',
                'username' => 'testUser',
                'password' => 'test',
            ]),
            'created_at' => Carbon::now(),
            'updated_at' => Carbon::now(),
        ],
    ];

    DB::table('configurations')->insert($data);
}

在 mysql 中，我启用了日志:

SET GLOBAL log_output = 'FILE';
SET GLOBAL general_log_file = '/tmp/my_logs.txt';
SET GLOBAL general_log = 'ON';

SELECT * FROM 配置的结果；

|  1 | System A | {"host": "127.0.0.1:80", "token": "", "password": "test", "username": "testUser", "grant_type": "password"} | 2018-01-12 09:03:14 | 2018-01-12 09:03:14 | NULL       |

问题出在触发器上。

触发器背后的想法是阻止对 name 字段的任何更新。

但是当我第一次测试触发器时，更新不会被阻止，但是当我第二次重新运行它时，它会被阻止:

更新配置 SET name = 'Some System' WHERE id = 1;(2 次)

输出:

日志:

2018-01-12T09:26:30.406449Z    31 Query UPDATE configurations SET name = 'Some System' WHERE id = 1
2018-01-12T09:26:35.430263Z    31 Query UPDATE configurations SET name = 'Some System' WHERE id = 1
2018-01-12T09:26:35.430465Z    31 Query SIGNAL SQLSTATE '45000'
                        SET MESSAGE_TEXT = 'Cannot update Configuration name'

此后，我尝试使用 Laravel 更新字段:

$c = Configuration::first();
$c->name = 'Some other system';
$c->save();

输出:

2018-01-12T09:34:39.292290Z    56 Connect   root@127.0.0.1 on system_db using TCP/IP
2018-01-12T09:34:39.293006Z    56 Query use `system_db`
2018-01-12T09:34:39.293510Z    56 Prepare   set names 'utf8mb4' collate 'utf8mb4_unicode_ci'
2018-01-12T09:34:39.293862Z    56 Execute   set names 'utf8mb4' collate 'utf8mb4_unicode_ci'
2018-01-12T09:34:39.294312Z    56 Close stmt
2018-01-12T09:34:39.294416Z    56 Prepare   set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
2018-01-12T09:34:39.294802Z    56 Execute   set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
2018-01-12T09:34:39.295227Z    56 Close stmt
2018-01-12T09:34:39.295493Z    56 Prepare   select * from `configurations` where `configurations`.`deleted_at` is null limit 1
2018-01-12T09:34:39.300104Z    56 Execute   select * from `configurations` where `configurations`.`deleted_at` is null limit 1
2018-01-12T09:34:39.300791Z    56 Close stmt
2018-01-12T09:34:39.367476Z    56 Prepare   update `configurations` set `name` = ?, `updated_at` = ? where `id` = ?
2018-01-12T09:34:39.367935Z    56 Execute   update `configurations` set `name` = 'Some other system', `updated_at` = '2018-01-12 09:34:39' where `id` = 1
2018-01-12T09:34:39.370971Z    56 Close stmt
2018-01-12T09:34:39.372737Z    56 Quit

结果:条目名称更改为其他系统

我真的很困惑为什么它会这样。如果还有其他方法可以使 name 字段不可变，那就太好了

Answer 1

正如评论中提到的，我一直使用错误的运算符 <=>而不是<>