通过MySql中的变量设置AUTO_INCREMENT值 [英] Set AUTO_INCREMENT value through variable in MySql
问题描述
我有一个带有auto_increment列的表.我将列的最后一个值保存在另一个名为ids_tbl的表中,当mysql重新启动时,从ids_tbl中读取该值并重新设置AUTO_INCREMENT值. 如果我这样做:
I have a table with an auto_increment column. I save the last value of the column in another table called ids_tbl, and when mysql restarts, read the value from ids_tbl and re-set the AUTO_INCREMENT value. If I do this:
alter table outgoing_tbl auto_increment=500;
有效
但是,如果我这样做
select @max_id:= max_id FROM ids_tbl;
alter table outgoing_tbl auto_increment=@max_id;
或者如果我这样做:
select @max_id:= max_id FROM ids_tbl;
alter table outgoing_tbl auto_increment=(select @max_id);
然后它不起作用,如何通过变量设置自动增量值?
Then it does not work, how do I set the auto increment value throgh a variable?
推荐答案
使用以下代码.一切正常. 并遵循MySQL准备的语句 https://dev .mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html
Use the below code. This is working fine. And follow the MySQL prepared statement https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html
SET @max_id = (SELECT MAX(id) FROM `ids_tbl` );
SET @sql = CONCAT('ALTER TABLE `outgoing_tbl` AUTO_INCREMENT = ', @max_id);
PREPARE st FROM @sql;
EXECUTE st;
这篇关于通过MySql中的变量设置AUTO_INCREMENT值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!