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.
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.
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;
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;
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.
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: