mysql的collation区分大小写设置

本文详细介绍了MySQL数据库字符校验规则的设置方法,包括如何通过调整collation参数实现大小写敏感或不敏感的查询匹配,以及创建表时如何应用不同校验规则以满足特定业务需求。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

                                        mysql的collation区分大小写设置

 

      mysql数据库在做查询时候,有时候是英文字母大小写敏感的,有时候又不是的,主要是由于mysql的字符校验规则的设置。通常默认是不支持的大小写字母敏感的,在主动设置mysql数据库的collation后,可以使得数据库满足大小写敏感,适合客户的一定要求。通过下面的试验进行理解学习.....

 

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.25    |
+-----------+
1 row in set (0.00 sec)

  

    

mysql> show variables like '%character%';
+--------------------------+-------------------------------------------------------
| Variable_name            | Value
+--------------------------+-------------------------------------------------------
| character_set_client     | utf8
| character_set_connection | utf8
| character_set_database   | latin1
| character_set_filesystem | binary
| character_set_results    | utf8
| character_set_server     | latin1
| character_set_system     | utf8
| character_sets_dir       | D:\database\mysql\mysql-5.5.25-winx64\share\charsets\

 

   

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

    默认即为:collation_connection = utf8_general_ci   大小写不敏感校验规则;

  

mysql> show collation like '%utf8%';
+--------------------------+---------+-----+---------+----------+---------+
| Collation                | Charset | Id  | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 |
| utf8_bin                 | utf8    |  83 |         | Yes      |       1 |
| utf8_unicode_ci          | utf8    | 192 |         | Yes      |       8 |
| utf8_icelandic_ci        | utf8    | 193 |         | Yes      |       8 |

    客户端字符集:utf8, 校验规则: utf8_general_ci, 默认为yes,即不是大小写敏感的匹配;

    而utf8_bin是区分大小写的校验规则;

    创建表做测试,看数据效果:

 

   

mysql> create table T_collation(first varchar(30) character set utf8
    -> collate utf8_bin,second varchar(30) character set utf8 collate
    -> utf8_general_ci);
Query OK, 0 rows affected (0.32 sec)

mysql> show create table t_collation\G;
*************************** 1. row ***************************
       Table: t_collation
Create Table: CREATE TABLE `t_collation` (
  `first` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `second` varchar(30) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> insert into t_collation values('M','M'),('N','N'),('a','a'),('b','b');
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t_collation;
+-------+--------+
| first | second |
+-------+--------+
| M     | M      |
| N     | N      |
| a     | a      |
| b     | b      |
+-------+--------+
4 rows in set (0.00 sec)

    比较查询结果:

   

mysql> insert into t_collation values('m','m'),('n','n');
Query OK, 2 rows affected (0.10 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t_collation;
+-------+--------+
| first | second |
+-------+--------+
| M     | M      |
| N     | N      |
| a     | a      |
| b     | b      |
| m     | m      |
| n     | n      |
+-------+--------+
6 rows in set (0.00 sec)

mysql> select * from t_collation where first='m';
+-------+--------+
| first | second |
+-------+--------+
| m     | m      |
+-------+--------+
1 row in set (0.02 sec)

mysql> select * from t_collation where second='m';
+-------+--------+
| first | second |
+-------+--------+
| M     | M      |
| m     | m      |
+-------+--------+
2 rows in set (0.00 sec)
mysql> select * from t_collation where second='M';
+-------+--------+
| first | second |
+-------+--------+
| M     | M      |
| m     | m      |
+-------+--------+
2 rows in set (0.00 sec)

mysql> select * from t_collation where first='M';
+-------+--------+
| first | second |
+-------+--------+
| M     | M      |
+-------+--------+
1 row in set (0.00 sec)

    比较各自的校验规则,utf8_bin是区分大小写的,而utf8_general_ci是不区分的,默认的。

    还可以从排序语句中进行比较,看看测试效果的.....

 

   

mysql> select * from t_collation;
+-------+--------+
| first | second |
+-------+--------+
| M     | M      |
| N     | N      |
| a     | a      |
| b     | b      |
| m     | m      |
| n     | n      |
+-------+--------+
6 rows in set (0.00 sec)

mysql> select * from t_collation order by first;
+-------+--------+
| first | second |
+-------+--------+
| M     | M      |
| N     | N      |
| a     | a      |
| b     | b      |
| m     | m      |
| n     | n      |
+-------+--------+
6 rows in set (0.00 sec)

mysql> select * from t_collation order by second;
+-------+--------+
| first | second |
+-------+--------+
| a     | a      |
| b     | b      |
| M     | M      |
| m     | m      |
| N     | N      |
| n     | n      |
+-------+--------+
6 rows in set (0.00 sec)

   同样符合校验规则的检查。

  结论: 在MYSQL数据库中,根据实际业务需要,适当可以调整字符集的(collation)校验规则,修改默认的大小写敏感问题,满足实际需要,这本身就是数据库的一种设置,熟悉标准、规则,适当利用为项目所用,可以针对具体的数据库或者表或者表的列进行设置。

  

 

   

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值