mysql怎么看大写 mysql 查看表内容是否区分大小写

mysql字符串区分大小写的问题

背景:最近有个需求,需要根据品牌名(varchar)去重数据。去重后发现数据有丢失。

创新互联建站坚持“要么做到,要么别承诺”的工作理念,服务领域包括:成都网站设计、做网站、成都外贸网站建设公司、企业官网、英文网站、手机端网站、网站推广等服务,满足客户于互联网时代的咸丰网站设计、移动媒体设计的需求,帮助企业找到有效的互联网解决方案。努力成为您成熟可靠的网络建设合作伙伴!

问题:MySql中字符串大小写默认不区分。

整个例子实验一下。像平常一样建一张表,插入两条数据。

执行如下脚本,查询出来两条数据,确实是不区分大小写的。

2.设置字符集和排序方式;

3.创建表的时候指定binary类型,或者更改字段类型;

4.在MySQL的配置文件my.ini中增加一行:lower_case_table_names = 0;

其中 0:区分大小写,1:不区分大小写。(这种方法我并没有尝试。)

mysql 怎么检查大小写敏感

1、linux下mysql安装完后是默认:区分表名的大小写,不区分列名的大小写;

2、用root帐号登录后,在/etc/my.cnf 中的[mysqld]后添加添加lower_case_table_names=1,重启MYSQL服务,这时已设置成功:不区分表名的大小写;

lower_case_table_names参数详解:

lower_case_table_names = 0

其中 0:区分大小写,1:不区分大小写

MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的:

1、数据库名与表名是严格区分大小写的;

2、表的别名是严格区分大小写的;

3、列名与列的别名在所有的情况下均是忽略大小写的;

4、变量名也是严格区分大小写的;

MySQL在Windows下都不区分大小写。

3、如果想在查询时区分字段值的大小写,则:字段值需要设置BINARY属性,设置的方法有多种:

A、创建时设置:

CREATE TABLE T(

A VARCHAR(10) BINARY

);

B、使用alter修改:

ALTER TABLE `tablename` MODIFY COLUMN `cloname` VARCHAR(45) BINARY;

C、mysql table editor中直接勾选BINARY项。

Mysql怎样在SQL中实现区分大小写的查询

1.问题描述

mysql版本:mariadb 10.1.16,采用utf8-ci,不区分大小写的排序规则。

开发人员反应业务要求要区分大小写,就是说当输入小写条件时,只能返回小写条件对应的数据,不能返回大写。

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.问题解决

在条件字段后面加COLLATE utf8_bin ,或在表级或字段级指定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)

使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,

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、还有一种方法就是在查询时指定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)


文章标题:mysql怎么看大写 mysql 查看表内容是否区分大小写
文章来源:http://scyanting.com/article/hgejgg.html