mysql表的分区和子分区
我的表结构是
CREATE TABLE IF NOT EXISTS `billing_total_success` (
`bill_id` int(11) NOT NULL AUTO_INCREMENT,
`location` char(10) NOT NULL,
`circle` varchar(2) NOT NULL,
`amount` int(11) NOT NULL,
`reference_id` varchar(100) NOT NULL,
`source` varchar(100) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`bill_id`),
KEY `location` (`location`),
KEY `soutime` (`source`,`time`),
KEY `circle` (`circle`,`source`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=80527470 ;
我需要根据源进行分区和子分区。
圆圈：是 11 个值集中的 2 个字符（“AA”、“XB”、“BT”...）
来源：可以是“RNE”（子分区 1）或“PR”（子分区 2）或任何其他字符串（子分区 3）。
我该如何进行这种分区？
My Table structure is
CREATE TABLE IF NOT EXISTS `billing_total_success` (
`bill_id` int(11) NOT NULL AUTO_INCREMENT,
`location` char(10) NOT NULL,
`circle` varchar(2) NOT NULL,
`amount` int(11) NOT NULL,
`reference_id` varchar(100) NOT NULL,
`source` varchar(100) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`bill_id`),
KEY `location` (`location`),
KEY `soutime` (`source`,`time`),
KEY `circle` (`circle`,`source`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=80527470 ;
I need to partition this based on circle and subpartition on source.
Circle: Is 2 character from a set of 11 values ("AA","XB","BT"...)
Source: can be either "RNE"(sub partition 1) or "PR"(sub partition 2) or any other string(sub partition 3).
How do I do this partitioning?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
看看 MySQL 范围分区 。您可以获得的最接近的结果是定义一个小于“RNE”的范围，然后定义一个小于“RNE”之后出现的下一个值的分区。与PR类似，然后你就可以拥有一个包罗万象的分区。这里唯一的问题是“RNE”之前、“RNE”和“PR”之间的空洞以及“PR”之后需要多个分区。
也许如果您可以解释为什么要以这种方式分区，我们可以提供替代解决方案:)
Take a look at MySQL range partitioning. The closest you can get is to define a range less than "RNE", then a partition less than the next value which would occur after "RNE". Similar with PR, and then you can have a catch-all partition. The only issue here is that you need multiple partitions for before "RNE", the hole between "RNE" and "PR", and after "PR".
Perhaps if you could explain why you want to partition in this way, we could provide an alternative solution :)