mysql 时间段分区表

酷酷的橙子
已于 2024-03-01 10:35:11 修改
阅读量116
点赞数 1
文章标签： mysql 数据库
于 2024-02-29 17:59:52 首次发布
本文链接：https://blog.csdn.net/weixin_44834916/article/details/136377442
版权
-- 创建分区表
CREATE TABLE sys_app_log (
  `id` bigint NOT NULL COMMENT 'id' ,
  `log_type_id` tinyint unsigned DEFAULT NULL COMMENT '日志类型ID 1：APP 2：管理后台',
  `log_type_name` varchar(32)   DEFAULT NULL COMMENT '日志类型名称',
  `event_type_id` tinyint unsigned DEFAULT NULL COMMENT '事件类型ID',
  `event_type_name` varchar(50)   DEFAULT NULL COMMENT '事件类型名称',
  `module` varchar(32)   DEFAULT NULL COMMENT '模块名称',
  `log_level_id` tinyint unsigned DEFAULT NULL COMMENT '日志级别ID 1：信息 2：错误 3：异常',
  `log_level_name` varchar(50)   DEFAULT NULL COMMENT '日志级别名称',
  `request_uri` varchar(200)   DEFAULT NULL COMMENT '请求URI',
  `request_method` varchar(20)   DEFAULT NULL COMMENT '请求方式',
  `request_params` text   COMMENT '请求参数',
  `response_params` text   COMMENT '响应参数',
  `request_time` int unsigned DEFAULT NULL COMMENT '耗时(毫秒)',
  `source_port` varchar(32)   DEFAULT NULL COMMENT '源端口',
  `target_port` varchar(32)   DEFAULT NULL COMMENT '目标端口',
  `user_agent` varchar(500)   DEFAULT NULL COMMENT '用户代理',
  `ip` varchar(160)   DEFAULT NULL COMMENT 'IP地址',
  `machine_name` varchar(50)   DEFAULT NULL COMMENT '机器名',
  `mac_address` varchar(50)   DEFAULT NULL COMMENT 'MAC地址',
  `status` tinyint unsigned DEFAULT NULL COMMENT '事件状态 0：失败 1：成功',
  `error_info` text   COMMENT '异常信息',
  `creator_name` varchar(50)   DEFAULT NULL COMMENT '操作人姓名',
  `creator` bigint DEFAULT NULL COMMENT '操作人',
  `create_date` datetime DEFAULT NULL COMMENT '创建时间',
  `creator_account` varchar(50)   DEFAULT NULL COMMENT '操作人账号'
) COMMENT='app日志';

ALTER TABLE sys_app_log
 PARTITION BY RANGE(TO_DAYS(create_date))(
    PARTITION P202301 VALUES LESS THAN (TO_DAYS('2023-01-31'))engine = innodb,
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-02-28'))engine = innodb,
    PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-03-31'))engine = innodb,
    PARTITION p202304 VALUES LESS THAN (TO_DAYS('2023-04-30'))engine = innodb,
    PARTITION p202305 VALUES LESS THAN (TO_DAYS('2023-05-31'))engine = innodb,
    PARTITION p202306 VALUES LESS THAN (TO_DAYS('2023-06-30'))engine = innodb,
    PARTITION p202307 VALUES LESS THAN (TO_DAYS('2023-07-31'))engine = innodb,
    PARTITION p202308 VALUES LESS THAN (TO_DAYS('2023-08-31'))engine = innodb,
    PARTITION p202309 VALUES LESS THAN (TO_DAYS('2023-09-30'))engine = innodb,
    PARTITION p202310 VALUES LESS THAN (TO_DAYS('2023-10-31'))engine = innodb,
    PARTITION p202311 VALUES LESS THAN (TO_DAYS('2023-11-30'))engine = innodb,
    PARTITION p202312 VALUES LESS THAN (TO_DAYS('2023-12-31'))engine = innodb,
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-01-31'))engine = innodb,
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-02-29'))engine = innodb,
    PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-03-31'))engine = innodb,
    PARTITION p202404 VALUES LESS THAN (TO_DAYS('2024-04-30'))engine = innodb,
    PARTITION p202405 VALUES LESS THAN (TO_DAYS('2024-05-31'))engine = innodb
 )
 -- 添加新的分区
ALTER TABLE sys_app_log ADD PARTITION ( PARTITION p202406 VALUES LESS THAN (TO_DAYS('2024-06-30'))engine = innodb)

-- 查看当前分区表结构及数据
SHOW CREATE TABLE sys_app_log; -- 显示创建表的DDL语句
SELECT * FROM sys_app_log; -- 显示所有数据



-- 添加全局索引
ALTER TABLE sys_app_log ADD INDEX idx_creator (creator);
ALTER TABLE sys_app_log ADD INDEX idx_module (module);
ALTER TABLE sys_app_log ADD INDEX idx_request_uri (request_uri);


-- id字段的值是非空且唯一，为该字段创建唯一索引：
create unique index idx_id on sys_app_log(id,create_date);


-- 再次查询分区表结构及数据
SHOW CREATE TABLE sys_app_log; -- 显示更新后的DDL语句
SELECT count(*) FROM sys_app_log; -- 显示所有数据

-- 查询特定分区（p202310）的所有数据
SELECT count(*) FROM sys_app_log PARTITION (p202301)



INSERT INTO `sys_app_log` (`id`, `log_type_id`, `log_type_name`, `event_type_id`, `event_type_name`, `module`, `log_level_id`, `log_level_name`, `request_uri`, `request_method`, `request_params`, `response_params`, `request_time`, `source_port`, `target_port`, `user_agent`, `ip`, `machine_name`, `mac_address`, `status`, `error_info`, `creator_name`, `creator`, `create_date`, `creator_account`) VALUES (7, 1, 'APP', NULL, '[APP]-Feign 根据 ', 'api', 1, '信息', '/api/tp/feign/client', 'GET', '\"PandaBank\"', '{\"code\":0,\"data\":99990.000000,\"msg\":\"success\"}', 27, NULL, NULL, ' ', '10', NULL, NULL, 1, NULL, 'LGL', 2, '2023-10-15 08:16:37', '+11111');
INSERT INTO `sys_app_log` (`id`, `log_type_id`, `log_type_name`, `event_type_id`, `event_type_name`, `module`, `log_level_id`, `log_level_name`, `request_uri`, `request_method`, `request_params`, `response_params`, `request_time`, `source_port`, `target_port`, `user_agent`, `ip`, `machine_name`, `mac_address`, `status`, `error_info`, `creator_name`, `creator`, `create_date`, `creator_account`) VALUES (8, 1, 'APP', NULL, '[APP]-Feign 根据 ', 'api', 1, '信息', '/api/tp/feign/client', 'GET', '\"PandaBank\"', '{\"code\":0,\"data\":99990.000000,\"msg\":\"success\"}', 27, NULL, NULL, ' ', '10', NULL, NULL, 1, NULL, 'LGL', 2, '2023-11-15 08:16:37', '+11111');
INSERT INTO `sys_app_log` (`id`, `log_type_id`, `log_type_name`, `event_type_id`, `event_type_name`, `module`, `log_level_id`, `log_level_name`, `request_uri`, `request_method`, `request_params`, `response_params`, `request_time`, `source_port`, `target_port`, `user_agent`, `ip`, `machine_name`, `mac_address`, `status`, `error_info`, `creator_name`, `creator`, `create_date`, `creator_account`) VALUES (9, 1, 'APP', NULL, '[APP]-Feign 根据 ', 'api', 1, '信息', '/api/tp/feign/client', 'GET', '\"PandaBank\"', '{\"code\":0,\"data\":99990.000000,\"msg\":\"success\"}', 27, NULL, NULL, ' ', '10', NULL, NULL, 1, NULL, 'LGL', 2, '2023-12-15 08:16:37', '+11111');
INSERT INTO `sys_app_log` (`id`, `log_type_id`, `log_type_name`, `event_type_id`, `event_type_name`, `module`, `log_level_id`, `log_level_name`, `request_uri`, `request_method`, `request_params`, `response_params`, `request_time`, `source_port`, `target_port`, `user_agent`, `ip`, `machine_name`, `mac_address`, `status`, `error_info`, `creator_name`, `creator`, `create_date`, `creator_account`) VALUES (10, 1, 'APP', NULL, '[APP]-Feign 根据 ', 'api', 1, '信息', '/api/tp/feign/client', 'GET', '\"PandaBank\"', '{\"code\":0,\"data\":99990.000000,\"msg\":\"success\"}', 27, NULL, NULL, ' ', '10', NULL, NULL, 1, NULL, 'LGL', 2, '2024-01-15 08:16:37', '+11111');
INSERT INTO `sys_app_log` (`id`, `log_type_id`, `log_type_name`, `event_type_id`, `event_type_name`, `module`, `log_level_id`, `log_level_name`, `request_uri`, `request_method`, `request_params`, `response_params`, `request_time`, `source_port`, `target_port`, `user_agent`, `ip`, `machine_name`, `mac_address`, `status`, `error_info`, `creator_name`, `creator`, `create_date`, `creator_account`) VALUES (11, 1, 'APP', NULL, '[APP]-Feign 根据 ', 'api', 1, '信息', '/api/tp/feign/client', 'GET', '\"PandaBank\"', '{\"code\":0,\"data\":99990.000000,\"msg\":\"success\"}', 27, NULL, NULL, ' ', '10', NULL, NULL, 1, NULL, 'LGL', 2, '2024-02-15 08:16:37', '+11111');