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查询区分大小写的实现方法】相关文章：

★ MySQL忘记密码恢复密码的实现方法

★ mysql数据库导出xml的实现方法

★ MySQL show命令的用法

★ Mysql 数据库双机热备的配置方法

★ MySQL查询和修改auto_increment的方法

★ 在CentOS上MySQL数据库服务器配置方法

★ MySQL 自动备份与数据库被破坏后的恢复方法第1/2页

★ MYSQL使用.frm恢复数据表结构的实现方法

★ SQL 2005 清空日志的方法

★ 解决Mysql连接过多错误的方法