批量修改mysql触发器的definner
说明:运行完成,会生成一个删除原有触发器,生成新的触发器的sql,方便选择性的运行
delimiter ////
set @definner = 'root@127.0.0.1';
SET SESSION group_concat_max_len = 100000000000;
select group_concat(
CONCAT('use ',dbname,char(10),'#######No.',(@rowNum:=@rowNum+1),',old definner=',obj_defer,',new definner=',@definner,'#######',char(10),'delimiter ****',char(10),
'DROP TRIGGER if exists `',trig_name,'`;',char(10),
'CREATE DEFINER=',@definner ,' TRIGGER `',trig_name,'` ',timing,' ',evnt,' ON `',tb_name,'`',char(10),
'FOR EACH ROW',char(10),
sql_body,';',char(10),
'****',char(10),
'delimiter ;',char(10),char(10)
)
separator '')
as s from (
select TRIGGER_SCHEMA as dbname,TRIGGER_NAME as trig_name,EVENT_MANIPULATION as evnt,EVENT_OBJECT_TABLE as tb_name,ACTION_STATEMENT as sql_body,ACTION_TIMING as timing,DEFINER as obj_defer from `information_schema`.`TRIGGERS`
,
(Select (@rowNum :=0) ) b
) x
where obj_defer !=@definner and obj_defer!=replace(@definner,'`','')
into dumpfile '/user/tmp/aaa.sql';
////