mysql如果行不存在,则获取默认值 [英] mysql if row doesn't exist, grab default value
问题描述
我正在为系统创建首选项模块,以便用户可以为某些部分(诸如布局,颜色方案,主屏幕等之类的东西)定义自己的首选项.表中的每个首选项都有一个默认值(以防用户尚未定义),一旦用户更改了首选项,系统应使用该用户定义的值.
I'm making a preferences module for a system so users can define their own preferences for certain parts (stuff like lay-out, color-scheme, homescreen etc.). Every preference in the table has got a default value (in case the user hasn't defined one yet) and once the user has changed their preferences the system should use that user-defined value.
我在选择所需的值时遇到了麻烦,我的查询使用的是用户定义的值,但是遗憾的是,当用户尚未定义首选项时,它会返回null
.
I'm having trouble with selecting the desired value, my query works with the user-defined value, but sadly it returns null
when the user hasn't defined a preference yet.
我有2张桌子
table **preferences**:
id
name
default_value
table **preferences_defined**:
id
preference_id
user_id
defined_value
我想创建一个函数,通过提供应用了首选项的人员的user_id和首选项的名称来轻松选择所需的值.目前,我的查询如下:
I want to create a function to easily select the desired value by giving the user_id of the person to whom the preference applies and the name of the preference. At the moment my query looks like this:
SELECT IF(ISNULL(defined_value),default_value,defined_value) AS result
FROM preferences
LEFT JOIN preferences_defined ON preferences_defined.id = preferences.id
WHERE user_id = 17
AND name = "menu_items"
我想WHERE user_id = 17
部分出了问题,因为当没有可用的定义值时,这意味着也没有名为user_id的列.
我需要找到一种方法来完成这项工作.
因此,我需要选择定义的值(如果存在)(考虑到首选项的user_id
和name
),如果不存在,则必须返回默认值.
I guess the WHERE user_id = 17
part is where things go wrong because when there is no defined value available it means there also isn't a column named user_id.
I need to find a way to make this work.
So, I need to select the defined value if it exist (given the user_id
and the name
of the preference), if not, it will have to return the default value.
推荐答案
WHERE (user_id = 17 OR user_id IS NULL) AND name = "menu_items"
只是为了清理其余查询:
And just to clean up the rest of the query:
不需要在首选项中定义id
,而是使用组合键preference_id
和user_id
.
The id
in preferences defined doesn't need to be there, use a combined key of preference_id
and user_id
instead.
如果preferences
的id
被命名为preference_id
,则长ON
语句可以替换为USING(preference_id)
If the id
of preferences
was named preference_id
, the long ON
statement could be replaced with USING(preference_id)
IF
函数可以替换为COALESCE(defined_value, default_value)
这篇关于mysql如果行不存在,则获取默认值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!