文档章节

mysql 大小写问题

H
 Haloooooo
发布于 2017/08/16 10:58
字数 1853
阅读 10
收藏 0

MySQL数据库在做查询的时候,有时候是英文字母大小写敏感的,有时候又不是的,主要是由mysql的字符校验规则的设置决定的,通常默认是不支持的大小写字母敏感的。

 

 1. 什么是字符集和校验规则?

字符集是一套符号和编码。校对规则是在字符集内用于比较字符的一套规则。任何一个给定的字符集至少有一个校对规则,它可能有几个校对规则。要想列出一个字符集的校对规则,使用SHOW COLLATION语句。

 

校对规则一般有这些特征:

  • 两个不同的字符集不能有相同的校对规则。
  • 每个字符集有一个默认校对规则。例如,utf8默认校对规则是utf8_general_ci。
  • 存在校对规则命名约定:它们以其相关的字符集名开始,通常包括一个语言名,并且以_ci(大小写不敏感)、_cs(大小写敏感)或_bin(二元)结束。

 

2. 不同级别的字符集和校验规则可控制大小写敏感

MySQL5.1在同一台服务器、同一个数据库或甚至在同一个表中使用不同字符集或校对规则来混合定义字符串。字符集和校对规则有4个级别的默认设置:服务器级、数据库级、表级和连接级。

2.1服务器级

MySQL按照如下方法确定服务器字符集和服务器校对规则:

(1)修改配置文件/etc/my.cnf

在[mysqld]下添加:collation_server = utf8_bin

重启实例

 

更改服务器级的校验规则(collation_server )后,数据库校验规则(collation_collation)默认会继承服务器级的。

注意:

这个只适用于在重新启动之后, 新建的库,已存在的库不受影响.

同样的, 即使库的校验规则改了,已经存在的表不受修改影响;

同理与已经存在的列…

 
mysql> create database yutest0;
Query OK, 1 row affected (0.00 sec)
mysql> use yutest0;
Database changed
mysql> create table t1 (name varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values('AAA');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values('aaa');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+
| name |
+------+
| AAA  |
| aaa  |
+------+
2 rows in set (0.00 sec)

mysql> select * from t1 where name='aaa';
+------+
| name |
+------+
| aaa  |
+------+
1 row in set (0.00 sec)

可以看出,在服务器级进行相应的校对规则设置,查询大小写敏感。

(2)当服务器启动时根据有效的选项设置

当启动mysqld时,根据使用的初始选项设置来确定服务器字符集和校对规则。

shell> mysqld --character-set-server=latin1 --collation-server=latin1_swedish_ci

2.2数据库级

MySQL这样选择数据库字符集和数据库校对规则:

  • 如果指定了character set X和collate Y,那么采用字符集X和校对规则Y。
  • 如果指定了character set X而没有指定collate Y,那么采用character set X和character set X的默认校对规则。
  • 否则,采用服务器字符集和服务器校对规则。

(1)修改配置文件/etc/my.cnf

进行了两组测试

1) 在[mysqld]下添加:

collation_server = utf8_bin

collation_database = utf8_bin

2) 在[mysqld]下添加:

collation_database = utf8_bin

重启实例,两组都不能正常启动,错误信息如下:

 

可见,my.cnf配置文件中不支持设置collation_database 变量。

 

(2)创建数据库时设置数据库校验规则

mysql> create database yutest default character set utf8 collate utf8_bin;
Query OK, 1 row affected (0.00 sec)
mysql> show variables like 'collation_%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_bin        |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)
mysql> select * from t1;
+------+
| name |
+------+
| ABC  |
| abc  |
+------+
2 rows in set (0.00 sec)

mysql> select * from t1 where name='abc';
+------+
| name |
+------+
| abc  |
+------+
1 row in set (0.01 sec)

可以看出,在数据库级进行相应的校对规则设置,查询大小写敏感。

 

2.3表级

MySQL按照下面的方式选择表字符集和校对规则:

  • 如果指定了character set X和collate Y,那么采用character set X和collate Y。
  • 如果指定了character set X而没有指定collate Y,那么采用character set X和character set X的默认校对规则。
  • 否则,采用数据库字符集和服务器校对规则。

在创建表时设置表级校验规则:

mysql> create database yutest2;
Query OK, 1 row affected (0.01 sec)
mysql> use yutest2;
Database changed

mysql> create table t1(name varchar(10))
    -> default character set utf8 collate utf8_bin;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values('ABC');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values('abc');
Query OK, 1 row affected (0.00 sec)

mysql> show variables like 'collation_%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

mysql> select * from t1;
+------+
| name |
+------+
| ABC  |
| abc  |
+------+
2 rows in set (0.00 sec)

mysql> select * from t1 where name='abc';
+------+
| name |
+------+
| abc  |
+------+
1 row in set (0.00 sec)

可以看出,在表级进行相应的校对规则设置,查询大小写敏感。

 

2.4 连接级

考虑什么是一个“连接”:它是连接服务器时所作的事情。客户端发送SQL语句,例如查询,通过连接发送到服务器。服务器通过连接发送响应给客户端,例如结果集。对于客户端连接,这样会导致一些关于连接的字符集和校对规则的问题,这些问题均能够通过系统变量来解决:

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
  • 当查询离开客户端后,在查询中使用哪种字符集?

服务器使用character_set_client变量作为客户端发送的查询中使用的字符集。

  • 服务器接收到查询后应该转换为哪种字符集?

转换时,服务器使用character_set_connection和collation_connection系统变量。它将客户端发送的查询从character_set_client系统变量转换到character_set_connection。

  • 服务器发送结果集或返回错误信息到客户端之前应该转换为哪种字符集?

character_set_results变量指示服务器返回查询结果到客户端使用的字符集。包括结果数据,例如列值和结果元数据(如列名)。

3. 创建数据库表时大小写不敏感,仍然有方法在查询时区分大小写

3.1 在SQL语句中使用collate

使用collate子句,能够为一个比较覆盖任何默认校对规则。collate可以用于多种SQL语句中,比如where,having,group by,order by,as,聚合函数。

mysql> select * from t1 where name collate utf8_bin = 'ABC';
+------+
| name |
+------+
| ABC |
+------+
1 row in set (0.00 sec)

mysql> select * from t1 where name = 'ABC';
+------+
| name |
+------+
| ABC |
| Abc |
| abc |
+------+
3 rows in set (0.00 sec)

mysql> select * from t1;
+------+
| name |
+------+
| ABC |
| Abc |
| abc |
+------+
3 rows in set (0.00 sec)

3.2 binary操作符

binary操作符是collate子句的一个速记符。binary ’x‘等价与’x‘ collate y,这里y是字符集’x‘二元校对规则的名字。每一个字符集有一个二元校对规则。例如,latin1字符集的二元校对规则是latin1_bin,因此,如果列a是字符集latin1,以下两个语句有相同效果:

select * from t1 order by binary a;

select * from t1 order by a collate latin1_bin;
mysql> select * from t1 where binary name = 'ABC';
+------+
| name |
+------+
| ABC |
+------+
1 row in set (0.00 sec)
mysql>
mysql> select * from t1 where name = 'ABC';
+------+
| name |
+------+
| ABC |
| Abc |
| abc |
+------+
3 rows in set (0.00 sec)

 

对于已经存在的数据库,如果没法通过导入导出重新设置字符集和校验字符集就只能通过在登录SQL设置字符集了。

本文转载自:http://blog.aliyun.com/538

共有 人打赏支持
H
粉丝 0
博文 27
码字总数 26126
作品 0
青岛
程序员
解决mysql数据库大小写敏感问题

今天在部署java项目到linux服务器上去的时候出现了个小问题,数据库是mysql,项目在我本地(windows环境下)是没问题的。错误信息:2012-09-06 00:01:29,314 INFO [org.springframework.jdbc....

java_T
2012/09/06
0
4
linux mysql 大小写问题

show variables like '%case%' mysql 在linux中与windows中是不同的。在windows中建表表名是不区分大小写的,而在linux是区分大小的的、但是linux中是可以设置的。 ------------------------...

Zero零_度
2016/10/28
14
0
linux mysql 大小写问题

mysql 在linux中与windows中是不同的。在windows中建表表名是不区分大小写的,而在linux是区分大小的的、但是linux中是可以设置的。 ----------------------------------------------- 以下的...

王小盼
2016/05/05
33
0
mysql大小写问题

MySQL的大小写问题 不是一个很大的问题,但是如果不了解的话,却会使用户产生迷惑 ;如下面 insert into t values('A'); insert into t values('a'); 当第二条执行的时候,如果是主键或者有唯...

foolbenben
2012/10/10
0
0
MYSQL中的空格及大小写问题

项目去O,有同学反映oracle中的数据导入到mysql中不对了,背景是oracle中的数据存在空格敏感字段,比如oracle中123,空格123,123空格就是3个不同的数据,符合唯一约束。而mysql则认为存在相同...

王小明123
2013/01/22
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

OSChina 周三乱弹 —— 公司女同事约我

Osc乱弹歌单(2018)请戳(这里) 【今日歌曲】 @莱布妮子:分享水木年华的单曲《蝴蝶花(2002年大提琴版)》 《蝴蝶花(2002年大提琴版)》- 水木年华 手机党少年们想听歌,请使劲儿戳(这里) ...

小小编辑
8分钟前
12
4
Linux环境搭建 | VMware下共享文件夹的实现

在进行程序开发的过程中,我们经常要在主机与虚拟机之间传递文件,比如说,源代码位于虚拟机,而在主机下阅读或修改源代码,这里就需要使用到 「共享文件」 这个机制了。本文介绍了两种共享文...

良许Linux
今天
5
0
JUC锁框架——AQS源码分析

JUC锁介绍 Java的并发框架JUC(java.util.concurrent)中锁是最重要的一个工具。因为锁,才能实现正确的并发访问。而AbstractQueuedSynchronizer(AQS)是一个用来构建锁和同步器的框架,使用A...

长头发-dawn
今天
3
0
docker中安装了RabbitMQ后无法访问其Web管理页面

在官网找了"$ docker run -d --hostname my-rabbit --name some-rabbit -p 8080:15672 rabbitmq:3-management"这条安装命令,在docker上安装了RabbitMQ,,结果输入http://localhost:8080并不......

钟然千落
今天
4
1
spring-cloud | 分布式session共享

写在前面的话 各位小伙伴,你们有福了,这一节不仅教大家怎么实现分布式session的问题,还用kotlin开发,喜欢kotlin的小伙伴是不是很开心! 以前在写Android的时候,就对客户端请求有一定的认...

冯文议
今天
3
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部