当我在Mysql存储Base64数据时,崩了!!

2,517 阅读3分钟

字符集与排序规则

每个数据库都有一个字符集和一个排序规则,字符集用来指定存储的字符串方式,而排序规则用来定义比较字符串的方式,他们是一对多的关系,也就是一个字符集可以对应多个排序规则,MySql支持30多种字符集,以及70多种排序规则,在使用语句CREATE DATABASE创建数据库时可以指定该数据库使用的字符集和排序规则,或者是使用ALTER DATABASE语句修改指定数据库字符集,在Mysql中,甚至可以对同一个数据库中同一个表的不同字段设置各自的字符集,对比其他数据库来说他们只能使用相同的字符集,可以看出MySql更具有灵活性。

还有语句SHOW CHARACTER SET可以获取字符集的默认排序规则,如下,可以看到默认的utf-8字符集的默认排序规则是utf8_general_ci。

mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
..........
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

排序规则最后的几位代表如下:

  • _ci 表示不区分大小写
  • _cs表示区分大小写
  • _bin二进制

如果我们设置的排序规则是_ci结尾的,那么就得注意了,他对大小写不敏感,也就是在他看来Aa是同一个字符,如下测试:

CREATE TABLE `tb_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `test` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO test.tb_test(test)VALUES('a');
INSERT INTO test.tb_test(test)VALUES('A');

下面查询语句你猜会得到什么?

SELECT  * from test.tb_test where test ='A'

没错,是查询出了全部的值。

mysql> SELECT  * from test.tb_test where test ='A';
+----+------+
| id | test |
+----+------+
|  1 | a    |
|  2 | A    |
+----+------+
2 rows in set (0.00 sec)

会出什么问题?

那么问题就在于,Base64恰恰是区分大小写的,Base64中的字符包括字母A-Z、a-z、数字0-9,共有62个字符,在加上符号"+"、"/",还有作为垫字的"=",实际上是65个字符。

在Base64中,字符A会被编码成QQ==,a会被编码成YQ==,如果某两个数据的Base64在不区分大小写的情况下是相等的,那么在查询时将会得到一个错误的结果。

解决办法有两个,一是修改字符的排序规则,二是修改数据类型。

修改字符排序规则

针对上面的test表,我们可以只修改一下字段的排序规则,这回再次查询就会区分大小写了。

mysql> ALTER TABLE `tb_test` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_cs_0900_as_cs;
Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT  * from test.tb_test where test ='A';
+----+------+
| id | test |
+----+------+
|  2 | A    |
+----+------+
1 row in set (0.00 sec)

或者直接修改表的。

mysql> ALTER TABLE tb_test CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_cs_0900_as_cs;
Query OK, 0 rows affected (0.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT  * from test.tb_test where test ='A';
+----+------+
| id | test |
+----+------+
|  2 | A    |
+----+------+
1 row in set (0.00 sec)

修改数据类型

VARBINARY类型是二进制字符串,也就是包含的是字节字符串,而不是字符字符串,比较的也是字节的二进制。他指定的最大宽度是以字节为单位的,要是使用这个数据类型,就必须指定一个大小。

因此可以利用这个特点,来解决大小写的问题。

mysql> ALTER TABLE tb_test MODIFY test VARBINARY(200);
Query OK, 2 rows affected (0.41 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT  * from test.tb_test where test ='A';
+----+------------+
| id | test       |
+----+------------+
|  2 | 0x41       |
+----+------------+
1 row in set (0.00 sec)