Ich möchte diese Funktion auf mySql 8 erstellen. Es wird eine Seriennummer wie 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
Ich weiß nicht, was mit dieser Abfrage falsch ist, aber ich erhalte immer diese Fehlermeldung.
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
Ich habe diese Abfrage auch ausprobiert.
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
Aber ich erhalte immer noch den gleichen Fehler. Was könnte bei meiner Funktionsabfrage schief gehen?
多次数据类型转换是多余的 - MySQL 会根据操作上下文隐式更改数据类型。
所有操作都可以在单个语句中执行,这使得声明变量和 BEGIN-END(以及分隔符重新分配)都变得不必要。
代码需要
lastNumber
才能转换为数字数据类型。如果不是,那么你和我的代码在严格 SQL 模式下都会失败。因此,我建议将lastNumber CHAR(255)
输入参数数据类型更改为 UNSIGNED / INT - 这将允许在函数调用阶段检测值的不正确性,而不是在函数代码中。