Blob and text data types in MySQL

Preface:

In previous articles, we looked at the use of some common data types, such as int, char, vARCHar, and so on. Blob and text types have not been covered in detail, but although they are not commonly used, they can be used in some scenarios. This article will focus on bloB and text data types.

1. The blob

Blob (Binary Large Object) is a container that can store binary large objects, such as images, audio and video files. Blobs can be classified into four types according to the size of the storage capacity:

The most common of these is the BLOB field type, which can store up to 65KB of data and is typically used to store ICONS or logo images. However, the database is not suitable for storing pictures directly. If there is a large number of pictures, please use object storage or file storage. The database can store pictures to call the path.

2. The text type

The text type, like char and vARCHar, can be used to store strings. In general, you can use the text type when you need to store long text strings. According to the storage size, text types can also be divided into the following four types:

However, in everyday scenarios, vARCHAR is used for storing strings, and text is used only for storing long text data. Compared to vARCHAR, the text type has the following characteristics:

• The text type does not need to specify length.
• If strict SQLMode is not enabled for the database, when an inserted value exceeds the maximum length of the TEXT column, the value is truncated and inserted with a warning.
• Fields of type text cannot have default values.
• Varchar can create an index directly, and text can specify the number of characters before creating an index.
• The efficiency of text retrieval is lower than that of VARCHAR.

Let’s test the use of the text type:

Mysql > alter table set character set utf8 mysql> show create table tb_text\G
*************************** 1. row ***************************
       Table: tb_text
Create Table: CREATE TABLE `tb_text` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `a` tinytext,
  `b` text,
  `c` varchar(255) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=Utf8 # create index test find text type must specify prefix length mysql> alter table tb_text add index idx_a (a);
ERROR 1170 (42000) :BLOB/TEXT column 'a' used in key specification without a key length
mysql> alter table tb_text add index idx_b (b); 
ERROR 1170 (42000) :BLOB/TEXT column 'b' used in key specification without a key length
mysql> alter table tb_text add index idx_c (c);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table tb_text add index idx_b (b(10));
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0# Insert data test (repeat function to generate duplicate data) # Insert mysql normally> insert into tb_text  (a,b,c) values (repeat('hello'.3),repeat('hello'.3),repeat('hello'.3));
Query OK, 1 row affected (0.01SEC) # insert English characters into mysql> insert into tb_text  (a) values (repeat('hello'.52));
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'a' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00SEC) # insert Chinese into mysql>  insert into tb_text  (a) values (repeat('hello'.100));
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'a' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00SEC) # Check data to find data interception tinytext type most stored255Mysql> select * from tb_text;
+----+------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ----------------------+-----------------+-----------------+
| id | a                                                                                                                                                                                                                                                               | b               | c               |
+----+------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ----------------------+-----------------+-----------------+
|  1 | hellohellohello                                                                                                                                                                                                                                                 | hellohellohello | hellohellohello |
|  2 |hellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohello hellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohello hellohellohello| NULL            | NULL            |
|  3 |Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello| NULL            | NULL            |
+----+------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ----------------------+-----------------+-----------------+
3 rows in set (0.00 sec)
Copy the code

Through the above tests, we notice that the storage capacity of the text type is in bytes, not characters. For example, tinytext stores a maximum of 255 bytes instead of 255 characters. In the UTF8 character set, an English letter or number takes up one byte, while a Chinese character takes up three bytes. That is, tinytext stores a maximum of 255/3=85 Chinese characters, and text stores a maximum of 65,55/3 = 21,845 Chinese characters. In VARCHar (M), M refers to the number of characters, and an English, a number, and a Chinese character all occupy one character, that is, the size of tinytext can be stored is not more than vARCHar (255).

Conclusion:

This article introduces bloB and text field types. Although blob and text are not generally recommended in the database specification, they can be used due to historical issues and certain scenarios. This article only as a record, when used can refer to.