title: Partitioning a MySQL table based on a column value.
body: <p>I want to partition a table in MySQL while preserving the table's structure.</p>
  
  <p>I have a column, <code>'Year'</code>, based on which I want to split up the table into different tables for each year respectively. The new tables will have names like <code>'table_2012'</code>, <code>'table_2013'</code> and so on. The resultant tables need to have all the fields exactly as in the source table.</p>
  
  <p>I have tried the following two pieces of SQL script with no success:</p>
  
  <p>1.</p>
  
  <blockquote>
  <pre><code>CREATE TABLE all_data_table
  (   column1 int default NULL,
      column2 varchar(30) default NULL,
      column3 date default NULL
  ) ENGINE=InnoDB  
  PARTITION BY RANGE ((year)) 
  (
      PARTITION p0 VALUES LESS THAN (2010),
      PARTITION p1 VALUES LESS THAN (2011) , PARTITION p2 VALUES LESS THAN (2012) ,
      PARTITION p3 VALUES LESS THAN (2013), PARTITION p4 VALUES LESS THAN MAXVALUE 
  );
  </code></pre>
  </blockquote>
  
  <p>2.</p>
  
  <blockquote>
  <pre><code>ALTER TABLE all_data_table PARTITION BY RANGE COLUMNS (`year`)  (
      PARTITION p0 VALUES LESS THAN (2011),
      PARTITION p1 VALUES LESS THAN (2012),
      PARTITION p2 VALUES LESS THAN (2013),
      PARTITION p3 VALUES LESS THAN (MAXVALUE)
  );
  </code></pre>
  </blockquote>
  
  <p>Any assistance would be appreciated!</p>