Обновление столбца таблицы MySQL последовательными числами

Быстрый ответ

Для присвоения уникальных порядковых номеров записям в MySQL воспользуйтесь переменной, действующей как счётчик:

SET @seq := 0;
UPDATE your_table SET your_column = (@seq := @seq + 1) ORDER BY sort_column;

Такой запрос обновит your_column, присвоив каждой записи уникальный порядковый номер, исходя из сортировки, определённой в sort_column.

Решение задачи циклической последовательности

Если необходимо создать циклическую последовательность в определённом диапазоне (скажем от 1 до 3), примените следующий метод:

SET @seq := 0;
UPDATE your_table SET your_column = 1 + (@seq := (@seq + 1) % 3);

Теперь каждой записи будет присвоен циклический номер от 1 до 3.

Обновление без применения ALTER TABLE

Несмотря на то, что может показаться привлекательной идеей использование ALTER TABLE для определения последовательностей, лучше не полагаться на функцию Auto_Increment, поскольку она не поддерживает циклическую нумерацию. Более эффективным решением будет использование команды UPDATE со счетчиком в качестве переменной.

Работа с большими объемами данных

Этот способ прекрасно справляется с большими объемами данных, например, с 50 тысячами записей и более. Он позволяет обновить все строки одновременно, уходя от излишних циклов и курсоров.

Визуализация

Мы можем представить обновление колонок с порядковыми номерами как расстановку книг на полке вопределённом порядке:

До:      [📗, 📘, 📙, 📕]
Номера книг: [  ?,   ?,   ?,   ?]

Каждая книга желает получить свой порядковый номер.

UPDATE books SET volume = (@volume := @volume + 1);

После: [📕🔢1, 📗🔢2, 📘🔢3, 📙🔢4]
Теперь каждая книга гордо носит свой порядковый номер.

Принятие альтернативных методов присвоения последовательности

Дружество с оператором JOIN

Если нужно обновить данные, основываясь на информации из другой таблицы, оператор JOIN способен помочь:

UPDATE your_table
JOIN other_table ON your_table.foreign_key = other_table.primary_key
SET your_table.sequence_column = (@seq := @seq + 1)
ORDER BY your_table.sort_column;

Подробнее об этом расскажет наш спикер на видео

Сохранение данных колонки при обновлении

При обновлении последовательности можно сохранить другие данные в таблице:

UPDATE your_table SET number_column = (@seq := @seq + 1), name_column = name_column;

Гарантирование уникальности последовательности в записях

Используйте уникальные поля, такие как rowid, для начала присвоения последовательности, чтобы гарантировать уникальность и избежать пересечений.

Улучшение производительности и обеспечение устойчивости стратегии

Учет индексации

Индексация sort_column важна для быстроты выполнения запросов с ORDER BY, особенно при работе с большими объемами данных.

Размышления о вычисляемых колонках

Вычисляемые колонки может быть полезны для хранения порядковых номеров, определённых на основании специфических критериев.

Избегание распространённых ошибок

Не забывайте о таких ошибках, как пропуски в AUTO_INCREMENT после удаления записей. Нумерация не будет исправлена автоматически, нужно внести корректировки вручную.

Полезные материалы

1. Multiple Updates in MySQL – Stack Overflow — Обстоятельная дискуссия о множественных обновлениях в MySQL.
2. MySQL :: MySQL 8.0 Reference Manual :: 5.6.9 Using AUTO_INCREMENT — Изучаем способности AUTO_INCREMENT.
3. MySQL – Get row number on select – Stack Overflow — Создаем порядковые номера с помощью SELECT.
4. MySQL :: MySQL 8.0 Reference Manual :: 11.4 User-Defined Variables — Руководство по использованию пользовательских переменных.
5. How to reset AUTO_INCREMENT in MySQL – Stack Overflow — Раскрываем тему сброса AUTO_INCREMENT.
6. Why is ORDER BY in a FROM Subquery Ignored? – MariaDB Knowledge Base — Понимаем особенности работы ORDER BY в подзапросах.