Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to partition a MySQL table based on char column?

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.

like image 526
John M Avatar asked Sep 20 '10 16:09

John M


People also ask

How do you partition a table in MySQL?

CREATE TABLE t1 ( id INT, year_col INT ); This table can be partitioned by HASH , using the id column as the partitioning key, into 8 partitions by means of this statement: ALTER TABLE t1 PARTITION BY HASH(id) PARTITIONS 8; MySQL supports an ALGORITHM option with [SUB]PARTITION BY [LINEAR] KEY .

How do I create a partition table from an existing table?

To create a partitioned table from an existing table, use the select into command. You can use select with the into_clause to create range-, hash-, list-, or round-robin–partitioned tables. The table from which you select can be partitioned or unpartitioned. See the Reference Manual: Commands.


1 Answers

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:

  • 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
like image 74
Daniel Vassallo Avatar answered Oct 03 '22 02:10

Daniel Vassallo