1.创建分区表
CREATE TABLE `api_log` (
`requestId` varchar(64) NOT NULL COMMENT '调用的唯一标示',
`systemCode` varchar(64) NOT NULL COMMENT '业务系统编码',
`tongID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`tong_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`pt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '分区字段',
PRIMARY KEY (`tongID`,`requestId`,`pt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='日志信息表'
/*!50100 PARTITION BY RANGE (TO_DAYS(pt))
(PARTITION p20230510 VALUES LESS THAN (739015) ENGINE = InnoDB,
PARTITION p20230511 VALUES LESS THAN (739016) ENGINE = InnoDB) */;
2.查看分区
-- 01 查询单分区
SELECT * from api_log partition (p20230510)
-- 02 查看所有分区
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM
INFORMATION_SCHEMA.PARTITIONS
WHERE
TABLE_NAME = 'api_log';
3.添加分区
-- 添加分区
ALTER TABLE hz_api_log_delta add PARTITION (PARTITION p20230512 VALUES LESS THAN (TO_DAYS('2023-05-12')));
4.存储过程：分区删除与创建
delimiter //
CREATE PROCEDURE `pro_create_del_pt`()
BEGIN
#Routine body goes here...
## 01.新建后面两天的分区
## 02获取当前时间
SELECT CONCAT('p',DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 2 DAY),'%Y%m%d')) into @pname;
SELECT DATE_ADD(NOW(),INTERVAL 2 DAY) into @pdate;
#删除的分区时间
SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -17 DAY) ,'%Y%m%d') into @ddate;
## 03新增分区
-- ALTER TABLE api_log add PARTITION (PARTITION p20230509 VALUES LESS THAN (TO_DAYS(DATE_ADD(NOW(),INTERVAL 1 DAY))));
set @sql_add_p=concat("ALTER TABLE api_log add PARTITION (PARTITION ",@pname," VALUES LESS THAN (TO_DAYS('",@pdate,"')))");
prepare sql_add_p from @sql_add_p;
EXECUTE sql_add_p ;
DEALLOCATE PREPARE sql_add_p;
## 04 删除分区
#alter table api_log drop partition p20230507;
set @sql_del_p=concat("ALTER TABLE api_log drop PARTITION p",@ddate);
prepare sql_del_p from @sql_del_p;
EXECUTE sql_del_p ;
DEALLOCATE PREPARE sql_del_p;
END
//
delimiter ;
5.事件定时
-- 创建事件
delimiter $$
create event event_pro_create_del_pt
on schedule
every 1 DAY
STARTS '2023-05-10 20:00:00 '
comment '每日20:00执行'
DO call pro_create_del_pt();
$$
delimiter ;
-- 查看事件
show events;
或者
select * from mysql.event;
6.触发器设计：子表每插入一行，总表获得一行
-- 创建事件
delimiter $$
CREATE TRIGGER `trigger_api_log` AFTER INSERT ON `api_log`
FOR EACH ROW BEGIN
insert into hz_api.api_log_all
(requestId,systemCode,sjly_part) VALUES
(NEW.requestId,NEW.systemCode,'区县');
end;
$$
delimiter ;
7.创建索引
-- 创建普通索引：在tong_time字段
ALTER TABLE db_base.api_log ADD INDEX index_name ( tong_time );
-- 查看索引
show index from db_base.api_log;
8.添加枚举型字段
ALTER TABLE db_base.api_log add SJLY_PART enum('线上','线下') default null COMMENT '数据来源';
文章来源地址https://www.toymoban.com/news/detail-596271.html
文章来源：https://www.toymoban.com/news/detail-596271.html
到了这里，关于mysql分区表：日期分区的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
