Go Design of the char, varchar and text of MySQL

From

Http://www.cnblogs.com/billyxp/p/3548540.html

The design of varchar (10000) has been discussed recently in the design of table structure, and we will analyze and analyze them.

First, we first popularize common sense:

1, char (n) and varchar (n) in parentheses in the number of characters, does not represent the number of bytes, so when the use of Chinese (UTF8) means that you can insert M Chinese, but the actual consumption of m*3 bytes.

2, at the same time char and varchar The biggest difference is that char regardless of the actual value will occupy n characters space, and varchar will only occupy the actual character should occupy +1 of the space, and the actual space +1<=n.

3, the string is truncated after the N setting of char and varchar is exceeded.

4. The upper limit of char is 255 bytes, the upper bound of varchar is 65535 bytes, and the upper limit of text is 65535.

5. Char will truncate trailing spaces when stored, varchar and text will not.

6, varchar will use 1-3 bytes to store the length, text does not.

You can see the results very clearly:

Value	CHAR (4)	Storage Required	VARCHAR (4)	Storage Required
‘‘	‘ ‘	4 bytes	‘‘	1 byte
' AB '	' AB '	4 bytes	' AB '	3 bytes
' ABCD '	' ABCD '	4 bytes	' ABCD '	5 bytes
' Abcdefgh '	' ABCD '	4 bytes	' ABCD '	5 bytes

Generally speaking:

1, char, fixed long, fast, there is the possibility of space waste, will handle the trailing space, the upper limit of 255.

2, varchar, long, slow, there is no space waste, do not handle the trailing space, the upper limit of 65535, but there is a storage length of the actual 65532 maximum available.

3, text, storage and growth data, slow, there is no space waste, do not handle the trailing space, the upper limit of 65535, will use extra space to store data length, gu can use all 65535.

Next, let's talk about the problem with this scenario:

When n at the back of the varchar (n) is very large, do we use varchar, or is it good text? This is an obvious problem of quantitative changes that lead to qualitative change. We consider from 2 aspects, the first is the space, the second is the performance.

First, from the spatial aspect:

From the official documentation we can tell that when varchar is larger than certain values, it is automatically converted to text and the approximate rule is as follows:

• • Greater than varchar (255) becomes tinytext
  • Greater than varchar (500) becomes text
  • Greater than varchar (20000) becomes mediumtext

So there's not much difference between using varchar and text for oversized content.

Second, from the performance side:

Indexes are the most critical factor affecting performance, and for text, only prefix indexes can be added, and the prefix index can be up to 1000 bytes maximum.

And it seems that Varhcar can add all the indexes, but after testing, it is not actually. Because of internal conversions, long varchar can actually add only 1000-byte indexes, which are truncated automatically if they are too long.

Localhost.test>create table Test (a varchar (1500)); Query OK, 0 rows affected (0.01 sec) localhost.test>alter Table Test Add index idx_a (a); Query OK, 0 rows affected, 2 warnings (0.00 sec) records:0  duplicates:0  warnings:2localhost.test>show Warning s;+---------+------+---------------------------------------------------------+| Level   | Code | Message                                                 |+---------+------+---------------------------------------------------------+| Warning | 1071 | Specified key was too long; Max key length is 767 bytes | | Warning | 1071 | Specified key was too long; Max key length is 767 bytes |+---------+------+---------------------------------------------------------+

From the above you can clearly see that the index is truncated. And what's going on with this 767? This is due to InnoDB's own problem, using Innodb_large_prefix settings.

From the index, there is not much difference between long varchar and text.

So we think that when there is more than 255 of the length, there is no essential difference between using varchar and text, just consider the two types of features. (The main consideration is the text does not have a default value problem)

CREATE TABLE ' test ' (  ' id ' int (one) default null,  ' a ' varchar ($) default NULL,  ' B ' text) Engine=innodb Defaul T charset=utf8+----------+------------+-----------------------------------+| query_id | Duration   | Query                             |+----------+------------+-----------------------------------+|        1 | 0.01513200 | Select a from test where id=10000 | |        2 | 0.01384500 | Select B from Test where id=10000 | |        3 | 0.01124300 | Select a from test where id=15000 | |        4 | 0.01971600 | Select B from Test where id=15000 |+----------+------------+-----------------------------------+

From the simple test above, there is basically no difference, but it is recommended to use varchar (10000), after all, this also has truncation, can ensure that the maximum value of the field can be controlled, if the use of text then if the code has a vulnerability is likely to write to the database a large content, will pose a risk.

Therefore, it is best to limit the maximum limit by using varchar based on the short is better principle.

Appendix: Storage Requirements for each field type

Data Type	Storage Required
TINYINT	1 byte
SMALLINT	2 bytes
Mediumint	3 bytes
INT, INTEGER	4 bytes
BIGINT	8 bytes
FLOAT (P)	4 bytes If 0 <= P <=, 8 bytes if <= p <=
FLOAT	4 bytes
DOUBLE [PRECISION], REAL	8 bytes
DECIMAL (m,D), NUMERIC (m,D)	varies; See following discussion
BIT (M)	Approximately (M+7)/8 bytes

Data Type	Storage Required before MySQL 5.6.4	Storage Required as of MySQL 5.6.4
Year	1 byte	1 byte
DATE	3 bytes	3 bytes
Time	3 bytes	3 bytes + Fractional seconds storage
Datetime	8 bytes	5 bytes + Fractional seconds storage
TIMESTAMP	4 bytes	4 bytes + Fractional seconds storage

Data Type	Storage Required
CHAR (M)	m x w bytes, 0 <= M <= 255, where W is the number of bytes Req Uired for the maximum-length character in the character set
BINARY (M)	m bytes, 0 < = m <= 255
VARCHAR (m), VARBINARY (m)	l + 1 bytes If column values require 0–255 bytes, l + 2 bytes If values may require more than 255 bytes
Tinyblob, tinytext	l + 1 bytes, where L < 28
BLOB, TEXT	l + 2 bytes, where L < 2
Mediumblob, mediumtext	l + 3 bytes, where L < 2
Longblob, longtext	l + 4 bytes, where L < 2
ENUM ('value1', 'value2',...)	1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
SET ('value1', 'value2',...)	1, 2, 3, 4, or 8 bytes, depending on the number of Set members (maximum)

Go Design of the char, varchar and text of MySQL