MySQL 如何根据char列对MySQL表进行分区？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3753317/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to partition a MySQL table based on char column?
提问by John M
Is it possible to partition based on char column?
是否可以基于字符列进行分区？
After reviewing the MySQL 5.1 documentation it appears that only integer types can be used.
查看 MySQL 5.1 文档后，似乎只能使用整数类型。
Is this correct? Or can I use some function to convert the char into an integer?
这样对吗？或者我可以使用一些函数将字符转换为整数吗？
The char field in question contains a unique identifier.
有问题的 char 字段包含唯一标识符。
回答by Daniel Vassallo
Partitioning in MySQL 5.1 can only deal with integer columns (Source). You can only use a few partitioning functionson non-integer columns. For example:
MySQL 5.1 中的分区只能处理整数列（Source）。您只能在非整数列上使用几个分区函数。例如：
CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
ENGINE=INNODB
PARTITION BY HASH( MONTH(tr_date) )
PARTITIONS 6;
You can also use key partitioningin MySQL 5.1, as long as the primary key includes all the columns in the table's partitioning function:
你也可以在 MySQL 5.1 中使用键分区，只要主键包括表的分区函数中的所有列：
CREATE TABLE k1 (
id CHAR(3) NOT NULL PRIMARY KEY,
value int
)
PARTITION BY KEY(id)
PARTITIONS 10;
On the other hand, in MySQL 5.5, you can use range column partitioningor list column partitioningon a wide variety of data types, including character-based columns.
另一方面，在 MySQL 5.5 中，您可以对各种数据类型（包括基于字符的列）使用范围列分区或列表列分区。
List Columns Example:
列表列示例：
CREATE TABLE expenses (
expense_date DATE NOT NULL,
category VARCHAR(30),
amount DECIMAL (10,3)
);
ALTER TABLE expenses
PARTITION BY LIST COLUMNS (category)
(
PARTITION p01 VALUES IN ('lodging', 'food'),
PARTITION p02 VALUES IN ('flights', 'ground transportation'),
PARTITION p03 VALUES IN ('leisure', 'customer entertainment'),
PARTITION p04 VALUES IN ('communications'),
PARTITION p05 VALUES IN ('fees')
);
Range Columns Example:
范围列示例：
CREATE TABLE range_test (
code CHAR(3),
value INT
)
PARTITION BY RANGE COLUMNS(code) (
PARTITION p0 VALUES LESS THAN ('MMM'),
PARTITION p1 VALUES LESS THAN ('ZZZ')
);
Further reading:
进一步阅读：
回答by Rick James
What do you hope to gain by PARTITIONing? HASH and LIST are unlikely to provide any performance gain. It is rarely useful to partition a table with less than a million rows.
您希望通过 PARTITIONing 获得什么？HASH 和 LIST 不太可能提供任何性能提升。对少于一百万行的表进行分区很少有用。
Other comments on partitioning: http://mysql.rjweb.org/doc.php/partitionmaint
关于分区的其他评论：http: //mysql.rjweb.org/doc.php/partitionmaint
5.6 and 5.7 have relaxed someof the restrictions mention in the other answers, but still partitioning is rarely better than indexing.
5.6 和 5.7 放宽了其他答案中提到的一些限制，但分区仍然很少比索引更好。
