mysql- 语句-对于表分区..

#创建表.带分区得表.

(range分区)
    	CREATE TABLE `lte2018` 
    					(`textid` LONGTEXT(65535) ,
    					(`textid` BIGINT(255) ,
    						`time` DATETIME,
    						PRIMARY KEY(textid,time))
    					ENGINE = InnoDB  PARTITION BY RANGE (TO_DAYS(time))
	    (PARTITION lte20180228 VALUES LESS THAN (TO_DAYS(20180228)))

有需求可以 添加

(PARTITION lte20180228 VALUES LESS THAN MAXVALUE)

#添加分区.

alter table lte2018 add partition(partition partition20190107 VALUES LESS THAN (20190107));

#删除分区.

 alter table lte2018 drop partition partition20190107;

#删除分区数据

 alter table lte2018 truncate partition lte20180227;  

#查找分区内是否有数据.

select * from lte2018 partition(lte20180228);

#查找分区.

SELECT  
	partition_name part,
#  partition_expression expr,
#  partition_description descr,
  FROM_DAYS(partition_description) lessthan_sendtime,
  table_rows
FROM
  INFORMATION_SCHEMA.partitions
WHERE
  TABLE_SCHEMA = SCHEMA()
  AND TABLE_NAME='lte2018'; ---这里是表名

---

SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE  TABLE_NAME = 'lte2018';

---

#查看表内所有分区,检查分区状况

SELECT DISTINCT *
 FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_NAME='lte20180228'

(●'◡'●) ❀ (●'◡'●) ❀ (●'◡'●) ==我是可爱的小分割== (●'◡'●) ❀ (●'◡'●) ❀ (●'◡'●) 

参考原博: https://blog.csdn.net/ydyang1126/article/details/72730051

https://ask.csdn.net/questions/692022

---

(ง •̀_•́)ง❄(ง •̀_•́)ง❄(ง •̀_•́)ง ==加油加油加油== (ง •̀_•́)ง❄(ง •̀_•́)ง❄(ง •̀_•́)ง

• #创建表(LIST 分区). 带分区

CREATE TABLE  `lte2018`  (
    `textid` BIGINT(255),
	`time` DATETIME,
	`avg` DOUBLE,
PRIMARY KEY(textid,time) )

ENGINE = InnoDB  
PARTITION BY LIST (TO_DAYS(time)) (
    PARTITION `20180227` VALUES in (TO_DAYS(20180227))
);

添加分区

ALTER TABLE lte2018 ADD PARTITION(PARTITION `20180229` VALUES in (TO_DAYS(20180229)));