mysql - 如何在插入sql时替换NULL值

Question

我的 table 是这样的

+--------+--------+--------------+--------------+
| CPI_id | Weight | score_100_UB | score_100_LB |
+--------+--------+--------------+--------------+
|    1.1 |     10 |          100 |           90 |
+--------+--------+--------------+--------------+

执行插入查询时，表应该看起来像

+--------+--------+--------------+--------------+
| CPI_id | Weight | score_100_UB | score_100_LB |
+--------+--------+--------------+--------------+
|    1.1 |     10 |          100 |           90 |
|    5.5 |     10 |         NULL |           93 |
+--------+--------+--------------+--------------+

但 NULL 值应替换为 100。我也试过使用触发器。我无法获取。

提前致谢

Answer 1

对于 MySQL 使用:

insert into table values (CPI_id , Weight ,IFNULL(score_100_UB ,100), score_100_LB )

或:

insert into table values (CPI_id , Weight ,COALESCE(score_100_UB ,100), score_100_LB )

SQL服务器:

insert into table values (CPI_id , Weight ,ISNULL(score_100_UB ,100), score_100_LB )

甲骨文:

insert into table values (CPI_id , Weight ,NVL(score_100_UB ,100), score_100_LB )