使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'),('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)

通常我们会遇到一个字段中存储多个相似单词，比如这样：

我们通过like进行查询时 like ‘%teacher%’，但是大写的Teacher也会被我们找到；

原因：由于sql在查询时默认不会进行大小写区分；

这时我们可以使用BINARY类型转换运算符；

BINARY 运算符将紧随其后的 string 转换为 二进制字符串。

主要用来强制进行按字节进行比较(byte by byte),字节而不是字符的字符。

这使得字符串比较 是区分大小写 的, 不管原始的列定义是否是 BINARY 或者 BLOB。

BINARY 也 对字符串末尾的空格敏感 。

sql可以这样写：select * from 表 where name like binary '%teacher%'

解决方案一：

于是怀疑Mysql的问题。做个实验：直接使用客户端用sql查询数据库。 发现的确是大小不敏感 。

通过查询资料发现需要设置collate（校对） 。 collate规则：

*_bin: 表示的是binary case sensitive collation，也就是说是区分大小写的

*_cs: case sensitive collation，区分大小写

*_ci: case insensitive collation，不区分大小写

解决方法。

1.可以将查询条件用binary()括起来。 比如：

?

1

select * from TableA where binary columnA ='aaa'

2. 可以修改该字段的collation 为 binary

比如：

?

1

ALTER TABLE TABLENAME MODIFY COLUMN COLUMNNAME VARCHAR(50) BINARY CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL

解决方案二：

mysql查询默认是不区分大小写的 如:

?

1

2

select * from some_table where str=‘abc'

select * from some_table where str='ABC'

得到的结果是一样的，如果我们需要进行区分的话可以按照如下方法来做：

第一种方法：

要让mysql查询区分大小写，可以：

?

1

2

select * from some_table where binary str='abc'

select * from some_table where binary str='ABC'

第二方法：

在建表时时候加以标识

?

1

2

3

create table some_table(

str char(20) binary

---