mysql 按照日期分区

 

1.分区的字段必须主键其中之一；

 

ALTER TABLE zj_node_price ADD PRIMARY KEY (id,data_date);

ALTER TABLE zj_node_price ADD INDEX u_data_date (data_date)

 

alter table zj_node_price partition by range COLUMNS(data_date) (

partition p20241101 values less than ('2024-11-01'),

partition p20241102 values less than ('2024-11-02'),

partition p20241103 values less than ('2024-11-03'),

partition px values less than maxvalue

);

 

 补充：

 

 

 

DROP TABLE IF EXISTS `sd_ss_node_price`;

 

CREATE TABLE `sd_ss_node_price` (

`id` varchar(32) NOT NULL COMMENT '物理主键',

`region` varchar(8) DEFAULT NULL COMMENT '区域',

 `data_date` char(10) NOT NULL COMMENT  '数据日期 YYYY-MM-DD',

PRIMARY KEY (`id`,`data_date`),

KEY `index_data_date` (`data_date`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='实时节点电价'

partition by range COLUMNS(data_date) (

partition p20241101 values less than ('2024-11-01'),

partition p20241102 values less than ('2024-11-02'),

partition p20241103 values less than ('2024-11-03'),

partition px values less than maxvalue

);

注意：

分区的字段一定要设置 非空，加成id 和  data_date 加成主键

查询：

各分区数据

select partition_name part,

partition_expression expr,

partition_description descr,

table_rows from information_schema.partitions where table_schema = schema()

and table_name='sd_ss_node_price_cs';  

mysql 名称按照字母排序

order by order_num  asc,cast(name as char character set gbk) asc