Shulou(Shulou.com)05/31 Report--

Today Xiaobian to share with you how to use MySQL blob and text data types related knowledge points, detailed content, clear logic, I believe most people still know too much about this knowledge, so share this article for everyone to refer to, I hope you read this article after some harvest, let's learn about it together.

1. blob type

blob(binary large object) is a container that can store binary files, mainly used to store binary large objects, such as images, audio and video files. Classified according to the size of storable capacity, blob types can be divided into the following four types:

One of the most commonly used is the blob field type, which can store up to 65KB of data and 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 database can store the picture path to call.

2. text type

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

However, in everyday scenarios, store strings as much as possible with varchar, only to store long text data, you can use the text type. In contrast to varchar, the text type has the following characteristics:

The text type does not require a specified length.

If strict sqlmode is not enabled for the database, values inserted that exceed the maximum length of the text column are truncated and a warning is generated.

text Type fields cannot have default values.

varchar can create an index directly, and the text field specifies the first number of characters to create an index.

Text type retrieval is less efficient than varchar.

Below we will test the use of the text type:

#Create test table character set is utf8mysql> show create table tb_text\G*************************** row *************************** Table: tb_textCreate Table: CREATE TABLE `tb_text`( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary',`a` tinytext, `b` text, `c` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8#Create index test found 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 lengthmysql> alter table tb_text add index idx_b (b); ERROR 1170 (42000): BLOB/TEXT column 'b' used in key specification without a key lengthmysql> alter table tb_text add index idx_c (c);Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> 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 #insert 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 characters exceeding 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 exceed 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)#View data found data truncated tinytext type Store 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| NULL| NULL| +----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+-----------------+3 rows in set (0.00 sec)

From the above tests, we note that the storable capacity of the text type is in bytes rather than characters. For example, tinytext stores up to 255 bytes instead of 255 characters. Under utf8 character set, an English letter or number occupies one byte, while a Chinese character occupies three bytes. That is to say, tinytext can store 255/3=85 Chinese characters at most, and text can store 65535/3=21845 Chinese characters at most. The M in varchar(M) refers to the number of characters, an English, a number, a Chinese character is occupied by a character, that is, tinytext can store no more than varchar(255).

The above is "MySQL blob and text data types how to use" all the content of this article, thank you for reading! I believe everyone has a great harvest after reading this article. Xiaobian will update different knowledge for everyone every day. If you want to learn more knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.