MySQL查询时区分大小写的方法
来源:互联网 发布:2017mac国服英雄联盟 编辑:程序博客网 时间:2024/06/10 16:34
感谢所有有奉献精神的人
转自:http://www.cnblogs.com/pinnasky/archive/2012/09/11/2680264.html
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)
要让mysql查询区分大小写,可以:
- select * from table_name where binary a like 'a%'
- select * from table_name where binary a like 'A%'
也可以在建表时,加以标识
create table table_name(
a varchar (20) binary
)
- MySQL查询时区分大小写的方法
- MySQL查询时区分大小写的方法
- MySQL查询时区分大小写的方法
- mysql查询时区分大小写的方法
- MySQL查询时区分大小写的方法
- MySQL查询时区分大小写的方法
- MySQL查询时区分大小写的方法
- Mysql查询时区分大小写
- 让Mysql在查询时区分大小写?
- MySQL查询字符串时区分大小写
- 在SQLServer中查询时区分大小写
- mysql 查询字段分大小写
- SQL SERVER 筛选时区分大小写的语法
- MYSQL 查询区分大小写方法
- 如何设置Sql Server数据库查询时区分大小写和全角半角
- 使MySQL查询区分大小写的实现方法
- MySQL查询时强制区分大小写的方法
- MySQL查询不区分大小写的原因
- eclipse 各种奇葩错误(不定期更新)
- QT实现类似QQ的抖屏效果
- docstring
- win8.1如何删除打开这台电脑最上面的几个文件夹
- 分布式系统中保证数据的正确性(插入与更新)
- MySQL查询时区分大小写的方法
- JS 面向对象版 贪吃蛇
- 以静态网页为原型制作dede动态模板
- hbase命令
- 15.给链表中的数据排序
- 理解linux文件系统VFS
- tomcat环境配置
- PHP学习笔记(一):开发微信平台
- C++内存管理