mysql 分区

先给原有数据分区

1
2
3
4
alter table tbName partition BY RANGE (fieldName) (
    PARTITION p201802 VALUES LESS THAN (TO_DAYS('2018-03-01')),
    PARTITION p201803 VALUES LESS THAN (TO_DAYS('2018-04-01'))
);

查询分区

1
2
3
4
5
6
7
8
select 
  partition_name part,  
  partition_expression expr,  
  partition_description descr,  
  table_rows  
from information_schema.partitions  where 
  table_schema = schema()  
  and table_name='tbName';

自动增加分区函数(按月)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
DELIMITER $$
#该表所在数据库名称
USE `dbName`$$
DROP PROCEDURE IF EXISTS `create_partition_by_month`$$
CREATE PROCEDURE `create_partition_by_month`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
    DECLARE ROWS_CNT INT UNSIGNED;
    DECLARE PARTITIONNAME VARCHAR(16);
    DECLARE ENDTIME_DATETIME VARCHAR(30);
    SET PARTITIONNAME = DATE_FORMAT( NOW(), 'p%Y%m' );
    SET ENDTIME_DATETIME = DATE_FORMAT((NOW() + INTERVAL 1 MONTH), '%Y-%m-01');
    SELECT COUNT(*) INTO ROWS_CNT FROM information_schema.partitions
	WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME;
    IF ROWS_CNT = 0 THEN
        SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
	    ' ADD PARTITION (PARTITION ', PARTITIONNAME, " VALUES LESS THAN (UNIX_TIMESTAMP('", 
            ENDTIME_DATETIME ,"')) ENGINE = InnoDB);" );
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
     ELSE
	SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result;
     END IF;
END$$
DELIMITER ;

新增事务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DELIMITER $$
#该表所在的数据库名称
USE `datong_collect`$$
CREATE EVENT IF NOT EXISTS `gps_part_manage`
ON SCHEDULE EVERY 1 MONTH   #执行周期，还有天、月等等
STARTS '2018-04-01 00:00:00'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Creating partitions'
DO BEGIN
    #调用刚才创建的存储过程，第一个参数是数据库名称，第二个参数是表名称
    CALL create_partition_by_month('datong_collect','tb_gps_trail');
END$$
DELIMITER ;

删除分区：

1
alter table voice drop partition p201907;

获取时间

1
2
3
4
5
select curdate();  #获取当前日期
select last_day(curdate());  #获取当月最后一天。
select DATE_ADD(curdate(),interval -day(curdate())+1 day);  #获取本月第一天
select date_add(curdate()-day(curdate())+1,interval 1 month);# 获取下个月的第一天
select DATEDIFF(date_add(curdate()-day(curdate())+1,interval 1 month ),DATE_ADD(curdate(),interval -day(curdate())+1 day)) from dual;#获取当前月的天数