网站建设培训业务心得,建设牌安全带厂家网站,wordpress开启多站点,济南网络公司排行什么时候用存储过程合适
当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时就要考虑用存储过程#xff1b;当在一个事务的完成需要很复杂的商业逻辑时#xff08;比如#xff0c;对多个数据的操作#xff0c;对多个状态的判断更改等#xff09;要考虑#xff1b…什么时候用存储过程合适
当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时就要考虑用存储过程当在一个事务的完成需要很复杂的商业逻辑时比如对多个数据的操作对多个状态的判断更改等要考虑还有就是比较复杂的统计和汇总也要考虑。
使用存储过程的优缺点
优点
速度快。尤其对于较为复杂的逻辑减少了网络流量之间的消耗。写程序简单采用存储过程调用类调用任何存储过程都只要1-2行代码。升级、维护方便。调试其实也并不麻烦可以用查询分析器。
缺点
可移植性差。数据量大时会比较耗时。
on duplicate key update实现(批量)插入或更新操作
一. 背景
背景业务上经常有这样的需求场景如果之前有这条数据就做更新如果没有就做新增。常用的处理方案通过主键id或者其他唯一键判断DB中是否有这条数据再判断调用insert或update语句。这样做逻辑处理起来比较复杂降低代码效率而且如果并发量高可能会存在数据问题。
二. on duplicate key update概述
为了应对这种业务场景MySQL有一种专有语法insert into ... on duplicate key update一条SQL语句实现插入或更新可单条可批量。使用要点 表要求必须有主键或唯一索引才能起效果否则insert或update无效。该语法是根据主键或唯一键来判断是新增还是更新。VALUES() 后面应为需要更新的字段不需要更新的字段不用罗列。遇到已存在记录根据唯一键或主键时自动更新已有的数据如果表中有多个唯一键可以是单列索引或复合索引则任意一个唯一键冲突时都会自动更新数据。所有操作均由SQL处理不需要额外程序代码分析能够大幅提高程序执行效率。
三. on duplicate key update的使用
创建测试—book科目表
CREATE TABLE book (id int NOT NULL AUTO_INCREMENT,unique_code varchar(30) NOT NULL,book_name varchar(20) DEFAULT NULL,PRIMARY KEY (id),UNIQUE KEY unique_code (unique_code) USING BTREE COMMENT book unique_code
) ENGINEInnoDB AUTO_INCREMENT3 DEFAULT CHARSETutf8;执行如下命令
insert into book(unique_code,book_name) values (zs-001,中文) on duplicate key update book_name数学;成功插入一条数据
再次执行步骤一命令你会发现book_name值改变 Mysql中substring_index函数的使用
一、语法
substring_index“待截取有用部分的字符串”“截取数据依据的字符”截取字符的位置N
二、案例
SELECT SUBSTRING_INDEX(‘58.33.115.130’, ‘.’, 1); //结果是58
//以第一个句号为分割截取
SELECT SUBSTRING_INDEX(‘58.33.115.130’, ‘.’, -1); //结果是130
//从后面开始算第一个句号
SELECT SUBSTRING_INDEX(‘58.33.115.130’, ‘.’, 2); //结果是58.33
使用PREPARE 动态执行sql语句
PREPARE STMT FROM sql_str; EXECUTE STMT; DEALLOCATE PREPARE STMT; 1: DROP TABLE IF EXISTS alarm;2: CREATE TABLE alarm ( id int(11) NOT NULL auto_increment, name varchar(255) default NULL,PRIMARY KEY (id));3: 4: INSERT INTO alarm (name) values(aa);5: INSERT INTO alarm (name) values(bb);6: 7: DROP procedure if exists statalarm;8: delimiter // 9: create procedure statalarm()10: begin11: SET id 1,2;12: 13: SET sql select * from alarm where id IN (?);14: PREPARE stmt FROM sql;15: /* 16: 注意 EXECUTE 的最终语句是select * from alarm where id IN (1,2);17: 而不是 select * from alarm where id IN (1,2);18: 这是因为如果用户变量的值是字符串在EXECUTE时 会自动的在变量的值前后加上引号19: */20: EXECUTE stmt USING id;21: DEALLOCATE PREPARE stmt;22: /*23: 如果想要 组成select * from alarm where id IN (1,2);可以使用下面的语句24: */25: SET sql concat(select * from alarm where id IN (,id,));26: PREPARE stmt FROM sql;27: EXECUTE stmt ;28: DEALLOCATE PREPARE stmt;29: 30: end;//31: delimiter ;DECLARE CONTINUE HANDLER FOR NOT FOUND 解释
在mysql的存储过程中经常会看到这句话DECLARE CONTINUE HANDLER FOR NOT FOUND。
它的含义是若没有数据返回,程序继续,并将变量IS_FOUND设为0 。
JAVA调用存储过程–MYSQL
业务代码
private void statFieldCollectByOpFlag(int opFlag){log.info(开始统计成果量opflag-{}, opFlag);try {Date date new Date();String statDate DateUtil.formatDate(date, yyyy-MM-dd);// 如果当前时间在6点以前则统计前一天的int hour date.getHours();if (hour 6){statDate DateUtil.formatDate(DateUtil.dateDiffer(date, D,-1), yyyy-MM-dd);}MapString, Object map new HashMapString, Object();map.put(i_op_flag, opFlag);map.put(i_stat_date, statDate);map.put(o_err_no, 0);map.put(o_err_msg, );try {statFruitRecordService.statFruitCountInfoByProc(map);BigDecimal bigDecimal (BigDecimal)map.get(o_err_no);Integer errNo bigDecimal.intValue();if ( !errNo.equals(0) ){String errMsg (String)map.get(o_err_msg);log.error(统计成果量[opFlag]异常[errNo.toString()]errMsg);}} catch (Exception e) {log.error(统计成果量[opFlag]异常, e);}log.info(结束统计成果量opflag-{}, opFlag);} catch (Exception e) {log.error(结束统计成果量[opFlag]异常, e);}}
Mapper代码
select idstatFruitCountInfo parameterTypemap statementTypeCALLABLE{call sp_stat_fruit_record(#{map.i_op_flag , modeIN , jdbcTypeNUMERIC},#{map.i_stat_date , modeIN , jdbcTypeVARCHAR},#{map.o_err_no , modeOUT , jdbcTypeNUMERIC},#{map.o_err_msg , modeOUT , jdbcTypeVARCHAR})}/select
存储过程
-- --------------------------------------------------------
-- 主机: xx.xx.xx.xx
-- 服务器版本: 5.7.27-log - MySQL Community Server (GPL)
-- 服务器操作系统: linux-glibc2.12
-- HeidiSQL 版本: 11.0.0.5919
-- --------------------------------------------------------/*!40101 SET OLD_CHARACTER_SET_CLIENTCHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET OLD_FOREIGN_KEY_CHECKSFOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS0 */;
/*!40101 SET OLD_SQL_MODESQL_MODE, SQL_MODENO_AUTO_VALUE_ON_ZERO */;-- 导出 存储过程 sp_stat_fruit_record 结构
DROP PROCEDURE IF EXISTS sp_stat_fruit_record;
DELIMITER //
CREATE PROCEDURE sp_stat_fruit_record(IN i_op_flag int,IN i_stat_date varchar(10),OUT o_err_no int,OUT o_err_msg varchar(128)
)COMMENT [104]入库成果量统计
update_proc:
BEGIN#Routine body goes here...DECLARE sp_code INT DEFAULT 104;-- START Declare ConditionsDECLARE sql_err_code INT DEFAULT 0;DECLARE var_cnt INT DEFAULT 0;DECLARE var_size INT DEFAULT 0;DECLARE var_idx INT DEFAULT 0;DECLARE var_tail varchar(16) DEFAULT ;DECLARE CONTINUE HANDLER FOR 1054 SET sql_err_code 1054;-- Unknown columnDECLARE CONTINUE HANDLER FOR 1062 SET sql_err_code 1062;-- Duplicate entryDECLARE CONTINUE HANDLER FOR 1329 SET sql_err_code 0;-- No data - zero rows fetched, SELECT ed, or processedDECLARE CONTINUE HANDLER FOR SQLEXCEPTION set sql_err_code 9999;-- END Declare Conditions-- check param-- START TRANSACTION;IF 1 ! 1OR (i_op_flag IS NULL AND (param_name : i_op_flag))OR (i_stat_date IS NULL AND (param_name : i_stat_date))THENSET o_err_no fn_sp_errno(sql_err_code, sp_code, 1);SET o_err_msg CONCAT(缺少参数, param_name);LEAVE update_proc;END IF;SET element_codes aoi,building,aoi_gate,unit,poi,room,internal_road;-- i_stat_date格式 YYYY-MM-DD-- i_op_flag-- 1 转历史-- 2 截止昨日总量统计-- 3 当日审核入库统计-- 4 当日采集入库统计IF 1 i_op_flag THEN# 保留1周的数据SET del_date CONCAT(DATE_SUB(i_stat_date, INTERVAL 1 WEEK), 00:00:00);SET var_size LENGTH(element_codes)- LENGTH(REPLACE(element_codes,,,)) 1;SET var_idx1;WHILE var_idx var_size DOSET var_tail SUBSTRING_INDEX(SUBSTRING_INDEX(element_codes,,,var_idx),,,-1);SET sql_str CONCAT(INSERT INTO his_fruit_, var_tail, SELECT * FROM t_fruit_, var_tail, WHERE update_time , del_date, );PREPARE STMT FROM sql_str;EXECUTE STMT;DEALLOCATE PREPARE STMT;IF 0 sql_err_code THENSET o_err_no fn_sp_errno(sql_err_code, sp_code, 11);SET o_err_msg CONCAT(审核成果转历史失败, var_tail);LEAVE update_proc;LEAVE update_proc;END IF;SET sql_str CONCAT(DELETE FROM t_fruit_, var_tail, WHERE update_time , del_date, );PREPARE STMT FROM sql_str;EXECUTE STMT;DEALLOCATE PREPARE STMT;IF 0 sql_err_code THENSET o_err_no fn_sp_errno(sql_err_code, sp_code, 12);SET o_err_msg CONCAT(审核成果转历史失败, var_tail);LEAVE update_proc;END IF;SET sql_str CONCAT(INSERT INTO his_collect_fruit_, var_tail, SELECT * FROM t_collect_fruit_, var_tail, WHERE update_time , del_date, );PREPARE STMT FROM sql_str;EXECUTE STMT;DEALLOCATE PREPARE STMT;IF 0 sql_err_code THENSET o_err_no fn_sp_errno(sql_err_code, sp_code, 13);SET o_err_msg CONCAT(采集成果转历史失败, var_tail);LEAVE update_proc;END IF;SET sql_str CONCAT(DELETE FROM t_collect_fruit_, var_tail, WHERE update_time , del_date, );PREPARE STMT FROM sql_str;EXECUTE STMT;DEALLOCATE PREPARE STMT;IF 0 sql_err_code THENSET o_err_no fn_sp_errno(sql_err_code, sp_code, 14);SET o_err_msg CONCAT(采集成果转历史失败, var_tail);LEAVE update_proc;END IF;SET var_idx var_idx 1;END WHILE;-- 2 截止昨日总量统计ELSEIF 2 i_op_flag THENSELECT COUNT(1) INTO var_cnt FROM t_stat_fruit_record WHERE stat_date 0000-00-00;IF 0 sql_err_code THENSET o_err_no fn_sp_errno(sql_err_code, sp_code, 21);SET o_err_msg 截止昨日总量统计失败;LEAVE update_proc;END IF;IF var_cnt 0 THENINSERT INTO t_stat_fruit_recordSELECT 0000-00-00, sum(right_count), sum(error_count), ALL,sum(right_count), sum(error_count), now(), now() FROM t_stat_fruit_record;INSERT INTO t_stat_fruit_recordSELECT 0000-00-00, sum(right_count), sum(error_count), element_code, sum(right_count), sum(error_count), now(), now() FROM t_stat_fruit_recordWHERE element_code ! ALLGROUP BY element_code;ELSEUPDATE t_stat_fruit_record SET plan_project_id right_count, plan_collect_id error_count, update_time now() WHERE stat_date 0000-00-00;END IF;IF 0 sql_err_code THENSET o_err_no fn_sp_errno(sql_err_code, sp_code, 22);SET o_err_msg 截止昨日总量统计失败;LEAVE update_proc;END IF;-- 3 当日审核入库统计ELSEIF 3 i_op_flag THENSET start_date CONCAT(i_stat_date, 00:00:00);SET end_date CONCAT(i_stat_date, 23:59:59);SET var_size LENGTH(element_codes)- LENGTH(REPLACE(element_codes,,,)) 1;SET var_idx1;WHILE var_idx var_size DOSET var_tail SUBSTRING_INDEX(SUBSTRING_INDEX(element_codes,,,var_idx),,,-1);SET sql_str CONCAT( INSERT INTO t_stat_fruit_recordSELECT ,i_stat_date,, project_manage_id, plan_id, , var_tail,, SUM(CASE WHEN result_type 1 THEN 1 ELSE 0 END) AS rightCount, SUM(CASE WHEN result_type 2 THEN 1 ELSE 0 END) AS errorCount, now(), now()FROM t_fruit_, var_tail, WHERE update_time , start_date, AND update_time , end_date, GROUP BY project_manage_id, plan_idON DUPLICATE KEY UPDATEright_count VALUES(right_count), error_count VALUES(error_count), update_time VALUES(update_time) );-- SELECT sql_str;PREPARE STMT FROM sql_str;EXECUTE STMT;DEALLOCATE PREPARE STMT;IF 0 sql_err_code THENSET o_err_no fn_sp_errno(sql_err_code, sp_code, 31);SET o_err_msg CONCAT(截止昨日总量统计失败, var_tail);LEAVE update_proc;END IF;SET var_idx var_idx 1;END WHILE;-- 4 当日采集入库统计ELSEIF 4 i_op_flag THENSET start_date CONCAT(i_stat_date, 00:00:00);SET end_date CONCAT(i_stat_date, 23:59:59);SET var_size LENGTH(element_codes)- LENGTH(REPLACE(element_codes,,,)) 1;SET var_idx1;WHILE var_idx var_size DOSET var_tail SUBSTRING_INDEX(SUBSTRING_INDEX(element_codes,,,var_idx),,,-1);SET sql_str CONCAT( INSERT INTO t_stat_fruit_collectSELECT ,i_stat_date,, project_manage_id, plan_id, , var_tail,, SUM(CASE WHEN result_type 1 THEN 1 ELSE 0 END) AS rightCount, SUM(CASE WHEN result_type 2 THEN 1 ELSE 0 END) AS errorCount, now(), now()FROM t_collect_fruit_, var_tail, WHERE update_time , start_date, AND update_time , end_date, GROUP BY project_manage_id, plan_idON DUPLICATE KEY UPDATEright_count VALUES(right_count), error_count VALUES(error_count), update_time VALUES(update_time) );-- SELECT sql_str;PREPARE STMT FROM sql_str;EXECUTE STMT;DEALLOCATE PREPARE STMT;IF 0 sql_err_code THENSET o_err_no fn_sp_errno(sql_err_code, sp_code, 41);SET o_err_msg CONCAT(截止昨日总量统计失败, var_tail);LEAVE update_proc;END IF;SET var_idx var_idx 1;END WHILE;ELSESET o_err_no fn_sp_errno(sql_err_code, sp_code, 99);SET o_err_msg 未支持的操作类型;LEAVE update_proc;END IF;-- ok-- COMMIT;-- okSET o_err_no 0;
END//
DELIMITER ;/*!40101 SET SQL_MODEIFNULL(OLD_SQL_MODE, ) */;
/*!40014 SET FOREIGN_KEY_CHECKSIF(OLD_FOREIGN_KEY_CHECKS IS NULL, 1, OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENTOLD_CHARACTER_SET_CLIENT */;
