I want to create this function on mySql 8. It will create a serial number like 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
I don't know what's wrong with this query but I always get this error.
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
I also tried this query.
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
but still gives me the same error. What could possibly go wrong with my function query?
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
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');Multiple data type conversions are redundant - MySQL implicitly changes data types based on the context of the operation.
All operations can be performed in a single statement, making both declaration of variables and BEGIN-END (and delimiter reassignment) unnecessary.
The code requires
lastNumberto convert to a numeric data type. If not, then both your code and mine will fail in strict SQL mode. Therefore, I recommend changing thelastNumber CHAR(255)input parameter data type to UNSIGNED / INT - this will allow incorrectness of the value to be detected at the function call stage, rather than in the function code.