只需要选择MySQL中包含反斜杠的数据 [英] Need to select only data that contains backslashes in MySQL

问题描述

我正在尝试更正两次转义的数据库中的条目.我相信当使用mysql_real_escape_string时magic_quotes处于打开状态.两次转义导致某些搜索结果不正确/丢失.

I'm attempting to correct entries in a database that have been double-escaped. I believe magic_quotes was on while mysql_real_escape_string was being used. The double-escaping has caused some search results to be incorrect/missing.

我已关闭magic_quotes，并计划搜索带有反斜杠的条目，并对其进行更新以删除任何两次转义符.麻烦的是，当我执行查询来搜索带有反斜杠的条目时，总是找不到结果.

I've turned magic_quotes off and am planning to search for entries with a backslash and update them to remove any double-escaping. Trouble is, when I perform a query to search for entries with backslashes, I always get no results.

SELECT title FROM exampletable WHERE title LIKE '%\\\\%'

我在这里按照建议使用'％\\\\％':

I'm using '%\\\\%' as recommended here: http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like

如果我输出每个标题，它们中的许多都会有多余的反斜杠，因此我知道它们在那里.我似乎无法将它们隔离在查询中.

If I output every title, many of them have unwanted backslashes, so I know they're there. I just can't seem to isolate them in a query.

Example Data:
Old King\'s Road
Running Down A Dream
Can\'t Stop The Sun
This One's For Me

同样，只是尝试返回其中带有\的条目.

Again, just trying to return entries with a \ in them.

MySQL版本是5.0.92-社区.排序规则为latin1_swedish_ci.字符集是UTF-8 Unicode

MySQL version is 5.0.92-community. Collation is latin1_swedish_ci. Charset is UTF-8 Unicode

％\\％不起作用.我试过了.在mysql.com上它被列为不正确:

%\\% does NOT work. I've tried. It is listed as incorrect on mysql.com:

要搜索"\"，请将其指定为"\\\\"；这是因为解析器会一次删除反斜杠，而在进行模式匹配时会再次反斜杠，因此要匹配一个反斜杠.

To search for "\", specify it as "\\\\"; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.

推荐答案

当我使用php运行命令时，此命令对我有用:

This command works for me when I'm running the command with php:

select * from exampletable where title like '%\\\\\\\\%' or title like '\\\\\\\\%' or title like '%\\\\';

之所以要包含开始，任何位置和结束匹配，是为了表明它们是不同的.如果最后要查找反斜杠，则只需4个反斜杠.这是因为它之后没有任何可逃避的东西.您在其他命令上使用8个反斜杠，因为它们由php解析.

The reason I include the beginning, anywhere, and end matches is to show that they are different. If you're looking for backslashes on the end, there only needs to be 4 backslashes. this is because there is nothing to escape after it. You use 8 backslashes on the other commands because they get parsed by php.

这篇关于只需要选择MySQL中包含反斜杠的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！