MySQLでひらがな以外取得

Question

ruby on railsでアプリケーションを作成しています。
DBはmysqlを使用しており、ひらがな以外で登録されたアイテムを取得するsqlを作成しているのですが、
いい対処法がわからず困っております。

select * from items where name not regexp '^[ぁ-ん]+$';

イメージとしてはこのようにしたかったのですが、日本語文字列に対してREGEXPは使えないということで

select * from items where name not regexp '^(あ|い|う|え|お)+';

このような書き方で実行することも考えましたが、あ〜んまで連ねるのもナンセンスかと思い、
いい方法をご存知でしたらご教示ください。

MySQLのバージョンは5.6です。

Comments

[KSwordOfHaste]

古いバージョンのMySQLではregexpが日本語を扱えないという記事も見かけます。とりあえずMySQLのバージョンを質問に記載したほうがよいのでは？

[退会済みユーザー]

ありがとうございます。MySQLのバージョンは5.6です。追記いたしました。

Answer 1

mregexpという任意の文字セットを用いた正規表現ライブラリーもあるようです。
mregexp - MySQLで日本語の正規表現を扱う

serverのカスタマイズが必要のようなのでSQLのみでの対処にはなりませんが・・・

Comments

[退会済みユーザー]

ご回答ありがとうございます。
調べていた時にその記事も読みましたが、今回はライブラリーを使用せず行いたいと思っております。
ただ、こちらの記事についてもとても勉強になりました。
ありがとうございます！

Answer 2

テーブルおよびフィールドの文字セットは、utf8_unicode_ci や utf8mb4_unicode_ci になっていませんか？
上記の場合、ひらがな。カタカナ、半角カナを区別せずに検索されます。
utf8_bin や utf8mb4_bin で定義すると、厳格な区別で検索されます。

---

追記
実験してみましたが、REGEXP のときと LIKE の時で挙動が異なるようです。LIKEのときは bin にすることで区別できますが、REGEXP の時は区別せずにマッチしてしまいますね。

Comments

[退会済みユーザー]

テーブルの文字セットを調べたところ、utf8_general_ci となっておりました。
utf8_binとは違うのでしょうか？

[退会済みユーザー]

質問の意味がよくわかりません。
utf8_general_ci と utf8_bin は違うのかとい言われれば、当然違いますとしか回答できません。

[退会済みユーザー]

ありがとうございます。
追記につきましてもありがとうございます。
今回は「ひらがな以外を抽出」したいため、 regexp '^[ぁ-ん]+$'のように記述するのが一番スマートかと思いましたが、LIKEで記述した方がいいかもしれないですね。。

[退会済みユーザー]

一番求めていた答えに近い回答をいただけたのでベストアンサーにさせていただきます。
ありがとうございます！

Answer 3

日本語文字列に対してREGEXPは使えないということで

これはどういうことでしょうか。試しにやってみましたが、上記クエリは問題なく動作いたします。
とりあえず試したものを下に貼っておきます。

SQL fiddle

Comments

[退会済みユーザー]

ご回答いただきありがとうございます。
文字列は、ひらがな以外にカタカナ、数字、記号等があります。
上記クエリを実行するとひらがな以外を検索対象としたいのですが、カタカナも検索対象外となり困っております。

[KSwordOfHaste]

たびたび失礼します。regexpのリファレンス(ver 5.6)には比較がバイトコードで行われるとあるのでカラムのcharacter-setに影響を受けるのではないでしょうか。character-setも念のため記載したほうがいいかも知れません。