mysql自定义全局定义变量 [英] mysql custom global defined variable

问题描述

在我的数据库设计中，我倾向于将一些变量用作SMALLINT来充当ROLE或TYPE.例如:

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,

在php中，我做

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

因此在php中，在SELECT查询中，我这样做:

So in php, in SELECT queries I do :

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

我的问题是:

1. 在php部分中，有没有更好的方法可以做到这一点?
2. 在mysql本身中，mysql是否还具有全局定义功能(如php中的define)?

我也想做

SELECT * FROM house  WHERE type = HOUSE_SMALL_TYPE

在mysql查询中.
我的目的是，当我在mysql中执行SELECT时，我将无法继续映射其实际含义的值0,1,2.只是希望查看表值，而无需更改结构表和字段.

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 convineance for viewing the tables values, without changing the structure table and fields.

推荐答案

我建议使用MySQL变量:

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;

此方法定义了会话变量:

如果更改会话系统变量，则该值仍然有效 直到会话结束或将变量更改为a 不同的价值.该更改对其他客户端不可见.

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.

如果要定义全局MySQL变量(适用于所有会话):

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

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

要明确表明变量是全局变量，请在前面 其名称为GLOBAL或@@ global.需要SUPER特权才能 设置全局变量.

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.

文档:
SET语句
使用系统变量
用户定义的变量

Documentation:
SET statement
Using system variables
User-defined variables

这篇关于mysql自定义全局定义变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！