dbに登録されている2文字扱いになっている濁点文字の修正

Question

mysqlに登録されている文字列の中の濁点文字が、文字と濁点で1文字のセットではなく、『 じ 』の場合だと、『 し 』と『 濁点 』の2文字扱いというふうに、一部ですが、なってしまっています。

『 じ 』でlike検索をした時に、きちんと1文字扱いで入っているデータは取り出せるのですが、『 し 』と『 濁点 』の2文字扱いになってしまっているものは取り出せません。
下記が『 ゛ 』でlike検索をした時のsqlです。

SELECT * FROM `categories` WHERE `name` LIKE '%゛%' 

dbに登録されている既存のデータに、2文字扱いになってしまっている濁点文字が存在するかを調べて、mysql内で修正したいのですが、濁点のみでlike検索しても、ひっかからないため、洗い出すことができません。

一度全てのdbに登録されたデータを呼び出してから、
conv関数でUTF-8文字列に変換し、またデータを入れ直す方法しかないでしょうか？

ご教示お願いいたします。

Comments

[ttyp03]

「濁点のみでlike検索」のクエリを提示したほうがよいのではないでしょうか。確認したところ濁点のみでの検索は問題なくできました。

[tarao]

「濁点のみでlike検索」のクエリを質問欄に追加しました！よろしくお願いいたします。

[kuniku]

できるかわかりませんが、　WHERE HEX(カラム) LIKE '%select hex('゛')%' のように、 濁点だけを16進にして検索や、TO_BASE64()でbase64にしてbase64で検索してみるとか。

[tarao]

回答ありがとうございます!SELECT * FROM `categories` WHERE HEX(`name`) LIKE '%select hex('゛')%' でやってみましたが、『　#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ã‚›')%' 　』のエラーが出てしまいました。またselect hex('゛')と実行して、取り出した値がE3829Bだったので、SELECT * FROM `categories` WHERE HEX(`name`) LIKE '%E3829B%' と実行したのですが、取り出すことができませんでした。。

Answer 1

これだとどうなりますかね？

SELECT * FROM `categories` WHERE `name` LIKE BINARY '%゛%'

Comments

[tarao]

回答ありがとうございます。
試してみましたが、教えていただいたsqlでも、呼び出しができませんでした。
LIKE BINARY '%じ%'　でも試してみましたが、２文字扱いになってしまっている『 じ 』は取り出せない状況です。

[ttyp03]

ダメでしたか。お役にたてずに申し訳ないです。
調べてみると濁点の扱いはなんか難しいみたいですね。

[tarao]

いえいえ、とんでもないです！どうもありがとうございます!

Answer 2

テーブルの照合順をutf8_unicode_ciにしていれば、濁点半濁点をとわず、ひらがな・カタカナ両方
ヒットしますがそういうことではないですか？

create table tbl1 (id int primary key,val varchar(20) character set utf8 collate utf8_unicode_ci);
insert into tbl1 values(1,'し'),(2,'し゛'),(3,'じ'),(4,'シ'),(5,'シ゛'),(6,'ジ');

create table tbl2 (id int primary key,val varchar(20) character set utf8 collate utf8_bin);
insert into tbl2 values(1,'し'),(2,'し゛'),(3,'じ'),(4,'シ'),(5,'シ゛'),(6,'ジ');

テスト

select * from tbl1 where val like '%し%'
/*戻り値：し,し゛,じ,シ,シ゛,ジ */
select * from tbl1 where val like '%し゛%'
/*戻り値：し゛,シ゛ */
select * from tbl1 where val like '%じ%'
/*戻り値：し,し゛,じ,シ,シ゛,ジ */
select * from tbl1 where val like '%゛%'
/*戻り値：し゛,シ゛ */


select * from tbl2 where val like '%し%'
/*戻り値：し,し゛ */
select * from tbl2 where val like '%し゛%'
/*戻り値：し゛ */
select * from tbl2 where val like '%じ%'
/*戻り値：じ */
select * from tbl2 where val like '%゛%'
/*戻り値：し゛,シ゛ */

Comments

[tarao]

回答ありがとうございます。
utf8_unicode_ciだと濁点半濁点をとわず、ひらがな・カタカナ両方ヒットするのですね。
今回はutf8_general_ciでという指定があるので変更はできないのですが、参考になりました!

[yambejp]

utf8_general_ciだとアルファベットの大文字小文字は同値として解釈してくれますが
日本語として拡大解釈してくれないのでutf8_binと近い挙動になるでしょうね

むりやりやるなら
select * from tbl where val like '%゛%'
で、全データをひっぱってきて、プログラムで一つ一つのデータを
「○゛」→一文字に変える処理をいれるしかないでしょうかね

[tarao]

utf8_general_ciのままの場合
やはり全データ引っ張ってきて、一つ一つのデータを変えていくしかなさそうですね。
ありがとうございました！

[yambejp]

まぁ今回は「utf8_general_ci」限定との話なのであれですが
依頼者に「utf8_unicode_ci」の有用性を説くのも一つかと思います
もちろんあまり曖昧な一致されても困るという考えもあるので無理強いは禁物ですが

[tarao]

そうですね。今回の事例を踏まえて考慮していきたいと思います！
またご質問させていただくことがあると思いますが、よろしくお願いいたします！
ありがとうございました。