MySQL会用WHERE name=name这样的琐碎条件来优化查询吗

Question

我在MySQL数据库中使用JDBC。我希望我的web应用程序(使用Java Servlet制作(执行以下操作：

• 使用可选参数更新表项
• 使用可选参数执行行筛选

以下是我为实现这一目标而编写的代码草案：

doUpdate(客户(

public void doUpdate(Customer customer) {
try (Connection con = ConPool.getConnection()) {
PreparedStatement ps = con.prepareStatement(
"UPDATE customer " +
"SET " +
"firstName = IFNULL(?, firstName), " +
"lastName = IFNULL(?, lastName), " +
"balance = IFNULL(?, balance), " +
"bookmarked = IFNULL(?, bookmarked) " +
"WHERE id = ?",
Statement.RETURN_GENERATED_KEYS);
ps.setString(1, customer.getFirstName());
ps.setString(2, customer.getLastName());
if(customer.getBalance() == null)
ps.setNull(3, Types.DOUBLE);
else
ps.setDouble(3, customer.getBalance());
if(customer.getBookmarked()==null)
ps.setNull(4,Types.BOOLEAN);
else
ps.setBoolean(4, customer.getBookmarked());
ps.setInt(5, customer.getId());
if (ps.executeUpdate() != 1) {
throw new RuntimeException("UPDATE error.");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}

doRetrieveByCond(minBalance，maxBalance，firstName，lastName，书签(

public List<Customer> doRetrieveByCond(Double minBalance, Double maxBalance, String firstName, String lastName, Boolean bookmarked) {
try (Connection con = ConPool.getConnection()) {
PreparedStatement ps =
con.prepareStatement("SELECT id, firstName, lastName, balance, bookmarked FROM customer n"+
"WHERE firstName LIKE IFNULL(?, firstName) n" +
"AND lastName LIKE IFNULL(?, lastName) n" +
"AND balance >= IFNULL(?, balance) n" +
"AND balance <= IFNULL(?, balance) n" +
"AND bookmarked = IFNULL(?, bookmarked)"
);
if(firstName == null)
ps.setNull(1, Types.VARCHAR);
else
ps.setString(1, '%' + firstName + '%');
if(lastName == null)
ps.setNull(2, Types.VARCHAR);
else
ps.setString(2, '%' + lastName + '%');
if(minBalance == null)
ps.setNull(3,Types.DOUBLE);
else
ps.setDouble(3, minBalance);
if(maxBalance == null)
ps.setNull(4,Types.DOUBLE);
else
ps.setDouble(4, maxBalance);
if(bookmarked == null)
ps.setNull(5, Types.BOOLEAN);
else
ps.setBoolean(5, bookmarked);

ResultSet rs = ps.executeQuery();
List<Customer> list = new ArrayList<>();
while(rs.next()){
Customer customer = new Customer();
customer.setId(rs.getInt(1));
customer.setFirstName(rs.getString(2));
customer.setLastName(rs.getString(3));
customer.setBalance(rs.getDouble(4));
customer.setBookmarked(rs.getBoolean(5));
list.add(customer);
}
return list;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}

通过这种方式，我可以灵活地访问数据库，而不必为每个参数组合重复代码。我想，使用Java Servlet实现这一点的其他方法并不多。(不，我不会使用Hibernate或Spring。这是为大学里的网络开发课程准备的(

如果您真的想搜索实际为NULL的值，或者更新条目列并将其设置为NULL，该怎么办？我可以用一个虚拟物体来达到这个目的。

DBMS是否优化了这些语句？假设doUpdate中的每个参数都为null。该声明将是

UPDATE customer
SET firstName = firstName, lastName = lastName, balance = balance, bookmarked = bookmarked 
WHERE id = ?

DBMS会忽略无用的操作吗？比如将字段设置为自身？

同样的问题也适用于doUpdate。

Answer 1

在MySQL中，没有操作不是免费的，但它会很便宜。它到达表中的行，然后意识到该语句不会更改任何内容。所以这与争吵无关。

它可能已经锁定了行(事务性地(，因此对其他连接可能有一些小干扰。

一句话：这不值得担心。