用 MySQL 開發的時候,如果 collation 選擇 utf8_general_ci,比較字串(使用等號 =)的時候大小寫的不同會被視為一致,但是 SQLite 並不會。解決的方法是加上 COLLATE NOCASE

可以在 SELECT 的時候把 COLLATE NOCASE 擺在 LIMIT 1 前面:


SQLite version 3.7.7.1 2011-06-28 17:39:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE `users` (`id`, `user_name`, `email`);
sqlite> INSERT INTO `users` (`id`, `user_name`, `email`) VALUES (1,
'Repeat', 'REPEAT@example.com');
sqlite> INSERT INTO `users` (`id`, `user_name`, `email`) VALUES (2, 'repeattest1', 'repeattest1@example.com');
sqlite> SELECT * FROM `users` WHERE `user_name` = 'repeat';
sqlite> SELECT * FROM `users` WHERE `user_name` = 'Repeat';
1|Repeat|REPEAT@example.com
sqlite> SELECT * FROM `users` WHERE `user_name` = 'REPEAT';
sqlite> SELECT * FROM `users` WHERE `user_name` = 'REPEAT' COLLATE NOCASE;
1|Repeat|REPEAT@example.com
sqlite> SELECT * FROM `users` WHERE `user_name` = 'repeat' COLLATE NOCASE;
1|Repeat|REPEAT@example.com
sqlite> SELECT * FROM `users` WHERE `user_name` = 'repEat' COLLATE NOCASE;
1|Repeat|REPEAT@example.com
sqlite> SELECT * FROM `users` WHERE `user_name` = 'repEat' COLLATE NOCASE LIMIT 1;
1|Repeat|REPEAT@example.com
sqlite> SELECT * FROM `users` WHERE `user_name` = 'repEat' LIMIT 1 COLLATE NOCASE;

另外在 CREATE TABLE 的時候做 COLLATE NOCASE 也可以:


SQLite version 3.7.7.1 2011-06-28 17:39:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE `users` (`id`, `user_name` TEXT COLLATE NOCASE,`email` TEXT);
sqlite> INSERT INTO `users` (`id`, `user_name`, `email`) VALUES (1, 'Repeat', 'REPEAT@example.com');
sqlite> SELECT * FROM `users` WHERE `user_name` = 'repeat';
1|Repeat|REPEAT@example.com
sqlite> SELECT * FROM `users` WHERE `email` = 'repeat@example.com';
sqlite> SELECT * FROM `users` WHERE `email` = 'REPEAT@example.com';
1|Repeat|REPEAT@example.com

參考資料:http://www.sqlite.org/faq.html#q18

文章標籤
創作者介紹

PIXNET Lab

PIXNET Lab 發表在 痞客邦 PIXNET 留言(0) 人氣()