How to define custom STATIC Global Variables/Constants in MySQL

Question

As a C# developer who did database work more than 7 years ago (with MSSQL) I am now developing a MySQL Database solution where Inline SQL is prohibited in code and the developers must go through Stored Procedures (Routines).

The core challenge is defining CONST/STATIC values on the database side in one place that all of my stored procedures can access. For example:

# == RECORD STATE 
SET GLOBAL RECORD_STATE_NORMAL = 0;
SET GLOBAL RECORD_STATE_ARCHIVED = 1;
SET GLOBAL RECORD_STATE_DELETED = 2;

These values will never change and the database stored procedures need to use them when writing certain record details. I also need to share these values with the developers as they may need to pass core static defined values to some of their stored procedure calls.

The core problem is MySQL apparently refuses to let me create and set my own global variables outside of the configuration file (which I don't want to do since we're deployed on AWS RDS). Any time I try to set a global variable I get an error 1193 "unknown system variable".

I need a way to define a long list of Global static values on the database side (simple numbers) that all of my stored procedures can reference.

How can I do this?

• Akina: Create service table with the names and values (and maybe types). Create server starting event/procedure which will load this values.
• Floobinator: @Akina - a service "table" would require a lookup every single time I wanted to reference one of these "STATIC" variables, which would be ultra slow. I did create a stored procedure to initialize all the values, but as far as I'm aware I'd first have to add them to the configuration file, which poses a problem on AWS RDS. I'm trying to find out if there's a better technique - IMO a lookup table isn't an option due to optimization issues.
• Akina: a service "table" would require a lookup every single time I wanted to reference one of these "STATIC" variables Please read 2nd sentense too...
• James: Since static variable is not possible here, you can try using stored function. which can be used as a column inside a select too.
• Rick James: For a single session, you can use @ variables.

Answer 1

Since MySQL 5.5 it's not possible to set a global user-defined variable.

A work-around might be to create a stored procedure that would return what you need.

DROP PROCEDURE IF EXISTS HOUSE_SMALL_TYPE;
DELIMITER //
CREATE PROCEDURE HOUSE_SMALL_TYPE ()
BEGIN 
SELECT 0;
END//
DELIMITER ;

and then call it.

CALL HOUSE_SMALL_TYPE(); The DROP statement is required in order to be able to modify it.

Credit to shock_one on SO for the above answer

I was also thinking another option may be setting your constant values in a seperate table that all of your stored procedures could access.

• Floobinator: Yah I'm finding out MySQL has a gaping hole in efficiently empowering the server side to control the data (by setting global constants). From what I can tell, the only way to really do this is to create a lookup table (either manual via SELECT CASE or via SQL select). It's actually pretty sad that in 2019 these are our only options. We need a better (more efficient) solution to this type of problem.
• tombom: @Floobinator In my opinion you see a problem where there is none. Saying that reading the values from a table is in my eyes nonsense. Make it a memory table if you want, but that wouldn't even be necessary. As often as this data would be accessed it would remain in the buffer pool anyway. For this to be slow you would have to use hundredthousands of entries in your table and not use an index. If you have benchmarked that this is really too slow for your business needs, consider using some other technology than a relational database.
• Floobinator: @tombom Failing to define constant static values accessible to all stored procedures IMO is a failure of the core language, and it's not nonsense. Yes, we can use lookup tables, but that means we have to look up a value every single time we want to fetch it instead of just referencing it. Every language I've ever used has CONST capabilities, and referencing a global variable stored in memory will always outperform a query. Always. It would be absolute cake for MySQL to add global static CONST values, and it would improve performance.
• Rick James: As James suggests, a Store Function (similar to this SP) may make sense.
• tombom: @Floobinator The whole idea of using a database as some sort of development plattform in the way you want to do it, is in my eyes absolute nonsense. And I say that as Business Intelligence developer with 5 years experience and another 5 years experience as DBA. Keep in mind that SQL is a query language, not a programming language. A database is there to store and retrieve data. It is slow for anything else.