Shulou(Shulou.com)06/01 Report--

I. the difference between TEXT and BLOB

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

Second, the problem of default value

The default value cannot be set under Strict Mode, otherwise an can't have a default value error will be reported:

Mysql > create table `test`.`text _ blob` (- > `a_ text`blob` text DEFAULT',-> `bblob`text->); ERROR 1101 (42000): BLOB/TEXT column 'atext` can't have a default value

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

Select @ @ sql_mode;// View Mode

There are the following restrictions under trict Mode:

1)。 Inserting null values into not null fields is not supported

2)。 Inserting "values for self-growing fields is not supported, but null values can be inserted."

3)。 Default values for text fields are not supported

Third, sort

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 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 (1024 sec)

Create a prefix index

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

Alter table table1 add key (text1 (1024))

Knowledge point expansion:

MySQL field design specification avoids the use of TEXT/BLOB data types

I. explanation

Holiday can have time to look at some table design, found a lot of problems, more than 100 fields on the old system, which contains a large number of mediumtext, less than 5 million of the data, the development specification requires that the table design should not exceed 5 million data. Looking at the 120g of disk space occupied by the table, there is no doubt that it is a large table, and the design was not considered at the time of the design. As long as a word can be used online, whatever.

II. MySQL specification

1. A single table should not exceed 5 million records, and the disk space occupied by a single table should not exceed 50 G, otherwise optimization and sub-tables should be considered.

2. Disable the text field type, and if necessary, you need to design the table separately.

III. Reasons for disabling text field types

1, poor performance, sorting and other operations, you can not use memory temporary tables, you must use disk temporary tables.

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

IV. Solutions

It is impossible to set the specification and strictly implement it (it is difficult to implement it). It is impossible for one or two DBA to face dozens of business systems, tens of thousands of tables, and millions of fields through manual audit of each table and field.

So far, this is the end of this article on the detailed explanation of MySQL's use of TEXT/BLOB types. For more information about MySQL's use of TEXT/BLOB types, please search for previous articles or continue to browse the relevant articles below. I hope you will support it in the future!

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.