mysql 按时间分区

为什么80%的码农都做不了架构师？>>>   

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';  
    

转载于:https://my.oschina.net/cpy/blog/883914