基于列/ID的最后一位的MySQL表分区 [英] Mysql table partition based on last digit of a column/id

问题描述

我想根据员工编号的最后一位创建分区， 即

I want to create partitions based on last digit of employee id, i.e

all ids ending with 0 go to first partition，

ending with 1 go to second partition and so on..

这样，我想创建10个分区，员工ID可以是int或varchar，这在创建分区时既容易又快速.

This way I want to create 10 partitions, employee id can be int or varchar which ever is easy and fast in making partition.

当前我们有10个单独的表employee_01，employee_02 ...(不要这样)

Currently we have 10 separate tables employee_01, employee_02... (dont like this)

尽管不会增加检索和插入时间，但是由于巨大的因素会增加可维护性，

Although the retrieval and insertion time will be enhanced by no means, but maintainability would be increased by huge factors, thanks

如何根据员工的最后一位数字指定分区标准

How do I specify partitioning criteria based on last digit of employee

推荐答案

我想知道创建分区时是否允许使用mod函数，我使用

I was wondering if there was a mod function allowed while creating a partition, I did it using

CREATE TABLE ti (id INT, amount DECIMAL(7,2))
ENGINE=INNODB
PARTITION BY HASH( MOD(id,10) )
PARTITIONS 10;

此操作创建了10个分区，每个id进入其分区，并以与该ID的最后一位数字相同的数字结尾

this created 10 partitions each id going in its partition ending with the same number as the last digit of the id

我添加了几行

    INSERT INTO ti VALUES (23123,343.22);
    INSERT INTO ti VALUES (23123,343.22);
    INSERT INTO ti VALUES (23144,343.22);
    INSERT INTO ti VALUES (23114,343.22);
    INSERT INTO ti VALUES (23124,343.22);
    INSERT INTO ti VALUES (23166,343.22);
    INSERT INTO ti VALUES (23116,343.22);
    INSERT INTO ti VALUES (23112,343.22);
    INSERT INTO ti VALUES (23199,343.22);

然后测试

 SELECT
  partition_name part,
  partition_expression expr,
  partition_description descr,
  table_rows
FROM
  INFORMATION_SCHEMA.partitions
WHERE
  TABLE_SCHEMA = SCHEMA()
  AND TABLE_NAME='ti';

输出:

part    expr    descr   table_rows
p0   MOD(id,10) \N  0
p1   MOD(id,10) \N  0
p2   MOD(id,10) \N  1
p3   MOD(id,10) \N  2
p4   MOD(id,10) \N  3
p5   MOD(id,10) \N  0
p6   MOD(id,10) \N  2
p7   MOD(id,10) \N  0
p8   MOD(id,10) \N  0
p9   MOD(id,10) \N  1

正是我想要的，感谢您指向正确的链接隐身"，但您的回答是错误的，也许您误解了

exactly what I wanted, thanks for pointing to the right link Incognito, but your answer was wrong, perhaps you misunderstood it

这篇关于基于列/ID的最后一位的MySQL表分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！