вторник, 26 апреля 2016 г.

Заполнение изначальной сортировки в MySQL.

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

Вы добавили код, который работает с сортировкой.

Но нужно ещё отсортировать ранее введённые данные. Когда их мало, это можно сделать вручную.

А если их много, то поможет такая процедура на MySQL:

CREATE DEFINER=`root`@`%` PROCEDURE `reindex_table`(IN `sorted_table` VARCHAR(50), IN `sorting_column` VARCHAR(50), IN `grouping_column` VARCHAR(50), IN `group_table` VARCHAR(50), IN `group_id_column` VARCHAR(50))
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'Заполняет поле сортировки.'
BEGIN

# Пример использования:
# CALL reindex_table('my_cool_table', 'sort', 'group_id', 'group_table', 'id');

DECLARE done INT DEFAULT 0;

# В нашей таблице используется бинарный UUID, 
# поэтому такой же тип назначаем переменной.
# В обычных таблицах, здесь был бы INT.
DECLARE group_id BINARY(16);

# Курсоры и обработчики объявляются последними.
DECLARE cur_1 CURSOR FOR SELECT id FROM group_table_tmp;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

# Создаём временную таблицу для прочёсывания курсором.
# Это единственный способ заставить курсор двигаться по таблице, 
# заданной водными параметрами.
# Есть ещё вариант с созданием VIEW, 
# с преимуществом по производительности и со своими недостатками.
# Так как скорость нас в данном случае не волнует, делаем через временную таблицу.
DROP TEMPORARY TABLE IF EXISTS group_table_tmp;
SET @sql_text = CONCAT('CREATE TEMPORARY TABLE group_table_tmp ENGINE=MEMORY AS SELECT ', group_id_column, ' AS id FROM ', group_table);
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

# Проходим цикл по внешнему ключу, который разбивает нашу таблицу на группы сортировки.
OPEN cur_1;
REPEAT
FETCH cur_1 INTO group_id;
# Бинарный UUID нуждается в преобразовании.
# Если у вас обычный целочисленный ID, вставляйте его, как есть.
SET @group_id_converted = CONCAT('0x', HEX(group_id));

# Пронумеровываем строки.
SET @curRow = 0;
SET @sql_text = CONCAT('UPDATE ', sorted_table, ' sorted_tbl,',
' (SELECT @curRow := @curRow + 1 AS row_number, ', sorted_table, '.* FROM ', sorted_table, ' WHERE ', grouping_column, '=',  @group_id_converted, ' AND deleted=0 ORDER BY date_create) indexed_rows',
' SET sorted_tbl.', sorting_column, ' = indexed_rows.row_number',
' WHERE sorted_tbl.id = indexed_rows.id;');
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

UNTIL done = 1 
END REPEAT;
CLOSE cur_1;

# Удаляем временную таблицу.
DROP TEMPORARY TABLE IF EXISTS group_table_tmp;

END