mysql 登录时间分区_mysql 按时间分区

DROP TABLE IF EXISTS `shop_diancai`;

CREATE TABLE `shop_diancai` (

`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '点餐ID，由程序生成',

`orderId` BIGINT(20) NOT NULL COMMENT '订单ID',

`diancaiNO` VARCHAR(50) NOT NULL COMMENT '点餐编号',

`vegetableId` BIGINT(20) NOT NULL COMMENT '菜品Id',

`num` INT(1) NOT NULL DEFAULT '0' COMMENT '数量',

`price` INT(6) NOT NULL COMMENT '价格',

`type` INT(1) NOT NULL DEFAULT '1' COMMENT '1点菜,2加菜',

`isDel` INT(1) NOT NULL DEFAULT '0' COMMENT '1未删除 0删除',

`createTime` DATETIME NOT NULL COMMENT '点餐时间',

`confirmed` INT(1) NOT NULL DEFAULT '0' COMMENT '0未确认,1已确认',

`adddish` INT(1) NOT NULL DEFAULT '0' COMMENT '与点菜无关加菜次数',

`confirmTime` DATETIME NULL COMMENT '修改时间',

PRIMARY KEY (`id`, `createTime`)

) ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='点餐表'

PARTITION BY RANGE (YEAR(createTime)) (

PARTITION p0 VALUES LESS THAN (2017) ,

PARTITION p1 VALUES LESS THAN (2018) ,

PARTITION p2 VALUES LESS THAN (2019) ,

PARTITION p3 VALUES LESS THAN (2020) ,

PARTITION p4 VALUES LESS THAN (2021) ,

PARTITION p5 VALUES LESS THAN (2022) ,

PARTITION p6 VALUES LESS THAN (2023) ,

PARTITION p7 VALUES LESS THAN (2024) ,

PARTITION p8 VALUES LESS THAN (2030) ,

PARTITION p9 VALUES LESS THAN (MAXVALUE) );

CREATE PROCEDURE load_shop_diancai()       BEGIN       DECLARE v INT DEFAULT 0;       WHILE v < 8000000       DO           INSERT INTO shop_diancai (orderId,diancaiNO,vegetableId,price,createTime)          VALUES (1,'diancaiNO',1,1.00,ADDDATE('2017-01-01',(RAND(v)*36520) MOD 3652));            SET v = v + 1;       END WHILE;       END       CALL load_shop_diancai();     SELECT COUNT(*) FROM shop_diancai WHERE createTime > DATE '2017-04-01' AND createTime < DATE '2020-12-31';