在MySQL中更新触发器定义者？

Question

如何使用MySQL更改过程和触发器中的定义者？

UPDATE `mysql`.`trig` p
    SET definer = 'user@%'
    WHERE definer='root@%'`

- Sriram

---

你查过 alter table 语法了吗？你可能需要删除并重新添加触发器。 - Sloan Thrasher

可能是如何批量更改MySQL触发器DEFINER的重复问题。 - Juicy Scripter

Answer 1

对于触发器和视图，您可以使用我在这里描述的内容。对于存储过程，同样的方法应该适用于表信息模式.routines。

原始帖子： 我知道这是一篇旧文章，但也许它可以帮助某些人。 我使用这个SQL查询来生成一个DROP和一个CREATE命令：

SELECT CONCAT("DROP TRIGGER ", trigger_name, ";", " CREATE TRIGGER ", TRIGGER_NAME, " AFTER ", EVENT_MANIPULATION, " ON ", EVENT_OBJECT_SCHEMA, ".", EVENT_OBJECT_TABLE, " FOR EACH ROW ", ACTION_STATEMENT, ";") AS sqlCommand FROM information_schema.triggers WHERE EVENT_OBJECT_SCHEMA = "yourdatabase";

当我将生产数据库带到开发机器上并使用foreach遍历所有命令并自动重新创建触发器时，我在我的应用程序中使用它。这使我有了自动化的选择。

PHP/Laravel示例：

    $this->info('DROP and CREATE TRIGGERS');
    $pdo = DB::connection()->getPdo();
    $sql = 'SELECT CONCAT("DROP TRIGGER ", trigger_name, ";", " CREATE TRIGGER ", TRIGGER_NAME, " AFTER ", EVENT_MANIPULATION, " ON ", EVENT_OBJECT_SCHEMA, ".", EVENT_OBJECT_TABLE, " FOR EACH ROW ", ACTION_STATEMENT, ";") AS sqlCommand FROM information_schema.triggers WHERE EVENT_OBJECT_SCHEMA = "mydatabase";';
    $stmt = $pdo->prepare($sql, [PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true]);
    $stmt->execute();
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $stmt->closeCursor();

    foreach($result as $rs){
        $pdo = DB::unprepared($rs['sqlCommand']);
        break;
    }

提示：我必须使用pdo来解决mysql缓冲查询问题，可以在这里找到描述。

我的视图也是同样的做法（您可以使用ALTER TABLE）：

    $pdo = DB::connection()->getPdo();
    $sql = 'SELECT CONCAT("ALTER DEFINER=`homestead` VIEW ", table_name," AS ", view_definition,";") AS sqlCommand FROM  information_schema.views WHERE table_schema="mydatabase";';
    $stmt = $pdo->prepare($sql, [PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true]);
    $stmt->execute();
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $stmt->closeCursor();
    $this->info('View definer changed');

    foreach($result as $rs){
        $pdo = DB::unprepared($rs['sqlCommand']);
    }

希望它有所帮助。

- ndberg