英文:
Simplify this MySQL select - I'd really want concat_ws to produce null
问题
这提供了所需的输出,但它非常丑陋。
select if(concat_ws('~', x, y) = '', null, concat_ws('~', x, y));
如果x和y都为null
,那么我希望返回null
,但它返回''
,普通的concat函数会这样做,但我希望在x、y不为null
时保留分隔符。
我尝试使用变量,但似乎它们在同一select语句中重复使用之前没有被设置。
我受限于一个框架,所以我真的不能做太多事情,所有操作都需要在一个单独的select字段中完成,就像上面的示例一样。
英文:
This gives the required output but it is very ugly
select if(concat_ws('~', x, y) = '', null, concat_ws('~', x, y));
if x & y are null
then I want null
returned, but it gives ''
, plain concat does this, but I want the separator (when x, y are not null
)
I tried using variables, but it seems they are not set early enough to reuse in the same select.
I'm tied to a framework so I really can't do too much, it needs to be all done in a single select field, like above.
答案1
得分: 1
你可以将你的 If()
函数重写为 NULLIF()
,它是你正在使用的相同逻辑的缩写版本。
SELECT NULLIF(concat_ws('~', x, y) , '');
我怀疑这不会为你的查询带来任何优化,除了对我们人类来说更容易阅读。话虽如此,我认为这是查询的最简洁和最优化的形式,因为concat_ws
本身就是具备空值安全性的。
英文:
You can rewrite your If()
function to be a NULLIF()
which is a shortened version of the same logic you are using
SELECT NULLIF(concat_ws('~', x, y) , '');
I doubt this will bring any optimization to your query besides being easier for us humans to read though. That being said, I believe this is the most concise and optimized that this query will get since concat_ws
is null-safe by design.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论