У меня есть таблица с первичным ключом, столбец идентификатора auto_increment. Когда я удаляю строку с наивысшим идентификатором, например, ID 100, я хочу использовать этот идентификатор 100 для новой строки только с помощью триггера mysql. Как мне это сделать?
Когда я удаляю, например, ID 1 И самый высокий ID равен 100, я больше не хочу использовать ID 1.
Я делаю инструкцию delete и insert с различными вызовами PHP.
Вы должны установить опцию AUTO_INCREMENT, например,
DELETE FROM table_name WHERE id = 100; ALTER TABLE table_name AUTO_INCREMENT = 100;
… затем вставьте новые записи.
INSERT INTO table_name (id) VALUES (NULL); --> will add 100
Вот триггер (с инструкцией drop / create):
DELIMITER $$ DROP TRIGGER IF EXISTS `trigger_name`$$ CREATE TRIGGER `trigger_name` BEFORE INSERT ON `table_name` FOR EACH ROW BEGIN DECLARE inc_val INT; SET inc_val := (SELECT IFNULL(MAX(ID), 0)+1 FROM table_name) ; SET NEW.ID = inc_val ; END; $$ DELIMITER ;
Я попытался изменить значение auto_increment используя alter table . Поскольку проверка каждого раза при вставке может привести к проблемам с производительностью. Поэтому я решил, что лучше удалить триггер при удалении.
Я попробовал это и пришел к выводу, что Alter table нельзя применять в триггерах. И Выполнение только "Alter table" Изменение "Alter table" для изменения auto_increment является таким решением, поэтому такой подход невозможен.
MySQL дает ошибку «Явные / неявные коммиты не разрешены в Trigger».
Я предлагаю вам создать хранимую процедуру, а затем вызвать ее, когда вы удаляетесь из таблицы. Я думаю, вы всегда знаете, где вы удаляете (так кажется возможным).
Процедура такова:
DELIMITER $$ USE `database_name`$$ DROP PROCEDURE IF EXISTS `new_max`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `new_max`() BEGIN DECLARE i INTEGER; SET i = (SELECT MAX(id) FROM `table_name`); SET @ddl = CONCAT('alter table `table_name` auto_increment = ',(i+1)); PREPARE STMT FROM @ddl; EXECUTE STMT; END$$ DELIMITER ;
И вы будете называть его call new_max();
В хранимой процедуре вам не нужно много программировать, просто добавьте простую строку в свой код после удаления команды удаления, и она будет работать так, как вы хотите. Надеюсь, поможет….
Наконец, я нашел правильное решение для своей проблемы. Спасибо всем вашим предложениям за критику. Вместо использования триггера я теперь использую процедуру (спасибо за идею @shubhansh).
Это инструкция create для процедуры:
DELIMITER $$ USE `[DATABASE]`$$ DROP PROCEDURE IF EXISTS `[PROCEDURE NAME]`$$ CREATE PROCEDURE `[PROCEDURE NAME]`(deleteID INT(11)) BEGIN DECLARE i INTEGER; SET @deleteRow = CONCAT('delete from `[TABLE NAME]` WHERE ID = ', deleteID); PREPARE a FROM @deleteRow; EXECUTE a; SET i = (SELECT IFNULL(MAX(ID), 0) FROM `[TABLE NAME]`); SET @alterTable = CONCAT('alter table `[TABLE NAME]` auto_increment = ',(i+1)); PREPARE b FROM @alterTable; EXECUTE b; END$$ DELIMITER ;
Теперь я могу удалить и изменить одну таблицу в одной инструкции PHP, и она сохраняется, потому что процедура блокирует таблицу.