表分区 示例

create table projectdata_instore
(
   serial_no            bigint not null,
   cky_no               int comment '配送中心',
   store_no             int comment '仓库编号',
   seller_no            varchar(60) comment '插入的时候算好是哪个商家',
   job_no               varchar(60) comment '作业类型',
   cost_no              varchar(60) comment '费用编码',
   cost_type            varchar(60) comment '费用类型',
   operate_type         varchar(60) comment '业务类型',   
   exp_no               varchar(60) comment '单据编号',
   exp_type             varchar(60) comment '1 采购单 2 客户订单 3 内配单',
   project_status       varchar(60) comment '0 初始 1 已计算 2 已汇总 21 没有商家信息 22 未定义费率 31 按单计费商品',
   goods_no   varchar(60) comment 'sku',
   goods_name   varchar(500) comment '商品名称',
   qyt           int comment '商品总数量',
   exp_date             datetime not null  comment '单据日期',
   exp_time             varchar(5) comment '单据时间',
   create_date          datetime comment '创建日期',
   update_date          datetime comment '更新日期',
   rate                 decimal(12,4) comment '费率',
   cost                 decimal(12,4) comment '费用',
   batch_no             bigint default 0 comment '批次',
   bill_balance_item_id bigint comment '对账单明细ID',
   bill_balance_no      varchar(60) comment '对账单号',  
   region_no int(11) DEFAULT NULL comment '分区号', 
   yn                   tinyint comment '1有效 0无效',
   remark     varchar(50) comment '备注',
   ts timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   primary key (serial_no, exp_date)
);
alter table projectdata_instore partition by range(DAYOFYEAR(exp_date)) 
(
partition p1 values less than (2),
partition p2 values less than (3),
partition p3 values less than (4),
partition p4 values less than (5),
partition p5 values less than (6),
partition p6 values less than (7),
partition p7 values less than (8),
partition p8 values less than (9),
partition p9 values less than (10),
partition p10 values less than (11),
partition p11 values less than (12),
partition p12 values less than (13),
partition p13 values less than (14),
partition p14 values less than (15),
partition p15 values less than (16),
partition p16 values less than (17),
partition p17 values less than (18),
partition p18 values less than (19),
partition p19 values less than (20),
partition p20 values less than (21),
partition p21 values less than (22),
partition p22 values less than (23),
partition p23 values less than (24),
partition p24 values less than (25),
partition p25 values less than (26),
partition p26 values less than (27),
partition p27 values less than (28),
partition p28 values less than (29),
partition p29 values less than (30),
partition p30 values less than (31),
partition p31 values less than (32),
partition p32 values less than (33),
partition p33 values less than (34),
partition p34 values less than (35),
partition p35 values less than (36),
partition p36 values less than (37),
partition p37 values less than (38),
partition p38 values less than (39),
partition p39 values less than (40),
partition p40 values less than (41),
partition p41 values less than (42),
partition p42 values less than (43),
partition p43 values less than (44),
partition p44 values less than (45),
partition p45 values less than (46),
partition p46 values less than (47),
partition p47 values less than (48),
partition p48 values less than (49),
partition p49 values less than (50),
partition p50 values less than (51),
partition p51 values less than (52),
partition p52 values less than (53),
partition p53 values less than (54),
partition p54 values less than (55),
partition p55 values less than (56),
partition p56 values less than (57),
partition p57 values less than (58),
partition p58 values less than (59),
partition p59 values less than (60),
partition p60 values less than (61),
partition p61 values less than (62),
partition p62 values less than (63),
partition p63 values less than (64),
partition p64 values less than (65),
partition p65 values less than (66),
partition p66 values less than (67),
partition p67 values less than (68),
partition p68 values less than (69),
partition p69 values less than (70),
partition p70 values less than (71),
partition p71 values less than (72),
partition p72 values less than (73),
partition p73 values less than (74),
partition p74 values less than (75),
partition p75 values less than (76),
partition p76 values less than (77),
partition p77 values less than (78),
partition p78 values less than (79),
partition p79 values less than (80),
partition p80 values less than (81),
partition p81 values less than (82),
partition p82 values less than (83),
partition p83 values less than (84),
partition p84 values less than (85),
partition p85 values less than (86),
partition p86 values less than (87),
partition p87 values less than (88),
partition p88 values less than (89),
partition p89 values less than (90),
partition p90 values less than (91),
partition p91 values less than (92),
partition p92 values less than (93),
partition p93 values less than (94),
partition p94 values less than (95),
partition p95 values less than (96),
partition p96 values less than (97),
partition p97 values less than (98),
partition p98 values less than (99),
partition p99 values less than (100),
partition p100 values less than (101),
partition p101 values less than (102),
partition p102 values less than (103),
partition p103 values less than (104),
partition p104 values less than (105),
partition p105 values less than (106),
partition p106 values less than (107),
partition p107 values less than (108),
partition p108 values less than (109),
partition p109 values less than (110),
partition p110 values less than (111),
partition p111 values less than (112),
partition p112 values less than (113),
partition p113 values less than (114),
partition p114 values less than (115),
partition p115 values less than (116),
partition p116 values less than (117),
partition p117 values less than (118),
partition p118 values less than (119),
partition p119 values less than (120),
partition p120 values less than (121),
partition p121 values less than (122),
partition p122 values less than (123),
partition p123 values less than (124),
partition p124 values less than (125),
partition p125 values less than (126),
partition p126 values less than (127),
partition p127 values less than (128),
partition p128 values less than (129),
partition p129 values less than (130),
partition p130 values less than (131),
partition p131 values less than (132),
partition p132 values less than (133),
partition p133 values less than (134),
partition p134 values less than (135),
partition p135 values less than (136),
partition p136 values less than (137),
partition p137 values less than (138),
partition p138 values less than (139),
partition p139 values less than (140),
partition p140 values less than (141),
partition p141 values less than (142),
partition p142 values less than (143),
partition p143 values less than (144),
partition p144 values less than (145),
partition p145 values less than (146),
partition p146 values less than (147),
partition p147 values less than (148),
partition p148 values less than (149),
partition p149 values less than (150),
partition p150 values less than (151),
partition p151 values less than (152),
partition p152 values less than (153),
partition p153 values less than (154),
partition p154 values less than (155),
partition p155 values less than (156),
partition p156 values less than (157),
partition p157 values less than (158),
partition p158 values less than (159),
partition p159 values less than (160),
partition p160 values less than (161),
partition p161 values less than (162),
partition p162 values less than (163),
partition p163 values less than (164),
partition p164 values less than (165),
partition p165 values less than (166),
partition p166 values less than (167),
partition p167 values less than (168),
partition p168 values less than (169),
partition p169 values less than (170),
partition p170 values less than (171),
partition p171 values less than (172),
partition p172 values less than (173),
partition p173 values less than (174),
partition p174 values less than (175),
partition p175 values less than (176),
partition p176 values less than (177),
partition p177 values less than (178),
partition p178 values less than (179),
partition p179 values less than (180),
partition p180 values less than (181),
partition p181 values less than (182),
partition p182 values less than (183),
partition p183 values less than (184),
partition p184 values less than (185),
partition p185 values less than (186),
partition p186 values less than (187),
partition p187 values less than (188),
partition p188 values less than (189),
partition p189 values less than (190),
partition p190 values less than (191),
partition p191 values less than (192),
partition p192 values less than (193),
partition p193 values less than (194),
partition p194 values less than (195),
partition p195 values less than (196),
partition p196 values less than (197),
partition p197 values less than (198),
partition p198 values less than (199),
partition p199 values less than (200),
partition p200 values less than (201),
partition p201 values less than (202),
partition p202 values less than (203),
partition p203 values less than (204),
partition p204 values less than (205),
partition p205 values less than (206),
partition p206 values less than (207),
partition p207 values less than (208),
partition p208 values less than (209),
partition p209 values less than (210),
partition p210 values less than (211),
partition p211 values less than (212),
partition p212 values less than (213),
partition p213 values less than (214),
partition p214 values less than (215),
partition p215 values less than (216),
partition p216 values less than (217),
partition p217 values less than (218),
partition p218 values less than (219),
partition p219 values less than (220),
partition p220 values less than (221),
partition p221 values less than (222),
partition p222 values less than (223),
partition p223 values less than (224),
partition p224 values less than (225),
partition p225 values less than (226),
partition p226 values less than (227),
partition p227 values less than (228),
partition p228 values less than (229),
partition p229 values less than (230),
partition p230 values less than (231),
partition p231 values less than (232),
partition p232 values less than (233),
partition p233 values less than (234),
partition p234 values less than (235),
partition p235 values less than (236),
partition p236 values less than (237),
partition p237 values less than (238),
partition p238 values less than (239),
partition p239 values less than (240),
partition p240 values less than (241),
partition p241 values less than (242),
partition p242 values less than (243),
partition p243 values less than (244),
partition p244 values less than (245),
partition p245 values less than (246),
partition p246 values less than (247),
partition p247 values less than (248),
partition p248 values less than (249),
partition p249 values less than (250),
partition p250 values less than (251),
partition p251 values less than (252),
partition p252 values less than (253),
partition p253 values less than (254),
partition p254 values less than (255),
partition p255 values less than (256),
partition p256 values less than (257),
partition p257 values less than (258),
partition p258 values less than (259),
partition p259 values less than (260),
partition p260 values less than (261),
partition p261 values less than (262),
partition p262 values less than (263),
partition p263 values less than (264),
partition p264 values less than (265),
partition p265 values less than (266),
partition p266 values less than (267),
partition p267 values less than (268),
partition p268 values less than (269),
partition p269 values less than (270),
partition p270 values less than (271),
partition p271 values less than (272),
partition p272 values less than (273),
partition p273 values less than (274),
partition p274 values less than (275),
partition p275 values less than (276),
partition p276 values less than (277),
partition p277 values less than (278),
partition p278 values less than (279),
partition p279 values less than (280),
partition p280 values less than (281),
partition p281 values less than (282),
partition p282 values less than (283),
partition p283 values less than (284),
partition p284 values less than (285),
partition p285 values less than (286),
partition p286 values less than (287),
partition p287 values less than (288),
partition p288 values less than (289),
partition p289 values less than (290),
partition p290 values less than (291),
partition p291 values less than (292),
partition p292 values less than (293),
partition p293 values less than (294),
partition p294 values less than (295),
partition p295 values less than (296),
partition p296 values less than (297),
partition p297 values less than (298),
partition p298 values less than (299),
partition p299 values less than (300),
partition p300 values less than (301),
partition p301 values less than (302),
partition p302 values less than (303),
partition p303 values less than (304),
partition p304 values less than (305),
partition p305 values less than (306),
partition p306 values less than (307),
partition p307 values less than (308),
partition p308 values less than (309),
partition p309 values less than (310),
partition p310 values less than (311),
partition p311 values less than (312),
partition p312 values less than (313),
partition p313 values less than (314),
partition p314 values less than (315),
partition p315 values less than (316),
partition p316 values less than (317),
partition p317 values less than (318),
partition p318 values less than (319),
partition p319 values less than (320),
partition p320 values less than (321),
partition p321 values less than (322),
partition p322 values less than (323),
partition p323 values less than (324),
partition p324 values less than (325),
partition p325 values less than (326),
partition p326 values less than (327),
partition p327 values less than (328),
partition p328 values less than (329),
partition p329 values less than (330),
partition p330 values less than (331),
partition p331 values less than (332),
partition p332 values less than (333),
partition p333 values less than (334),
partition p334 values less than (335),
partition p335 values less than (336),
partition p336 values less than (337),
partition p337 values less than (338),
partition p338 values less than (339),
partition p339 values less than (340),
partition p340 values less than (341),
partition p341 values less than (342),
partition p342 values less than (343),
partition p343 values less than (344),
partition p344 values less than (345),
partition p345 values less than (346),
partition p346 values less than (347),
partition p347 values less than (348),
partition p348 values less than (349),
partition p349 values less than (350),
partition p350 values less than (351),
partition p351 values less than (352),
partition p352 values less than (353),
partition p353 values less than (354),
partition p354 values less than (355),
partition p355 values less than (356),
partition p356 values less than (357),
partition p357 values less than (358),
partition p358 values less than (359),
partition p359 values less than (360),
partition p360 values less than (361),
partition p361 values less than (362),
partition p362 values less than (363),
partition p363 values less than (364),
partition p364 values less than (365),
partition p365 values less than (366),
partition p366 values less than MAXVALUE  
);
create index idx_date_region on projectdata_instore(exp_date,region_no);
create index idx_date_seller on projectdata_instore(exp_date,seller_no,cky_no,store_no);
create index idx_date_cost on projectdata_instore(exp_date,cost_no);