Learn why

In development, it is common to see fields of vARCHAR (20) or vARCHar (32) length, but navicat is basically the default vARCHar (255) length when building your own tables. So let’s take a look at database table field length design with some questions.

Length limits and field selection

After looking through the data, I found some information about database length limits and table design

Related information: MySQL > select vARCHAR, datetime, vARCHAR, varchar, datetime, varchar, datetime, varchar, datetime, varchAR, datetime, varchAR, datetime, varchAR, datetime, varchAR, datetime, varchAR, datetime, varchAR, datetime Why is the vARCHar character length in mysql frequently set to 255

After carefully reading the above related materials, it can be regarded as a solution to a lot of questions, but also to learn a lot of database books to learn the experience. In general is the database table name and field length is a limitation that is not good, the greater the create table in design field also want to consider the field length, according to some actual situation field types and manually choose table about the length of the field, can the data maintenance, query efficiency and performance.

Verify the difference between vARCHAR (20) and VARCHAR (255)

About this problem, also found a lot of information on the Internet to view, since it is learning, light is no meaning, or to start to verify.

Is vARCHAR (500) more advantageous than VARCHAR (8000)? The performance impact of using the types varchar(20) and varchar(255) in MySQL

Create a test table with related fields and store 1000 entries. Specific operations are as follows

CREATE TABLE ABC ( id int(11) DEFAULT NULL, name varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE ABC ADD INDEX nameIndex (name); Explain select name from ABC; Analyze the query SQL statementCopy the code

The results are shown below:You can see that KEY_LEN is 63 and then add 1000 rows to the table, copy and paste 1000 rows of the same data to test the tablespace size

SELECT CONCAT(TRUNCATE(SUM(data_length),2),'B') AS data_size,
CONCAT(TRUNCATE(SUM(max_data_length),2),'B') AS max_data_size,
CONCAT(TRUNCATE(SUM(data_free),2),'B') AS data_free,
CONCAT(TRUNCATE(SUM(index_length),2),'B') AS index_size
FROM information_schema.tables WHERE TABLE_NAME = 'ABC';
Copy the code

The results are shown below:

You can see that data_size is 16384

Then change the length of the field name to 255 and look again at the key_len and data_size values

alter table ABC  modify name varchar(255);
explain select name from ABC;
SELECT CONCAT(TRUNCATE(SUM(data_length),2),'B') AS data_size,
CONCAT(TRUNCATE(SUM(max_data_length),2),'B') AS max_data_size,
CONCAT(TRUNCATE(SUM(data_free),2),'B') AS data_free,
CONCAT(TRUNCATE(SUM(index_length),2),'B') AS index_size
FROM information_schema.tables WHERE TABLE_NAME = 'ABC';
Copy the code

The results are as follows: KEY_LEN becomes 768 but the data_size doesn’t change. Validation completed: It is concluded that vARCHar (20) and VARCHar (255) generally take the same amount of space, but the index length is different. So try to keep a reasonable length range when designing.

Additional findings

There is another discovery in the test: table field types are actually limited in length, int type maximum 255, etc. A table in which the total length of all fields cannot exceed 65535 bytes is bytes, not characters. Fields of type TEXT BLOb are not included.