# `LIST` Partition - Example

+ The partition key is of integer type

```
tidb> CREATE TABLE subscribers (
 -> sub_id INT NOT NULL,
 -> firstname VARCHAR(25) NOT NULL,
 -> lastname VARCHAR(25) NOT NULL,
 -> journalid INT NOT NULL,
 -> joined DATE NOT NULL
 -> )
 -> ~!@PARTITION BY LIST (journalid)@!~ (
 -> PARTITION p0 VALUES IN (5001, 5002),
 -> PARTITION p1 VALUES IN (6001, 6002),
 -> PARTITION p2 VALUES IN (7001, 7002),
 -> PARTITION p3 VALUES IN (8001, 8002),
 -> PARTITION p4 VALUES IN (9001, 9002)
 -> );
Query OK, 0 rows affected (0.54 sec)

tidb> INSERT INTO subscribers VALUES(1, 'David', 'Jones', 8001, '2023-11-24');
Query OK, 1 row affected (0.02 sec)

tidb> INSERT INTO subscribers VALUES(1, 'David', 'Jones', 8003, '2023-11-24');
ERROR 1526 (HY000): Table has no partition for value 8003

tidb> INSERT IGNORE INTO subscribers VALUES(1, 'David', 'Jones', 8003, '2023-11-24');
Query OK, 0 rows affected, 1 warning (0.00 sec)

tidb> SHOW WARNINGS;
+-----------+--------+---------------------------------------+
|   Level   |  Code  | Message                               |
+-----------+--------+---------------------------------------+
|  Warning  |  1526  | Table has no partition for value 8003 |
+-----------+--------+---------------------------------------+
1 row in set (0.00 sec)
```
------------------------------------------------------------------------------
With `LIST` partitioning, you specify lists of values for each partition so that rows with matching column values are stored in the corresponding partition. The example in the slide assumes that you want to partition your table based on the journals that your subscribers subscribe to. You can use `LIST` partitioning to achieve this. For example:

```
CREATE TABLE subscribers (
sub_id INT NOT NULL,
 firstname VARCHAR(25) NOT NULL,
 lastname VARCHAR(25) NOT NULL,
 journalid INT NOT NULL,
 joined DATE NOT NULL
)
PARTITION BY LIST ( journalid ) (
PARTITION p0 VALUES IN (5001, 5002),
PARTITION p1 VALUES IN (6001, 6002),
PARTITION p2 VALUES IN (7001, 7002),
PARTITION p3 VALUES IN (8001, 8002),
PARTITION p4 VALUES IN (9001, 9002)
);
```

After creating the partitions as above, you can easily add or delete records related to a specific region in the table. For example, suppose that all subscribers with `journalid` is 5001 or 5002 are canceled. Then all the row data with `journalid` is 5001 or 5002 can be deleted by executing `ALTER TABLE subscribers TRUNCATE PARTITION p0`, which is much more efficient than the equivalent statement `DELETE FROM subscribers WHERE journalid IN (5001, 5002)`.

You can also execute `ALTER TABLE subscribers DROP PARTITION p0` to delete all related rows, but this statement also deletes the `p0` partition from the table definition.

Unlike `RANGE` partitioning, `LIST` partitioning does not have a similar `MAXVALUE` partition to store all values that do not belong to other partitions. Instead, all expected values of the partition expression must be included in the `PARTITION ... VALUES IN (...)` clause. If the value to be inserted in an `INSERT` statement does not match the column value set of any partition, the statement fails to execute and an error is reported. See the following example:

```
tidb> INSERT IGNORE INTO subscribers VALUES(1, 'David', 'Jones', 8001, '2023-11-24');
Query OK, 1 row affected (0.02 sec)

tidb> INSERT IGNORE INTO subscribers VALUES(1, 'David', 'Jones', 8003, '2023-11-24');
ERROR 1526 (HY000): Table has no partition for value 8003
```

To ignore the errors above, you can use the `IGNORE` keyword. After using this keyword, if a row contains values that do not match the column value set of any partition, this row will not be inserted. Instead, any row with matched values will be inserted, and no error is reported:

```
tidb> INSERT IGNORE INTO subscribers VALUES(1, 'David', 'Jones', 8003, '2023-11-24');
Query OK, 0 rows affected, 1 warning (0.00 sec)

tidb> SHOW WARNINGS;
+-----------+--------+-------------------------------------------------+
| Level     | Code   | Message                                         |
+-----------+--------+-------------------------------------------------+
| Warning   | 1526   | Table has no partition for value 8003           |
+-----------+--------+-------------------------------------------------+
1 row in set (0.00 sec)
```

Before creating a `LIST` partitioned table, you need to set the value of the session variable `tidb_enable_list_partition` to `ON`.

`set @@session.tidb_enable_list_partition = ON`

Also, make sure that `tidb_enable_table_partition` is set to `ON`, which is the default setting. `LIST` partitioning requires the partition key or its expression to be of type `INT`. Otherwise, an error will be thrown, as shown below:

```
tidb> CREATE TABLE users (
 -> uid INT NOT NULL,
 -> uname VARCHAR(25) NOT NULL,
 -> ucity VARCHAR(25) NOT NULL,
 -> addr VARCHAR(25) NOT NULL
 -> )
 -> PARTITION BY LIST ( ucity ) (
 -> PARTITION p0 VALUES IN ('Beijing','Seoul','Tokyo','Sydney'),
 -> PARTITION p1 VALUES IN ('Berlin', 'Cairo', 'Madrid'),
 -> PARTITION p2 VALUES IN ('Vancouver', 'Ottawa', 'Chicago')
 -> );
ERROR 1697 (HY000): VALUES value for partition 'p0' must have type INT
```

In addition, it is required that only one column or one column expression can follow `PARTITION BY LIST`. If there are multiple columns, the following issues may arise:

```
tidb> CREATE TABLE users (
 -> uid INT NOT NULL,
 -> ugroupid INT NOT NULL,
 -> uname VARCHAR(25) NOT NULL,
 -> ucity VARCHAR(25) NOT NULL,
 -> addr VARCHAR(25) NOT NULL
 -> )
 -> PARTITION BY LIST ( ugroupid, ucity ) (
 -> PARTITION p0 VALUES IN ((1,'Beijing'),(1,'Seoul'),(2,'Tokyo'),(2,'Sydney')),
 -> PARTITION p1 VALUES IN ((1,'Berlin'), (2,'Beijing'), (1,'Tokyo')),
 -> PARTITION p2 VALUES IN ((1,'Sydney'), (2,'Seoul'), (2,'Berlin'))
 -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 8 column 30 near ", ucity) (
PARTITION p0 VALUES IN ((1,'Beijing'),(1,'Seoul'),(2,'Tokyo'),(2,'Sydney')),
PARTITION p1 VALUES IN ((1,'Berlin'), (2,'Beijing'), (1,'Tokyo')),
PARTITION p2 VALUES IN ((1,'Sydney'), (2,'Seoul'), (2,'Berlin'))
)
```

For the above two problems, `LIST COLUMNS` partitioning provides extended features to solve them.

More details: `https://docs.pingcap.com/tidb/stable/partitioned-table#list-partitioning`.