如何在MySQL INSERT...SELECT中跳过Committee_member为NULL的记录

需要实现MySQL的INSERT...SELECT操作，且跳过Committee_member为NULL的记录。当前使用的SQL语句如下：

insert into payments2 (`ID`, `StartDate`, `EndDate`, `Committee_member`, `TotalPay`) 
select 1, '" + dateBegin + "','" + dateEnd + "',`Committee_member`,sum(Payment) 
from (
    select * 
    from sitting_registry 
    WHERE Committee_member='" + memberName + "' and Date>='" + dateBegin + "' 
        and Date<='" + dateEnd + "' 
        and committee_member IS NOT NULL 
        order by sitting_registry.Payment desc 
        limit 16
) as subt ;

其中dateBegin、dateEnd、memberName为外部查询传入的变量。

执行后插入的记录中出现了Committee_member为NULL的行（如下表所示），触发了SQL约束异常：

异常信息：

sql exception:
java.sql.SQLIntegrityConstraintViolationException: Column 'Committee_member' cannot be null

请问如何在包含子查询的INSERT...SELECT语句中跳过Committee_member为NULL的记录，避免触发该异常？

出现NULL行的核心原因是：当子查询subt没有返回任何符合条件的数据时，外层聚合函数sum(Payment)会返回0，同时Committee_member会被置为NULL，最终插入一条违反约束的无效记录。以下是两种直接有效的解决方法：

方法一：外层查询添加非空过滤 #

在外层SELECT语句末尾添加WHERE Committee_member IS NOT NULL，直接过滤掉Committee_member为NULL的结果：

insert into payments2 (`ID`, `StartDate`, `EndDate`, `Committee_member`, `TotalPay`) 
select 1, '" + dateBegin + "','" + dateEnd + "',`Committee_member`,sum(Payment) 
from (
    select * 
    from sitting_registry 
    WHERE Committee_member='" + memberName + "' and Date>='" + dateBegin + "' 
        and Date<='" + dateEnd + "' 
        and committee_member IS NOT NULL 
        order by sitting_registry.Payment desc 
        limit 16
) as subt 
WHERE Committee_member IS NOT NULL; -- 新增过滤条件

方法二：用HAVING确保子查询有数据才聚合 #

通过HAVING COUNT(*) > 0判断子查询是否返回有效数据，只有当存在符合条件的记录时才生成插入结果：

insert into payments2 (`ID`, `StartDate`, `EndDate`, `Committee_member`, `TotalPay`) 
select 1, '" + dateBegin + "','" + dateEnd + "',`Committee_member`,sum(Payment) 
from (
    select * 
    from sitting_registry 
    WHERE Committee_member='" + memberName + "' and Date>='" + dateBegin + "' 
        and Date<='" + dateEnd + "' 
        and committee_member IS NOT NULL 
        order by sitting_registry.Payment desc 
        limit 16
) as subt 
HAVING COUNT(*) > 0; -- 新增条件确保子查询有数据

重要提示：修复SQL注入风险 #

当前语句直接拼接外部变量，存在严重的SQL注入漏洞。建议改用参数化查询，以Java为例：

String sql = "insert into payments2 (`ID`, `StartDate`, `EndDate`, `Committee_member`, `TotalPay`) " +
             "select 1, ?, ?, `Committee_member`, sum(Payment) " +
             "from ( " +
             "    select * " +
             "    from sitting_registry " +
             "    WHERE Committee_member=? and Date>=? and Date<=? and committee_member IS NOT NULL " +
             "    order by Payment desc limit 16 " +
             ") as subt " +
             "WHERE Committee_member IS NOT NULL";

PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, dateBegin);
pstmt.setString(2, dateEnd);
pstmt.setString(3, memberName);
pstmt.setString(4, dateBegin);
pstmt.setString(5, dateEnd);
pstmt.executeUpdate();

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