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
注意:只对修改参数并重启实例后新建的表有效果,老表还需修改表的字符集校验