Mysql分区

– **

Mysql 分区

日分区、月分区、年分区

**
– 分区表创建

CREATE TABLE T_DATA_2021_DAY_TEST 
   (TIMEST VARCHAR(24) COMMENT '账期（日）', 
	TIMEST2 VARCHAR(24) COMMENT '账期（月）',  
	COL_1 VARCHAR(60) COMMENT '值1', 
	COL_2 VARCHAR(60) COMMENT '值2', 
	COL_3 VARCHAR(60)COMMENT '值3', 
	COL_4 VARCHAR(60)COMMENT '值4' , 
	COL_5 VARCHAR(60)COMMENT '值5', 
	COL_6 VARCHAR(60)COMMENT '值6', 
	COL_7 VARCHAR(60)COMMENT '值7', 
	COL_8 VARCHAR(60)COMMENT '值8',

	 KEY `T_DATA_2021_DAY_IND1` (`TIMEST`) USING BTREE,
   KEY `T_DATA_2021_DAY_IND2` (`TIMEST2`) USING BTREE

   )   COMMENT '2021年日数据表'

PARTITION BY RANGE COLUMNS(TIMEST) (
	 
    PARTITION P20210101 VALUES LESS THAN ( '20210102' )
		-- ......  分区语句

);

– 替换表名

--  rename table tbl_name to new_tbl_name
rename table T_DATA_2021_DAY  to T_DATA_2021_DAY_DROP
rename table T_DATA_2021_DAY_TEST  to T_DATA_2021_DAY

–导入数据

INSERT INTO T_DATA_2021_DAY
SELECT * FROM T_DATA_2021_DAY_DROP

–删除未分区表

DROP TABLE T_DATA_2021_DAY_DROP

– 在oracle 中执行此语句 得到结果复制到上面分区语句下
– 分区：

WITH lvl AS
 (SELECT to_char((p + LEVEL-1),'yyyymmdd') p,to_char((p + LEVEL),'yyyymmdd') p1
    FROM (SELECT to_date('20210101', -- 从哪天开始创建分区
                         'yyyymmdd') p
            FROM dual)
  CONNECT BY LEVEL <= 365 -- 一次性创建多少天的数据
  )
SELECT ' partition P' || p ||
       ' values less than  ('''|| p1 || '''),' a
  FROM lvl