Mysql入门使MySQL查询区分大小写的实现方法

概述介绍《Mysql入门使MySQL查询区分大小写的实现方法》开发教程，希望对您有用。

《MysqL入门使MySQL查询区分大小写的实现方法》要点：
本文介绍了MysqL入门使MySQL查询区分大小写的实现方法，希望对您有用。如果有疑问，可以联系我们。

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'),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'),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入门使MySQL查询区分大小写的实现方法》讨论，分享您的想法，内存溢出PHP学院为您提供专业教程。

总结

以上是内存溢出为你收集整理的Mysql入门使MySQL查询区分大小写的实现方法全部内容，希望文章能够帮你解决Mysql入门使MySQL查询区分大小写的实现方法所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。