背景:最近有個(gè)需求,需要根據(jù)品牌名(varchar)去重?cái)?shù)據(jù)。去重后發(fā)現(xiàn)數(shù)據(jù)有丟失。
創(chuàng)新互聯(lián)建站堅(jiān)持“要么做到,要么別承諾”的工作理念,服務(wù)領(lǐng)域包括:成都網(wǎng)站設(shè)計(jì)、做網(wǎng)站、成都外貿(mào)網(wǎng)站建設(shè)公司、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù),滿(mǎn)足客戶(hù)于互聯(lián)網(wǎng)時(shí)代的咸豐網(wǎng)站設(shè)計(jì)、移動(dòng)媒體設(shè)計(jì)的需求,幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴!
問(wèn)題:MySql中字符串大小寫(xiě)默認(rèn)不區(qū)分。
整個(gè)例子實(shí)驗(yàn)一下。像平常一樣建一張表,插入兩條數(shù)據(jù)。
執(zhí)行如下腳本,查詢(xún)出來(lái)兩條數(shù)據(jù),確實(shí)是不區(qū)分大小寫(xiě)的。
2.設(shè)置字符集和排序方式;
3.創(chuàng)建表的時(shí)候指定binary類(lèi)型,或者更改字段類(lèi)型;
4.在MySQL的配置文件my.ini中增加一行:lower_case_table_names = 0;
其中 0:區(qū)分大小寫(xiě),1:不區(qū)分大小寫(xiě)。(這種方法我并沒(méi)有嘗試。)
1、linux下mysql安裝完后是默認(rèn):區(qū)分表名的大小寫(xiě),不區(qū)分列名的大小寫(xiě);
2、用root帳號(hào)登錄后,在/etc/my.cnf 中的[mysqld]后添加添加lower_case_table_names=1,重啟MYSQL服務(wù),這時(shí)已設(shè)置成功:不區(qū)分表名的大小寫(xiě);
lower_case_table_names參數(shù)詳解:
lower_case_table_names = 0
其中 0:區(qū)分大小寫(xiě),1:不區(qū)分大小寫(xiě)
MySQL在Linux下數(shù)據(jù)庫(kù)名、表名、列名、別名大小寫(xiě)規(guī)則是這樣的:
1、數(shù)據(jù)庫(kù)名與表名是嚴(yán)格區(qū)分大小寫(xiě)的;
2、表的別名是嚴(yán)格區(qū)分大小寫(xiě)的;
3、列名與列的別名在所有的情況下均是忽略大小寫(xiě)的;
4、變量名也是嚴(yán)格區(qū)分大小寫(xiě)的;
MySQL在Windows下都不區(qū)分大小寫(xiě)。
3、如果想在查詢(xún)時(shí)區(qū)分字段值的大小寫(xiě),則:字段值需要設(shè)置BINARY屬性,設(shè)置的方法有多種:
A、創(chuàng)建時(shí)設(shè)置:
CREATE TABLE T(
A VARCHAR(10) BINARY
);
B、使用alter修改:
ALTER TABLE `tablename` MODIFY COLUMN `cloname` VARCHAR(45) BINARY;
C、mysql table editor中直接勾選BINARY項(xiàng)。
1.問(wèn)題描述
mysql版本:mariadb 10.1.16,采用utf8-ci,不區(qū)分大小寫(xiě)的排序規(guī)則。
開(kāi)發(fā)人員反應(yīng)業(yè)務(wù)要求要區(qū)分大小寫(xiě),就是說(shuō)當(dāng)輸入小寫(xiě)條件時(shí),只能返回小寫(xiě)條件對(duì)應(yīng)的數(shù)據(jù),不能返回大寫(xiě)。
dba@192.168.19.215:3306mdls01:58:57SELECT
- cabinet_no
- FROM
- go_task_ycon_refrence gtyr
- WHERE
- 1 = 1
- AND cabinet_no = 'abcd4499503'
- ORDER BY
- 1 ASC;
+-------------+
| cabinet_no |
+-------------+
| ABCD4499503 |
+-------------+
1 row in set (0.00 sec)
2.問(wèn)題解決
在條件字段后面加COLLATE utf8_bin ,或在表級(jí)或字段級(jí)指定COLLATE utf8_bin
dba@192.168.19.215:3306mdls02:08:08SELECT
- cabinet_no
- FROM
- gtyr
- WHERE
- 1 = 1
- AND cabinet_no COLLATE utf8_bin = 'ABCDU4499503'
- ORDER BY
- 1 ASC;
+-------------+
| cabinet_no |
+-------------+
| ABCD4499503 |
+-------------+
1 row in set (0.00 sec)
dba@192.168.19.215:3306mdls02:08:43SELECT
- cabinet_no
- FROM
- gtyr
- WHERE
- 1 = 1
- AND cabinet_no COLLATE utf8_bin = 'abcd4499503'
- ORDER BY
- 1 ASC;
Empty set (0.00 sec)
1、一種方法是可以設(shè)置表或行的collation,使其為binary或case
sensitive。在MySQL中,對(duì)于Column
Collate其約定的命名方法如下:
*_bin:
表示的是binary
case
sensitive
collation,也就是說(shuō)是區(qū)分大小寫(xiě)的
*_cs:
case
sensitive
collation,區(qū)分大小寫(xiě)
*_ci:
case
insensitive
collation,不區(qū)分大小寫(xiě)
###########
#
Start
binary
collation
example
###########
mysql
create
table
case_bin_test
(word
VARCHAR(10))
CHARACTER
SET
latin1
COLLATE
latin1_bin;
Query
OK,
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:
Warnings:
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,
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:
Warnings:
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、還有一種方法就是在查詢(xún)時(shí)指定collation
mysql
create
table
case_test
(word
VARCHAR(10))
CHARACTER
SET
latin1;
Query
OK,
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:
Warnings:
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)
文章標(biāo)題:mysql怎么看大寫(xiě) mysql 查看表內(nèi)容是否區(qū)分大小寫(xiě)
分享地址:http://muchs.cn/article16/hgejgg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供小程序開(kāi)發(fā)、關(guān)鍵詞優(yōu)化、網(wǎng)站內(nèi)鏈、ChatGPT、軟件開(kāi)發(fā)、Google
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀(guān)點(diǎn)不代表本網(wǎng)站立場(chǎng),如需處理請(qǐng)聯(lián)系客服。電話(huà):028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來(lái)源: 創(chuàng)新互聯(lián)