使用select语句替换sql中的空值？

Ho to do this? What query can be written by using select statement where all nulls should be replaced with 123?

何要这样做？使用select语句可以写入什么查询，其中所有空值应该替换为123？

I know we can do this y using, update tablename set fieldname = "123" where fieldname is null;

我知道我们可以使用，更新tablename set fieldname =“123”，其中fieldname为null;

but can't do it using select statement.

但不能使用select语句。

4 个解决方案

#1

You have a lot of options for substituting NULL values in MySQL:

在MySQL中有很多替换NULL值的选项：

CASE

案件

select case 
    when fieldname is null then '123' 
    else fieldname end as fieldname 
from tablename

COALESCE

合并

select coalesce(fieldname, '123') as fieldname 
from tablename

IFNULL

IFNULL

select ifnull(fieldname, '123') as fieldname 
from tablename

#2

There is a statement called IFNULL, which takes all the input values and returns the first non NULL value.

有一个名为IFNULL的语句，它接受所有输入值并返回第一个非NULL值。

example:

例：

select IFNULL(column, 1) FROM table;

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull

#3

I think you're looking for the IFNULL function IFNULL(field, 0) will return a 0 when the field returns null

我认为你正在寻找IFNULL函数IFNULL（field，0）将在字段返回null时返回0

#4

An UPDATE statement is needed to update data in a table. You cannot use the SELECT statement to do so.

需要UPDATE语句来更新表中的数据。您不能使用SELECT语句来执行此操作。

#1