Автор:
RXL.
Дата написания: 22.11.2009.
Права на статью принадлежат автору и
Клубу программистов «Весельчак У».
- Часть 1.
- Часть 2.
- Часть 3. (Выйдет позже)
- Часть 4. (Выйдет позже)
Не знаю, как кому, а мне сполна хватает штатных возможностей MySQL по генерации уникальных числовых последовательностей. Тем не менее, на форумах, посвященных MySQL, время от времени появляется один и тот же вопрос — как реализовать функционал наподобие SEQUENCE из Oracle. Заявленное применение — использование общей сквозной нумерации записей в нескольких таблицах без создания главной таблицы. Привыкли, понимаешь, к одному интерфейсу и не хотят уже проектировать базы данных иначе.
Не следует забывать, что MySQL и Oracle — СУБД разного класса, и к ним предявляются различные требования: MySQL — легкая, компактная и быстрая СУБД, Oracle же — СУБД более серьезная, насыщенная функционалом, но и более тяжелая в эксплуатации.
Кстати, исторический факт (для тех, кто не знает): на момент написания статьи MySQL принадлежит Sun Microsystems, которая находится сейчас в стадии покупки ее Oracle Corporation. Это означает, что MySQL уже не перейдет в более высокий класс, дабы не создавать конкуренцию Oracle Database — по крайней мере, пока Oracle Corp. не продаст MySQL кому-либо еще.
Ну да ладно с ней, с политикой, вернемся с небес к нашим базам.
В данной статье я хочу в режиме монолога рассмотреть штатные возможности MySQL, принципы работы SEQUENCE и возможность реализовать ее подобие.
Еще в процессе написания первых пунктов я понял, что статья получается длинная и, чтобы быстрее выпустить и чтобы легче читалось, решил разбить ее на части.
Штатные возможности MySQL крайне просты, хотя и тут есть нюансы.
Для генерации уникальных последовательностей (далее будем условно называть «ID») используется признак AUTO_INCREMENT на целочисленной колонке. При этом колонка обязательно должна быть проиндексирована — должна входить в индекс. Индекс, в отличие от распространенного мнения, не обязан быть PRIMARY KEY или уникальным вообще. Он может быть любым, даже любого формата — BTREE или HASH (используется в MEMORY). Конечно, использование уникальных индексов полезно для обеспечения целостности, но знать о возможностях нужно, чтобы не застревать в общих шаблонах.
Форматы INNODB и MEMORY требуют, чтобы колонка ID в индексе была единственной. Формат же MYISAM допускает, чтобы колонка ID была не единственной в индексе. В последнем случае возникает интересный эффект: следующее значение ID берется как увеличенное на единицу максимальное значение части ключа, описывающее нашe ID для данной ветки BTREE, определяемой столбцами, предшествующими ID. Покажу на примере.
CREATE TABLE homonyms
(
surname VARCHAR(64) NOT NULL,
firstname VARCHAR(64) NOT NULL,
patronymic VARCHAR(64) NOT NULL,
id INT NOT NULL AUTO_INCREMENT,
UNIQUE homonym_id (surname, firstname, patronymic, id)
) ENGINE=MYISAM;
Для каждого добавленного в таблицу полного тезки будет присвоен уникальный номер, но нумерация у каждой группы тезок своя, независимая.
Трижды выполнив:
INSERT INTO homonyms (surname, firstname, patronymic)
VALUES ('Иванов', 'Иван', 'Иванович');
и еще раз трижды выполнив:
INSERT INTO homonyms (surname, firstname, patronymic)
VALUES ('Петров', 'Петр', 'Петрович');
мы обнаружим следующее:
mysql> SELECT * FROM homonyms ORDER BY 1, 2, 3, 4;
+---------+-----------+------------+----+
| surname | firstname | patronymic | id |
+---------+-----------+------------+----+
| Иванов | Иван | Иванович | 1 |
| Иванов | Иван | Иванович | 2 |
| Иванов | Иван | Иванович | 3 |
| Петров | Петр | Петрович | 1 |
| Петров | Петр | Петрович | 2 |
| Петров | Петр | Петрович | 3 |
+---------+-----------+------------+----+
6 rows in set (0.01 sec)
Эту возможность мы и будем использовать в части реализаций.
Почему такого функционала нет для INNODB и MEMORY, я не разбирался.
Кстати, в индексе после ID могут быть и другие столбцы, но они уже не влияют на AUTO_INCREMENT.
Есть несколько путей реализации. Еще до того, как я сел за написание статьи, на нашем форуме программистов мы обсудили некоторые особенности и недостатки. Сперва были две реализации — с CONNECTION_ID и с временными таблицами, которые мы обсудили, а в процессе обсуждения появились еще два варианта. Еще одну я придумал парой месяцев раньше, когда увлекся написанием UDF.
Общим для всех реализаций должно быть: возможность создавать и удалять генераторы последовательностей (create и drop), возможность генерации нового значения (nextval), возможность получить последнее сгенерированное в рамках текущей сессии значение (currval) и гарантия уникальности генерируемых значений с учетом многопоточной работы MySQL. Остальные характеристики будут зависеть от реализации.
Еще один важный момент: наша реализация не должна сама генерировать транзакции, чтобы не пересекаться с транзакциями пользователя. Кроме того, внутри функции невозможно закрыть транзакцию. Т.е., если реализация и использует транзакции, то они должны открываться и закрываться пользователем.