MySQL喜欢查询但不喜欢其他

    $SQL = "SELECT * FROM `user_posts` WHERE (`post` LIKE '%@".$user."%')";

For instance, if my username is @Jake, it will show any post that has @Jake in it. But it will also do for instance, @Jake11, it will also show. How can I fix this?

例如，如果我的用户名是@Jake，它将显示任何包含@Jake的帖子。但它也会做，例如，@ Jake11，它也将显示。我怎样才能解决这个问题？

7 个解决方案

#1

You might consider using some sort of regular expression instead of LIKE '%...%'.

您可以考虑使用某种正则表达式而不是LIKE'％...％'。

An example might be:

一个例子可能是：

... WHERE `post` REGEXP '@" . mysql_real_escape_string( $user ) . "[[:>:]]'"

The [[:>:]] matches on a right word boundary. As pointed out by Bill Karwin, there's no need for a left-hand boundary pattern in this case as there is an implied word boundary at the @ character. (Indeed, you can't have a left-boundary to the left of a non-word character.)

[[：>：]]匹配右边界。正如Bill Karwin所指出的那样，在这种情况下不需要左手边界模式，因为在@字符处有一个隐含的单词边界。（实际上，你不能在非单词字符的左边有一个左边界。）

(I'm sure others will comment on your possible exposure to SQL injection attack too.)

（我相信其他人也会评论你可能接触到SQL注入攻击。）

#2

$SQL = "SELECT * FROM `user_posts` WHERE (`post` LIKE '%@".$user."')";

#3

http://devzone.zend.com/article/1304

Use a FULLTEXT column :)

使用FULLTEXT列:)

ALTER TABLE user_posts ADD FULLTEXT(post);

$sql = ' SELECT * FROM user_posts WHERE MATCH (post) AGAINST ("' . $user . '") ';

#4

The '%' is a wildcard, it will match any amount of characters in that spot. Remove those from your query and you'll be set. What that query is saying is, "find a row that has post matching [anything]@Jake[anything]"

'％'是一个通配符，它将匹配该点中的任意数量的字符。从您的查询中删除那些，你将被设置。该查询的含义是，“找到一个匹配[thing]的行[任何] @Jake [nothing]”

It doesn't sound like you want to use like, just do:

它听起来不像你想要使用，只是做：

$SQL = "SELECT * FROM `user_posts` WHERE (`post` = '@".$user."')";

#5

It looks like you don't have a need for LIKE here, a simple equality check will work:

看起来你在这里不需要LIKE，一个简单的相等检查将起作用：

$SQL = "SELECT * FROM `user_posts` WHERE (`post` = '@".$user."')";

Be warned, you have a sql injection possibility here and should be using mysql_real_escape_string:

请注意，这里有一个sql注入的可能性，应该使用mysql_real_escape_string：

$SQL = sprintf("SELECT * FROM `user_posts` WHERE (`post` = %s)", mysql_real_escape_string('@' . $user));

If you want to find @Joe within post you can add spaces around your like item, but this will perform slowly:

如果你想在帖子中找到@Joe，你可以在你喜欢的项目周围添加空格，但这会表现得很慢：

$SQL = sprintf("SELECT * FROM `user_posts` WHERE (`post` LIKE %s)", mysql_real_escape_string('% @' . $user . ' %'));

For performance use a FULLTEXT index:

为了性能，请使用FULLTEXT索引：

$SQL = sprintf("SELECT * FROM `user_posts` WHERE MATCH (post) AGAINST (%s)", mysql_real_escape_string('@' . $user));

#6

This kind of query will not be using an index and will require a full table scan. This will obviously be extremely slow once your database grows to a reasonable size. Ideally, when the post is created, you can parse the text and appropriately insert rows in a one-to-many table (properly indexed) to identity the relationship.

这种查询不会使用索引，需要全表扫描。一旦数据库增长到合理的大小，这显然会非常慢。理想情况下，创建帖子时，您可以解析文本并在一对多表中正确插入行（正确编入索引）以标识关系。

#7

Use a regex:

使用正则表达式：

$SQL = "SELECT *
        FROM `user_posts1
        WHERE (`post` LIKE '%@".$user."%)
        AND (`post` NOT RLIKE '%@".$user."[a-zA-Z0-9]%')"

In the case of your example, this will include @jake as long as the character immediately following "jake" ($user) is not between a and z, A and Z or 0-a.

在你的例子中，只要紧跟在“jake”（$ user）之后的字符不在a和z，A和Z或0-a之间，这将包括@jake。

#1