doubian19900911 2013-03-13 01:30
浏览 134
已采纳

mysql自定义全局定义变量

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

  • 写回答

4条回答 默认 最新

  • douweilei2307 2013-03-13 03:39
    关注

    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

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(3条)
编辑
预览

报告相同问题?

手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部