对于首选项模块,我有“系统默认值”和“用户首选项”.
如果没有存储个人/用户首选项,请改用系统默认值.
这是我的系统偏好设置表:
mysql> desc rbl;
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| id | varchar(3) | NO | PRI | | |
| rbl_url | varchar(100) | NO | | | |
| description | varchar(100) | NO | | | |
| is_default | tinyint(1) unsigned | YES | | 1 | |
+-------------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
来自系统偏好设置的示例数据:
mysql> select * from rbl;
+----+----------------------+------------------------------+------------+
| id | rbl_url | description | is_default |
+----+----------------------+------------------------------+------------+
| 1 | sbl-xbl.spamhaus.org | Spamhaus SBL-XBL | 1 |
| 2 | pbl.spamhaus.org | Spamhaus PBL | 1 |
| 3 | bl.spamcop.net | Spamcop Blacklist | 1 |
| 4 | rbl.example.com | Example RBL - not functional | 0 |
+----+----------------------+------------------------------+------------+
…并查询系统默认值:
mysql> SELECT rbl_url FROM rbl WHERE is_default='1';
+----------------------+
| rbl_url |
+----------------------+
| sbl-xbl.spamhaus.org |
| pbl.spamhaus.org |
| bl.spamcop.net |
+----------------------+
3 rows in set (0.01 sec)
到现在为止还挺好.
好.现在,我需要一个用户首选项表,然后我想到了:
mysql> desc rbl_pref;
+-----------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------------------+------+-----+---------+----------------+
| id | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |
| domain_id | mediumint(8) unsigned | NO | | NULL | |
| rbl_id | tinyint(1) unsigned | NO | | NULL | |
+-----------+-----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
(仅供参考-“用户”由“ domain_id”表示.)
让我们查看保存了个性化首选项的特定用户的首选项:
mysql> select * from rbl_pref where domain_id='2277';
+----+-----------+--------+
| id | domain_id | rbl_id |
+----+-----------+--------+
| 4 | 2277 | 1 |
| 5 | 2277 | 2 |
| 6 | 2277 | 4 |
+----+-----------+--------+
3 rows in set (0.00 sec)
再次,但格式更简单:
mysql> SELECT rbl.rbl_url FROM rbl_pref,rbl
WHERE rbl_pref.rbl_id=rbl.id AND domain_id='2277';
+----------------------+
| rbl_url |
+----------------------+
| sbl-xbl.spamhaus.org |
| pbl.spamhaus.org |
| rbl.example.com |
+----------------------+
3 rows in set (0.00 sec)
.. 到现在为止还挺好.如果用户存储了首选项,则会找到结果.
现在的问题示例是,用户1999没有自定义首选项.
我希望使用系统默认值来代替“空集”结果.
mysql> SELECT rbl.rbl_url FROM rbl_pref,rbl
WHERE rbl_pref.rbl_id=rbl.id AND domain_id='1999';
Empty set (0.00 sec)
我很高兴找到一个非常类似的问题:
mysql if row doesn’t exist, grab default value
但是,经过几天的反复试验和文档审查,我无法将答案翻译到此处.
像上面的问题一样,这必须作为单个MySQL查询来完成.我实际上不是从PHP进行此查询,而是从Exim宏进行查询(这是一种非常挑剔的语言,最好将它作为变量赋值给“一个衬里”,就像我在这里尝试做的那样.)
更新:尝试了以下@Biff McGriff建议的一种UNION查询类型.该表未显示在我的评论回复中,因此这里再次出现:
mysql> SELECT rbl.rbl_url FROM rbl_pref,rbl
WHERE rbl_pref.rbl_id=rbl.id AND domain_id='2277'
UNION SELECT rbl_url FROM rbl WHERE is_default='1';
+----------------------+
| rbl_url |
+----------------------+
| sbl-xbl.spamhaus.org |
| pbl.spamhaus.org |
| rbl.example.com |
| bl.spamcop.net |
+----------------------+
4 rows in set (0.00 sec)
如您在上面看到的,用户2277并未选择加入rbl_id 3(bl.spamcop.net),但是无论如何这还是会出现.
我的UNION查询似乎在做的是合并结果集.因此,user_pref充当全局默认值的“补充”,并且我假设/期望得到的结果集将与查询的一半匹配.
所以我现在的问题是,将其解决为“两个结果集”(UNION两侧的任一子查询)是否更好(或可能如何)?还是我真的需要在rbl_pref上添加一个新字段,例如“ enabled”.后者似乎更正确-我需要在rbl_pref中有一些东西来显式指定选择加入或选择退出(除了隐式的“那个pref不在这里-no rbl_id = 3-在过度覆盖的用户结果SET中”)
更新:全部设置,谢谢@Imre L,以及其他所有人.通过这个例子,我学到了一些东西.
解决方法:
注意:您必须在两个位置输入domain_id.
SELECT rbl.rbl_url FROM rbl
JOIN rbl_pref ON rbl_pref.rbl_id=rbl.id AND domain_id=2277
UNION
SELECT rbl.rbl_url FROM rbl
WHERE rbl.is_default
AND NOT EXISTS (SELECT 1 FROM rbl_pref WHERE domain_id=2277 LIMIT 1)
;
现在,UNION的另一侧将被优化,不可能在任何地方
您也不应将varchar(3)用于rbl.id,而应使用某种整数
并且最好与tinyint太小的与rbl_pref.rbl_id相同的类型
当您比较sql代码domain_id =’2277’中的整数字段时,请勿在常量整数周围使用’或’.
您大都可以摆脱它,但有时它可能会使mysql优化器感到困惑.
另外,为了获得最佳性能和一致性,我建议您添加索引:
ALTER TABLE rbl_pref
ADD UNIQUE INDEX ux_domain_rbl (domain_id, rbl_id);
标签:mysql
来源: https://codeday.me/bug/20191102/1990570.html