☰

URLからドメインを抽出するMySQLクエリ 質問する

tag-icon • tag-icon mysql
URLからドメインを抽出するMySQLクエリ 質問する

URL からドメインを抽出するためのクエリがあります...

SELECT SUBSTRING(LEFT(url, LOCATE('/', url, 8) - 1), 8) AS domain...

これは、URL が のような場合にのみ機能します。 (末尾のスラッシュなし) や(パーマリンク)www.google.com/somethingのような URL では機能しません。www.google.itwww.google.it/abc/xzy/

このクエリを修正して、さまざまな種類の URL に対してより堅牢にする方法をご存知ですか?

ベストアンサー1

以前の回答のいくつかを組み合わせ、さらにデータセットにもう少しハッキングを加える必要がありました。これが私にとってはうまく機能し、ドメインとサブドメインを返します。

SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(target_url, '/', 3), '://', -1), '/', 1), '?', 1) AS domain

説明 (非自明な SQL はほとんど意味をなさないため):

SUBSTRING_INDEX(target_url, '/', 3)- URL にプロトコルがある場合はパスを削除します
SUBSTRING_INDEX(THAT, '://', -1)- そこからプロトコルを削除します
SUBSTRING_INDEX(THAT, '/', 1)- そこからパスを削除します (プロトコルがない場合)
SUBSTRING_INDEX(THAT, '?', 1)- そこからクエリ文字列を削除します (パスまたは末尾の / がない場合)

テストケース:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(target_url, '/', 3), '://', -1), '/', 1), '?', 1) AS domain
FROM ( 
    SELECT       'http://test.com' as target_url 
    UNION SELECT 'https://test.com' 
    UNION SELECT 'http://test.com/one' 
    UNION SELECT 'http://test.com/?huh' 
    UNION SELECT 'http://test.com?http://ouch.foo' 
    UNION SELECT 'test.com' 
    UNION SELECT 'test.com/one'
    UNION SELECT 'test.com/one/two'
    UNION SELECT 'test.com/one/two/three'
    UNION SELECT 'test.com/one/two/three?u=http://maaaaannn'
    UNION SELECT 'http://one.test.com'
    UNION SELECT 'one.test.com/one'
    UNION SELECT 'two.one.test.com/one' ) AS Test;

結果：

'test.com'
'test.com'
'test.com'
'test.com'
'test.com'
'test.com'
'test.com'
'test.com'
'test.com'
'test.com'
'one.test.com'
'one.test.com'
'two.one.test.com'

おすすめ記事