mysql按照时间分区

来源：互联网发布：语音软件排行编辑：程序博客网时间：2024/06/06 18:59

-- -----------------------------------------------
-- 全量脚本
-- -----------------------------------------------
-- 4.创建表 fs_calculate_result_his(计算结果历史表)的当前表
SELECT 'Create Table fs_calculate_result_his-计算结果历史表...';
DROP TABLE IF EXISTS fs_calculate_result_his;
CREATE TABLE fs_calculate_result_his
(
yyyymm int DEFAULT 0 ,
fund_account varchar(18) DEFAULT ' ' ,
money_type varchar(3) DEFAULT ' ' ,
income_ratio varchar(1000) DEFAULT ' ' ,
fund_use_rate varchar(1000) DEFAULT ' ' ,
stock_rate varchar(1000) DEFAULT ' ' ,
total_trade_days varchar(1000) DEFAULT ' ' ,
hold_days varchar(1000) DEFAULT ' ' ,
total_asset varchar(1000) DEFAULT ' ',
trade_date varchar(1000) DEFAULT ' '
)
partition by range (yyyymm)
(PARTITION p0 VALUES LESS THAN (201601),
PARTITION p1 VALUES LESS THAN (201602),
PARTITION p2 VALUES LESS THAN (201603),
PARTITION p3 VALUES LESS THAN (201604),
PARTITION p4 VALUES LESS THAN (201605),
PARTITION p5 VALUES LESS THAN (201606),
PARTITION p6 VALUES LESS THAN (201607),
PARTITION p7 VALUES LESS THAN (201608),
PARTITION p8 VALUES LESS THAN (201609),
PARTITION p9 VALUES LESS THAN (201610),
PARTITION p10 VALUES LESS THAN (201611),
PARTITION p11 VALUES LESS THAN (201612),
PARTITION p12 VALUES LESS THAN (201701),
PARTITION p13 VALUES LESS THAN (201702),
PARTITION p14 VALUES LESS THAN (201703),
PARTITION p15 VALUES LESS THAN (201704),
PARTITION p16 VALUES LESS THAN (201705),
PARTITION p17 VALUES LESS THAN (201706),
PARTITION p18 VALUES LESS THAN (201707),
PARTITION p19 VALUES LESS THAN (201708),
PARTITION p20 VALUES LESS THAN (201709),
PARTITION p21 VALUES LESS THAN (201710),
PARTITION p22 VALUES LESS THAN (201711),
PARTITION p23 VALUES LESS THAN (201712),
PARTITION p24 VALUES LESS THAN (201801)
);
CREATE UNIQUE INDEX idx_fund_date ON fs_calculate_result_his(fund_account ASC ,money_type ASC ,yyyymm ASC );

-- -----------------------------------------------
-- 升级脚本
-- -----------------------------------------------

0 0