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:

In the PHP part, is there is a better way to do this?
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.

Define a criteria for "better way". What exactly you want to improve? — Your Common Sense
– Your Common Sense, Commented Mar 13, 2013 at 9:38
@Rikesh Means that I'll have to change my db type using ENUM right ? My tables some are created, but I'll take this into consideration for next creation table. — Hendry H.
– Hendry H., Commented Mar 13, 2013 at 9:46
@HendryH. You can read the post I have linked too. Your question is vague so if you looking something like than use it. — Rikesh
– Rikesh, Commented Mar 13, 2013 at 9:49
@Your Common Sense Actually, I'm just curious how you guys do it in php. I consider any alternative, since I'm still in early development stage. — Hendry H.
– Hendry H., Commented Mar 13, 2013 at 9:49

shock_one · Accepted Answer · 2015-05-12 11:57:08Z

4

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.

edited May 12, 2015 at 11:57

answered May 12, 2015 at 11:43

shock_one

5,9333 gold badges31 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ifedi Okonkwo · Accepted Answer · 2015-05-21 12:48:48Z

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"

answered May 21, 2015 at 12:48

Ifedi Okonkwo

3,7085 gold badges37 silver badges45 bronze badges

1 Comment

lepe Over a year ago

It seems that there is no way MySQL can access to environment variables. As they state Environment variables can be set at the command prompt to affect the current invocation of your command processor... and that's it.

GuruBob · Accepted Answer · 2019-01-28 22:31:00Z

Your approach is fine, if you want to see the values in MySQL instead of 1, 2, 3 etc. then consider this:

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

Then in MySQL you can use:

SELECT * FROM house  WHERE type = 'HOUSE_SMALL_TYPE';

You just need to remember that you cannot just jam any value you like into house.type without having support for it in PHP.

Even better consider this:

class HouseType {
  const SMALL = 'SMALL';
  const MEDIUM = 'MEDIUM';
}

or

class House {
  const TYPE_SMALL = 'SMALL';
  const TYPE_MEDIUM = 'MEDIUM';
}

because then you can use HouseType::SMALL or House::TYPE_SMALL in your PHP code rather than using a global define. By doing this you may benefit from code completion in some IDE's.

Josef Marianek · Accepted Answer · 2021-11-16 15:33:36Z

0

Since MySQL 5.5 it's not possible to set a global user-defined variable, another workaround could be helping table like

create table glob_var(key varchar(10) unique not null, val varchar(10) not null);

answered Nov 16, 2021 at 15:33

Josef Marianek

254 bronze badges

Comments

Jocelyn · Accepted Answer · 2013-03-13 11:39:10Z

-2

I suggest using MySQL variables:

SET HOUSE_SMALL_TYPE = 0;
SET HOUSE_MEDIUM_TYPE = 1;

Then, in your queries you may use these variables:

SELECT * FROM house  WHERE type = @HOUSE_SMALL_TYPE;

This method defines session variables:

If you change a session system variable, the value remains in effect until your session ends or until you change the variable to a different value. The change is not visible to other clients.

If you want to define global MySQL variables (available to all sessions):

SET GLOBAL HOUSE_SMALL_TYPE = 0;
SET GLOBAL HOUSE_MEDIUM_TYPE = 1;

To indicate explicitly that a variable is a global variable, precede its name by GLOBAL or @@global.. The SUPER privilege is required to set global variables.

Documentation:
SET statement
Using system variables
User-defined variables

answered Mar 13, 2013 at 11:39

Jocelyn

11.4k11 gold badges47 silver badges63 bronze badges

5 Comments

Hendry H. Over a year ago

AHa..This is exactly what I wanted. In front of HOUSE_SMALL_TYPE I added @ to make it work.

Hari Over a year ago

MySQL doesn't allow global user variables, only global user variables. From MySQL SET statement reference: "[GLOBAL | SESSION] system_var_name = expr". In your answer above, SET GLOBAL HOUSE_SMALL_TYPE = 0; fails for me on MySQL 5.5 with an "UNKNOWN SYSTEM VARIABLE 'HOUSE_SMALL_TYPE'" message.

lepe Over a year ago

"Hari's" comment should be read as: MySQL doesn't allow global user variables, only user variables

EzPizza Over a year ago

Or it should be read as MySQL doesn't allow global user variables, only global system variables

site80443 Over a year ago

This answer has so much detail but gets a critical thing wrong. As @lepe and EzPizza point out, Mysql doesn't allow global user variables. It's unavoidable to use a table to store global variables as columns or rows in it.

Collectives™ on Stack Overflow

MySQL custom global defined variable

5 Answers 5

Comments

1 Comment

Comments

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related