MySQL MySql查询在选择中用空字符串替换NULL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9560723/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySql Query Replace NULL with Empty String in Select
提问by Mosty Mostacho
How do you replace a NULL value in the select with an empty string? It doesnt look very professional to output "NULL" values.
如何用空字符串替换选择中的 NULL 值？输出“NULL”值看起来不太专业。
This is very unusual and based on my syntax I would expect it to work. Hoping for an explanation why it doesnt.
这是非常不寻常的，根据我的语法，我希望它能够工作。希望解释为什么它没有。
select CASE prereq WHEN (prereq IS NULL) THEN " " ELSE prereq end from test;
Example of what the original table looks like, what I want, and what actual prints:
原始表格的示例、我想要的内容以及实际打印的内容：
original wanted what actually prints
-------- ------ ---------------------
value1 value1
NULL NULL
value2 value2
NULL NULL
As you can see it does the opposite of what I want, hence I tried flipping the IS NULL to IS NOT NULL and of course that didnt fix it, also tried swapping the position of when case, which did not work.
正如您所看到的，它与我想要的相反，因此我尝试将 IS NULL 翻转为 IS NOT NULL，当然这没有修复它，还尝试交换 when case 的位置，但没有用。
Edit: It seems the 3 solutions given below all do the task. regards
编辑：似乎下面给出的 3 个解决方案都可以完成任务。问候
select if(prereq IS NULL ," ",prereq ) from test
select IFNULL(prereq,"") from test
select coalesce(prereq, '') from test
采纳答案by Abderrahmane
Some of these built in functions should work:
其中一些内置函数应该可以工作：
Coalesce
Is Null
IfNull
回答by JScoobyCed
If you really must output every values including the NULL ones:
如果您真的必须输出包括 NULL 在内的每个值：
select IFNULL(prereq,"") from test
回答by Mosty Mostacho
SELECT COALESCE(prereq, '') FROM test
Coalesce will return the first non-null argument passed to it from left to right. If all arguemnts are null, it'll return null, but we're forcing an empty string there, so no null values will be returned.
Coalesce 将返回从左到右传递给它的第一个非空参数。如果所有的参数都是空的，它会返回空，但我们在那里强制一个空字符串，所以不会返回空值。
Also note that the COALESCEoperator is supported in standard SQL. This is not the case of IFNULL. So it is a good practice to get use the former. Additionally, bear in mind that COALESCE supports more than 2 parameters and it will iterate over them until a non-null coincidence is found.
另请注意，标准 SQL 支持COALESCE运算符。这不是IFNULL的情况。所以使用前者是一个很好的做法。此外，请记住 COALESCE 支持 2 个以上的参数，它会迭代它们，直到找到非空的巧合。
回答by Mosty Mostacho
Try below ;
试试下面;
select if(prereq IS NULL ," ",prereq ) from test
回答by Abderrahmane
select IFNULL(`prereq`,'') as ColumnName FROM test
this query is selecting "prereq" values and if any one of the values are null it show an empty string as you like So, it shows all values but the NULL ones are showns in blank
此查询正在选择“prereq”值，如果其中任何一个值为空，它会根据您的喜好显示一个空字符串 因此，它显示所有值，但空值显示为空白
回答by M.Nemes
The original form is nearly perfect, you just have to omit prereqafter CASE:
原始形式几乎是完美的，您只需在CASE之后省略prereq：
SELECT
CASE
WHEN prereq IS NULL THEN ' '
ELSE prereq
END AS prereq
FROM test;
回答by SenorAmor
回答by Hunter McMillen
Try this, this should also get rid of those empty lines also:
试试这个，这也应该摆脱那些空行：
SELECT prereq FROM test WHERE prereq IS NOT NULL;
回答by Daniel
I have nulls (NULL as default value) in a non-required field in a database. They do not cause the value "null" to show up in a web page. However, the value "null" is put in place when creating data via a web page input form. This was due to the JavaScript taking null and transcribing it to the string "null" when submitting the data via AJAX and jQuery. Make sure that this is not the base issue as simply doing the above is only a band-aid to the actual issue. I also implemented the above solution IFNULL(...) as a double measure. Thanks.
我在数据库的非必填字段中有空值（NULL 作为默认值）。它们不会导致值“null”出现在网页中。但是，当通过网页输入表单创建数据时，值“null”被放置到位。这是因为 JavaScript 在通过 AJAX 和 jQuery 提交数据时取 null 并将其转录为字符串“null”。确保这不是基本问题，因为简单地执行上述操作只是对实际问题的创可贴。我还实施了上述解决方案 IFNULL(...) 作为双重措施。谢谢。
回答by Abderrahmane
UPDATE your_table set your_field="" where your_field is null
