MySQL-获取下一个自动增量的过程 [英] Mysql - procedure to get next auto increment

问题描述

我在编写存储过程以获取Auto Incremented列的下一个值时遇到一些问题.我希望能够传递表名并将值返回给指定的变量.

I'm having some problems writing a stored procedure to get next value of Auto Incremented column. I want to be able to pass a table name and have the value returned to a specified variable.

过程如下:

CREATE PROCEDURE Find_last_AI(
        IN table_name VARCHAR(255),
        OUT last_AI INT)
BEGIN
    SELECT AUTO_INCREMENT
    INTO last_AI
    FROM  INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'MySchema'
    AND   TABLE_NAME = table_name
    LIMIT 1;
END;

问题是，在我这样调用函数之后:

The problem is, after I call the function like this:

CALL Find_last_AI('MyTable', @out);    
SELECT @out;

我得到相同的错误结果-无论表输入是什么，都为数值(5).关键是，当我将代码作为查询执行时，结果会产生正确的值.

I get the same wrong result - a numeric value (5) no matter what the table input is. The thing is, when I execute the code as a query the results yield correct values.

SELECT AUTO_INCREMENT
    FROM  INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'MySchema'
    AND   TABLE_NAME   = 'table_name'
    LIMIT 1;

似乎我在程序本身中忘记了一些东西，但看不到吗?

It seems that I'm forgetting something in the procedure itself, but I can't see it ?

推荐答案

我像下面那样尝试了一下，效果很好.

I tried myself like below and it worked fine.

set @tabname = 'tab';

    SELECT AUTO_INCREMENT
    INTO @incre
    FROM  INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = @tabname
    LIMIT 1;

select @incre;

也要提及的是，使用上面的查询，您将获得下一个(未来)auto_increment值(除了，现值+ 1).

Also Good to mention that, using the query above you will get the next (future) auto_increment value (nothing but, present value + 1).

终于明白了；问题是您的查询中的LIMIT 1.试试下面的代码，它可以正常工作(测试).另外，请勿使用任何保留字作为变量名.

Finally got it; problem is with LIMIT 1 in your query. Try the below code and it's guaranteed to work fine (TESTED). Also, don't use any reserved word as variable name.

DELIMITER $$
CREATE PROCEDURE Find_last_AI(IN tname VARCHAR(255), OUT last_AI INT)
BEGIN
    SELECT AUTO_INCREMENT
    INTO last_AI
    FROM  INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'test'
    AND   TABLE_NAME = tname;
END$$
DELIMITER ;

运行类似

CALL Find_last_AI('test3', @last_AI);    
SELECT @last_AI;

这篇关于MySQL-获取下一个自动增量的过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！