©
Роман Чернышов23.08.2012—24.08.2012
В современных версиях MySQL (5.1, 5.5, 5.6) есть несколько функций работы со списками и специальный списочный тип SET. Под списком подразумевается строка текста, составленная из разделенных запятыми подстрок (естественно, не содержащих запятые). Некоторые функции поддерживают произвольный разделитель, что несколько расширяет их применение, но это выходит за рамки статьи, и об этом вы и сами можете прочитать в документации. Здесь я расскажу про тип SET, возможности его применения, опишу встроенные функции работы со списками и предложу варианты расширений.
Английское слово “set” в данном контексте можно перевести как «множество» или «набор». Набор строк. Учитывая, что набор представлен в форме списка, считаю, что и называть его нужно списком. Чтобы не путаться с другими списками, когда возможна неоднозначность понимания, буду называть его CSV‑списком (comma separated values — значения, разделенные запятыми).
Хранится тип SET как число (1, 2, 3, 4 или 8 байт), а точнее — как битовая карта (до 64 бит), где порядковый номер бита соответствует порядковому номеру строки в описании столбца, а значение бита управляет вхождением ассоциированной строки в результирующий CSV‑список. Работать с этим типом можно как с числом, как с битовой картой или как со строкой (списком). Приведу небольшой пример:
mysql> CREATE TABLE t (a SET ('aaa', 'bbb', 'ccc'));
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO t VALUES ('aaa'), ('aaa,bbb'), ('bbb'), ('ccc,aaa');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT a `string`, a + 0 `decimal`, LPAD(BIN(a + 0), 8, '0') `binary` FROM t;
+---------+---------+----------+
| string | decimal | binary |
+---------+---------+----------+
| aaa | 1 | 00000001 |
| aaa,bbb | 3 | 00000011 |
| bbb | 2 | 00000010 |
| aaa,ccc | 5 | 00000101 |
+---------+---------+----------+
4 rows in set (0.00 sec)
Наглядно видно соответствие бит и строк. К сожалению, битовых индексов MySQL не имеет и ниже приведенные операции приводят к полному просмотру всех строк таблицы (или полному сканированию индекса, если такое возможно).
mysql> SELECT a FROM t WHERE a & 1;
+---------+
| a |
+---------+
| aaa |
| aaa,bbb |
| aaa,ccc |
+---------+
3 rows in set (0.00 sec)
mysql> SELECT a FROM t WHERE FIND_IN_SET('aaa', a);
+---------+
| a |
+---------+
| aaa |
| aaa,bbb |
| aaa,ccc |
+---------+
3 rows in set (0.00 sec)
mysql> SELECT a FROM t WHERE a LIKE '%aaa%';
+---------+
| a |
+---------+
| aaa |
| aaa,bbb |
| aaa,ccc |
+---------+
3 rows in set (0.00 sec)
И только равенство позволяет использовать индекс. Индексировать столбец SET можно, но бессмысленно.
mysql> SELECT a FROM t WHERE a = 'aaa';
+-----+
| a |
+-----+
| aaa |
+-----+
1 row in set (0.00 sec)
Но есть у SET и положительные качества. Его можно рассматривать как заменитель двух таблиц: таблицы‑справочника и связующей таблицы. Не верите? Наглядное сравнение подрывает сомнение:
CREATE TABLE t (id INT PRIMARY KEY);
CREATE TABLE dict_a (id INT PRIMARY KEY, name VARCHAR(32) NOT NULL);
CREATE TABLE list_a (t_id INT NOT NULL, dict_id INT NOT NULL, PRIMARY KEY (t_id, dict_id));
SELECT t.id, GROUP_CONCAT(dict_a.name ORDER BY dict_a.id SEPARATOR ',')
FROM t, list_a, dict_a
WHERE list_a.t_id = t.id
AND dict_a.id = list_a.dict_id;
CREATE TABLE t (id INT PRIMARY KEY, list SET('aaa', 'bbb', 'ccc'));
SELECT id, list
FROM t;
Конечно, за это удобство надо платить:
- Получить список всех возможных значений элементов можно только через SHOW CREATE TABLE или INFORMATION_SCHEMA.COLUMNS и результат еще нужно парсить.
- Изменить состав возможных значений элементов можно только через ALTER TABLE, что требует повышенных прав и приведет к перестроению таблицы и всех ее индексов.
- Затруднительно сравнить столбцы типа SET в двух разных таблицах. Если DDL столбцов полностью идентичны, то можно использовать равенство/неравенство и битовые операции, но данная схема не защищена от ошибки при изменении DDL столбцов.
- Возможно устанавливать и снимать отдельные биты столбца оперируя им как числом, но затруднительно удалить или добавить подстроку в CSV‑список.
Помимо типа SET, CSV‑списки можно хранить в любом текстовом столбце, переменной сессии или локальной переменной пользовательской процедуры. Конечно, такие списки уже не будут контролироваться MySQL: целостность списка, повторные элементы, порядок элементов — все на совести DBA и программиста. Но положительные качества у таких списков не хуже чем у SET. Встроенные функции работы со одинаково работают с SET и обычными строками. Рассмотрим функционал, представленный в MySQL 5.5. Я буду кратко описывать практическое использование функций, а за детальным описанием прошу обратиться к официальной документации.
Функции, формирующие список:
list GROUP_CONCAT(field1 [, ...] [ORDER BY ...] [SEPARATOR str])Агрегатная функция, используемая совместно с GROUP BY. Недостаток: максимальная длина формируемой строки ограничена значением переменной group_concat_max_len (можно менять на ходу).
list CONCAT_WS(separator, str1 [, ...])Аналог конструкции CONCAT(str1, separator, str2 [, separator, ...]).
list EXPORT_SET(bits, on, off [, separator [, number_of_bits]])Это быстрее визуализация битовой карты, чем формирование списка.
int FIELD(str, str1 [, ...])Поиск строки в списке аргументов и выдача индекса найденного совпадения. Недостаток: список представлен в виде переменного числа аргументов функции, а не в виде одной строки. Можно использовать в prepared statements.
int FIND_IN_SET(str, list)Поиск строки в CSV-списке и выдача индекса найденного совпадения. Наиболее полезная функция работы со списками.
list MAKE_SET(bits, str1 [, ...])Формирует список на основе битовой карты. Действия аналогичны присвоению числа столбцу типа SET.
list SUBSTRING_INDEX(list, delimiter, count)Функция оперирует разделителями: обрезает строку по указанный разделитель. При отрицательном count отсчет происходит с конца строки.
Вот, собственно, и все... Учитывая то, что в процедурах MySQL не предусмотрены массивы, приходится частично заменять их CSV‑строками, а с штатным набором далеко не уедешь. Парсить строку самому — значит сделать программу нечитаемой.
В ниже приведенных функциях я использовал следующие ограничения:
- длина элемента списка — от 0 до 32 символов (полагаю, этого достаточно),
- разделитель элементов — запятая,
- количество элементов — от 0 до 64,
- длина списка — до 2111 символов ((32 + 1) × 63 + 32),
- пустая строка считается списком из одного пустого элемента.
Если вам в этих рамках тесно, измените значения в коде. Убрать поддержку пустых элементов немного сложнее — я пока до конца не понял, вредны ли они или полезны.
string ITEM_FROM_SET(position, list)Прежде всего, вспомним, что FIND_IN_SET позволяет нам найти позицию элемента в списке. ITEM_FROM_SET является обратной функцией получения элемента по номеру позиции.
mysql> SELECT ITEM_FROM_SET(3, 'a,b,c,d') `Result`\G
*************************** 1. row ***************************
Result: c
1 row in set (0.00 sec)
int COUNT_OF_SET(list)Возвращает количество элементов в списке.
mysql> SELECT COUNT_OF_SET('1,2,3,4') `Result`\G
*************************** 1. row ***************************
Result: 4
1 row in set (0.00 sec)
list APPEND_TO_SET(item, list)Добавляет элемент к списку. Эта функция несколько выбивается из вышеперечисленных правил и рассматривает пустой список как действительно пустой! Если нужно иное поведение, используйте CONCAT или CONCAT_WS.
mysql> SELECT APPEND_TO_SET('qqq', 'aa,bb,cc') `Result`\G
*************************** 1. row ***************************
Result: aa,bb,cc,qqq
1 row in set (0.00 sec)
list REMOVE_FROM_SET(item, list)Удаляет элемент из списка. Нормально работает с пустыми элементами.
mysql> SELECT REMOVE_FROM_SET('bb', 'aa,bb,cc') `Result`\G
*************************** 1. row ***************************
Result: aa,cc
1 row in set (0.00 sec)
list UNIQUE_SET(list)Никто не запрещает иметь в списке несколько одинаковых элементов. Эта функция поможет навести порядок и избавиться от повторов,
mysql> SELECT UNIQUE_SET('1,2,3,1,2,3,1,2,3,4') `Result`\G
*************************** 1. row ***************************
Result: 1,2,3,4
1 row in set (0.00 sec)
Нужна возможность манипулировать множествами?
list MERGE_SETS(list1, list2)Слияние списков. Внимание: оба списка рассматриваются также, как в APPEND_TO_SET!
mysql> SELECT MERGE_SETS('a,b,c', '1,2,3') `Result`\G
*************************** 1. row ***************************
Result: a,b,c,1,2,3
1 row in set (0.00 sec)
list DIFF_SETS(list1, list2)Возвращает список с несовпадающими элементами исходных списков.
mysql> SELECT DIFF_SETS('1,2,3,4,5,6', '3,5,2,7,11') `Result`\G
*************************** 1. row ***************************
Result: 1,4,6,7,11
1 row in set (0.00 sec)
list INTERSECT_SETS(list1, list2)Возвращает список с совпадающими элементами исходных списков.
mysql> SELECT INTERSECT_SETS('1,2,3,4,5,6', '3,5,2') `Result`\G
*************************** 1. row ***************************
Result: 2,3,5
1 row in set (0.00 sec)
bool INTERSECTED_SETS(list1, list2)Возвращает логическое значение при совпадении. Работает быстрее INTERSECT_SETS, так как завершается при нахождении первого совпадения.
mysql> SELECT INTERSECTED_SETS('1,2,3,4,5,6', '3,5,2') `Result`\G
*************************** 1. row ***************************
Result: 1
1 row in set (0.00 sec)
SORT_SET(INOUT list)Ну и для полноты картины, процедура сортировки списка. Реализация «ленивая», через временную таблицу. Учитывая тот факт, что в рамках сессии одновременно может выполняться только один пользовательский запрос, конфликт с имененем временной таблицы практически исключен.
mysql> SET @list = '3,5,2,1,aaa,1a,33';
Query OK, 0 rows affected (0.00 sec)
mysql> CALL SORT_SET(@list);
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT @list `Result`\G
*************************** 1. row ***************************
Result: 1,1a,2,3,33,5,aaa
1 row in set (0.00 sec)
Код всех перечисленных функций прилагаю к статье. Разрешаю свободное использование и модификацию.
Файлы: