variable_get as a mysql function
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.