数据库根据时间自动创建分区的流程

1、创建分区表

CREATE TABLE `表名称` (
  `id` char(32) NOT NULL COMMENT '12位的imei',
  `ts` bigint(20) NOT NULL COMMENT '报文解析出来的时间戳',  
  `create_time` bigint(20) NOT NULL COMMENT '存入数据库时间戳',
  PRIMARY KEY (`id`,`ts`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT=''
PARTITION BY RANGE COLUMNS(ts) (
  PARTITION `ts_2021_03_01` VALUES LESS THAN (1614614400000),
  PARTITION `ts_2021_03_02` VALUES LESS THAN (1614700800000),
  PARTITION `ts_2021_03_03` VALUES LESS THAN (1614787200000)
)

2、查看当前分区是否存在

SELECT * 
FROM information_schema.PARTITIONS 
WHERE table_schema='数据库名称' 
AND TABLE_NAME='表名称' 
AND partition_name='ts_2021_03_01'

3、上述查询有结果则说明有分区，无结果则说明无分区，需要建分区

ALTER TABLE `表名称` ADD PARTITION (partition ts_2021_03_04 VALUES LESS THAN (UNIX_TIMESTAMP('2021-03-05 00:00:00')*1000));