mysql分区表：日期分区

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='日志信息表';

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_pton 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 BEGINinsert 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://blog.csdn.net/Phillip_xian/article/details/130606475