CREATE PROCEDURE `pro_auto_subject`() COMMENT '数据迁移' BEGIN /**procedure body**/ DECLARE autoSubjectID INT DEFAULT 0; DECLARE professionID INT DEFAULT 0; DECLARE _done INT DEFAULT 0; DECLARE cur_1 CURSOR FOR SELECT s.`id` AS autoSubjectID,p.`id` AS professionID FROM `auto_subject` s join `profession` p on(s.`profession_new_id`=p.`new_id` ); DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done=1; OPEN cur_1; REPEAT FETCH cur_1 INTO autoSubjectID,professionID; IF NOT _done THEN UPDATE auto_subject SET `profession` = professionID WHERE `id` =autoSubjectID; END IF; UNTIL _done END REPEAT; CLOSE cur_1; END
1、mysql以下直接在sql窗口可以创建存储过程,格式如下:
/*----套餐表迁移---- */ DELIMITER ;; DROP PROCEDURE IF EXISTS `pro_move_meal` $$ CREATE PROCEDURE `pro_move_meal`() COMMENT '套餐表迁移(meal表->benefit_package表)' BEGIN /**procedure body**/ DECLARE p_id VARCHAR(50); DECLARE p_createTime VARCHAR(100); -- 创建时间 DECLARE p_updateTime VARCHAR(100);-- 更新时间 DECLARE p_createUser VARCHAR(100);-- 创建人 DECLARE p_updateUser VARCHAR(100);-- 更新人 DECLARE p_isDeleted VARCHAR(100);-- 是否删除 0否1是 -- DECLARE p_type INT DEFAULT NULL;-- 类型 1 次数套餐 2 优惠券套餐 DECLARE p_name VARCHAR(100); -- 套餐名称 DECLARE p_des VARCHAR(500); -- 套餐说明 DECLARE _done INT DEFAULT 0; DECLARE cur_1 CURSOR FOR /* 套餐表 */ select id,create_time,update_time,create_user,update_user,is_deleted,name,des from `airport_cloud`.meal; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done=1; OPEN cur_1; REPEAT FETCH cur_1 INTO p_id,p_createTime,p_updateTime,p_createUser,p_updateUser,p_isDeleted,p_name,p_des; IF NOT _done THEN /** 新表-benefit_package*/ insert into benefit_package(old_id,old_table,create_time,update_time,create_userid,update_userid,is_deleted,package_name,description) values(p_id,'meal',p_createTime,p_updateTime,p_createUser,p_updateUser,p_isDeleted,p_name,p_des); END IF; UNTIL _done END REPEAT; CLOSE cur_1; END;; DELIMITER ;
2、清空mysql数据库中所有表数据
/*----清空account_service数据库中所有表数据---- */ DELIMITER ;; CREATE PROCEDURE `pro_clear_database`() BEGIN DECLARE count INT; DECLARE tb VARCHAR(200); DECLARE dbname VARCHAR(200) DEFAULT 'account_service'; -- 清理数据库名 DECLARE tbnames cursor FOR SELECT CONCAT('TRUNCATE TABLE `',dbname,'`.`',table_name,'`') FROM information_schema.tables WHERE table_schema = dbname; SELECT count(*) INTO count FROM information_schema.tables WHERE table_schema = dbname; OPEN tbnames; loop_i:LOOP IF count = 0 THEN LEAVE loop_i; END IF; FETCH tbnames INTO tb; SET @tb = tb; PREPARE stmt FROM @tb; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET count = count - 1; END LOOP; CLOSE tbnames; END;; DELIMITER ;
3、防止select into游标不循环
/*----用户休息室权益使用记录表数据迁移---- */ DELIMITER ;; CREATE PROCEDURE `pro_move_benefit_use_record`() COMMENT '用户休息室权益使用记录表数据迁移(benefit_use_record表->user_lounge_benefit_records表)' BEGIN /**procedure body**/ DECLARE p_id VARCHAR(50); DECLARE p_orderNo VARCHAR(100); -- 订单 DECLARE p_openId VARCHAR(500); -- openid DECLARE p_benefitId VARCHAR(500); -- 权益id DECLARE p_count VARCHAR(500); -- 次数 DECLARE p_createTime VARCHAR(100); -- 创建时间 DECLARE p_createUser VARCHAR(100);-- 创建人 DECLARE p_isDeleted VARCHAR(100);-- 是否删除 0否1是 DECLARE p_loungeBenefitId bigint(20) default 0; -- 新表权益id DECLARE _done INT DEFAULT 0; DECLARE cur_1 CURSOR FOR /* 套餐表 */ select id,order_no,open_id,benefit_id,count,create_time,create_user,is_deleted from `airport_cloud`.benefit_use_record; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done=1; OPEN cur_1; REPEAT FETCH cur_1 INTO p_id,p_orderNo,p_openId,p_benefitId,p_count,p_createTime,p_createUser,p_isDeleted; IF NOT _done THEN -- 查新id select id from lounge_benefit where old_id=p_benefitId into p_loungeBenefitId; insert into user_lounge_benefit_records (is_move,old_benefit_id,old_table,create_userid,create_time,is_deleted,open_id,lounge_benefit_id, consume_count,order_no) values('1',p_benefitId,'benefit_use_record',p_createUser,p_createTime,p_isDeleted,p_openId, p_loungeBenefitId,p_count,p_orderNo); set _done = 0; -- 防止select into 游标不循环 END IF; UNTIL _done END REPEAT; CLOSE cur_1; END;; DELIMITER ;
4、大量数据插入性能问题,目前有80w多数据要迁移,按正常一秒只能插入5条左右数据,这样算需要迁移1天左右。现用批量commit性能大大提升。见以下代码:
DELIMITER ;; CREATE PROCEDURE `pro_move_user_third_party_benefit_one`() COMMENT '第三方权益数据迁移-韩国免税金卡(third_party_benefit表->user_third_party_benefit表)' BEGIN /**procedure body**/ DECLARE p_id VARCHAR(50); DECLARE p_userId VARCHAR(100); -- 用户id DECLARE p_openId VARCHAR(100); -- openid DECLARE p_mobile VARCHAR(100); -- 手机号 DECLARE p_gradeId VARCHAR(100); -- 等级id DECLARE p_gradeVersion VARCHAR(100); -- 等级版本 DECLARE p_thirdPartyBenefitId VARCHAR(100); -- 第三方权益id DECLARE p_benefitVersion VARCHAR(100); -- 第三方权益版本号 DECLARE p_benefitPackageId VARCHAR(100);-- 套餐id DECLARE p_effectiveStartDate VARCHAR(100) default null;-- 开始日期 DECLARE p_effectiveEndDate VARCHAR(100) default null; -- 结束日期 DECLARE _done INT DEFAULT 0; DECLARE cur_1 CURSOR FOR /* 表 */ select a.wx_client_id,a.open_id from `airport_cloud_test`.client_info a where a.client_phone_verified=1; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done=1; OPEN cur_1; SET AUTOCOMMIT=0; -- 只提交一次 REPEAT FETCH cur_1 INTO p_userId,p_openId; IF NOT _done THEN -- 查第三方权益 select id,benefit_version from `account_service`.third_party_benefit where benefit_name='韩国免税金卡' into p_thirdPartyBenefitId,p_benefitVersion; -- 套餐id select benefit_package_id from benefit_package_relation where is_deleted=0 and benefit_id=p_thirdPartyBenefitId limit 0,1 into p_benefitPackageId; -- 查手机、等级、有效期等信息 select mobile,grade_id,grade_version,effective_start_date,effective_end_date from user_grade where is_deleted=0 and open_id=p_openId limit 0,1 into p_mobile,p_gradeId,p_gradeVersion, p_effectiveStartDate,p_effectiveEndDate; -- 插入第三方权益 insert into user_third_party_benefit (is_move,is_deleted,user_id,open_id,mobile,source_type,source,third_party_benefit_id, third_party_benefit_version,grade_id,grade_version,send_type,create_time, effective_start_date,effective_end_date) values('1',0,p_userId,p_openId,p_mobile,2,p_benefitPackageId,p_thirdPartyBenefitId, p_benefitVersion,p_gradeId,p_gradeVersion,1,now(),p_effectiveStartDate,p_effectiveEndDate); set _done = 0; -- 防止select into 游标不循环 END IF; UNTIL _done END REPEAT; commit; CLOSE cur_1; END;; DELIMITER ;
相关推荐
user_id,duty_time 排序,分页控制逻辑,四条记录组成一条记录显示,此处一同天的日期为轴,控制分页
Node.js中调用MySQL存储过程示例涵盖了在Node.js环境中如何与MySQL数据库交互,特别是在Windows操作系统下创建和调用存储过程的一系列步骤。以下知识点详细描述了整个过程: 1. Node.js与MySQL的交互 Node.js作为...
本文实例讲述了mysql存储过程之返回多个值的方法。分享给大家供大家参考,具体如下: mysql存储函数只返回一个值。要开发返回多个值的存储过程,需要使用带有INOUT或OUT参数的存储过程。咱们先来看一个orders表它的...
MySQL存储过程是数据库管理系统中的一种重要功能,它允许开发者预编译一系列的SQL语句,形成一个可重复使用的代码块,以提高数据处理的效率和应用程序的性能。在这个"MySQL存储过程学习"的主题中,我们将深入探讨...
mysql 存储过程 ,存储函数的调用示例
创建MySQL存储过程示例 ```sql DELIMITER // CREATE PROCEDURE proc1 (OUT s INT) BEGIN SELECT COUNT(*) INTO s FROM user; END // DELIMITER ; ``` 在这个例子中,`DELIMITER //`和`DELIMITER ;`用于改变语句...
资源包中囊括了MySQL数据库中的存储过程的...该资源下所有内容都是本人的日常软件开发经验总结,对于初学者使用MySQL存储过程的程序员具有重要参考价值,问大家要10分是不过分的,用过就知道了,欢迎大家下载参考及使用
### MySQL存储过程详解 #### 一、MySQL存储过程概述 MySQL存储过程是一种预先定义并编译好的SQL语句集合,它存储在服务器上,并能在不同的应用程序中被调用执行。自MySQL 5.0版本起,引入了存储过程这一强大功能,...
### MySQL存储过程详解 #### 一、MySQL存储过程概述 MySQL 存储过程是一种服务器端的编程技术,它允许开发者创建可重复使用的代码模块。这些模块可以包含一系列复杂的 SQL 语句和其他流程控制语句。MySQL 5.0 版本...
### MySQL存储过程实现分页 #### 背景与需求 在数据库操作中,分页是一种常见的需求,尤其是在处理大量数据时。通过分页技术,可以有效地减少每次查询的数据量,提高系统的响应速度和用户体验。MySQL作为一种广泛...
MySQL存储过程 MySQL存储过程(Stored Procedure)是一种复杂的数据库对象,允许用户将多个SQL语句组合成一个单一的执行单元,以提高数据库的性能和可维护性。下面是 MySQL 存储过程的相关知识点: 存储过程的定义...
### MySQL存储过程实战知识点 #### 一、存储过程概述 MySQL 存储过程是一种预编译的 SQL 脚本,它可以包含复杂的逻辑控制结构、循环等操作,并且可以接受参数,执行完后还可以返回结果。存储过程可以提高数据处理...
他们的合作不仅提供了大量的代码示例和基础理论知识,更难得的是融入了真实世界的实践和协调,使得本书成为MySQL存储过程编程领域的重要参考资源。 在实际应用中,存储过程的正确使用能极大提升数据库应用的性能和...
MySQL存储过程是数据库管理系统中的一种重要功能,它允许开发者预定义一组SQL语句,并将其封装成一个可重复使用的单元。这个教程将深入讲解如何在MySQL中创建、调用和管理存储过程,以及它们在实际应用中的价值。 ...
### MySQL存储过程实例教程 #### 存储过程概念与优势 存储过程,作为数据库中一种预编译的SQL语句集合,旨在实现特定功能并存储于数据库内,用户仅需指定其名称及必要参数即可调用执行。这种设计极大地简化了...
MySQL存储过程是数据库管理系统中的一种重要功能,它允许开发者预定义一组SQL语句,然后作为一个单元来执行。在MySQL 5.0版本之后,存储过程得到了广泛的支持,为数据库操作提供了许多优势,如提高性能、增强安全性...
以下是一个简单的分页存储过程示例: ```sql DELIMITER // CREATE PROCEDURE GetPagedData(IN page INT, IN pageSize INT, OUT totalRows INT) BEGIN DECLARE rowCount INT DEFAULT 0; SELECT COUNT(*) INTO ...