联合多关系 MySQL 数据库中的高级搜索

Question

这可能解释起来有点高级，因为这是我想做的一件相当复杂的事情（至少对我来说）。

我目前正在用 PHP 和 MySQL 构建一个供个人使用的电影数据库，而 MySQL 部分让我很烦恼。当前的设置是这样的：

我有一个主电影数据库，其中包含名称、描述和值以及单个选项（例如年份、年龄限制和媒体类型（DVD、蓝光等））。

我还有额外的语言表、字幕、音频格式等，它们都有两列，一列用于匹配索引（例如语言 ID）。 理想

我的语言表的示例：

movid | langid
--------------
1     | 2
1     | 4
2     | 4
3     | 5     

情况下，我想要这样的内容：

| ID | name    | description | year | subtitles | languages | audio |
--------------------------------------------------------------------
| 1  | One     | Bla bla     | 2010 | 2,3,5,6,7 | 3,6,22,6  | 10,5  |
| 2  | Another | foo bar     | 2008 | 6,33,5,27 | 10,4,2,3  | 8,15  |

随着字幕和语言能够分解为 PHP 数组，我实际上已经使用 GROUP_CONCAT 很好地工作了。直到我需要搜索特定 subid 或 langid 的部分，这是我到目前为止一直在使用的查询，即使我还没有写出所有表信息，我也希望您能明白：

SET SESSION group_concat_max_len = 512;

SELECT
movie.id,
movie.name,
movie.origname,
movie.`year`,
movie.`type`,
movie.duration,
movie.age,
GROUP_CONCAT(DISTINCT movie_language.langid ORDER BY langid) AS lang,
GROUP_CONCAT(DISTINCT movie_subtitles.subid ORDER BY subid) AS subtitles

FROM `movie`

LEFT JOIN `movie_audio` ON `movie`.`id`=`movie_audio`.`movid` 
LEFT JOIN `movie_company` ON `movie`.`id`=`movie_company`.`movid` 
LEFT JOIN `movie_genre` ON `movie`.`id`=`movie_genre`.`movid` 
LEFT JOIN `movie_language` ON `movie`.`id`=`movie_language`.`movid` 
LEFT JOIN `movie_subtitles` ON `movie`.`id`=`movie_subtitles`.`movid` 

GROUP BY movie.id

我使用 group_concat_max_len 来。阻止我得到 BLOB，到目前为止我只尝试了 group_concatting 我的两个连接表（稍后将添加其余的表）。

这完全返回了我想要的结果，但每个连接表只能有一个 WHERE 子句，否则它将返回 0 行。同样，如果我只搜索一个，它只会在 GROUP_CONCAT'ted 字段中返回搜索到的数字/id。

然后我使用 IN() 函数修复了它。至少我认为我做到了。但问题是这只适用于我所说的 OR 搜索。添加：

WHERE movie_subtitles.subid IN ()

对于不在字幕表中的数字，仍将返回该行，只是仅包含匹配的数字。这对于一半的搜索来说很好，但我还需要一种使用类似 AND 的方法进行搜索的方法。

我不知道我是否需要完全重组，或者需要完全不同的查询，但我希望得到一些帮助或提示。

也许我应该说我也看过 HAVING 选项，但据我所知，它对我的​​查询不起作用。

顺便说一句，如果这是不可能做到的，我考虑过废弃连接的表，并将它们替换为主电影表中易于搜索的字段（例如使用此“语法”：'#2##4#'，然后使用 LIKE '%#2#%' AND '%#4#%' 来匹配结果，或者作为最后的手段使用 PHP 来排序（我宁愿死也不愿这样做），尽管我宁愿喜欢它如果上述解决方案可以修复并使用）。

预先非常感谢您帮助我解决头痛问题！

Answer 1

对您的选择进行子查询，然后您将可以更轻松地使用子句。

就像：

select *
   from (`your big query above`) as t
where subtitles regexp `your ids you want`
and lang regexp `your ids you want`

嗯，它并不完美，因为你的 id 将被转换成字符串（在 postgres 中你有数组，所以你可以从顶层进行适当的搜索。）我不认为我真的想用正则表达式搜索 id尽管。

那么最好不要在最终关卡之前连接您的 id。所以你想要 3 级查询：

select 
  stuff, ...
  group_concats
from
(
    select *
       from (`your big query above but without the group_concat`) as inner
    conditions ...

) as outer

编辑

试试这个：

SELECT
id,
name,
origname,
`year`,
`type`,
duration,
age,

-- at this point we have the right rows we are just
-- grouping lang and subtitles
GROUP_CONCAT(DISTINCT langid ORDER BY langid) AS lang,
GROUP_CONCAT(DISTINCT subid ORDER BY subid) AS subtitles

from
(

    (
        SELECT
            movie.id,
            movie.name,
            movie.origname,
            movie.`year`,
            movie.`type`,
            movie.duration,
            movie.age,
            langid,
            subid


            FROM `movie`

            LEFT JOIN `movie_audio` ON `movie`.`id`=`movie_audio`.`movid`
            LEFT JOIN `movie_company` ON `movie`.`id`=`movie_company`.`movid`
            LEFT JOIN `movie_genre` ON `movie`.`id`=`movie_genre`.`movid`
            LEFT JOIN `movie_language` ON `movie`.`id`=`movie_language`.`movid`
            LEFT JOIN `movie_subtitles` ON `movie`.`id`=`movie_subtitles`.`movid`


            -- each row will have a different langid and different subid
            GROUP BY
                movie.id, langid, subid

    ) as inner

    -- you should be able to do any complex condition as this point
    where
        (langid = 1 or langid = 2)
        and (subid = 2 or subid = 3)

) as outer