使MySQL查询区分大小写的实现方法
发布网友
发布时间：2022-04-26 20:09
共2个回答
懂视网
时间：2022-04-30 13:52
mysql查询默认是不区分大小写的 如：
select * from table_name where a like ‘a%‘
select * from table_name where a like ‘A%‘
select * from table_name where a like ‘a%‘
select * from table_name where a like ‘A%‘
效果是一样的。
要让mysql查询区分大小写，可以：
select * from table_name where binary a like ‘a%‘
select * from table_name where binary a like ‘A%‘
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区分大小写的查询
标签：
热心网友
时间：2022-04-30 11:00
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)
