(MySQL, SQLite3)大文字小文字を区別する・しない方法いろいろ

原則

下記のような、大文字小文字の種類だけが異なるデータがあるとします。

HOGE Hoge hoge

下記のSQLを実行すると、大文字小文字は区別されるのでしょうか?



SELECT * FROM posts WHERE name = 'hoge'

SELECT * FROM posts WHERE name LIKE 'hoge'

結果はご覧の通り。

	MySQL	SQLite3
=	区別しない	区別する
LIKE	区別しない	区別しない

MySQL: 大文字小文字を区別させる

MySQLは特に指定がなければ大文字小文字は区別しません。
では、区別させるには?

テーブル作成時に、フィールドにBINARY属性を加える

これは恒久的な設定です。

phpMyAdminで変更する場合

f:id:sutara_lumpur:20101202085415p:image

SQLで変更する場合

テーブル作成時



CREATE TABLE `posts` (

  `id` INT(11) PRIMARY KEY AUTO_INCREMENT,

  `name` VARCHAR(100) BINARY,

  ...

);

途中で

ALTER TABLE `posts` MODIFY `name` VARCHAR(100) BINARY;

検索時に、フィールドにBINARY演算子を加える

これは一時的な設定です。

SELECT * FROM `posts` WHERE `name` = BINARY 'hoge';

参考: MYSQLのCHAR、VARCHARカラムの大文字小文字を区別する（binary） - 新宿御苑近くで働くSEのブログ

SQLite3: 『=』で大文字小文字を区別させない

SQLite3の=は大文字小文字を区別します。
では、区別させないようにするには?

テーブル作成時に、COLLATE句で『nocase』を指定する

これは恒久的な設定です。
※SQLite2ではCOLLATE句は使えません。



CREATE TABLE "posts" (

  "id" INTEGER PRIMARY KEY AUTO_INCREMENT,

  "name" CHAR COLLATE nocase,

  ...

);

検索時に、COLLATE句で"nocase"を指定する

これは一時的な設定です。

SELECT * FROM "posts" WHERE "name" COLLATE nocase = 'hoge';

MySQL, SQLite3: "LIKE"で区別させない

MySQLでもSQLite3でも、LIKE演算子を使うと大文字小文字は区別されません。
つまり、下記のSQLではHOGE,Hoge,hogeの全てにヒットしてしまいます。



SELECT name FROM posts WHERE name LIKE 'ho%'

あいまい検索のために"LIKE"を使いたいが、大文字小文字を区別してほしい。
MySQLならば可能ですが、今のところ、SQLite3では解決法を見つけられませんでした。
CREATE時にCOLLATE nocaseを指定してもダメですorz



(MySQL)区別される

SELECT * FROM `posts` WHERE `name` LIKE BINARY 'ho%';
(SQLite3)区別されない

SELECT * FROM "posts" WHERE "name" COLLATE BINARY LIKE 'ho%';

MySQL, SQLite3: 重複データを探したい

で、ここからが本題です。
重複データを探したい場合。
MySQL, SQLite3共通のSQLは下記。
しかし、これは大文字小文字が区別されてしまいます。



大文字小文字が区別される

SELECT name, COUNT(name) FROM posts GROUP BY name HAVING COUNT(name) > 1;

HOGE,Hoge,hogeを"重複している!"と見做したい場合は下記のように。



大文字小文字を区別しない

(MySQL)

SELECT `name`, COUNT(`name`) FROM `posts` GROUP BY `name` COLLATE utf8_unicode_ci HAVING COUNT(`name`) > 1;
(SQLite3)

SELECT "name", COUNT("name") FROM "posts" GROUP BY "name" COLLATE nocase HAVING COUNT("name") > 1;

参考: mysqlのcollateを使って大文字-小文字や全角-半角を無視した検索 - end0tknrのkipple - web写経開発

これを何に応用するのかというと…、Twitter ハッシュタグのデータベースの整理です。

先日、さくらサーバからロリポップへ引越ししたのですが、その際にデータベースもMySQLからSQLite3へ変更しました。
で、MySQLの時は、ハッシュタグを新規登録する際に大文字小文字を区別せずに重複がないかを確認する処理が正しく動作していたのですが、SQLite3ではそれがうまくいかず、重複タグが多数生まれてしまいました。
それを検出して削除・統合するために、上記のSQLが必要になるわけです。

…あー…、めんどくさい…orz
でも、とても勉強になりました。

なお、CakePHPのfindメソッド各種の大文字小文字の区別の違いについては下記をご覧ください。

ブログ内記事: 【CakePHP】DB検索の際の大文字小文字の区別のまとめ

すたら日記