sql 查询，如果某字段没有数据，就设置默认数据

Question

使用 sql 查询数据库，如果该字段没有数据，就让该字段的数据设置为某个值？这样的 sql该如何写呢？

Answer 1

mysql> select * from test2;
+----+------+
| id | id2  |
+----+------+
| -1 |   -1 |
|  0 | NULL |
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  5 |    5 |
+----+------+
6 rows in set (0.00 sec)

mysql> select id,ifnull(id2,"空") from test2;
+----+-------------------+
| id | ifnull(id2,"空")  |
+----+-------------------+
| -1 | -1                |
|  0 | 空                |
|  1 | 1                 |
|  2 | 2                 |
|  3 | 3                 |
|  5 | 5                 |
+----+-------------------+
6 rows in set (0.00 sec)

不知是否能帮到你

Answer 2

拆开做，单 SELECT 是做不到的；
加子语句，IF 函数对接 UPDATE。

老戴里暂时只有这两种方法。

Answer 3

1:

SELECT RegName,
       RegEmail,
       RegPhone,
       RegOrg,
       RegCountry,
       DateReg,
       ISNULL(Website,'no website')  AS WebSite 
FROM   RegTakePart 
WHERE  Reject IS NULL

2:

SELECT RegName,
       RegEmail,
       RegPhone,
       RegOrg,
       RegCountry,
       DateReg,
       COALESCE(Website,'no website')  AS WebSite 
FROM   RegTakePart 
WHERE  Reject IS NULL

via : Giannis Paraskevopoulos

Answer 4

ifnull(columnValue,defaultValue)