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

最新推荐文章于 2025-07-02 11:26:06 发布

DBA日志小记

最新推荐文章于 2025-07-02 11:26:06 发布

阅读量1.2k

点赞数

CC 4.0 BY-SA版权

分类专栏： MySQL

本文链接：https://blog.youkuaiyun.com/m18580239676/article/details/48368083

MySQL 专栏收录该内容

47 篇文章

订阅专栏

本文介绍了如何使用SQL语句和mysqldump命令来导出并创建MySQL的存储过程、函数、视图和触发器，尤其解决了因包含definer信息导致的导入问题。提供了具体的SQL脚本和mysqldump命令行参数，适用于不同版本的MySQL。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

--导出存储过程和函数

SELECT CONCAT("DROP ",TYPE," IF EXISTS `",db,"`.`", NAME,"`;\nDELIMITER ;;\nCREATE ",TYPE," `",db,"`.`",NAME,"`(", param_list,") ",IF ( TYPE = "FUNCTION", CONCAT ("RETURNS ", RETURNS, "\n"),"\n"), body_utf8,";;\nDELIMITER ;") FROM mysql.proc;

--导出视图

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

--导出触发器

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

--导出事件

SELECT CONCAT( "DROP EVENT IF EXISTS `", EVENT_SCHEMA, "`.`", EVENT_NAME, "`;\nDELIMITER ;;\nCREATE 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, ";;\nDELIMITER ;" ) 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