mysql - 在两列中查找具有最大匹配字符串的行

Question

假设我有这个:

page_url                      | canvas_url
---------------------------------------------------------------
http://www.google.com/        | http://www.google.com/barfoobaz
http://www.google.com/foo/bar | http://www.google.com/foo

我想找到按最长匹配排序的字符串开头的行。我面临的问题是找到最长的匹配字符串，而不仅仅是匹配的行也有匹配的字符串。即

http://www.google.com/foo匹配第 1 行中的 page_url 和第 2 行中的 canvas_url，但如果它是两列的长度而不是匹配，它会认为第 1 行是更好的匹配 canvas_url 第 1 行较长。

我可以抓取所有匹配项，然后过滤代码中的长度，执行如下操作:

SELECT *, LENGTH(canvas_url), LENGTH(page_url)
FROM app 
WHERE
    'http://www.google.com/foo' LIKE CONCAT(canvas_url, '%') OR
    'http://www.google.com/foo' LIKE CONCAT(page_url, '%')

或者执行 2 个子选择来获取 canvas_url 各自 page_url 的顶部匹配，然后在代码中将其过滤为 1，但我更喜欢(除非有任何荒谬的性能问题)数据库只返回我需要的内容。

我最关心的是 MySQL，但我需要以 SQLite 和 Postgress 为目标，所以我很乐意在其中任何一个中得到答案。

建议？

Answer 1

这将用于获取最长的实际匹配长度(而不仅仅是记录中最长的 url):

-- Get page_url matches
SELECT *, LENGTH(page_url) AS MatchLen
FROM app 
WHERE 'http://www.google.com/foo' LIKE CONCAT(page_url, '%') -- can't tell from question if this should be reversed
UNION ALL
-- Get canvas_url matches
SELECT *, LENGTH(canvas_url) AS MatchLen
FROM app 
WHERE 'http://www.google.com/foo' LIKE CONCAT(canvas_url, '%')
-- Bring the longest matches to the top
ORDER BY MatchLen DESC -- May need to add a tie-breaker here
LIMIT 1

这是一个running example on SqlFiddle .