Data storage type of MySQL study notes, mysql Study Notes

Data storage type of MySQL study notes, mysql Study Notes

Note: This article is a summary of the MySQL database data storage type.

Numeric Type (number Type)

1、TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
It is mainly divided based on different storage Bytes:

• TINYINT: 1 byte

• SMALLINT: 2 bytes

• MEDIUMINT: 3 bytes

• INT: 4 bytes

• BIGINT: 8 bytes

If you do not need the number of symbols, we recommend that you add unsigned to double the storage length to improve storage performance. For example, tinyint unsigned (0 ~ 255)/TINYINT (-128 ~ 127 ).

2、DECIMAL/NUMERIC
DECIMAL, like NUMERIC, stores the NUMERIC type in binary format. For example, DECIMAL (99.99) indicates a number with a total of four digits after the DECIMAL point. The value range is ~ 99.99. The maximum number of digits is 65.

3. FLOAT and DOUBLE
FLOAT is 4-byte storage, while DOUBLE is 8-byte storage. FLOAT (6, 4) indicates to store a number with a total of six precision digits after the decimal point. If the decimal point is exceeded, the number is rounded off. For example, if 99.0000990 is saved, the actual number is 99.0001, which will make the number distorted. Therefore, if the bank account amount requires high precision, it can be stored with double precision, or move the original number to the left.

String Type (character Type)

1. CHAR and VARCHAR
CHARIs a fixed character length, and the maximum character length is 255 = 2e8-1, the processing speed is faster than VARCHAR, but it is easy to waste storage space. For example, CHAR (10) indicates that a storage space with a fixed length of 10 characters is allocated. When 'php' is stored, a storage space with a length of 7 characters is wasted. For spaces at the end of a character, CHAR needs to be processed.
VARCHARIs a variable length. The storage space is allocated based on the actual length of the character. The maximum length is 65535 = 2e16-1. VARCHAR (10) indicates a string with a maximum length of 10 characters. When 'php' is stored, only three buckets are allocated, saving storage space.
VARCHAR is preferred when the InnoDB engine is used by default. Generally, there are many VARCHAR types.

2. BINARY and VARBINARY
BINARY/VARBINARYAndCHAR/VARCHARSimilarly, only BINARY/VARBINARY stores BINARY characters. The length also indicates the length of a byte rather than the length of a character (ASCII code: one English letter, one byte, and two Chinese characters; the UTF-8 encodes an English letter 1 byte 1 Chinese Character 3 byte ).

3. BLOB and TEXT
BLOBStores large amounts of binary data, such as an image. BLOB is divided into TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB, which are classified based on the maximum length of storage bytes.
TEXTString data that stores a large amount of data. TEXT can be divided into TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT, which are classified based on the maximum length of stored TEXT.

4、ENUM
Enumeration type. Each value has a corresponding index. A maximum of two bytes are stored, with 65535 different members. Note:
Field in the Table: numbers ('0', '1', '2 ')
Insert into test (numbers) values (2), ('2'), ('3 ');
Select * from test;
Will return:
Numbers

• 1

• 2

• 2

This indicates that when storing integer 2, 2 is used as the index to store the second value '1', and the storage character '2' is the character '2 ', when the storage character '3' is used as index 3, the storage is '2 '.

5、SET
Set type, similar to ENU, which can contain up to 64 members.

CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');SELECT * FROM myset;

We will worry about repeated data:

Date and Time Type (Date and Time Type)

DATE、TIME、DATETIME、TIMESTAMP、YEAR
The default format is:

• DATE: 1000-01-01 ~ 9999-12-31

• TIME: 00:00:01 ~ 23:59:59

• DATETIME: 1000-01-01 00:00:00 ~ 9999-12-31 59: 59: 59

• TIMESTAMP: 00:00:00 ~ 2038-01-19 03:14:07

• YEAR: 0001 ~ 9999

Which of the following is more convenient?timestampIt can be converted according to the time zone, which is easy to use.