网站运营 | 站长学院 | 技术文档 | 成语 | 歇后语 | 桌面壁纸 | 帝国时代 | 代码收藏 | IP地址查询 | 生活百科 | 生日密码 | CSS压缩 | 用户评论

使MySQL查询区分大小写

【 更新时间:2010-12-10 | 字体:
[导读]我们在MySQL中使用SELECT语句查询时<<<<,可不可以使查询区分大小写<<?今天从网络上找到了方法,现总结如下: 1<、一种方法是可以设置表或行的collation<,使其为binary或case sensitive。在MySQL中,对于Column Collate其约...

我们在MySQL中使用SELECT语句查询时<,可不可以使查询区分大小写<<?今天从网络上找到了方法<<,现总结如下:         1<、一种方法是可以设置表或行的collation,使其为binary或case sensitive<。在MySQL中<<,对于Column Collate其约定的命名方法如下:             *_bin: 表示的是binary case sensitive collation<<,也就是说是区分大小写的
            *_cs: case sensitive collation<,区分大小写
            *_ci: case insensitive collation<<,不区分大小写
###########
# Start binary collation example
###########
mysql> create table case_bin_test (word VARCHAR(10)) CHARACTER SET latin1 COLLATE latin1_bin;
Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO case_bin_test VALUES ('Frank'),('Google'),('froogle'),('flickr'),('FlicKr');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0 mysql> SELECT * FROM case_bin_test WHERE word LIKE 'f%';
+---------+
| word    |
+---------+
| froogle |
| flickr  |
+---------+
2 rows in set (0.00 sec) mysql> SELECT * FROM case_bin_test WHERE word LIKE 'F%';
+---------+
| word    |
+---------+
| Frank   |
| FlicKr  |
+---------+
4 rows in set (0.00 sec) ###########
# End
###########         2<<、另外一种方法 ###########
# Start case sensitive collation example
########### mysql> create table case_cs_test (word VARCHAR(10)) CHARACTER SET latin1 COLLATE latin1_general_cs;
Query OK, 0 rows affected (0.08 sec) mysql> INSERT INTO case_cs_test VALUES ('Frank'),('Google'),('froogle'),('flickr'),('FlicKr');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0 mysql> SELECT * FROM case_cs_test WHERE word  LIKE 'F%';
+---------+
| word    |
+---------+
| Frank   |
| FlicKr  |
+---------+
4 rows in set (0.00 sec) mysql> SELECT * FROM case_cs_test WHERE word  LIKE 'f%';
+---------+
| word    |
+---------+
| froogle |
| flickr  |
+---------+
2 rows in set (0.00 sec) ###########
# end
###########           3<<、还有一种方法就是在查询时指定collation mysql> create table case_test (word VARCHAR(10)) CHARACTER SET latin1;
Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO case_test VALUES ('Frank'),('Google'),('froogle'),('flickr'),('FlicKr');
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM case_test WHERE word LIKE 'f%';
+---------+
| word    |
+---------+
| Frank   |
| froogle |
| flickr  |
| FlicKr  |
+---------+
6 rows in set (0.01 sec) mysql> SELECT * FROM case_test WHERE word LIKE 'F%';
+---------+
| word    |
+---------+
| Frank   |
| froogle |
| flickr  |
| FlicKr  |
+---------+
6 rows in set (0.01 sec)
mysql> SELECT * FROM case_test WHERE word COLLATE latin1_bin LIKE 'F%';
+---------+
| word    |
+---------+
| Frank   |
| FlicKr  |
+---------+
4 rows in set (0.05 sec) mysql> SELECT * FROM case_test WHERE word COLLATE latin1_bin LIKE 'f%';
+---------+
| word    |
+---------+
| froogle |
| flickr  |
+---------+
2 rows in set (0.00 sec)   mysql> SELECT * FROM case_test WHERE word  LIKE 'f%' COLLATE latin1_bin;
+---------+
| word    |
+---------+
| froogle |
| flickr  |
+---------+
2 rows in set (0.00 sec) mysql> SELECT * FROM case_test WHERE word  LIKE 'F%' COLLATE latin1_bin;
+---------+
| word    |
+---------+
| Frank   |
| FlicKr  |
+---------+
4 rows in set (0.01 sec)
mysql> SELECT * FROM case_test WHERE word  LIKE 'F%' COLLATE latin1_general_cs;
+---------+
| word    |
+---------+
| Frank   |
| FlicKr  |
+---------+
4 rows in set (0.04 sec)

友荐云推荐
  • 转载请注明来源:网站运营 网址:http://www.chinawobo.com/ 向您的朋友推荐此文章
  • 特别声明: 本站除部分特别声明禁止转载的专稿外的其他文章可以自由转载,但请务必注明出处和原始作者<。文章版权归文章原始作者所有<。对于被本站转载文章的个人和网站,我们表示深深的谢意<。如果本站转载的文章有版权问题请联系我们,我们会尽快予以更正。
RSS订阅
  • QQ邮箱
  • 填写您的邮件地址<,订阅我们的精彩内容:
更多
© 2014 网站运营 - T086.com(原itlearner.com)
微商货源 | 冠珠陶瓷 | 迪威乐云商devmsn | 易奇八字 | wwe美国职业摔角 | 八字算命 | 河南旅游景点大全 |
RunTime:5.94ms QueryTime:7