Wie verwende ich LIKE-Platzhalter mit vorbereiteten Anweisungen in MySQL-Abfragen?

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.

Das obige ist der detaillierte Inhalt vonWie verwende ich LIKE-Platzhalter mit vorbereiteten Anweisungen in MySQL-Abfragen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!