比较MySQL中的列表

Question

假设我有一个电影表和一个类型表。通过多对多的关系联系在一起。

TABLE movie
id
name

_

TABLE movie_genre
movie_fk
genre_fk

_

TABLE genre
id
name

太好了，正常化了。但我也将CSV文件导入到一个表中，该表的格式为：

TABLE csv
name, genres 
'Die Hard', 'action~drama'

现在我想检查是否有更改，csv是否列出了我没有的流派，反之亦然，所以我将更改显示给用户，然后将其同步到

我是这样做的：

SELECT * FROM movie 
JOIN movie_genre ON movie.id = movie_genre.movie_fk 
JOIN genre ON genre.id = movie_genre.genre_fk 
WHERE 
FIND_IN_SET(genre.name, REPLACE(csv.genres, '~', ',')) = 0 

问题是，这只会以一种方式标记更改。例如。如果在我的数据库中，我有与流派相关的《虎胆龙威》：动作、戏剧

CSV包含动作、戏剧、恐怖

因为我数据库中的每个流派都包含在csv数据中，所以它不会被标记为更改。

请注意csv中列出的流派可能不会按任何特定顺序列出。

希望我已经解释得足够透彻了。

我如何才能实现我想要做的事情？可以使用REGEX或自定义MySQL函数完成吗？

Answer 1

FIND_IN_SET用于查找元素是否在集合内。您还打算查找另一个表中缺少的元素。因此，我会将CSV表修改为的形式，而不是未规范化的CSV导入结果

csv(movie_id，genre_id(

并生成将插入它的导入代码。因此，您可以检查以下差异(未测试的代码(：

(
select movie_id, genre_id, "missing" as status
from csv
where not exists (select 1 from movie_genre where movie_genre.movie_fk = csv.movie_id and movie_genre.genre_fk = csv.genre_id)
)
union
(
select movie_fk as movie_id, genre_fk as genre_id, "surplus" as status
from movie_genre
where not exists (select 1 from csv where movie_genre.movie_fk = csv.movie_id and movie_genre.genre_fk = csv.genre_id);
)

您也可以执行更改：

insert into movie_genre(movie_fk, genre_fk)
select movie_id, genre_id
from csv
where not exists (select 1 from movie_genre where movie_genre.movie_fk = csv.movie_id and movie_genre.genre_fk = csv.genre_id)

和

delete
from movie_genre
where where not exists (select 1 from csv where movie_genre.movie_fk = csv.movie_id and movie_genre.genre_fk = csv.genre_id);

Answer 2

如果有人感兴趣，这不是最优雅的解决方案，但我通过以下操作解决了这个问题：

• 首先我更新了导入代码，以确保导入的流派列表列按字母顺序排序
• 然后我更新了我的查询，以便对group_concat的结果进行直接的字符串比较

_

SELECT * FROM movie 
LEFT JOIN 
( 
SELECT GROUP_CONCAT(genre.name ORDER BY genre.name SEPERATOR "~") AS genres, movie_genre.movie_fk FROM genre 
JOIN movie_genre ON genre.id = movie_genre.genre_fk 
GROUP BY movie_genre.movie_fk
) AS sub_genres ON movie.id = sub_genres.movie_fk
WHERE sub_genres.genres != csv.genres