mysql数据类型区别之BLOB与TEXT

当数据量比较小时我们会选择使用占用空间相对较小的char或varchar数据类型，当数据量相对较大时通常会选择blob或text数据类型来存储他们。他们都可以存储可变数量的文本数据，在TEXT或BLOB列的存储或检索过程中，不存在大小写转换。两者的区别在于，blob能保存存储二进制数据，比如相片，blob也没有字符集概念，并且排序和比较基于列值字节的数值值。TEXT列有一个字符集，并且根据字符集的校对规则对值进行排序和比较。有4种blob类型，分别为：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。text类型也有相对应的4种数据类型，分别为：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。在大多数方面，可以将BLOB列视为能够足够大的VARBINARY列。同样，可以将TEXT列视为VARCHAR列。BLOB和TEXT在以下几个方面不同于VARBINARY和VARCHAR：
1、当保存或检索BLOB和TEXT列的值时不删除尾部空格。(这与VARBINARY和VARCHAR列相同）。
2、请注意比较时将用空格对TEXT进行扩充以适合比较的对象，正如CHAR和VARCHAR。
3、对于BLOB和TEXT列的索引，必须指定索引前缀的长度。对于CHAR和VARCHAR，前缀长度是可选的。
4、BLOB和TEXT列不能有 默认值。
当对blob和text数据类型执行大量删除操作时存在很到的性能问题，删除操作能给数据表格留下很大的的空洞，以后填入这些空洞的插入操作存在性能问题，下面通过一个具体事例来说明这个空洞问题。

mysql> create table t(id varchar(100), context text);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t values(1, repeat('beijing', 100)),(2, repeat('beijing', 100)),(3, repeat('beijing', 100));
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

再连续执行如下语句16次

mysql> insert into t select * from t;
Query OK, 196608 rows affected (1.20 sec)
Records: 196608 Duplicates: 0 Warnings: 0

退出mysql，在操作系统下查看mysql数据文件夹下的，t.MYD文件大小

[root@backup ~]# du -sh /usr/local/mysql/data/test/t*
12K /usr/local/mysql/data/test/t.frm
268M /usr/local/mysql/data/test/t.MYD
4.0K /usr/local/mysql/data/test/t.MYI

从上面看到数据文件大小为268M。
进入mysql中删除id为1的数据，通过以上数据插入操作，我们知道id为1的数据应该为总数据的1/3。

mysql> delete from t where id=1;
Query OK, 131072 rows affected (1.11 sec)

退出mysql，继续在操作系统下查看mysql数据文件夹下的，t.MYD文件大小

[root@backup ~]# du -sh /usr/local/mysql/data/test/t*
12K /usr/local/mysql/data/test/t.frm
268M /usr/local/mysql/data/test/t.MYD
4.0K /usr/local/mysql/data/test/t.MYI

从上面可以看到数据大小并没有发生变化，这就产生了上面所说的空洞，接下来通过optimize table对表进行碎片整理，避免空洞造成的性能问题

mysql> optimize table t;
+--------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------+----------+----------+----------+
| test.t | optimize | status | OK |
+--------+----------+----------+----------+
1 row in set (0.99 sec)

再次退出mysql，在操作系统下查看mysql数据文件夹下的，t.MYD文件大小

[root@backup ~]# du -sh /usr/local/mysql/data/test/t*
12K /usr/local/mysql/data/test/t.frm
179M /usr/local/mysql/data/test/t.MYD
4.0K /usr/local/mysql/data/test/t.MYI

可以发现表的数据文件变小了，说明空洞所占领的空间已经被回收。对于采用了blob字段或text字段的数据表格，建议定期对表格进行optimize table操作，避免因空洞带来的性能问题。
由于text和blob数据量大，对于text和blob数据类型的检索，一般不建议直接采用mysql对他们进行检索。同时建议吧text和blob数据列分离到单独的表中，这样避免text或blob数据删除所带来的性能问题。