PARTITION BY LIST - Oracle to MySQL Migration

Both Oracle and MySQL support list partitioning, which allows you to partition a table based on a list of values for a specified column, but some clauses differ and require conversion.

Oracle:

   -- A sample list-partitioned table
  CREATE TABLE orders (
    name VARCHAR2(30),
    category CHAR(1)
  )
  PARTITION BY LIST (category)  
   (PARTITION "A" VALUES ('A'),
    PARTITION "B" VALUES ('B'));
  /* Table created. */
 
  -- Inserting a sample row
  INSERT INTO orders VALUES ('Apple', 'A');
  /* 1 row created. */

MySQL:

   -- A sample range-partitioned table
   CREATE TABLE orders (
     name VARCHAR(30),
     category CHAR(1)
   )
  PARTITION BY LIST COLUMNS (category)  
   (PARTITION A VALUES IN ('A'),
    PARTITION B VALUES IN ('B'));
  /* Query OK, 0 rows affected */
 
  -- Inserting a sample row
  INSERT INTO orders VALUES ('Apple', 'A');
  /* Query OK, 1 row affected */

Note that we use LIST COLUMNS instead of LIST in MySQL. LIST can only be used for integer partition values, otherwise, an error is returned:

  ERROR 1697 (HY000): VALUES value for partition '<value>' must have type INT

For more information, see Oracle to MySQL Migration.