constants and mysql, best practices

Question

I currently have a list of defined constants and a function that regex'es every pulled MySQL string and looks for things like CLIENT_NAME, LOCAL_API_ADDRESS and auto-changes it.

// several fields
define ('CLIENT_NAME', '...');
define ('LOCAL_API_ADDRESS', '...');
...

The thing is, as my app is getting larger I feel this is pretty inefficient and bound to loose strings (forgetting to regex here or there).

How do you guys cope with this?

Answer 1

MySQL has had User-defined variables since v3.23.6, but they...

...are connection-specific. That is, a user variable defined by one client cannot be seen or used by other clients. All variables for a given client connection are automatically freed when that client exits.

Effectively, there's no convention in MySQL for storing constants.

You could store the values in a table, but things like MySQL's REGEXP might require dynamic SQL (using MySQL's Prepared Statements) in order to take advantage of the setup.

Answer 2

Honestly, unless you are doing this an abnormally large number of times, it's not that bad to do this. Many templating engines uses regex search and replaces. Most of the overhead is in loading the regex engine the first time. Multiple queries aren't that expensive.

You are better off ensuring that your REGEX's are optimized.

Answer 3

Be careful handling data like this. If you pull data from the database that overrides certain constants, you might end up with security issues !