MySQL数据表中内容大小写区分的设置

今日hadoop部门，有新的需要根据内容大小写过滤数据。

需求如下：

select * from mnb;

+----+------+

| id | name |

+----+------+

| 1 | mnb |

| 2 | MNB |

+----+------+

2 rows in set (0.00 sec)

 

select * from mnb where name='MNB';

+----+------+

| id | name |

+----+------+

| 2 | MNB |

+----+------+

1 row in set (0.00 sec)

 

 

解决方案：

1、修改sql语句

select * from mnb where name= binary 'MNB';

+----+------+

| id | name |

+----+------+

| 2 | MNB |

+----+------+

1 row in set (0.00 sec)

 

2、修改表字符集校验

CREATE TABLE `mnb` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) COLLATE latin1_bin DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

 

3、修改my.cnf

collation_server = utf8_bin

注意：只对修改参数并重启实例后新建的表有效果，老表还需修改表的字符集校验