Mysql – How to define custom STATIC Global Variables/Constants in MySQL

Under Option 1, you are guaranteed a single row (if you remember to insert the one row firsthand).

• If you want to introduce a new setting, you would have to do ALTER TABLE to add the needed column.
• If you wanted to remove an old setting, you would have to do ALTER TABLE to remove the unneeded column.
• You would have to assume to the presence of the column in the code. Otherwise, you may have to query information_schema.columns to make sure the column representing the setting needed exists in the table or not. Querying the table information_schema.columns is extremely bad in the presence of dozens (or even hundreds) of InnoDB tables in a heavy write environment. That could potentially lock information_schema.columns intermittently until it is free to read.
• You should make sure the table count is always one(1).

Under Option 2, things are a little different

• If you want to introduce a new setting, you would have to do an INSERT to add the needed setting as a row.
• If you wanted to remove an old setting, you would have to do a DELETE to remove the needed setting as a row.
• You have to query the table by variable name to see if the option is present.
• No virtual limit to adding to or deleting a setting from a table

What do these Options have in common ?

• If you leave old settings alone, this can allow for old versions of app code to use the old settings, but that just introduces clutter into the table.
• Storage Engine should be InnoDB to allow multiple people to read from it.

Personally, I would choose Option 2 because creating new settings, optionally removing old settings, and checking for existing settings would all occur at one table. The method of retrieving settings would all be the same. Under Option 1, you would have to query information_schema.columns plus make certain assumptions about the settings table. The sequence of events for retrieving settings may change from code version to code version.

In my opinion, a procedure should do one thing and one thing only. The logic should be performed in the application code. Secondly, a procedure should not return a result set. It should do work and then possibly return a status e.g. success, failure or something similar. Also, the name of your procedure is meaningless. Choose a descriptive name.

So, break it up into three procedures (insert, update and delete). Move the select statement into a view rather than a procedure. Let your programmers add the logic in their code to determine which one to call. Make sure you put index on the table.

Never do a select *. Only select the columns you need. It may be that you add columns later on but you will still SELECT *. Your SELECT * will then store all (incl unnecessary columns) columns in memory and, in worst case, run out of memory and be swapping to disk.