mysql表分区笔记

最新推荐文章于 2024-08-27 13:58:29 发布

爱凯悦的大力

最新推荐文章于 2024-08-27 13:58:29 发布

阅读量279

点赞数 1

分类专栏： mysql 文章标签： mysl 表空间

本文链接：https://blog.csdn.net/qq_22022213/article/details/79240434

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

CREATE TABLE `trecord` (
`tid` INT(11) NOT NULL AUTO_INCREMENT,
`task_id` VARCHAR(255) NULL DEFAULT NULL COMMENT '任务ID' COLLATE 'utf8_unicode_ci',
`taskCount` VARCHAR(255) NULL DEFAULT NULL COMMENT 'menupath id号' COLLATE 'utf8_unicode_ci',
`ttype` VARCHAR(50) NULL DEFAULT NULL COMMENT '类型：安卓 js pc' COLLATE 'utf8_unicode_ci',
`ip` VARCHAR(50) NULL DEFAULT NULL COMMENT 'ip' COLLATE 'utf8_unicode_ci',
`deviceID` VARCHAR(255) NULL DEFAULT NULL COMMENT '设备ID' COLLATE 'utf8_unicode_ci',
`createdate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`tid`,`createdate`)
)
COLLATE='utf8_unicode_ci'
ENGINE=MyISAM
PARTITION BY RANGE (UNIX_TIMESTAMP(createdate)) (
    PARTITION p20180201 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-01')),
    PARTITION p20180202 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-02')),
    PARTITION p20180203 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-03')),
    PARTITION p20180204 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-04')),
    PARTITION p20180205 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-05')),
    PARTITION p20180206 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-06')),
    PARTITION p20180207 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-07')),
    PARTITION p20180208 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-08')),
    PARTITION p20180209 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-09')),
    PARTITION p20180210 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-10')),
    PARTITION p20180211 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-11')),
    PARTITION p20180212 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-12')),
    PARTITION p20180213 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-13')),
    PARTITION p20180214 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-14')),
    PARTITION p20180215 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-15')),
    PARTITION p20180216 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-16')),
    PARTITION p20180217 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-17')),
    PARTITION p20180218 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-18')),
    PARTITION p20180219 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-19')),
    PARTITION p20180220 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-20')),
    PARTITION p20180221 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-21')),
    PARTITION p20180222 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-22')),
    PARTITION p20180223 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-23')),
    PARTITION p20180224 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-24')),
    PARTITION p20180225 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-25')),
    PARTITION p20180226 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-26')),
    PARTITION p20180227 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-27')),
    PARTITION p20180228 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-28'))
);


SELECT * FROM information_schema.partitions WHERE table_name = 'trecord'##查询该表所有的表空间


alter table trecord drop partition p20180204;##删除指定表空间
 
ALTER TABLE trecord ADD PARTITION(PARTITION p20180301 VALUES LESS THAN (UNIX_TIMESTAMP('2018-03-01')))##新增trecord表空间