postgresql 为什么我的 LIKE '%\_' 查询返回所有行，而不仅仅是那些以下划线结尾的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/483011/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why does my query with LIKE '%\_' return all rows and not just those ending in an underscore?
提问by Hates_
I currently have the query running on Postgres:
我目前在 Postgres 上运行查询：
SELECT * FROM addenda.users WHERE users.username LIKE '%\_'
But rather then returning just entries ending in an underscore, I get all results back, regardless of whether it contains an underscore or not.
但不是只返回以下划线结尾的条目，我会返回所有结果，无论它是否包含下划线。
Running the query below returns a username which is just an underscore, so the escaping does work:
运行下面的查询返回一个用户名，它只是一个下划线，所以转义确实有效：
SELECT * FROM addenda.users WHERE users.username LIKE '\_'
And running the query below returns a username which ends with a specific letter (s):
运行下面的查询会返回一个以特定字母结尾的用户名：
SELECT * FROM addenda.users WHERE users.username LIKE '%s'
What am I doing wrong?
我究竟做错了什么？
回答by Stephen Denne
Is your backslash not getting through to PostgreSQL? If you're passing the string through another layer that treats the backslash as an escape character (e.g. a Java String), then that layer may be removing the backslash, and you might need to escape your backslash for that layer.
你的反斜杠没有通过 PostgreSQL 吗？如果您将字符串传递到另一个将反斜杠视为转义字符（例如 Java 字符串）的层，那么该层可能会删除反斜杠，并且您可能需要为该层转义您的反斜杠。
Do you have any more single character usernames? If the backslash wasn't getting through to PostgreSQL then they would also match '_'
你有更多的单字符用户名吗？如果反斜杠没有通过 PostgreSQL 那么它们也会匹配'_'
You might be able to try the ESCAPE clause: username LIKE '%!_' ESCAPE '!'
您也许可以尝试ESCAPE 子句：username LIKE '%!_' ESCAPE '!'
回答by Cade Roux
"_"is the single-character wildcard in most SQL variants. You HAVE to escape it if you want to match an actual "_"character.
"_"是大多数 SQL 变体中的单字符通配符。如果要匹配实际"_"字符，则必须对其进行转义。
回答by sigma
You have to replace the backslash on your query with a double one, like this:
SELECT * FROM addenda.users WHERE users.username LIKE '%\\_'
Still you may get a nonstandard use of \\ in a string literalwarning, but the query will be executed.
Tested in Postgres 8.4.
您必须将查询中的反斜杠替换为双反斜杠，如下所示：
SELECT * FROM addenda.users WHERE users.username LIKE '%\\_'
您仍然可能会在字符串文字警告中非标准地使用 \\，但查询将被执行。
在 Postgres 8.4 中测试。
回答by Matthew Wright
It's been a while since I've used postgres, but I think you can do '%[_]' to get what you want. That certainly works on SQL server, though I don't have a postgres database setup right now to try it on
自从我使用 postgres 已经有一段时间了，但我认为你可以做 '%[_]' 来得到你想要的。这当然适用于 SQL 服务器，尽管我现在没有 postgres 数据库设置来尝试它
