RLIKE / REGEXP和LIKE的等价性 - 匹配整个字符串与任意子字符串

I have two queries.

我有两个问题。

SELECT count(AlbumID)
  FROM album
  WHERE albumname like '%[%]';

Result: 15733

SELECT count(AlbumName)
  FROM album
  WHERE AlbumName RLIKE '.*\\[.*\\]';

Result: 15740

So as you can see like returns 7 elements less than rlike. I have two questions why is that? Are the statements not the same? And if I am looking for names that contain lets say Result: Artist - Song [Live] or Result: Artist- Song [Gold CD, Excplicit Lyrics] what would query would return the correct result?

所以你可以看到返回7个元素比rlike少。我有两个问题,为什么呢?这些陈述不一样吗?如果我正在寻找包含的名称,请说结果:艺术家 - 歌曲[现场]或结果:艺术家 - 歌曲[Gold CD,Excplicit Lyrics]什么会查询返回正确的结果?

1 个解决方案

#1

The exact equivalency of

完全相同的

LIKE '%[%]'

REGEXP '^.*\\[.*\\]$'

(note that RLIKE is just a mSQL'ish synonym of REGEXP). In short that means that LIKE always matches the whole string, whilst REGEXP and RLIKE may match any substring.

(请注意,RLIKE只是一个mSQL的REGEXP同义词)。简而言之,这意味着LIKE始终匹配整个字符串,而REGEXP和RLIKE可以匹配任何子字符串。

That is why I would assume that there are apparently seven AlbumNames around like

这就是为什么我会假设周围有七个AlbumNames

MyName[abc]plus

MyName [abc]!

You may try to determine these seven records by

您可以尝试通过以下方式确定这7条记录

SELECT AlbumName FROM album 
WHERE AlbumName RLIKE '.*\\[.*\\]' AND AlbumName NOT RLIKE '^.*\\[.*\\]$';

#1