mysql 问号转义,如何在JDBC预准备语句中转义文字问号（'？'）

I'd like to create a JDBC PreparedStatement like:

SELECT URL,LOCATE ( '?', URL ) pos FROM Links WHERE pageId=? ORDER BY pos ASC

Where the 1st ? is a literal and the 2nd ? is a parameter. I could use CHAR(63) in place of the '?' but I think the extra function call would slow down the SQL execution. Is there some way to escape that 1st ??

Edit:

The following code tests dkatzel's assertion that the ? character in a string is not considered a marker:

public class Test {

public static void main(String[] args) throws SQLException {

Connection conn = DriverManager.getConnection("jdbc:h2:mem:test");

Statement stmt = conn.createStatement();

stmt.executeUpdate("CREATE TABLE Links(URL VARCHAR(255) PRIMARY KEY,pageId BIGINT)");

stmt.executeUpdate("INSERT INTO Links(URL,pageId) VALUES('http://foo.bar?baz',1)");

stmt.executeUpdate("INSERT INTO Links(URL,pageId) VALUES('http://foo.bar/baz',1)");

stmt.close();

PreparedStatement ps = conn

.prepareStatement("SELECT URL,LOCATE ( '?', URL ) pos FROM Links WHERE pageId=? ORDER BY pos ASC");

ps.setLong(1, 1);

ResultSet rs = ps.executeQuery();

while (rs.next()) {

System.out.println(rs.getString(1) + ":" + rs.getInt(2));

}

rs.close();

ps.close();

conn.close();

}

}

The output:

http://foo.bar/baz:0

http://foo.bar?baz:15

It appears that dkatzel is correct. I searched the the JDBC Spec and could not find any mention that the ? parameter marker would be ignored if it's within quotes, but the few implementations of PreparedStatement parsers that I found (MySql,c-JDBC,H2) all appear to exclude text within single quotes from consideration as parameter markers.

解决方案

The meaning of the ? is specified in the SQL specification, and the JDBC specification defers to the SQL specification for this.

A driver doesn't (and shouldn't) interpret a question mark in a literal as a parameter placeholder, as a question mark within a string literal is simply a character within the string literal. For more information look at chapter 5 of SQL:2011 Foundation (ISO-9075-2:2011).

So escaping is not necessary (nor possible).