Is DETERMINISTIC NO SQL function which returns the same value always a good alternative for using as a constant in MySQL?

Question

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

Answer 1

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.