如何在mysql中按标题排序，忽略单词“the”?

I have a list of movies that I have grouped by letter. Naturally, the movies starting with the letter "T" have about 80% of movies that begin with "The". Movies such as "The Dark Knight" should appear in the "D" list, and preferably in the "T" as well. Any way I can do that?

我有一份按字母排列的电影清单。当然，以字母“T”开头的电影中有80%都是以“the”开头的。像《黑暗骑士》这样的电影应该出现在“D”名单中，最好也出现在“T”名单中。有什么办法吗?

I use the following code in the WHERE clause to display movies that start with a certain letter, ignoring "the", but this also had a convenient side effect of having a movie such as "The Dark Knight" appear for letter "D" and "T".

我在WHERE子句中使用了以下代码来显示以某个字母开头的电影，忽略了“the”，但这也有一个方便的副作用，比如让“黑暗骑士”出现在字母“D”和“T”中。

WHERE movie_title REGEXP CONCAT('^(the )?', '$letter')

I would like to achieve this when I echo out all the movies that are in the database.

我想要实现这一点，当我回显数据库中的所有电影时。

6 个解决方案

#1

If you are going to be performing this query frequently, you will want to create a separate field in the table with the 'sorted' name. Using regular expressions or other operations make it impossible for MySQL to take advantage of the index.

如果您打算频繁执行此查询，您将希望在表中创建一个具有“已排序”名称的单独字段。使用正则表达式或其他操作使MySQL无法利用索引。

So, the simplest and most efficient solution is to make your add a movie_title_short field, which contains movie_title without the "The" or "A". Be sure to add an index to the movie_title_short field too!

因此，最简单、最有效的解决方案是让您添加一个movie_title_short字段，其中包含movie_title，没有“the”或“a”。一定要向movie_title_short字段添加索引!

#2

As Carl said, I'd build this into its own indexable field to avoid having to compute it each time. I'd recommend doing it in a slightly different way to avoid redundancy though.

正如Carl所说，我将把它构建到它自己的可索引字段中，以避免每次都要计算它。我建议用一种稍微不同的方式来避免冗余。

movies (id, name, namePrefix)

eg:

例如:

| Dark Knight        | The |
| Affair To Remember | An  |
| Beautiful Mind     | A   |

This way you can show these movies in two different ways: "name, namePrefix" or "namePrefix name" and can be sorted accordingly.

通过这种方式，您可以以两种不同的方式显示这些电影:“name, namePrefix”或“namePrefix name”，并可以按顺序进行排序。

#3

select right(movie_title, char_length(movie_title)-4) as movie_title
from movies 
where left(movie_title,3) = 'the'
union
select movie_title
from movies

#4

You can use the mysql replace function in the select clause...

您可以在select子句中使用mysql replace函数…

 select replace(movie_title,'The ','') from ... order by replace(movie_title,'The ','')'

#5

Just had that problem myself... solution is:

我自己也有这个问题……解决方案是:

SELECT * FROM movies WHERE title REGEXP '^d' AND title NOT REGEXP '^the ' OR title REGEXP '^the d'

this will give you only results that starts with "The D" or "D"

这只会得到以D或D开头的结果

#6

Use this:

用这个:

SELECT * FROM movies ORDER BY TRIM(LEADING 'the ' FROM LOWER(`movie_title`));

#1