Let’s talk about blob and text data types in MySQL (detailed examples)

In this article, let’s take a look at the blob and text data types in mysql. Blob is a container that can store binary files. The text type is similar to char and varchar, and can be used to store strings. Let’s take a look at it together. Let’s take a look at the relevant knowledge of these two data types. I hope it will be helpful to everyone.

1. The blob type

blob (binary large object) is a binary file that can be stored Containers are mainly used to store large binary objects, such as pictures, audio and video files. Classified according to the size of the 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. , generally used to store icons or logo images. However, the database is not suitable for directly storing images. If there is a need to store a large number of images, please use object storage or file storage. The image path can be stored in the database for calling.

2. Text type

The text type is similar to char and varchar, and can be used to store strings. In general, when it comes to storage Consider using the text type when long text strings are required. According to the storage size, the text type can also be divided into the following four types:

However, in daily scenarios, it is better to use varchar to store strings, only if you want to store long text For data, you can use the text type. Compared with varchar, the text type has the following characteristics:

• The text type does not need to specify the length.

• If strict sqlmode is not enabled in the database, when the inserted value exceeds the maximum length of the text column, the value will be truncated and inserted and a warning will be generated.

• text type fields cannot have default values.

• varchar can create an index directly. To create an index on the text field, you need to specify the first number of characters.

• text type retrieval efficiency is lower than varchar.

Let’s test the usage of text type in detail:

# 创建测试表 字符集是 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 '主键',
  `a` tinytext,
  `b` text,
  `c` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
# 创建索引测试 发现text类型必须指定前缀长度
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
# 插入数据测试（repeat函数用于生成重复数据）
# 正常插入
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)
# 插入英文字符超标
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)
# 插入中文超标
mysql>  insert into tb_text  (a) values (repeat('你好',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)
# 查看数据 发现数据有所截取 tinytext 类型最多存储255字节数据
mysql> select * from tb_text;
+----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+-----------------+
| id | a | b | c |
+----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+-----------------+
|  1 | hellohellohello | hellohellohello | hellohellohello |
|  2 | hellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohello | NULL| NULL|
|  3 | 你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你| NULL| NULL|
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+-----------------+
3 rows in set (0.00 sec)

Through the above test, we noticed that the storage capacity of text type is in bytes. instead of characters. For example, tinytext can store up to 255 bytes instead of 255 characters. Under the utf8 character set, one English letter or number occupies one byte, while one Chinese character occupies three bytes. That is to say, tinytext can store up to 255/3=85 Chinese characters, and text can store up to 65535/3=21845 Chinese characters. The M in varchar(M) refers to the number of characters. Each English, number, and Chinese character occupies one character, that is, the size that tinytext can store is no more than varchar(255).

Summary:

This article introduces knowledge about blob and text field types. Although the use of blob and text types is generally not recommended in database specifications, these two data types are still used due to some historical issues or certain scenarios. This article is only for record, you can refer to it when using it.

Recommended learning: mysql video tutorial

The above is the detailed content of Let’s talk about blob and text data types in MySQL (detailed examples). For more information, please follow other related articles on the PHP Chinese website!