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

This article mainly introduces how to do too many blob fields in mysql, which is very detailed and has certain reference value. Friends who are interested must finish reading it!

According to MySQL official document data type website: http://www.htmlite.com/mysql003.php

A string with a maximum length of 65535 (64 KB) characters in BLOB.

The MySQL manual says that the maximum size of a BLOB or Text object depends on its type, but the maximum value you can actually transfer between the client and server depends on the amount of memory available and the size of the communication buffer.

If you need a longer amount of data, consider using:

MEDIUMBLOB16777215 bytes (16 MB)

LONGBLOB4294967295 bytes (4GB)

Frequently asked questions:

Table type: innodb, row_format=compact (this is the default row format)

Insert more than 10 blob, blob has a large amount of data (> 768 bytes), insert failure: report Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

Table type: innodb, row_format=dynamic (this is the row format supported by innodb's new file storage format Barracuda)

Insert more than 10 blob. Blob has a large amount of data (> 768 bytes). The insertion is successful.

Note:

BLOB and TEXT are data types designed to store extremely large strings, using binary and string storage. Mysql takes great pains to deal with these two types. Mysql treats the values of these two types as separate objects, and the storage engine usually makes special treatment when storing them. When the values of BLOB and TEXT are too large, InnoDB will use a special "external" storage area for storage. In this case, each value will use 1 to 4 own storage pointers in the row to store the actual value in the external storage area.

Mysql sorts BLOB and TEXT types differently from other types, sorting only for the first max_sort_length itself, and if you only need to sort the previous fewer bytes, you can intercept part of the string by setting the max_sort_length parameter or substring (value,length).

These two types should be used with caution in practical use, especially if temporary tables are created, because if the temporary table size is larger than max_heap_table_size or tmp_table_size, the temporary table will be stored on disk, resulting in a decline in overall speed!

The above is all the content of the article "what to do if there are too many blob fields in mysql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow 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.