我想在 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 - 這將允許在函數呼叫階段檢測值的不正確性，而不是在函數程式碼中。