Mysql 一个简单的拆分表存储过程

来源：互联网发布：网络营销策划书的内容编辑：程序博客网时间：2024/06/06 19:44

需求：

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

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

0 0