【MySQL】MySQL的where条件字符串区分大小写的问题

矫情吗；* 2021-09-24 13:04 486阅读 0赞

MySQL默认情况下，where条件遇到字符串是不区分大小写的。

以下两条SQL语句结果是一样的：

MySQL [datawarehouse]> select * from temp_01 t where browser = 'ie:11' limit 3;
    +-----+----------+-----------+---------+--------+
    | id  | platform | version   | browser | counts |
    +-----+----------+-----------+---------+--------+
    |  48 |        1 | website:1 | IE:11   |     70 |
    | 117 |       20 | website:1 | IE:11   |     70 |
    +-----+----------+-----------+---------+--------+
    2 rows in set (0.01 sec)
    
    MySQL [datawarehouse]> select * from temp_01 t where browser = 'IE:11' limit 3;
    +-----+----------+-----------+---------+--------+
    | id  | platform | version   | browser | counts |
    +-----+----------+-----------+---------+--------+
    |  48 |        1 | website:1 | IE:11   |     70 |
    | 117 |       20 | website:1 | IE:11   |     70 |
    +-----+----------+-----------+---------+--------+
    2 rows in set (0.01 sec)

解决方案一：

建表时在字符串字段后加入关键字`BINARY`

--创建相同结果的表，将browser字段加上关键字BINARY
    CREATE TABLE `temp_02` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `platform` int(10) DEFAULT NULL,
      `version` varchar(50) DEFAULT NULL,
      `browser` varchar(50) BINARY DEFAULT NULL,
      `counts` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=181 DEFAULT CHARSET=utf8;
    
    --将temp_01表的数据导入temp_02中
    insert into temp_02(id, platform, version, browser, counts) select id, platform, version, browser, counts from temp_01;

这次查询时结果完全不一样

MySQL [datawarehouse]> select * from temp_02 t where browser = 'ie:11' limit 3;
    Empty set (0.00 sec)
    ;
    
    MySQL [datawarehouse]> select * from temp_02 t where browser = 'IE:11' limit 3;
    +-----+----------+-----------+---------+--------+
    | id  | platform | version   | browser | counts |
    +-----+----------+-----------+---------+--------+
    |  48 |        1 | website:1 | IE:11   |     70 |
    | 117 |       20 | website:1 | IE:11   |     70 |
    +-----+----------+-----------+---------+--------+
    2 rows in set (0.00 sec)

解决方案二：

在查询语句where条件字符串字段前加上关键字：`BINARY`

MySQL [datawarehouse]> select * from temp_01 t where BINARY browser = 'IE:11' and version = 'Website:1' limit 3;
    +-----+----------+-----------+---------+--------+
    | id  | platform | version   | browser | counts |
    +-----+----------+-----------+---------+--------+
    |  48 |        1 | website:1 | IE:11   |     70 |
    | 117 |       20 | website:1 | IE:11   |     70 |
    +-----+----------+-----------+---------+--------+
    2 rows in set (0.00 sec)
    
    MySQL [datawarehouse]> select * from temp_01 t where BINARY browser = 'IE:11' and BINARY version = 'Website:1' limit 3;
    Empty set (0.00 sec)

解决方案三：

修改字段字符集：

*  utf8\_general\_ci --不区分大小写
 *  utf8\_bin–区分大小写

MySQL [datawarehouse]> ALTER TABLE temp_01 MODIFY COLUMN browser VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL;
    Query OK, 180 rows affected (0.01 sec)
    Records: 180  Duplicates: 0  Warnings: 0
    
    MySQL [datawarehouse]> 
    MySQL [datawarehouse]> select * from temp_01 t where browser = 'ie:11' limit 3;
    Empty set (0.00 sec)