MySQL 5.6 – Is DETERMINISTIC NO SQL Function a Good Alternative for Constants?

MySQLmysql-5.6stored-procedures

MySQL does not support declaring constants which can be used between many stored procedures.
For me, it seems that a deterministic no sql function can be used as an alternative for a constant.
Example:
I want to use a constant named "OPERATION_TYPE_ABORT" with the value "ABORT".
So what I think I can use as an alternative is defining a stored function like below.

DROP FUNCTION IF EXISTS OPERATION_TYPE_ABORT;
DELIMITER //
CREATE FUNCTION OPERATION_TYPE_ABORT() RETURNS VARCHAR(6) 
DETERMINISTIC NO SQL
BEGIN
    RETURN ("ABORT");
END//
DELIMITER ;

Output:

mysql> select "test" FROM DUAL WHERE "ABORT" = OPERATION_TYPE_ABORT();
+------+
| test |
+------+
| test |
+------+
1 row in set (0.00 sec)

mysql> select "test" FROM DUAL WHERE "ABORT2" = OPERATION_TYPE_ABORT();
Empty set (0.00 sec)

Do you see any disadvantage or a issue in this approach? (for e.g. Significant performance issue etc).
P.S: The queries which use these constants are using small sized tables in the query. MySQL version 5.6

Thanks

Best Answer

I'd say yes, it is a good technique.

When there are no input arguments to a stored function declared as DETERMINISTIC and NO SQL, the optimizer does the right thing -- it executes the function once, and resolves the result from the function into an internal constant when the query is planned, so applicable indexes on the column being compared to the result of the function are used, as expected.

The behavior and performance using this technique should (for all practical purposes) be no different than if the query actually contained the literal constant.

Related Question