Допустим, у вас большой набор данных, для которых внезапно потребовалось сделать возможность ручной сортировки.
Вы добавили код, который работает с сортировкой.
Но нужно ещё отсортировать ранее введённые данные. Когда их мало, это можно сделать вручную.
А если их много, то поможет такая процедура на 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
Вы добавили код, который работает с сортировкой.
Но нужно ещё отсортировать ранее введённые данные. Когда их мало, это можно сделать вручную.
А если их много, то поможет такая процедура на 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
Комментариев нет:
Отправить комментарий