向空表插入数据时Coalesce函数失效问题求助

这个问题我之前也碰到过，根源出在空表时MAX()函数的返回值处理上，咱们一步步来拆解和解决：

问题原因 #

你当前的SQL写法是：

(SELECT MAX(COALESCE(TABLE_ONE_UID, 0)) + 1 FROM TABLE_ONE)

当表TABLE_ONE为空时，MAX()函数因为没有任何行可以计算，会直接返回NULL——哪怕你在MAX()里面套了COALESCE也没用，因为COALESCE是对每行的列值做处理，空表根本没有行，所以整个子查询的结果是NULL，而你的TABLE_ONE_UID列应该设置了NOT NULL约束，自然就会抛出cannot insert the value null into column 'TABLE_ONE_UID'的错误。

解决方案 #

方案1：调整Coalesce和MAX的顺序 #

把COALESCE放在MAX()外面，这样就能处理MAX()返回NULL的情况：

INSERT INTO TABLE_ONE (TABLE_ONE_UID, USER_UID, SHT_DATE, C_S_UID, CST_DATE, CET_DATE, S_M, PGS) 
VALUES (
    (SELECT COALESCE(MAX(TABLE_ONE_UID), 0) + 1 FROM TABLE_ONE), 
    127, 
    '2009-06-15T13:45:30', 
    0, 
    '2009-06-15T13:45:30',
    '2010-06-15T13:45:30', 
    'TEST DELETE THIS ROW', 
    0 
)

原理是：当表为空时，MAX(TABLE_ONE_UID)返回NULL，COALESCE会把这个NULL替换成0，加1后得到1，就能正常插入非空值了。

方案2：使用数据库原生自增列（推荐） #

手动模拟自增其实存在并发风险（多用户同时插入时可能生成重复的UID），更稳妥的方式是直接把TABLE_ONE_UID设为数据库原生的自增列：

• 如果是SQL Server，设置列类型为INT IDENTITY(1,1)
• 如果是MySQL，设置列类型为INT AUTO_INCREMENT
• 如果是PostgreSQL，设置列类型为SERIAL或GENERATED AS IDENTITY

设置完成后，插入时不需要指定TABLE_ONE_UID列，数据库会自动帮你生成唯一的自增值：

INSERT INTO TABLE_ONE (USER_UID, SHT_DATE, C_S_UID, CST_DATE, CET_DATE, S_M, PGS) 
VALUES (
    127, 
    '2009-06-15T13:45:30', 
    0, 
    '2009-06-15T13:45:30',
    '2010-06-15T13:45:30', 
    'TEST DELETE THIS ROW', 
    0 
)

这种方式不仅能彻底解决空表插入的问题，还能保证并发场景下UID的唯一性，是工业界的标准做法。

内容的提问来源于stack exchange，提问作者Joe Joe Joe