我正在使用 MySQL
,并且我有一个歌曲的SQL数据库,其中包含一首歌曲单词的8列信息。每一行都代表歌曲中的一个单词:
-
songSerial
-歌曲的序列号 -
songName
-歌曲名称 -
word
-歌词的单词 -
row_number
-找到单词的行 -
word_position_in_row
-仅行中的单词数 -
house_number
-单词属于
的房屋数 house_row
-在
中找到单词的房子中的行数word_number
-所有歌曲中的单词数量
一排的示例:{4,科学家,秘密,8、4、2、1、37}
现在我想查询所有包含一组单词的歌曲。例如,所有包含句子的单词:"我爱你"。必须按顺序,而不是来自不同的行或房屋。
这是我的OneDrive中的脚本,用于创建数据容易,大约400行:twotextscriptfilesandtheirzip
任何人可以帮忙吗?
谢谢
一种方法是使用join
S:
select s.*
from songwords sw1 join
songwords sw2
on sw2.songSerial = sw1.songSerial and
sw2.word_number = sw1.word_number + 1 join
songwords sw3
on sw3.songSerial = sw2.songSerial and
sw3.word_number = sw2.word_number + 1
where sw1.word = 'I' and sw2.word = 'love' and sw3.word = 'you';
或,如果您喜欢:
where concat_ws(' ', sw1.word, sw2.word, sw3.word) = 'I love you'
从优化的角度来看(使用word
的索引无助于性能),但很明显查询在做什么。
搜索这种类型的搜索建议使用全文索引。唯一的警告是,您需要删除停止单词列表并索引所有单词,无论长度如何。(" i"one_answers" you"是停止单词的典型示例。)
这是大桌子的昂贵方法,假设word
不是零,我们可以做这样的事情:
SET group_concat_max_len = 16777216 ;
SELECT t.song_serial
, t.house_number
, t.row_number
FROM mytable t
GROUP
BY t.songserial
, t.house_number
, t.row_number
HAVING CONCAT(' ',GROUP_CONCAT(t.word ORDER BY t.word_position_by_row),' ')
LIKE CONCAT('% ','I love you',' %')
我们肯定需要一个合适的索引,例如
... ON `mytable` (`songserial`,`house_number`,`row_number`,`word`)
如果短语中的一个单词很不常见,我们可能会首先搜索该单词,然后将所有单词放在同一行上...
SELECT t.song_serial
, t.house_number
, t.row_number
FROM ( SELECT r.songserial
, r.house_number
, r.row_number
FROM mytable r
WHERE r.word = 'love'
GROUP
BY r.word
, r.songserial
, r.house_number
, r.row_number
) s
JOIN mytable t
ON t.songserial = s.songserial
AND t.house_number = s.house_number
AND t.row_number = s.row_number
GROUP
BY t.songserial
, t.house_number
, t.row_number
HAVING CONCAT(' ',GROUP_CONCAT(t.word ORDER BY t.word_position_by_row),' ')
LIKE CONCAT('% ','I love you',' %')
inline视图 s
将受益于带有单词的覆盖索引作为领先的列
... ON `mytable` (`word`,`songserial`,`house_number`,`row_number`)
您寻找这些单词和相对搜索位置:1 = i,2 = love,3 =您。让我们将它们与两首歌曲线进行比较:
我爱,爱,爱你真正的POS:1 2 3 4 5 6搜索POS:-1 2 2 2 3差:-1 1 2 3 3 3 我想你,我爱你真实POS:1 2 3 4 5 6 7搜索POS:1-3-1 2 3差:0-0-4 4 4
如果我们查看第一行的位置三角洲,我们会得到1(两次),2(一次)和3(两次)。
对于第二行,我们得到了Deltas 0(两次),4(三次)。
因此,对于第二首歌曲,我们找到了一个与搜索单词一样多的匹配的三角洲,对于第一行而言。第二行是匹配。
这是查询。我假设我们有一个临时表search
充满了搜索词和相对位置以进行可读性。
select distinct w.songserial, w.songname, w.house_number
from words w
join search s on s.word = w.word
group by
w.songserial, w.songname, w.row_number, w.house_number, w.house_row, -- song line
w.word_position_in_row - s.pos -- delta
having count(*) = (select count(*) from search);
此查询基于:
-
songserial
songname
house_number
确定一首歌 -
songserial
songname
row_number
house_number
house_row
确定歌曲线
这可能是错误的;我不知道参考歌曲的房子和房屋号码是什么意思。但这很容易调整。