CODEWITHSUNDEEP
mysqlpartitioning
CarlJohn
CarlJohn

Reputation: 727

Partitioning MySQL table column

I want to create the table in MySQL with the partitioning like below

CREATE TABLE `ccd` (
  `Created Month` varchar(255) DEFAULT NULL,
  `Created` varchar(255) DEFAULT NULL,
  `Employee Organization Name` varchar(255) DEFAULT NULL,
  `Employee Login` varchar(255) DEFAULT NULL,
  `Service Request #` varchar(255) DEFAULT NULL,
  `Status` varchar(255) DEFAULT NULL,
  `Description` longtext)
ENGINE=MyISAM DEFAULT CHARSET=latin1

PARTITION BY RANGE (SUBSTRING(`Created Month`,1,4)  )

(PARTITION sp0 VALUES LESS THAN ('2010') ENGINE = MyISAM,

 PARTITION sp1 VALUES LESS THAN   ('2012' ) ENGINE = MyISAM,

 PARTITION sp2 VALUES LESS THAN ('2014' ) ENGINE = MyISAM

);

In the table , Created Month column will be having the values like 

Created Month
2011 / 02
2011 / 06
2012 / 02
2013 / 01
2013 / 02
2013 / 05
2013 / 05

When i'm trying to run the statement, i'm getting the error message like 

ERROR: This partition function is not allowed
Error Code: 1564

Anyone tell me, where i'm making the mistake..?

Upvotes: 2

Views: 1567

Answers (1)

Stephan
Stephan

Reputation: 8090

Not all functions are allowed to be used in the data partitioning rule, as you can imagine SUBSTRING is one of them.

List of allowed functions

Solution:

Change Created Month from varchar to date and then use :
- PARTITION BY RANGE (MONTH(Created Month) )

Upvotes: 2

Related Questions