日常

ニチジョウ

0%

MySQL分区使用

发表于 更新于 分类于 阅读次数： Valine：

创建

LIST分区

1
2
3
4
5
6
7
8
CREATE TABLE `test` (
  `date` int(11) NOT NULL DEFAULT '0' COMMENT 'date',
  PRIMARY KEY (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
PARTITION BY LIST (`date`)
(
  PARTITION P_20191201 VALUES IN (20191201)
);

RANGE分区

1
2
3
4
5
6
7
8
CREATE TABLE `test` (
  `date` int(11) NOT NULL DEFAULT '0' COMMENT 'date',
  PRIMARY KEY (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
PARTITION BY RANGE (`date`)
(
  PARTITION P_20191201 VALUES LESS THAN (20191202)
);

HASH分区

1
2
3
4
5
6
CREATE TABLE `test` (
  `date` int(11) NOT NULL DEFAULT '0' COMMENT 'date',
  PRIMARY KEY (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
PARTITION BY HASH (`date`)
PARTITIONS 10;

增加

1
2
3
ALTER TABLE `test` ADD PARTITION (
  PARTITION P_20191202 VALUES IN (20191202)
);

删除

1
ALTER TABLE `test` DROP PARTITION P_20191202;