Mysql 一个简单的拆分表存储过程_mysql慢sql拆分为存储过程-优快云博客

本文链接：https://blog.youkuaiyun.com/u012814696/article/details/60122445

本文介绍了一个用于MySQL 5.6版本的存储过程，该过程可以创建新的表来存储从原始大表中按指定日期筛选出来的数据。通过定义表结构和插入相关记录，实现了数据的有效拆分。

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

需求：

需要拆分一个大表，并且按照原表新建一个按照额定日期的形式的新表，和原表一模一样并插入相关的数据：

Mysql 版本：5.6

create procedure sm_creatMonthTbProc(mdate varchar (8))  
begin  
declare sm_results_name varchar(30); 
declare table_name varchar(30);
declare existsFlag int default 0;
SET @table_name = 'dt_aleve';
set @sm_results_name = concat('d',mdate,'t_aleve');  
SET @drop_sql = CONCAT("DROP TABLE IF EXISTS ",@sm_results_name );
set @csql = concat("create table if not exists ",@sm_results_name , " 
(`sid` bigint(20) NOT NULL AUTO_INCREMENT,
  `fid` bigint(20) DEFAULT NULL,
  `tims` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `BANK` varchar(4) DEFAULT NULL,
  `CARDNBR` varchar(19) DEFAULT NULL,
  `AMOUNT` varchar(17) DEFAULT NULL,
  `CUR_NUM` varchar(3) DEFAULT NULL,
  `FUND_CHECK_RESULT` char(1) NOT NULL DEFAULT '0',
  `RESV` varchar(200) DEFAULT NULL,
  `unid` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`sid`),
  UNIQUE KEY `idx_inpdate_inptime_tranno` (`INPDATE`,`INPTIME`,`TRANNO`) USING BTREE,
  KEY `idx_inpdate` (`INPDATE`),
  KEY `idx_transtype` (`TRANSTYPE`),
  KEY `idx_unid` (`unid`),
  KEY `IDX_TR_UN_IN_AM` (`TRANSTYPE`,`unid`,`INPDATE`,`AMOUNT`),
  KEY `IDX_FID` (`fid`),
  KEY `idx_revind` (`REVIND`) USING BTREE,
  KEY `idx_cardnbr` (`CARDNBR`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=57137056 DEFAULT CHARSET=utf8 ;");  
SET @inster_data = CONCAT("INSERT INTO ",@sm_results_name," (SELECT * FROM ",@table_name," WHERE INPDATE=",mdate,")");
PREPARE drop_tab from @drop_sql; 
EXECUTE drop_tab; 
PREPARE create_stmt from @csql; 
EXECUTE create_stmt; 
PREPARE data_inster FROM @inster_data;
EXECUTE data_inster;
end