ALTER TABLE E_info PARTITION BY RANGE(DATE(dt) DIV 100)
(
PARTITION p_2015_01 VALUES LESS THAN (201501),
PARTITION p_2015_02 VALUES LESS THAN (201502),
PARTITION p_2015_03 VALUES LESS THAN (201503),
PARTITION p_2015_04 VALUES LESS THAN (201504),
PARTITION p_2015_05 VALUES LESS THAN (201505),
PARTITION p_2015_06 VALUES LESS THAN (201506),
PARTITION p_2015_07 VALUES LESS THAN (201507),
PARTITION p_2015_08 VALUES LESS THAN (201508),
PARTITION p_2015_09 VALUES LESS THAN (201509),
PARTITION p_2015_10 VALUES LESS THAN (201510),
PARTITION p_2015_11 VALUES LESS THAN (201511),
PARTITION p_2015_12 VALUES LESS THAN (201512),
PARTITION p_catch_all VALUES LESS THAN MAXVALUE
);
mysql> CREATE TABLE t1 (
-> id int DEFAULT NULL,
-> name varchar(20) DEFAULT NULL,
-> t timestamp
-> )
-> partition by range(YEAR(t)*100+MONTH(t))
-> (
-> PARTITION p201001 VALUES LESS THAN (201002),
->
-> PARTITION p201002 VALUES LESS THAN (201003),
->
-> PARTITION p201003 VALUES LESS THAN (201004),
->
-> PARTITION p201004 VALUES LESS THAN (201005),
->
-> PARTITION p201005 VALUES LESS THAN (201006),
->
-> PARTITION p201006 VALUES LESS THAN (201007),
->
-> PARTITION p201007 VALUES LESS THAN (201008),
->
-> PARTITION p201008 VALUES LESS THAN (201009),
->
-> PARTITION p201009 VALUES LESS THAN (201010),
->
-> PARTITION pcatchall VALUES LESS THAN MAXVALUE
-> ); ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
mysql>
如果是采用这种方法,也会报错:
mysql> CREATE TABLE t2 (
-> id int DEFAULT NULL,
-> name varchar(20) DEFAULT NULL,
-> t timestamp
-> )
-> partition by range(t)
-> (
-> PARTITION p201001 VALUES LESS THAN ('2010-02-01'),
->
-> PARTITION p201002 VALUES LESS THAN ('2010-02-03'),
->
-> PARTITION pcatchall VALUES LESS THAN MAXVALUE
-> ); ERROR 1697 (HY000): VALUES value for partition 'p201001' must have type INT
mysql>
按时间戳 SELECT FROM_UNIXTIME(create_time,'%Y%m%d') days,COUNT(id) COUNT FROM account_water GROUP BY days; 函数:DATE_FORMAT 作用:根据format字符串格式化date值。 语法:DATE_FORMAT(date,format) 下列...
create_time时间格式 SELECT DATE_FORMAT(create_time,'%Y%u') weeks,COUNT(id) COUNT FROM role GROUP BY weeks; SELECT DATE_FORMAT(create_time,'%Y%m%d') days,COUNT(id) COUNT FROM role GROUP B
查看分区数据量,查看全库数据量 USE information_schema; SELECT PARTITION_NAME,TABLE_ROWSFROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'sale_data'; SELECT table_name,table_rows FROM TABLES ...
以下实现分区名字按日期递增,例如现有表分区p20200421,p20200422,则在此基础上新创建分区p20200423 1、创建存储过程 CREATE PROCEDURE partition_add() begin select replace(partition_name,‘p’,"") into @p_...
MySQL创建表分区 对已有的表创建分区 以20190815这个时间之前的表数据创建分区p20190815 alter table 表名 partition by range (unix_timestamp(time)) ( partition p20190815 values less than unix_timestamp(...