我想在 mySql 8 上创建此函数。它将创建一个类似 00001,00002 的序列号
CREATE FUNCTION dbOne.create_sequence_number(lastNumber CHAR(255), numberLength INT, lastValue CHAR(255) ) RETURNS char(255)
BEGIN
DECLARE select_var CHAR(255);
SET select_var = (SELECT
CASE WHEN lastNumber = lastValue
THEN
LPAD( '1', numberLength, '0' )
ELSE
LPAD(CAST(( CAST(COALESCE ( lastNumber, '0' ) AS INT) + 1 ) AS VARCHAR, numberLength, '0' ) INTO select_var);
RETURN select_var;
END
我不知道这个查询有什么问题，但我总是收到这个错误。
SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT) + 1 ) AS VARCHAR, numberLength, '0' ) INTO select_var);
RETURN select_var' at line 9
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT) + 1 ) AS VARCHAR, numberLength, '0' ) INTO select_var);
RETURN select_var' at line 9
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT) + 1 ) AS VARCHAR, numberLength, '0' ) INTO select_var);
RETURN select_var' at line 9
我也尝试过这个查询。
CREATE FUNCTION erhav2_db.create_sequence_number(lastNumber CHAR(255), numberLength INT, lastValue CHAR(255) ) RETURNS char(255)
BEGIN
DECLARE select_var CHAR(255);
SELECT
(CASE WHEN lastNumber = lastValue
THEN
lpad( '1', numberLength, '0' )
ELSE
lpad(CAST(( CAST(COALESCE ( lastNumber, '0' ) AS INT) + 1 ) AS VARCHAR, numberLength, '0' ))) INTO select_var;
RETURN select_var;
END
但仍然给我同样的错误。我的函数查询可能会出现什么问题？
CREATE FUNCTION dbOne.create_sequence_number( lastNumber /* CHAR(255) */ UNSIGNED, numberLength INT, lastValue CHAR(255) ) RETURNS CHAR(255) RETURN LPAD(CASE WHEN lastNumber = lastValue THEN 1 ELSE COALESCE(lastNumber, 0) + 1 END, numberLength, '0');多次数据类型转换是多余的 - MySQL 会根据操作上下文隐式更改数据类型。
所有操作都可以在单个语句中执行，这使得声明变量和 BEGIN-END（以及分隔符重新分配）都变得不必要。
代码需要
lastNumber才能转换为数字数据类型。如果不是，那么你和我的代码在严格 SQL 模式下都会失败。因此，我建议将lastNumber CHAR(255)输入参数数据类型更改为 UNSIGNED / INT - 这将允许在函数调用阶段检测值的不正确性，而不是在函数代码中。