如何在 MySQL 查詢中將 LIKE 通配符與預先準備語句一起使用？

Using LIKE Wildcard with Prepared Statements in MySQL Queries

In MySQL database queries, the LIKE operator allows for pattern-based searches. When using prepared statements to execute such queries, it is essential to correctly incorporate the LIKE wildcard.

One common requirement is to perform a prefix-match search. To achieve this, the keyword% should be appended to the search term. However, when using prepared statements, it is incorrect to concatenate the wildcard directly to the parameter value.

Instead, set the value itself within the prepared statement query. For a prefix-match, modify the search term as follows:

notes = notes
    .replace("!", "!!")
    .replace("%", "!%")
    .replace("_", "!_")
    .replace("[", "![");

PreparedStatement pstmt = con.prepareStatement(
        "SELECT * FROM analysis WHERE notes LIKE ? ESCAPE '!'");
pstmt.setString(1, notes + "%");

The ESCAPE keyword indicates the escape character used to escape special characters within the wildcard. By default, it is a backslash (), but it can be changed to any character.

Similarly, for a suffix-match, use the following:

pstmt.setString(1, "%" + notes);

And for a global match:

pstmt.setString(1, "%" + notes + "%");

By setting the wildcard in the search term, the LIKE operator can be used effectively with prepared statements to perform various pattern-based searches in MySQL databases.

以上是如何在 MySQL 查詢中將 LIKE 通配符與預先準備語句一起使用？的詳細內容。更多資訊請關注PHP中文網其他相關文章！