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)));