preface:

Previous article We have introduced the usage of some common data types, such as int, char, varchar, etc. Blob and text types have not been introduced in detail. Although these two types of data types are not commonly used, they are still used in some scenarios. This article will mainly introduce the related knowledge of blob and text data types.

1. blob type

blob(binary large object) is a container that can store binary files. It is mainly used to store binary large objects, such as pictures, audio and video files. According to different storage capacity, blob types can be divided into the following four types:

The most commonly used is the blob field type, which can store up to 65KB of data. It can generally be used to store icons or logo images. However, the database is not suitable for storing pictures directly. If there is a need to store a large number of pictures, please use object storage or file storage. The image path can be stored in the database to call.

2. text type

The text type is similar to char and varchar, and can be used to store strings. In general, the text type can be considered when meeting the need to store long text strings. According to the storage size, text types can also be divided into the following four types:

However, in daily scenarios, varchar is used as much as possible to store strings. Text type can be used only when long text data is to be stored. Compared with varchar, the text type has the following characteristics:

• text type does not need to specify length.
• If the database does not enable strict sqlmode, when the inserted value exceeds the maximum length of the text column, the value will be truncated and a warning will be generated.
• text type field cannot have a default value.
• varchar can directly create an index, and the text field needs to specify the first few characters to create an index.
• The retrieval efficiency of text type is lower than that of varchar.

Let's test the usage of text type:

# Creating a test table character set is 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

# The index creation test found that the text type must specify a 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 is used to generate duplicate data)
# Normal insertion
mysql> insert into tb_text  (a,b,c) values (repeat('hello',3),repeat('hello',3),repeat('hello',3));
Query OK, 1 row affected (0.01 sec)
# Insert English character exceeds standard
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.00 sec)
# Insert Chinese exceeding standard
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.00 sec)
# When viewing the data, it is found that the data is intercepted. The tinytext type stores up to 255 bytes of data
mysql> select * from tb_text;
+----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+-----------------+
| id | a                                                                                                                                                                                                                                                               | b               | c               |
+----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+-----------------+
|  1 | hellohellohello                                                                                                                                                                                                                                                 | hellohellohello | hellohellohello |
|  2 | hellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohello | 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 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)

Through the above tests, we notice that the storage capacity of text type is in bytes rather than characters. For example, tinytext can store up to 255 bytes instead of 255 characters. Under the utf8 character set, an English letter or number occupies one byte, while a Chinese character occupies three bytes. In other words, tinytext can store up to 255 / 3 = 85 Chinese characters, and text can store up to 65535 / 3 = 21845 Chinese characters. M in varchar(M) refers to the number of characters. An English, number and Chinese character occupy one character, that is, tinytext can store no more than varchar(255).

Summary:

This article introduces the related knowledge of blob and text field types. Although blob and text types are generally not recommended in database specifications, they are still used due to some historical problems or in some scenarios. This article is just a record. You can refer to it when you use it.