mysql导出存储过程、函数、视图、触发器

由于通过mysqldump导出的存储过程、函数、视图、触发器包含definer信息，导致很多时候无法正常导入。通过以下SQL可以自动生成创建语句。

--导出存储过程和函数

SELECT CONCAT("DROP ",TYPE," IF EXISTS `",db,"`.`", NAME,"`; DELIMITER ;; CREATE ",TYPE," `",db,"`.`",NAME,"`(", param_list,") ",IF ( TYPE = "FUNCTION", CONCAT ("RETURNS ", RETURNS, " ")," "), body_utf8,";; DELIMITER ;") FROM mysql.proc;

--导出视图

SELECT CONCAT("DROP VIEW IF EXISTS `",TABLE_SCHEMA,"`.`",TABLE_NAME,"`; CREATE VIEW `", TABLE_SCHEMA,"`.`",TABLE_NAME,"` as ",VIEW_DEFINITION,";") FROM information_schema.VIEWS;

--导出触发器

SELECT CONCAT("DROP TRIGGER IF EXISTS `",TRIGGER_SCHEMA, "`.`", TRIGGER_NAME,"`; DELIMITER ;; CREATE TRIGGER `",TRIGGER_SCHEMA,"`.`",TRIGGER_NAME,"` ",ACTION_TIMING," ",EVENT_MANIPULATION," ON `",EVENT_OBJECT_SCHEMA,"`.`",EVENT_OBJECT_TABLE,"` FOR EACH ROW ",ACTION_STATEMENT,";; DELIMITER ;") FROM information_schema.TRIGGERS;

--导出事件

SELECT CONCAT( "DROP EVENT IF EXISTS `", EVENT_SCHEMA, "`.`", EVENT_NAME, "`; DELIMITER ;; CREATE EVENT `", EVENT_SCHEMA, "`.`", EVENT_NAME, "` ON SCHEDULE EVERY ", INTERVAL_VALUE, " ", INTERVAL_FIELD, " STARTS '", STARTS,"'", IF ( ENDS <>NULL, CONCAT (" ENDS '",ENDS,"'"),""), " ON COMPLETION ", ON_COMPLETION, " ENABLE DO ", EVENT_DEFINITION, ";; DELIMITER ;" ) FROM information_schema.events;

或者(针对版本mysql.5.6.14)

mysqldump -h127.0.0.1 -uroot -proot@123 -P5320 -B -R -E --triggers --default-character-set=utf8 --opt --max-allowed-packet=64M --net_buffer_length=163840 --single-transaction ad |sed '/DEFINER.*SQL SECURITY DEFINER/d;s/^CREATE DEFINER.*PROCEDURE /CREATE PROCEDURE /;s/^CREATE DEFINER.*FUNCTION /CREATE FUNCTION /;s/^.*CREATE.*DEFINER.*trigger /CREATE /*!50003 trigger /;s/^.*CREATE.*DEFINER.*EVENT /CREATE /*!50106 EVENT /;' > ad.sql