如何基于char列对MySQL表进行分区? [英] How to partition a MySQL table based on char column?
问题描述
是否可以基于char列进行分区?
Is it possible to partition based on char column?
在查看MySQL 5.1文档后，似乎只能使用整数类型.
After reviewing the MySQL 5.1 documentation it appears that only integer types can be used.
这是正确的吗?还是可以使用某些函数将char转换为整数?
Is this correct? Or can I use some function to convert the char into an integer?
有问题的char字段包含唯一标识符.
The char field in question contains a unique identifier.
推荐答案
MySQL 5.1中的分区只能处理整数列(少量分区函数上使用非整数列.例如:
Partitioning in MySQL 5.1 can only deal with integer columns (Source). You can only use a few partitioning functions on non-integer columns. For example:
CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
ENGINE=INNODB
PARTITION BY HASH( MONTH(tr_date) )
PARTITIONS 6;
您还可以在MySQL中使用密钥分区 5.1，只要主键包括表分区功能中的所有列:
You can also use key partitioning in MySQL 5.1, as long as the primary key includes all the columns in the table's partitioning function:
CREATE TABLE k1 (
id CHAR(3) NOT NULL PRIMARY KEY,
value int
)
PARTITION BY KEY(id)
PARTITIONS 10;
另一方面，在MySQL 5.5中，您可以使用列表列分区处理各种数据类型，包括基于字符的列.
On the other hand, in MySQL 5.5, you can use range column partitioning or list column partitioning on a wide variety of data types, including character-based columns.
列表列示例:
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')
);
范围列示例:
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')
);
进一步阅读:
- MySQL 5.1 Reference Manual :: Key partitioning
- A deep look at MySQL 5.5 partitioning enhancements
- MySQL 5.5 Reference Manual :: Range columns partitioning
- MySQL 5.5 Reference Manual :: List columns partitioning
这篇关于如何基于char列对MySQL表进行分区?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
