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

In the table structure design of MySQL, I suddenly remember that there are many problems encountered in several places, generally speaking, one is the character set, the other is the data type.

There is an interesting detail about the combination of character sets and data types, and that is the problem of line length.

For example, we create a table using the type of varchar. If you specify gbk, the table contains a field that can be specified as 32766 bytes, but not if it is longer.

The calculation method needs to be understood, because the length of the varhcar type is greater than 255. the length of the value needs to be stored in 2 bytes, while the unit of the page in MySQL is 16k, which is stored in IOT. So if you exceed this length, there will be an overflow, similar to Oracle's overflow.

So for the gbk type, the maximum row length is 65535, then the algorithm for the maximum length of the varchar column is (65535-2) / 2 = 32766.5, so this is 32766.

> create table test_char (v varchar (32766)) charset=gbk

Query OK, 0 rows affected (0.00 sec)

> create table test_char1 (v varchar (32767)) charset=gbk

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 some columns to TEXT or BLOBs is another character set, which is also the default character set latin1, which is preferred by some systems that support Martian.

Its length is different, corresponding to 1 byte, so there is no problem with varchar (32767), and the maximum length is 65532.

> create table test_char1 (v varchar (32767)) charset=latin1

Query OK, 0 rows affected (0.01 sec)

> create table test_char2 (v varchar (65535)) charset=latin1

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 some columns to TEXT or BLOBs still makes a big difference for utf8, which corresponds to 3 bytes, so it needs to be divided by 3, and according to (65535-2) / 3, the maximum value is 21844.

> create table test_char2 (v varchar (21844)) charset=utf8

Query OK, 0 rows affected (0.00 sec)

> create table test_char3 (v varchar (21845)) charset=utf8

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. The scenario above in You have to change some columns to TEXT or BLOBs has some relative limitations, so let's introduce the design of the table structure.

If the gbk character set contains the following fields, what is the maximum length of the varchar type of the memo field?

> create table test_char3 (id int,name varchar (20), memo varchar (32766)) charset=gbk

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 some columns to TEXT or BLOBs

This problem is still the same, using the previous calculation method, the numerical type is 4 bytes, the character type is multiplied by 2, and the length of the character type is less than 255. so subtract 1, so that (65535-1-4-20) is equal to about 32743.

> create table test_char3 (id int,name varchar (20), memo varchar (32744)) charset=gbk

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. The results of the two You have to change some columns to TEXT or BLOB tests can be simply compared.

> create table test_char3 (id int,name varchar (20), memo varchar (32743)) charset=gbk

Query OK, 0 rows affected (0.01 sec)

Select (65535-1-4-20) / 2

+-+

| | (65535-1-4-20 / 2) / 2 |

+-+

| | 32744.0000 |

+-+

1 row in set (0.00 sec)

These points still need to be taken into account in the whole process, otherwise there will be great limitations when we do not pay enough attention to it in the early stage.

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.