栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

Mysql 表分区创建方法

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

Mysql 表分区创建方法

查看表分区信息
SELECt
	partition_name part,
	partition_expression expr,
	partition_description descr,
	table_rows 
FROM
	information_schema.PARTITIONS 
WHERe
	table_schema = SCHEMA () 
	AND table_name = '表名';
创建range分区 —— 分区字段为整型
DROP TABLE IF EXISTS `range_emp`;
CREATE TABLE `range_emp` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `empno` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `empname` varchar(20) NOT NULL DEFAULT '',
  `job` varchar(9) NOT NULL DEFAULT '',
  `mgr` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `hiredate` datetime NOT NULL,
  `sal` decimal(7,2) NOT NULL,
  `comn` decimal(7,2) NOT NULL,
  `depno` mediumint(8) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6000001 DEFAULT CHARSET=utf8
PARTITION BY RANGE (id) (
PARTITION part0 VALUES LESS THAN (500000), 
PARTITION part1 VALUES LESS THAN (1000000), 
PARTITION part2 VALUES LESS THAN (1500000),
PARTITION part3 VALUES LESS THAN (2000000),
PARTITION part4 VALUES LESS THAN (2500000),
PARTITION part5 VALUES LESS THAN (3000000),
PARTITION part6 VALUES LESS THAN (3500000),
PARTITION part7 VALUES LESS THAN (4000000),
PARTITION part8 VALUES LESS THAN (4500000),
PARTITION part9 VALUES LESS THAN (5000000),
PARTITION part10 VALUES LESS THAN (5500000),
PARTITION part11 VALUES LESS THAN MAXVALUE);
hash分区 数字字段
DROP TABLE IF EXISTS `product_partiton_hash`;
CREATE TABLE `product_partiton_hash`  (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `ProductName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `ProductId` int(11) NOT NULL,
  PRIMARY KEY (`Id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 
 
PARTITION BY HASH (Id) PARTITIONS 分区数量 ;

# List分区
DROP TABLE IF EXISTS `product_partiton_list`;
CREATE TABLE `product_partiton_list`  (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `ProductName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `ProductId` int(11) NOT NULL,
    `ProductStatus` int(11) NOT NULL,
  PRIMARY KEY (`Id`,`ProductStatus`) ,
  INDEX `ProductId_index` (`ProductId`) 
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 
PARTITION BY LIST(ProductStatus)(
    PARTITION p0 VALUES in(0,1),
    PARTITION p1 VALUES in(2,3,4)
);
转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/944420.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号