mysql - 这个sql函数的正确版本是什么？

Question

生成新的 id 作为 varchar 的正确方法是什么？在这里，我尝试从 SQL Server 转换为 MySQL 数据库。

需要帮助。

我尝试了互联网，但它没有解决我的问题。

--*GENERATES ANY ID WHEN NEED*
DELIMITER //

CREATE FUNCTION getNewID(needTable VARCHAR(20)) RETURNS VARCHAR(10)
BEGIN
    DECLARE lastvalue VARCHAR(10);
    DECLARE i INT;
    DECLARE newId VARCHAR(10);

    IF needTable = 'Item'
            SELECT lastvalue = MAX(resourceID) FROM Item;

            SELECT MAX(resourceID) INTO lastvalue FROM Item;

            IF IS NULL(lastvalue)
                SET lastvalue = 'I00000000';
                SET i = RIGHT(lastvalue,9) + 1;
                SET newId = 'S' + RIGHT('00000000'+CONVERT(VARCHAR(10),i),9);

            RETURN newId;
END; //

DELIMITER ;

SELECT getNewID ('Item');

DROP FUNCTION getNewID

错误说:

Error code 1064, SQL state 42000
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT MAX(resourceID) INTO lastvalue FROM Item;

IF IS NULL(lastval' at line 10
Line 3, column 1

Execution finished after 0 s, 1 error(s) occurred.

Answer 1

试试这个

DELIMITER //

CREATE FUNCTION getNewID(needTable VARCHAR(20)) RETURNS VARCHAR(10)
BEGIN
    DECLARE lastvalue, newId VARCHAR(10);
    DECLARE i INT;

    SELECT MAX(resourceID) INTO lastvalue FROM Item where needTable = 'Item';

            IF(lastvalue IS NULL) THEN
                SET lastvalue = 'I00000000';
                SET i = RIGHT(lastvalue,9) + 1;
                SET newId = 'S' + RIGHT('00000000'+CONVERT(VARCHAR(10),i),9); -- what you are trying to do here?
                END IF;
-- what you need to return whenlastvalue is not null?
            RETURN newId;
END //

DELIMITER ;