Разбиение по списку в MySQL во многом похоже на разбиение по диапазону. Как и при разбиении по RANGE, каждый раздел должен быть явно определен. Основное различие между этими двумя типами разбиения заключается в том, что при разбиении по списку каждый раздел определяется и выбирается на основе принадлежности значения столбца к одному из набора списков значений, а не к одному из набора смежных диапазонов значений. Это делается с помощью PARTITION BY LIST(expr), где expr — это значение столбца или выражение, основанное на значении столбца, возвращающее целое значение, а затем каждый раздел определяется с помощью VALUES IN (value_list), где value_list — это список целых чисел, разделенных запятыми.

В отличие от разбиения по диапазону, разделы по списку не обязательно должны быть объявлены в определенном порядке. Более подробную информацию о синтаксисе см. в Разделе 15.1.21, «Выполнение инструкции CREATE TABLE».

Для последующих примеров мы предполагаем, что базовое определение разделяемой таблицы предоставляется инструкцией CREATE TABLE, показанной здесь:

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
);

(Эта таблица используется в качестве основы для примеров в Разделе 26.2.1, «Разбиение по диапазону». Как и в других примерах разбиения, мы предполагаем, что default_storage_engine равно InnoDB.)

Предположим, что существует 20 видеомагазинов, распределенных между 4 франшизами, как показано в следующей таблице.

Для того, чтобы разбить эту таблицу таким образом, чтобы строки магазинов, принадлежащие одному региону, хранились в одном разделе, можно использовать инструкцию CREATE TABLE, показанную здесь:

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 LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

Это упрощает добавление или удаление записей сотрудников, относящихся к определенным регионам, в таблицу или из неё. Например, предположим, что все магазины в Западном регионе проданы другой компании. В MySQL 9.2 все строки, относящиеся к сотрудникам, работающим в магазинах этого региона, можно удалить с помощью запроса ALTER TABLE employees TRUNCATE PARTITION pWest, который можно выполнить гораздо эффективнее, чем эквивалентную инструкцию DELETE DELETE FROM employees WHERE store_id IN (4,12,13,14,18);. (Использование ALTER TABLE employees DROP PARTITION pWest также удалит все эти строки, но также удалит раздел pWest из определения таблицы; для восстановления первоначальной схемы разбиения таблицы потребуется использовать инструкцию ALTER TABLE ... ADD PARTITION.)

Как и при разбиении по RANGE, можно объединить разбиение по LIST с разбиением по хэшу или ключу, чтобы получить составное разбиение (подразбиение). См. Раздел 26.2.6, «Подразбиение».

В отличие от разбиения по RANGE, нет “ловушки” типа MAXVALUE; все ожидаемые значения для выражения разбиения должны быть покрыты в PARTITION ... VALUES IN (...). Ошибка возникает при выполнении инструкции INSERT с недопустимым значением столбца разбиения, как показано в этом примере:

mysql> CREATE TABLE h2 (
    ->   c1 INT,
    ->   c2 INT
    -> )
    -> PARTITION BY LIST(c1) (
    ->   PARTITION p0 VALUES IN (1, 4, 7),
    ->   PARTITION p1 VALUES IN (2, 5, 8)
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO h2 VALUES (3, 5);
ERROR 1525 (HY000): Table has no partition for value 3

При вставке нескольких строк с помощью одной инструкции INSERT в одну таблицу InnoDB InnoDB рассматривает инструкцию как одну транзакцию, так что наличие любых несоответствующих значений приводит к полному провалу инструкции, и никакие строки не вставляются.

Вы можете заставить этот тип ошибки игнорироваться, используя ключевое слово IGNORE, хотя для каждой строки с несоответствующими значениями столбца разбиения выдается предупреждение, как показано здесь.

mysql> TRUNCATE h2;
Query OK, 1 row affected (0.00 sec)

mysql> TABLE h2;
Empty set (0.00 sec)

mysql> INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);
Query OK, 3 rows affected, 2 warnings (0.01 sec)
Records: 5  Duplicates: 2  Warnings: 2

mysql> SHOW WARNINGS;
+---------+------+------------------------------------+
| Level   | Code | Message                            |
+---------+------+------------------------------------+
| Warning | 1526 | Table has no partition for value 6 |
| Warning | 1526 | Table has no partition for value 3 |
+---------+------+------------------------------------+
2 rows in set (0.00 sec)

Из вывода следующей инструкции TABLE можно увидеть, что строки с несоответствующими значениями столбца разбиения были молча отклонены, а строки без несоответствующих значений были вставлены в таблицу:

mysql> TABLE h2;
+------+------+
| c1   | c2   |
+------+------+
|    7 |    5 |
|    1 |    9 |
|    2 |    5 |
+------+------+
3 rows in set (0.00 sec)

MySQL также поддерживает разбиение по LIST COLUMNS, разновидность разбиения по LIST, которая позволяет использовать столбцы типов, отличных от целого, для столбцов разбиения и использовать несколько столбцов в качестве ключей разбиения. Дополнительную информацию см. в Разделе 26.2.3.2, «Разбиение по списку столбцов».