); 

In this instance, all rows relating to in-store workers would be stored in partition p0 , those relating to office and support staff in p1 , and those relating to managers in partition p2 . 

It is also possible to use an expression in VALUES LESS THAN  clauses. However, MySQL must be able to evaluate the expression’s return value as part of a LESS THAN  ( < ) comparison. 

Rather than splitting up the table data according to store number, you can use an expression based on one of the two DATE  columns instead. For example, let us suppose that you wish to partition based on the year that each employee left the company; that is, the value of YEAR(separated) . An example of a CREATE TABLE  statement that implements such a partitioning scheme is shown here: 

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT ’1970-01-01’, separated DATE NOT NULL DEFAULT ’9999-12-31’, job_code INT, store_id INT ) PARTITION BY RANGE ( YEAR(separated) ) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1996), PARTITION p2 VALUES LESS THAN (2001), PARTITION p3 VALUES LESS THAN MAXVALUE ); 

In this scheme, for all employees who left before 1991, the rows are stored in partition p0 ; for those who left in the years 1991 through 1995, in p1 ; for those who left in the years 1996 through 2000, in p2 ; and for any workers who left after the year 2000, in p3 . 

It is also possible to partition a table by RANGE , based on the value of a TIMESTAMP  column, using the UNIX_TIMESTAMP()  function, as shown in this example: 

CREATE TABLE quarterly_report_status ( report_id INT NOT NULL, report_status VARCHAR(20) NOT NULL, report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) ( PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP(’2008-01-01 00:00:00’) ), PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP(’2008-04-01 00:00:00’) ), PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP(’2008-07-01 00:00:00’) ), PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP(’2008-10-01 00:00:00’) ), PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP(’2009-01-01 00:00:00’) ), PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP(’2009-04-01 00:00:00’) ), PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP(’2009-07-01 00:00:00’) ), PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP(’2009-10-01 00:00:00’) ), PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP(’2010-01-01 00:00:00’) ), PARTITION p9 VALUES LESS THAN (MAXVALUE) ); 

Any other expressions involving TIMESTAMP  values are not permitted. (See Bug #42849.) 

Range partitioning is particularly useful when one or more of the following conditions is true: 

•You want or need to delete “old”  data. If you are using the partitioning scheme shown previously for the employees  table, you can simply use ALTER TABLE employees DROP PARTITION p0;  to delete all rows relating to employees who stopped working for the firm prior to 1991. (See Section 13.1.9, “ALTER TABLE Statement” , and Section 24.3, “Partition Management” , for more information.) For a 

