转载

MySQL中大小写问题

1.MySQL大小写敏感规则

MySQL中，一个库会对应一个文件夹，库里的表会则以文件的方式存放在文件夹内，所以，操作系统对大小写的敏感性决定了数据库和表的大小写敏感（MySQL有一个只读的系统变量lower_case_file_system，其值反映的正是当前文件系统是否区分大小写）因此：在Windows下Mysql的数据库和表名是大小写不敏感的，而在大多数类型的Unix系统中是大小写敏感的。

以下是MySQL详细的大小写区分规则：

在Linux下：

1、数据库名与表名是严格区分大小写的；
2、表的别名是严格区分大小写的；
3、列名与列的别名在所有的情况下均是忽略大小写的；
4、变量名也是严格区分大小写的；

在Windows下：

全部不区分大小写

补充：

1.MySQL中有一个系统变量：lower_case_table_names，专门用来配置是否区分据库名与表名的大小写。如果你的系统是Windows，而你又希望mysql能够区分大小写，那么你可以在my.ini文件中，在[mysqld]一节的最后，加入如下部分：

			[plain] view plain copy
			
			#If set to 0, table names are stored as specified and comparisons are case sensitive.    
		
			#If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive.    
		
			#If set to 2, table names are stored as given but compared in lowercase. This option also applies to database names and table aliases.   
		
			lower_case_table_names=0

2.MySQL在查询字符串时是大小写不敏感的。如某字段值需要区分大小写，可在定义该字段时指定BINARY属性。

2.一种MySQL命名规范

为了避免大小写引发的问题，一种推荐的命名规则是：在定义数据库、表、列的时候全部采用小写字母加下划线的方式，不使用任何大写字母。

3.关于Hibernate/JPA数据库schema自动映射的问题

使用Hibernate/JPA的工程可以自动生成数据库的schema,由于java有着自己的类与字段命名规则，这与mysql推荐的命名规则不太相符，于是出现了follow哪一种命名规范的问题。一般来说这会由团队和DBA协商确定，使用mysql的命名规范不会出现大小写带来的问题，对于DBA来说可能也更加友好，而使用java的命名规范则对开发人员来说无疑是更加友好的，这将省去列名的映射配置，同时也方便开发人员查看数据库。如果你的项目使用了后者，你最好像上面讲到的，设置一下lower_case_table_names=0。

MySQL语句中字母大小写规则随着语句元素的不同而变化，同时还要取决于MySQL服务器主机上的操作系统。

SQL关键字与函数名

关键字和函数名不区分字母的大小写。如、abs、bin、now、version、floor等函数、SELECT、WHERE、ORDER、GROUP BY等关键字。
MySQL中大小写问题

数据库、数据表和视图的名字

在服务器主机上，MySQL数据库和数据表用底层文件系统中的目录和文件表示。因此数据库和数据表名字的默认字母大小写情况取决于服务器主机上的操作系统在文件名方面的规定。Windows文件名不区分字母的大小写，所以运行在windows主机上的MySQL服务器就不区分数据库和数据表名字的字母大小写。运行在Unix/Linux主机上的MySQL服务器区分数据库和数据表名字的大小写。
MySQL中使用一个文件来表示一个试图，所以视图也符合上述规则。

存储程序的名字

存储函数、存储过程、事件的名字不区分字母的大小写。触发器的名字要区分字母的大小写。

数据列和索引的名字

数据列和索引的名字在MySQL环境里不区分字母的大小写。实例如下：
MySQL中大小写问题

MySQL的大小写问题不是一个很大的问题，但是如果不了解的话，却会使用户产生迷惑；如下面

Sql代码
insert into t values('A');

insert into t values('a');

当第二条执行的时候，如果是主键或者有唯一性约束的话，会发生

Sql代码
Duplicate entry for XXX

更加迷惑的是下面的2条语句

Java代码
insert into t values('~');

insert into t values('y');

也会发生插入错误。

在查询的时候也会出现这样的问题

Java代码
/* 查询的结果一眼 */
select * from t where a like 'a%'
select * from t where a like 'A%'

/* 下面的查询结果也是一样 */
select * from t where a = 'y';
select * from t where a = ''~;

1.问题产生的原因

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

1、数据库名与表名是严格区分大小写的；
2、表的别名是严格区分大小写的；
3、列名与列的别名在所有的情况下均是忽略大小写的；
4、变量名也是严格区分大小写的；

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

MySQL在查询字符串时是大小写不敏感的，在编绎MySQL时一般以ISO-8859字符集作为默认的字符集，这个字符集对大小写不敏感,因此在比较过程中中文编码字符大小写转换造成了这种现象。

2.解决办法

A.表名区分大小写

在my.conf加入：
ower_case_table_names = 0
其中 0：区分大小写，1：不区分大小写

B.插入查询时区分大小写

字段值需要设置BINARY属性，即可区分大小写。

设置的方法有多种：
创建时设置：
CREATE TABLE T(
A VARCHAR(10) BINARY
);

使用alter修改：
ALTER TABLE `tablename` MODIFY COLUMN `cloname` VARCHAR(45) BINARY;

mysql table editor中直接勾选BINARY项。

 mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+
2 rows in set (0.01 sec)


--修改发现变量为只读变量
mysql> set global lower_case_table_names=1;
ERROR 1238 (HY000): Variable 'lower_case_table_names' is a read only variable

修改my.cnf后重启数据库
lower_case_table_names=1

mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.01 sec)

mysql> use test;
Database changed

mysql> create database Test;
ERROR 1007 (HY000): Can't create database 'test'; database exists

mysql> create table T  as select * from t;
ERROR 1050 (42S01): Table 'T' already exists

变量含义解释

lower_case_file_system：数据库所在的文件系统对文件名大小写敏感度。ON表示大小写不敏感 OFF表示敏感

lower_case_table_names：表名大小写敏感度

0表示使用Create语句指定的大小写保存文件
1表示大小写敏感文件系统以小写保存
2表示使用Create语句指定的大小写保存文件，但MySQL会将之转化为小写(?)

(当Linux设置为2时，错误日志显示[Warning] lower_case_table_names was set to 2, even though your the file system '/home/mysql/master_a/data/' is case sensitive. Now setting lower_case_table_names to 0 to avoid future problems.)

列名大小写

列名列别名均不区分大小写

字段值大小写

1、表与行的collation，bin与cs区分大小写，ci不区分大小写

MySQL对collation约定的命名方式如下：

*_ci:case insensitive collation，不区分大小写
*_cs: case sensitive collation，区分大小写
*_bin: 表示的是binary case sensitive collation，区分大小写的

 # 指定collate为区分大小写
mysql> create table b( id varchar(10)) default charset=utf8 default collate=utf8_bin; 
Query OK, 0 rows affected (0.03 sec)sec)

mysql> insert into b values ('A'),('a'),('B');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from b where id like 'a';
+------+
| id   |
+------+
| a    |
+------+
1 row in set (0.00 sec)

mysql> select id,count(*) from b group by id;
+------+----------+
| id   | count(*) |
+------+----------+
| A    |        1 |
| B    |        1 |
| a    |        1 |
+------+----------+
3 rows in set (0.03 sec)

 # 指定collate为忽略大小写
mysql> create table t (name varchar(10)) default charset=utf8 default collate=utf8_general_ci;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t values('a'),('A'),('B'),('b'),('c');
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t where name like 'a';
+------+
| name |
+------+
| a    |
| A    |
+------+
2 rows in set (0.01 sec)

mysql> select name,count(*) from t group by name;
+------+----------+
| name | count(*) |
+------+----------+
| a    |        2 |
| B    |        2 |
| c    |        1 |
+------+----------+
3 rows in set (0.04 sec)

2、字段指定binary

 mysql> alter table t change name name varchar(10) binary;
Query OK, 5 rows affected (0.08 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t where name like 'A';
+------+
| name |
+------+
| A    |
+------+
1 row in set (0.00 sec)

mysql> select name,count(*) from t group by name;
+------+----------+
| name | count(*) |
+------+----------+
| A    |        1 |
| B    |        1 |
| a    |        1 |
| b    |        1 |
| c    |        1 |
+------+----------+
5 rows in set (0.01 sec)

MySQL字符串大小写问题的5种解决方法

MySQL字符串大小写经常会让我们在注册网站时受困了注册名的大小写，出现这个问题的原因是：MySQL在查询字符串时是大小写不敏感的，在编绎MySQL时一般以ISO-8859字符集作为默认的字符集，这个字符集对MySQL字符串大小写不敏感,因此在比较过程中中文编码字符大小写转换造成了这种MySQL字符串大小写的问题。

方法一:

解决方法是对于包含中文的字段加上"binary"属性，使之作为二进制比较，例如将"name char(10)"改成"name char(10)binary"。

方法二:

如果你使用源码编译MySQL，可以编译MySQL时使用--with--charset=gbk 参数，这样MySQL就会直接支持中文查找和排序了。

方法三:

可以使用 Mysql 的 locate 函数来判断。以上述问题为例,使用方法为:
SELECT * FROM table WHERE locate(field,'李') > 0;

方法四:
把您的Select语句改成这样,SELECT * FROM TABLE WHERE FIELDS LIKE BINARY '%FIND%'即可!

方法五:
如果你在Windows下使用已经编译好的MySQL，可以修改My.ini中的字符集选项。
default-character-set = gb2312

如果是自己的服务器使用win系统，方法五是最好的方法！如果是linux系统，可以使用方法二！如果不是自己的机子，可以使用方法一，这样你的用户名是严格区分大小写的！

About Me

...............................................................................................................................

● 本文整理自网络

● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群：230161599 微信群：私聊

● 联系我请加QQ好友(642808185)，注明添加缘由

● 于 2017-03-22 09:00 ~ 2017-03-22 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。

 mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+
2 rows in set (0.01 sec)


--修改发现变量为只读变量
mysql> set global lower_case_table_names=1;
ERROR 1238 (HY000): Variable 'lower_case_table_names' is a read only variable

修改my.cnf后重启数据库
lower_case_table_names=1

mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.01 sec)

mysql> use test;
Database changed

mysql> create database Test;
ERROR 1007 (HY000): Can't create database 'test'; database exists

mysql> create table T  as select * from t;
ERROR 1050 (42S01): Table 'T' already exists