MySQL LIKE JOIN というアウトローなクエリーを試してみる

こんなの不可能だろと思いつつやってみたら以外と出来たというクエリーについて紹介します。

JOIN のON句にLIKEを使うというだけなのですが、これが以外と使える場面があります。今回、音楽のアーティスト名周りでこの難解なSQLをどのように活用するかを考えてみました。

テーブル

アーティストテーブル

アーティスト名とId (アーティストID）が含まれているテーブルです

達人に学ぶSQL徹底指南書第2版初級者で終わりたくないあなたへ (CodeZine BOOKS)

> DESC tbl_artist ;
+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| Id          | bigint(20) unsigned | NO   |     | NULL    |       |
| artist_name | varchar(128)        | NO   |     | NULL    |       |
+-------------+---------------------+------+-----+---------+-------+

データ

> SELECT * FROM tbl_artist ;
+-----------------+---------------+
| Id              | artist_name   |
+-----------------+---------------+
| 606456507475634 | Justin Bieber |
| 626480280643963 | Lady Gaga     |
+-----------------+---------------+

music テーブル

曲名と、Id (music_id)が含まれているテーブルです

> DESC musics ;
+-----------+---------------------+------+-----+---------+-------+
| Field     | Type                | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| Id        | bigint(20) unsigned | NO   |     | NULL    |       |
| artist_id | bigint(20)          | NO   |     | 0       |       |
| title     | varchar(256)        | YES  |     | NULL    |       |
+-----------+---------------------+------+-----+---------+-------+

データ

一部抜粋です。titleカラムに Lady GaGa が含まれるデータになります

> SELECT * FROM musics where title like '%lady gaga%' LIMIT 5;
+-----------------+-----------+--------------------------------------------------------------------------------------------+
| Id              | artist_id | title                                                                                      |
+-----------------+-----------+--------------------------------------------------------------------------------------------+
| 730084057776243 |         0 | 3-Way (The Golden Rule) (feat. Justin Timberlake & Lady Gaga)                              |
|  74178572636916 |         0 | Bad Romance - Vintage 1920's Gatsby Style Lady Gaga Cover ft. Ariana Savalas & Sarah Reich |
| 538914551048274 |         0 | BAT ROMANCE [Batman Original MUSIC VIDEO] Dark Knight Rises Lady Gaga Bad Romance Parody   |
| 252558645779415 |         0 | Big Girl Now (feat. Lady GaGa)                                                             |
| 263593181188306 |         0 | Big Girl Now (feat. Lady GaGa)                                                             |
+-----------------+-----------+--------------------------------------------------------------------------------------------+

musicsとtbl_artistを紐付けたい！

現在、musics.artist_idには一律0が入っておりますが、イメージとしては、対象の音楽に合致する、アーティストを、tbl_artistから取得したい、つまり、tbl_artist.Id を　musics.artist_id にセットしたいのです。

その紐付けは、

musics.title に対象のアーティストが含まれている

ことが条件になります

SQL ( LIKE JOIN)

ここで LIKE JOIN での手法が活躍します。今まで、JOIN ON 句って完全一致が当たり前だと思っていたのですが、結構アウトローな方法でこれが実現出来ると思っていませんでした。

LIKE JOIN UPDATE

一つ、JOIN UPDATEという若干特殊な構文を入れてしまっておりますが、LIKE JOINでの例は以下の通りです

tbl_artistのアーティスト名がmusic.title内で部分一致するtbl_artist.Id を musics.artist_idにセットする

UPDATE
  musics a
INNER JOIN
  tbl_artist b
ON
  a.Title LIKE CONCAT('%', b.artist_name , '%')
SET
  a.artist_id = b.Id
;

通常 JOINのON句って

ON
  a = b

のような書き方が正しいのですが、これを無理やり、LIKEで実現するために、CONCATを利用しております。CONCATは文字列結合の関数です

例えば上記の例で値を入れた形でイメージすると

INNER JOIN
  tbl_artist b
ON
 'Lady Gaga' LIKE '%Bad Romance - Vintage 1920's Gatsby Style Lady Gaga Cover ft. Ariana Savalas & Sarah Reich%'

といった感じになります（余計混乱しそうですが）

結果確認

結果、以下の通りとなりました

SELECT
  m.title,
  m.artist_id,
  a.artist_name
FROM
  musics m
INNER JOIN
  tbl_artist a
ON
  m.artist_id = a.Id
WHERE
  a.artist_name = 'Lady Gaga'
;

Lady Gagaが含まれているタイトルに、artist_idが割り振られているのは分かりますでしょうか？

+--------------------------------------------------------------------------------------------+-----------------+-------------+
| title                                                                                      | artist_id       | artist_name |
+--------------------------------------------------------------------------------------------+-----------------+-------------+
| 3-Way (The Golden Rule) (feat. Justin Timberlake & Lady Gaga)                              | 626480280643963 | Lady Gaga   |
| Bad Romance - Vintage 1920's Gatsby Style Lady Gaga Cover ft. Ariana Savalas & Sarah Reich | 626480280643963 | Lady Gaga   |
| BAT ROMANCE [Batman Original MUSIC VIDEO] Dark Knight Rises Lady Gaga Bad Romance Parody   | 626480280643963 | Lady Gaga   |
| Big Girl Now (feat. Lady GaGa)                                                             | 626480280643963 | Lady Gaga   |

注意点として。。

こんなことできるんだねーで、この話は終わりなのですが、LIKE JOINにはリスクもあります。

たとえば、tbl_artistのartist_nameが、Xだった場合、どうしましょう？(正式には、X Japanなので、この例はあまりないとして)

この場合ですと、musicsのタイトルで、Xの一文字を含むレコードを更新しちったりするので、要注意です。

今回は、artist名とそれを含むタイトルで紐付けを作成するとリスクの高い例なので、このような弊害があることに注意してください。

f:id:hit10231023:20180309104332j:plain

Database JUNKY

MySQL,MariaDBを中心としたブログです