mysql - 匹配 MySQL 列中的空字符串，而不是空值

Question

我无法获得正确的语法 - 我正在尝试将列匹配为空字符串，而不是 null。我已经尝试使用单双引号以多种方式分隔字符串。

containerRefNo = "\"\"";

ps = getConnection().prepareStatement(
                "delete from  inumber_join where container_no = ?");

我收到的错误是

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from inumber_join where container_no = '""''

Answer 1

使用参数化查询时，您不需要在参数值中包含任何 分隔符。只需使用 ps.setString(1, ""); 对我来说效果很好。

也就是说，...

// setup
try (Statement st = conn.createStatement()) {
    st.executeUpdate(
            "CREATE TEMPORARY TABLE inumber_join_temp (" +
                "id INT AUTO_INCREMENT, " +
                "container_no VARCHAR(10) NULL, " +
                "PRIMARY KEY (id)" +
            ")");
    st.executeUpdate(
            "INSERT INTO inumber_join_temp (container_no) " +
                "VALUES (null), (''), (null), (''), (null)");
}

// test
String sql = 
        "SELECT COUNT(*) AS n " +
        "FROM inumber_join_temp " +
        "WHERE container_no = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, "");  // search for empty string
    try (ResultSet rs = ps.executeQuery()) {
        rs.next();
        System.out.println(rs.getInt(1));
    }
}

...返回

2