详解MySQL中的TEXT、BLOB

通常我们在选择存储视频或者大文本的时候会选择TEXT和BLOB.那么两者有什么区别呢?
BLOB主要用来存储二进制,通俗的来讲就是存储比如视频、图片等等,而TEXT顾名思义是存储字符数据比如一篇文章.那么TEXT和BLOB中又包括TEXT、MEDIUMTEXT、LONGTEXT、BLOB、MEDIUMBLOB、LONGBLOB,它们之间的差别在于存储的文本和字符长度不同.我们只讨论BLOB、TEXT.


Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.14 MySQL Community Server (GPL)


Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql>
mysql>
mysql>
mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| test               |
| world              |
+--------------------+
6 rows in set (0.09 sec)


mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)


mysql> drop table test;
Query OK, 0 rows affected (0.63 sec)


mysql> create table test (id varchar(100),con text);
Query OK, 0 rows affected (0.56 sec)


mysql> insert into test values (1,repeat('hhhh',100));
Query OK, 1 row affected (0.18 sec)


mysql> insert into test values (2,repeat('hhhh',100));
Query OK, 1 row affected (0.08 sec)


mysql> insert into test values (3,repeat('hhhh',100));
Query OK, 1 row affected (0.06 sec)


mysql> insert into test values (4,repeat('hhhh',100));
Query OK, 1 row affected (0.11 sec)


mysql> insert into test select * from test;
Query OK, 4 rows affected (0.23 sec)
Records: 4  Duplicates: 0  Warnings: 0


mysql> insert into test select * from test;
Query OK, 8 rows affected (0.09 sec)
Records: 8  Duplicates: 0  Warnings: 0


mysql> insert into test select * from test;
Query OK, 16 rows affected (0.15 sec)
Records: 16  Duplicates: 0  Warnings: 0


..............


mysql> insert into test select * from test;
Query OK, 16384 rows affected (3.95 sec)
Records: 16384  Duplicates: 0  Warnings: 0


mysql> insert into test select * from test;
Query OK, 32768 rows affected (9.08 sec)
Records: 32768  Duplicates: 0  Warnings: 0


mysql> commit;
Query OK, 0 rows affected (0.06 sec)


此时检查一下磁盘中TEST表物理文件的大小




从Test表中删除id=3的数据


mysql> delete from test where id=3;
Query OK, 16384 rows affected (19.04 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql>


在一次查看Test表占用的物理空间


此时可以发现,表Test数据文件仍然是40MB,并没有因为数据删除而减少


接下来对表执行OPTIMIZE在查看一下


mysql> optimize table test;
+-----------+----------+----------+---------------------------------------------
----------------------+
| Table     | Op       | Msg_type | Msg_text
                      |
+-----------+----------+----------+---------------------------------------------
----------------------+
| test.test | optimize | note     | Table does not support optimize, doing recre
ate + analyze instead |
| test.test | optimize | status   | OK
                      |
+-----------+----------+----------+---------------------------------------------
----------------------+
2 rows in set (13.23 sec)


mysql>



再一次查看物理文件发现从40M变成30M了.这说明MySQL中的BLOB和TEXT在执行删除操作并不会自动回收空间.可能会引起一些性能问题.为了减少这些问题建议定期对含有大对象的表执行OPTIMIZE操作进行碎片整理避免空间浪费.