Last updated September 14, 2009. Created by Jody Lynn on January 15, 2008.
Edited by bekasu, chx. Log in to edit this page.
Here is how you can get an integer variable to be reused in a trigger or a stored procedure:
DELIMITER //
CREATE FUNCTION variable_get(name_asked VARCHAR(255)) RETURNS INT
DETERMINISTIC
BEGIN
DECLARE v INT;
SELECT TRIM(BOTH '"' FROM TRIM(TRAILING ';' FROM SUBSTRING_INDEX(value, ':', -1))) INTO v
FROM variable
WHERE name = name_asked;
RETURN v;
END //
DELIMITER ;This mostly works for strings too but if there are quotes in the beginning of the string it will fail. SUBSTRING(SUBSTRING_INDEX(value, ':', 2), 3) contains the length of string -- you can work from there.
Collation Issue and Solution
This (very handy!) function was used for a project, and it works except for a problem with collations, depending on how the tables are set up. It created the following error:
SELECT variable_get('foobar') FROM users;
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
To fix it, change the WHERE line in the function above to:
WHERE name COLLATE utf8_unicode_ci = name_asked;
That seemed to fix it.