Detailed explanation of the knowledge points of using TEXT/BLOB types in MySQL

1. The difference between TEXT and BLOB

The only difference between the TEXT and BLOB families is that the BLOB type stores binary data without a collation or character set, while the TEXT type has a character set or collation. To put it simply, if you want to store Chinese, choose TEXT.

2. Default Value Issue

You cannot set the default value in Strict Mode, otherwise you will get an error message saying "can't have a default value":

mysql> create table `test`.`text_blob`( 
 -> `a_text` text DEFAULT ' ' , 
 -> `b_blob` blob 
 -> );
ERROR 1101 (42000): BLOB/TEXT column 'a_text' can't have a default value

Reason: https://www.jb51.net/article/181667.htm

select @@sql_mode; //View mode

The following restrictions apply in trict mode:

1). Does not support inserting null values ​​into not null fields

2). It does not support inserting "values" into auto-increment fields, but null values ​​can be inserted

3). Does not support default values ​​for text fields

3. Sorting

MySQL sorts TEXT and BLOB columns differently from other types: only the first max_sort_length bytes of each column are sorted, not the entire string.
The default value is 1024, which can be modified through the configuration file.

mysql> SHOW VARIABLES LIKE 'max_sort_length';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_sort_length | 1024 |
+-----------------+-------+
1 row in set (0.00 sec)

4. Create a prefix index

It is usually best to set the beginning characters as the index, which can greatly save index space and thus improve index efficiency.

alter table table1 add key (text1(1024));

Knowledge point expansion:

MySQL field design specifications avoid using TEXT/BLOB data types

1. Description

If you have time during the holidays, you can take a look at the design of some tables and find many problems. The old system has more than 100 fields, which contain a lot of mediumtext and less than 5 million data volumes. The development specifications require that the table design should not exceed 5 million data volumes. I checked the disk space occupied by the table, which is 120G. It is undoubtedly a large table, and this was not considered in the design when it was first designed. In a word, as long as it works online, who cares?

MySQL Specifications

1. A single table should not contain more than 5 million records. At the same time, the disk space occupied by a single table should not exceed 50G. Otherwise, consider optimization and table partitioning.

2. Disable the text field type. If it is necessary to use it, you also need to design a separate table.

3. Reasons for disabling text field type

1. The performance is very poor. When performing operations such as sorting, you cannot use temporary tables in memory and must use temporary tables on disk.

2. TEXT or BLOB types can only use prefix indexes, and MySQL has restrictions on the length of index fields.

4. Solution

Set standards and strictly enforce them (implementation is difficult). It is impossible for one or two DBAs to manually review dozens or hundreds of business systems, tens of thousands of tables, and millions of fields. Ultimately, it depends on an intelligent audit system!

This is the end of this article on the detailed explanation of the knowledge points about using TEXT/BLOB types in MySQL. For more information about using TEXT/BLOB types in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!