//分区函数
CREATE DEFINER=`root`@`%` FUNCTION `query_str`() RETURNS varchar(2000) CHARSET utf8
BEGIN
DECLARE start_date date DEFAULT '2018-05-17';
DECLARE res_str varchar(2000) DEFAULT '';
WHILE start_date<'2018-06-16' DO
set res_str = concat(res_str,'PARTITION p',DATE_FORMAT(DATE_SUB(start_date,interval 1 day),'%Y%m%d'),' VALUES LESS THAN (TO_DAYS(''',start_date,'''))',',');
set start_date = DATE_ADD(start_date,INTERVAL 1 DAY);
END WHILE;
RETURN res_str;
END
//自动分区事件
DELIMITER $$
CREATE EVENT `memberorder_partition` ON SCHEDULE EVERY 1 DAY STARTS '2016-11-04 23:30:01' ON COMPLETION NOT PRESERVE DISABLE ON SLAVE COMMENT '分区表memberorder维护定时调度 1.每天23:30创建下一天分区' DO BEGIN
/*START auto_ADD_partition*/
SET @auto_add_sql = CONCAT(
"alter table `memberorder` add partition (partition p",
DATE_FORMAT(DATE_ADD(CURRENT_DATE(),INTERVAL 1 DAY), '%Y%m%d'),
" values less than (to_days('",
DATE_ADD(CURRENT_DATE(),INTERVAL 2 DAY),
"')))"
) ;
PREPARE stmt_add FROM @auto_add_sql ;
EXECUTE stmt_add ;
/*END auto_ADD_partition*/
END$$
DELIMITER ;
//创建新分区表
CREATE TABLE `memberorder_tmp` (
`Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`MemberId` int(11) NOT NULL DEFAULT '0' COMMENT '商户编号',
`OrderId` varchar(64) CHARACTER SET utf32 NOT NULL DEFAULT '' COMMENT '系统订单编号_平台',
`OrderStatus` int(11) NOT NULL DEFAULT '0' COMMENT '订单状态',
`OrderStatusTitile` varchar(50) NOT NULL DEFAULT '' COMMENT '订单状态描述',
`OrderTime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '订单时间',
`OrderAmount` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT '订单金额',
`OrderAmountFee` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT '平台利润-实际利润包含分润',
`OrderMoneyFee` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT '平台利润-不包含分润 下游手续费-上游成本',
`OrderAmountRate` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT '平台利润千分比',
`OrderNotifyUrl` varchar(255) NOT NULL DEFAULT '' COMMENT '平台收取上游回调地址',
`MerchantChannelRate` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT '商户费率',
`MerchantSharpProfit` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT '商户分润',
`TopAmountFee` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT '上游收取平台费率金额-手续费',
`TopSharpProfitFee` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT '上游分润金额',
`ChannelPayTypeRate` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT '上游费率',
`ChannelSharpProfit` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT '通道上游分润',
`LowAmountFee` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT '平台收取商户费率金额-手续费',
`LowSharpProfitFee` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT '下游分润金额',
`OrderIsComplete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '订单是否完成',
`PayTypeId` int(11) NOT NULL DEFAULT '0' COMMENT '支付方式编号',
`ChannelId` int(11) NOT NULL COMMENT '通道编号',
`ChannelPayId` int(11) NOT NULL DEFAULT '0' COMMENT 'channelpaytype支付通道编号',
`TopOrderId` varchar(64) NOT NULL DEFAULT '' COMMENT '通道订单编号_上游',
`MerchantOrderId` varchar(64) NOT NULL DEFAULT '' COMMENT '商户订单编号_下游',
`MerchantOrderTime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '商户提交订单时间',
`MerchantNotifyUrl` varchar(500) NOT NULL DEFAULT '' COMMENT '商户回调地址',
`MerchantHttpJson` text NOT NULL COMMENT '商户请求时数据包',
`IsTopNotify` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否收到上游回调',
`IsTopNotifyResPond` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否响应了上游回调',
`TopNotify` text NOT NULL COMMENT '上游回调内容',
`TopNotifyTime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '上游回调时间',
`IsLowNotify` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否发送下游回调',
`IsLowNotifyResPond` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否收到下游响应回调',
`LowNotify` text NOT NULL COMMENT '下游回调内容',
`LowNotifyTime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '下游回调发送时间',
`ChannelStatemenType` int(11) NOT NULL DEFAULT '1' COMMENT '通道结算类型 1-T1 2-T0 3-D1 4-D0',
`IsSettlementResPond` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否执行了结算',
`IsBillSettlement` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否完成账单结算',
`SettlementType` int(11) NOT NULL DEFAULT '0' COMMENT '结算类型 0-未进行结算 1-T1 2-T0 3-D1 4-D0',
`SettlementAmountFee` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT '账单结算金额',
`SettlementTime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '账单结算时间',
`SettlementDayId` int(11) NOT NULL DEFAULT '0' COMMENT '结算日表编号',
`SettlementMonthId` int(11) NOT NULL COMMENT '结算月表编号',
`SettlementYearId` int(11) NOT NULL COMMENT '结算年表编号',
`SalespersonId` int(11) NOT NULL DEFAULT '0' COMMENT '业务员编号',
`SalesmanDayId` int(11) NOT NULL COMMENT '结算业务员日表',
`ConditionTablekey` int(11) NOT NULL DEFAULT '0' COMMENT '分表键',
`ExceptionStatus` int(11) NOT NULL DEFAULT '0' COMMENT '是否异常 0-未异常 1-支付异常 2-回调异常',
`ExceptionTitle` varchar(255) NOT NULL DEFAULT '' COMMENT '异常信息',
`Remark` varchar(255) NOT NULL DEFAULT '' COMMENT '订单备注',
`CreateUser` int(11) NOT NULL DEFAULT '0' COMMENT '创建者',
`CreateTime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '创建时间',
`ModifyUser` int(11) NOT NULL DEFAULT '0' COMMENT '修改者编号',
`ModifyTime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '修改时间',
`IsValid` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否有效',
`IsDel` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除',
PRIMARY KEY (`Id`,`CreateTime`) USING BTREE,
UNIQUE KEY `Id` (`Id`,`CreateTime`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=142473 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
PARTITION BY RANGE (TO_DAYS(CreateTime))
(
PARTITION p20180517 VALUES LESS THAN (TO_DAYS('2018-05-18')),PARTITION p20180518 VALUES LESS THAN (TO_DAYS('2018-05-19')),PARTITION p20180519 VALUES LESS THAN (TO_DAYS('2018-05-20')),PARTITION p20180520 VALUES LESS THAN (TO_DAYS('2018-05-21')),PARTITION p20180521 VALUES LESS THAN (TO_DAYS('2018-05-22')),PARTITION p20180522 VALUES LESS THAN (TO_DAYS('2018-05-23')),PARTITION p20180523 VALUES LESS THAN (TO_DAYS('2018-05-24')),PARTITION p20180524 VALUES LESS THAN (TO_DAYS('2018-05-25')),PARTITION p20180525 VALUES LESS THAN (TO_DAYS('2018-05-26')),PARTITION p20180526 VALUES LESS THAN (TO_DAYS('2018-05-27')),PARTITION p20180527 VALUES LESS THAN (TO_DAYS('2018-05-28')),PARTITION p20180528 VALUES LESS THAN (TO_DAYS('2018-05-29')),PARTITION p20180529 VALUES LESS THAN (TO_DAYS('2018-05-30')),PARTITION p20180530 VALUES LESS THAN (TO_DAYS('2018-05-31')),PARTITION p20180531 VALUES LESS THAN (TO_DAYS('2018-06-01')),PARTITION p20180601 VALUES LESS THAN (TO_DAYS('2018-06-02')),PARTITION p20180602 VALUES LESS THAN (TO_DAYS('2018-06-03')),PARTITION p20180603 VALUES LESS THAN (TO_DAYS('2018-06-04')),PARTITION p20180604 VALUES LESS THAN (TO_DAYS('2018-06-05')),PARTITION p20180605 VALUES LESS THAN (TO_DAYS('2018-06-06')),PARTITION p20180606 VALUES LESS THAN (TO_DAYS('2018-06-07')),PARTITION p20180607 VALUES LESS THAN (TO_DAYS('2018-06-08')),PARTITION p20180608 VALUES LESS THAN (TO_DAYS('2018-06-09')),PARTITION p20180609 VALUES LESS THAN (TO_DAYS('2018-06-10')),PARTITION p20180610 VALUES LESS THAN (TO_DAYS('2018-06-11')),PARTITION p20180611 VALUES LESS THAN (TO_DAYS('2018-06-12')),PARTITION p20180612 VALUES LESS THAN (TO_DAYS('2018-06-13')),PARTITION p20180613 VALUES LESS THAN (TO_DAYS('2018-06-14')),PARTITION p20180614 VALUES LESS THAN (TO_DAYS('2018-06-15')),PARTITION p20180615 VALUES LESS THAN (TO_DAYS('2018-06-16')),PARTITION p20180616 VALUES LESS THAN (TO_DAYS('2018-06-17'))
)
//重命表名
RENAME TABLE memberorder TO memberorderhis,memberorder_tmp TO memberorder;
//查看表分区情况
SELECT PARTITION_NAME,TABLE_ROWS,table_name FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'memberorder';
//将源有数据转移到新表
INSERT INTO memberorder SELECT * FROM memberorderhis;
1.查看事件是否开启
SHOW VARIABLES LIKE 'event_scheduler'
2.设置当前事件开启
SET GLOBAL event_scheduler = 1;
//删除分区
alter table memberorder drop partition p20180616
//增加虚拟列
ALTER TABLE `memberorder`
ADD COLUMN `minute_subsection_5` char(19) AS (concat(convert(date_format(`OrderTime`,'%Y-%m-%d %H:') using utf8mb4),LPAD((floor((date_format(`OrderTime`,'%i') / 5)) * 5), 2, '0'),'_',LPAD(((floor((date_format(`OrderTime`,'%i') / 5)) + 1) * 5), 2, '0'))) STORED NULL AFTER `IsDel`
//增加索引
ALTER TABLE `memberorder`
ADD index (`minute_subsection_5` )
select minute_subsection_5,count(*),sum(t.OrderAmount) from memberorder t
where t.MemberId=1 and t.OrderStatus=1
group by t.minute_subsection_5;
--- 十分快了，你整合到你的SQL里 试一下
select * from memberorder t where t.OrderTime BETWEEN '2019-12-25 17:10:00' and '2019-12-25 17:15:00';