MariaDB – Correct Way to Declare and Use User Defined Variables

mariadb

I'm using MariaDB version 10.3.28-MariaDB-cll-lve

This site https://mariadb.com/kb/en/user-defined-variables/ says

User-defined variables names must be preceded by a single at character (@)

That's what I usually do but recently I was rushing, forgot the '@ and wrote the following function which seems to work fine without the variables having '@' at the front.

So is having a '@' in front of a user defined variable a firm requirement or not?

If it is, why does my function work correctly without it?

DELIMITER $$
CREATE  FUNCTION get_next_redacted_member_id() RETURNS INT(11)
BEGIN
       DECLARE result INT SIGNED;
       DECLARE lastid INT;
       INSERT INTO redacted_member_id ( date_used) VALUES ( NOW() ); 
       SET lastid = (SELECT LAST_INSERT_ID() );
       SET result =  -1 * lastid ;
       RETURN result;
END$$
DELIMITER ;

Best Answer

The main difference ist that you can use DECALRE ony in stored function and procedzres

DECLARE var_name

This statement is used to declare local variables within stored programs.

see manual

User-Defined Variables

User-defined variables are variables which can be created by the user and exist in the session.

see manual

Which means every user defined variable is accessible every where(in a session) not only in a stored procedure/function. So you set your user defined Variable in a stored procudure, you can get the value eve after the stored procedure ends. That is very helpful for debugging such procedures/functions.

Also when you DECLARE a variable, you set also its data type and size.

With user defined MysQL decides what it needs when You SET @a = 123455

Related Question