sql的like区分大小写
来源:互联网 发布:matlab 矩阵横向拼接 编辑:程序博客网 时间:2024/06/01 09:12
1.建表的时候指定校对字符集
2.查询的时候 加 collate utf8_bin
eg:
SELECT * FROM ir WHERE NAME COLLATE utf8_bin LIKE '%DUMP%';
http://edu.codepub.com/2010/0531/23124.php
我们在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)
- sql的like区分大小写
- SQL like 不区分大小写
- 区分大小写的Like用法
- MSSQL like 查询区分大小写的问题
- oracle like通配符区分大小写
- mysql的like查询默认是不区分大小写的
- 创建区分大小写的sql语句
- SQL Server 中大小写区分的处理
- SQL Server 中大小写区分的处理
- SQL Server不区分大小写的问题
- SQL查询区分大小写
- sql区分大小写
- SQL区分大小写
- SQL区分大小写
- SQL查询区分大小写
- SQL查询区分大小写
- sql查询区分大小写
- SQL 区分大小写
- MySQL 存储过程实例
- C语言程序的内存布局
- JavaScript高级培训-自定义对象
- 交换两个数的值,且不引入第三个变量的实现
- asp.net,c#,同时上传多个文件
- sql的like区分大小写
- 屏幕解锁加锁的监听
- 吸血鬼数字的实现(thinking in java练习题)
- Linux 的多线程编程的高效开发经验
- topcoder SRM495 div1 level2
- 斐波那契数列(thinking in java练习题)
- 九个数字
- 2、文件的各个属性 3、目录文件的操作
- QPushButton 按下 由别的控件使其弹起