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

What does the maximum and minimum of MySQL refer to? aiming at this question, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

1. A table can have up to 1017 columns (up to 1000 columns before MySQL 5.6.9). Virtual columns are also subject to this restriction.

two。 A table can have up to 64 secondary indexes.

3. If innodb_large_prefix is turned on, the index prefix can be up to the first 3072 bytes in the InnoDB table DYNAMIC or COMPRESSED column format; if not, the first 767bytes are supported in any column format. This restriction applies to both prefix indexes and full-column indexes.

4. A page based on a 16KB can hold a maximum of 3072 bytes. If you reduce the page size of InnoDB from 8KB to 4KB, the length of the index will be reduced accordingly. That is, the maximum index length is 1536 bytes when the page is 8KB and 768 bytes when the page size is 4KB

5. The federated index supports up to 16 columns, and if you exceed this limit, you will encounter the following error:

ERROR 1070 (42000): Too many key parts specified; max 16 parts allowed

6. The row length (excluding the variable length type: VARBINARY/VARCHAR/BLOB/TEXT) is less than half of the page length (such as 4KB, 8KB, 16KB, and 32KB).

For example, if the length of innodb_page_size is 16KB, the president must not exceed 8KB; if innodb_page_size is 64KB, the president must not exceed 16KB; the LONGBLOB/LONGTEXT/BLOB/TEXT column must be less than 4GB, and the whole president must be less than 4GB.

If a line is less than half of a page, it can be stored in a page. If it is more than half of the page, the variable length column will be placed in the additional page memory.

7. Although InnoDB internally supports a length greater than 65535 bytes, MySQL limits the combined length of all columns. For example:

Mysql > CREATE TABLE t (a VARCHAR (8000), b VARCHAR (10000)

C VARCHAR (10000), d VARCHAR (10000), e VARCHAR (10000)

-> f VARCHAR (10000), g VARCHAR (10000) ENGINE=InnoDB

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change somecolumns to TEXT or BLOBs

8. In some older operating systems, the file must be smaller than 2GB. This is not a limitation of InnoDB itself. If you need a large table space, configure to use several small data files instead of one large data file.

9. The maximum InnoDB log file combination size can be 512GB.

10. The smallest table space is 10MB, and the largest table space depends on the InnoDB page size (the maximum table space is the maximum table size).

The answer to the question about what the maximum and minimum of MySQL refers to is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.