单行查询没有值时返回默认值的几种处理方式在业务开发中经常会遇到一种查询场景系统需要读取某个配置项、状态值、开关值或参数值如果数据库中存在对应记录则返回数据库中的值如果不存在则返回一个默认值。例如SELECTdata_valueASdataFROMbsyc_config_dataWHEREidenablePay;这个 SQL 的问题是如果bsyc_config_data表中不存在id enablePay的记录那么查询结果是空结果集而不是返回NULL。很多开发人员容易误以为可以直接使用SELECTCOALESCE(data_value,1)ASdataFROMbsyc_config_dataWHEREidenablePay;但这只能处理字段值为 NULL的情况不能处理整行数据不存在的情况。一、问题本质NULL 和无记录不是一回事在 SQL 中下面两种情况完全不同1. 有记录但字段值为 NULLid data_value --------- ---------- enablePay NULL这种情况下下面的 SQL 可以生效SELECTCOALESCE(data_value,1)ASdataFROMbsyc_config_dataWHEREidenablePay;结果data ---- 12. 根本没有记录没有 id enablePay 的数据此时 SQLSELECTCOALESCE(data_value,1)ASdataFROMbsyc_config_dataWHEREidenablePay;不会返回任何行。也就是说COALESCE只能处理列值为NULL不能凭空生成一行数据。二、方式一使用标量子查询 COALESCE这是最简洁的一种方式适合查询单个配置项。SELECTCOALESCE((SELECTdata_valueFROMbsyc_config_dataWHEREidenablePay),1)ASdata;含义是先通过子查询读取enablePay的配置值。如果子查询有值则返回data_value。如果子查询没有记录标量子查询结果会被视为NULL。COALESCE将NULL替换成默认值1。返回结果始终是一行data ---- 1适用场景这种方式适合查询单个系统配置查询单个开关值查询单个参数值业务上确定只会返回一条记录例如SELECTCOALESCE((SELECTdata_valueFROMbsyc_config_dataWHEREidenablePay),1)ASenablePay;注意事项如果id不是唯一的子查询可能返回多行部分数据库会报错。例如 SQL Server 会报Subquery returned more than 1 value因此如果不能保证唯一性需要限制只取一条。MySQLSELECTCOALESCE((SELECTdata_valueFROMbsyc_config_dataWHEREidenablePayLIMIT1),1)ASdata;SQL ServerSELECTCOALESCE((SELECTTOP1data_valueFROMbsyc_config_dataWHEREidenablePay),1)ASdata;三、方式二使用 UNION ALL NOT EXISTS如果希望逻辑表达得更清楚也可以使用UNION ALL。SELECTdata_valueASdataFROMbsyc_config_dataWHEREidenablePayUNIONALLSELECT1ASdataWHERENOTEXISTS(SELECT1FROMbsyc_config_dataWHEREidenablePay);这个 SQL 的逻辑非常直观第一段SELECTdata_valueASdataFROMbsyc_config_dataWHEREidenablePay表示如果配置存在则返回配置值。第二段SELECT1ASdataWHERENOTEXISTS(SELECT1FROMbsyc_config_dataWHEREidenablePay)表示如果配置不存在则返回默认值1。返回效果如果表中存在配置id data_value --------- ---------- enablePay 0返回data ---- 0如果表中不存在配置没有 enablePay 配置返回data ---- 1四、UNION ALL 方式的优点UNION ALL NOT EXISTS的优点是语义非常明确。它把逻辑分成了两段有值时返回实际值没值时返回默认值这种写法特别适合动态 SQL、模板 SQL、报表 SQL、配置查询 SQL因为阅读成本低后期维护也比较直观。相比COALESCE 子查询它的业务意图更明显。五、UNION 和 UNION ALL 的区别这里建议使用UNION ALL不要使用UNION。UNIONUNION会自动去重。SELECT1UNIONSELECT1;结果只返回一行。UNION ALLUNION ALL不会去重性能更好。SELECT1UNIONALLSELECT1;会保留所有结果。在当前场景中第二段 SQL 只有在第一段没有数据时才会返回默认值因此不会出现重复结果。所以使用UNION ALL更合适。六、如果 data_value 可能为 NULL 怎么办前面的UNION ALL写法只能处理没有记录的情况。如果记录存在但是data_value是NULL那么返回的仍然是NULL。例如id data_value --------- ---------- enablePay NULL下面 SQL 会返回NULLSELECTdata_valueASdataFROMbsyc_config_dataWHEREidenablePayUNIONALLSELECT1ASdataWHERENOTEXISTS(SELECT1FROMbsyc_config_dataWHEREidenablePay);如果希望同时处理没有记录返回默认值有记录但字段为 NULL也返回默认值可以这样写SELECTCOALESCE(data_value,1)ASdataFROMbsyc_config_dataWHEREidenablePayUNIONALLSELECT1ASdataWHERENOTEXISTS(SELECT1FROMbsyc_config_dataWHEREidenablePay);这样就更加完整。七、推荐写法如果是系统配置项查询并且希望 SQL 逻辑清晰推荐使用SELECTCOALESCE(data_value,1)ASdataFROMbsyc_config_dataWHEREidenablePayUNIONALLSELECT1ASdataWHERENOTEXISTS(SELECT1FROMbsyc_config_dataWHEREidenablePay);这个写法同时解决了两个问题数据不存在时返回默认值1数据存在但data_value为NULL时也返回默认值1八、如果只允许返回一行如果bsyc_config_data中可能存在多条enablePay配置需要额外控制只返回一行。MySQL 写法SELECTdataFROM(SELECTCOALESCE(data_value,1)ASdataFROMbsyc_config_dataWHEREidenablePayLIMIT1)tUNIONALLSELECT1ASdataWHERENOTEXISTS(SELECT1FROMbsyc_config_dataWHEREidenablePay);SQL Server 写法SELECTdataFROM(SELECTTOP1COALESCE(data_value,1)ASdataFROMbsyc_config_dataWHEREidenablePay)tUNIONALLSELECT1ASdataWHERENOTEXISTS(SELECT1FROMbsyc_config_dataWHEREidenablePay);不过从设计角度看配置表中的配置项id应该保持唯一。更推荐在表结构上增加唯一约束避免后续出现脏数据。例如ALTERTABLEbsyc_config_dataADDCONSTRAINTuk_bsyc_config_data_idUNIQUE(id);九、实际业务建议对于配置项表建议遵守以下设计原则1. 配置项必须唯一例如enablePay enableSms enableMemberCard defaultStoreId这类配置项不应该出现多条。2. 默认值可以写在 SQL 层也可以写在业务层如果默认值属于系统级规则可以写在 SQL 中SELECTCOALESCE((SELECTdata_valueFROMbsyc_config_dataWHEREidenablePay),1)ASdata;如果默认值会随业务场景变化建议在 Java 或服务层处理。3. 配置表适合做缓存配置项通常变化频率低读取频率高。可以在服务启动时加载到缓存也可以结合 Redis、本地缓存或配置刷新机制来减少数据库访问。十、总结单行查询没有值时返回默认值是一个非常常见的 SQL 场景。需要注意的是COALESCE(data_value,1)只能处理字段为NULL的情况不能处理没有记录的情况。如果要在没有记录时也返回默认值可以使用两种方式方式一标量子查询 COALESCESELECTCOALESCE((SELECTdata_valueFROMbsyc_config_dataWHEREidenablePay),1)ASdata;优点是简洁适合单值查询。方式二UNION ALL NOT EXISTSSELECTCOALESCE(data_value,1)ASdataFROMbsyc_config_dataWHEREidenablePayUNIONALLSELECT1ASdataWHERENOTEXISTS(SELECT1FROMbsyc_config_dataWHEREidenablePay);优点是逻辑清晰适合动态 SQL、报表 SQL 和模板 SQL。在实际项目中如果是配置项查询我更推荐使用UNION ALL NOT EXISTS因为它表达的业务含义更直接有配置用配置没有配置用默认值。