煮酒听雨

MySQL实现表字段区分大小写

编辑时间：2018-11-06 编辑：忘尘阅读：584

####MySQL在Linux下数据库名、表名、列名、别名大小写规则：

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

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

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

4、变量名也是严格区分大小写的； 
<hr>
#####linux下mysql安装完默认不区分列名的大小写
查询示例
```sql
mysql> select id,nickname from think_user ; 
+----+----------+
| id | nickname |
+----+----------+
| 32 | abc      |
| 34 | ABC      |
| 36 | DEF      |
+----+----------+
3 rows in set (0.00 sec)

mysql> SELECT id,nickname FROM `think_user` WHERE nickname = 'ABC';
+----+----------+
| id | nickname |
+----+----------+
| 32 | abc      |
| 34 | ABC      |
+----+----------+
2 rows in set (0.00 sec)

mysql> SELECT nickname,count(*) FROM `think_user`  group by nickname ;
+----------+----------+
| nickname | count(*) |
+----------+----------+
| abc      |        2 |
| DEF      |        1 |
+----------+----------+
2 rows in set (0.00 sec)
```

#####如果想在查询时区分字段值的大小写，则：字段值需要设置BINARY属性
BINARY 运算符将紧随其后的 string 转换为 二进制字符串。主要用来强制进行按字节进行比较(byte by byte),字节而不是字符的字符。这使得字符串比较是区分大小写的, 不管原始的列定义是否是 BINARY 或者 BLOB。BINARY 也对字符串末尾的空格敏感。

1、查询时字段添加BINARY属性
```sql
mysql> SELECT id,nickname FROM `think_user` WHERE nickname= BINARY 'ABC';
+----+----------+
| id | nickname |
+----+----------+
| 34 | ABC      |
+----+----------+
1 row in set (0.00 sec)

mysql> SELECT nickname,count(*) FROM `think_user` group by binary nickname ;
+----------+----------+
| nickname | count(*) |
+----------+----------+
| ABC      |        1 |
| DEF      |        1 |
| abc      |        1 |
+----------+----------+
3 rows in set (0.00 sec)
```

2、修改表字段结构
```sql
mysql> ALTER TABLE `think_user` MODIFY COLUMN `nickname` VARCHAR(32) BINARY;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT id,nickname FROM `think_user` WHERE nickname=  'ABC';          
+----+----------+
| id | nickname |
+----+----------+
| 34 | ABC      |
+----+----------+
1 row in set (0.00 sec)
```

3、创建表结构时添加BINARY属性
```sql
mysql> use test;                                           
Database changed

mysql> CREATE TABLE `think_users` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `nickname` varchar(32) BINARY,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM `think_users`;
+----+----------+
| id | nickname |
+----+----------+
|  1 | abc      |
|  2 | ABC      |
|  3 | EF       |
+----+----------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM `think_users` WHERE nickname='ABC';
+----+----------+
| id | nickname |
+----+----------+
|  2 | ABC      |
+----+----------+
1 row in set (0.00 sec)
```
<hr>

####表名区分大小写配置方法
linux在/etc/my.cnf 中的[mysqld]后添加添加lower_case_table_names=0，重启MYSQL服务，这时已设置不区分表名的大小写
其中配置值 0：区分大小写，1：不区分大小写

来说两句吧