mysql分区操作

最新推荐文章于 2024-08-08 14:13:02 发布

我将在黎明出发

最新推荐文章于 2024-08-08 14:13:02 发布

阅读量396

点赞数

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_43217405/article/details/122992803

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

查询分区信息

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, 
PARTITION_ORDINAL_POSITION, SUBPARTITION_ORDINAL_POSITION, PARTITION_METHOD, 
SUBPARTITION_METHOD, PARTITION_EXPRESSION, SUBPARTITION_EXPRESSION, PARTITION_DESCRIPTION, 
TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, 
DATE_FORMAT( CREATE_TIME, '%Y-%m-%d %H:%i:%s' ) AS CREATE_TIME, UPDATE_TIME, CHECK_TIME, 
CHECKSUM, PARTITION_COMMENT, NODEGROUP, TABLESPACE_NAME 
FROM information_schema.PARTITIONS 
WHERE TABLE_SCHEMA = 'inteldev' 
AND TABLE_NAME= 'employees'

#添加RANGE分区

ALTER TABLE AGG_T_LTE_IPV_DY ADD PARTITION(PARTITION p20211202 VALUES LESS THAN (20211203) ENGINE = InnoDB);

#删除分区

alter table employees drop partition p20210103

#添加RANGE分区表

DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `code` int(11) NOT NULL,
  `insert_time` int(11) DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4
PARTITION BY RANGE (insert_time)
(PARTITION `p2109290000` VALUES LESS THAN (2109300000) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0);