MySQL custom global defined variable

Question

In my database design, I tend to store some variable that is meant to be acting as a ROLE or TYPE as SMALLINT.

For example:

CREATE TABLE `house` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `type` smallint(11) NOT NULL,

And in PHP, I do:

define('HOUSE_SMALL_TYPE', '0');
define('HOUSE_MEDIUM_TYPE', '1');

So in PHP, in SELECT queries I do:

$this->db->query("SELECT * FROM house  
                  WHERE type = ?;", HOUSE_SMALL_TYPE);

My questions are:

1. In the PHP part, is there is a better way to do this?
2. In the MySQL itself, does MySQL also has global define functionality (like the define in PHP)?

I also want to do kind of

SELECT * FROM house  WHERE type = HOUSE_SMALL_TYPE;

in MySQL query.

My purpose is that when I do SELECT in MySQL, no way I'm going to keep mapping the value 0,1,2 with its real meaning. Just convenience for viewing the tables values, without changing the structure table and fields.

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.

Answer 2

IMHO, MySQL has a huge gap in this area, apparently in the latter versions. One alternative might have been to resort to setting OS environment variables, but how such values can be retrieved from within MySQL, I've been unable to see.

There's a whole page here: https://dev.mysql.com/doc/refman/5.0/en/setting-environment-variables.html teaching us how to "set" OS environment variables in the shell, but not a word on actually calling such variables in MySQL.

As another workaround, using a FUNCTION might be considered more lightweight than a STORED PROCEDURE, like so:

CREATE DEFINER=`root`@`localhost` FUNCTION `DEFAULT_COUNTRY_CODE`() RETURNS CHAR(4)
DETERMINISTIC
RETURN '+234';

Elsewhere in your query, you can then do:

SELECT CONCAT(DEFAULT_COUNTRY_CODE(), "-", telephone) FROM contacts WHERE CountryCode = "NGA"