MySQL data type

Introduction to Essentials1. Introduction2. Integer type3. Floating-point types4. String Type5. Time and date6. Collection type

1. Introduction

Data types play a fundamental but important role in the database, so the choice of data types will affect the performance of the application interacting with the database.

　　in general, if you can store as many rows in a page as possible, the better the database is , so it is important to choose the right data type.

2. Integer type

There are 5 main types of integers in MySQL, TINYINT, SMALLINT, Mediumint, INT and bigint respectively.

They are to a large extent identical, only when the size of the storage is different, as shown in the following table:

Type	Size	Range (signed)	Range (unsigned)	Use
TINYINT	1 bytes	(-128,127)	(0,255)	Small integer value
SMALLINT	2 bytes	(-32 768,32 767)	(0,65 535)	Large integer value
Mediumint	3 bytes	(-8 388 608,8 388 607)	(0,4 294 967 295)	Large integer value
INT or INTEGER	4 bytes	(-2 147 483 648,2 147 483 647)	(0,4 294 967 295)	Large integer value
BIGINT	8 bytes	(-9 233 372 036 854 775 808, 9 223 372 036 854 775 807)	(0,18 446 744 073 709 551 615)	Maximum integer value

　　

If the column of an integer type is to be stored outside of the range, MySQL is stored after it is truncated as far as the allowable range is closest to its end.

In addition, MySQL will automatically modify the value to 0 before it is inserted into the table in the non-compliant values.

The columns of the data type have the following two properties.

　　

1) UNSIGNED Property

The UNSIGNED property is the unsigned number type, which functions as a UNSIGNED keyword in a program language such as C, C + +, and so on.

For example, the INT type has a signed range of -2 147 483 648 ~ 2 147 483 647,int UNSIGNED (unsigned) range is 0 ~ 4 294 967 295.

2) Zerofill Property

The effect of the Zerofill property is that if the value of an integer type column is less than the width of the setting, 0 is automatically filled in front.

For example, a column of type INT (4) is displayed as "0001" after inserting 1.

3. Floating-point type (to be added)

4. String type

The string type is the most commonly used data type, and MySQL provides 10 basic string types that can store strings ranging from simple one character to huge chunks of text or binary string data.

This is illustrated in the following table:

Type	Size	Use
CHAR	0 ~ 255 bytes	Fixed-length strings
VARCHAR	0 ~ 255 bytes	Variable-length strings
Tinyblob	0 ~ 255 bytes	A binary string of no more than 255 characters
Tinytext	0 ~ 255 bytes	Short text string
Blob	0 ~ 65 535 bytes	Long text string in binary form
TEXT	0 ~ 65 535 bytes	Long Text data
Mediumblob	0 ~ 16 777 215 bytes	Medium-length text data in binary form
Mediumtext	0 ~ 16 777 215 bytes	Medium-Length text data
Logngblob	0 ~ 4 294 967 295 bytes	Large text data in binary form
Longtext	0 ~ 4 294 967 295 bytes	Maximum text data

　　　　

　　

　　

The use of the various types of methods and considerations are as follows:

4.1 CHAR and VARCHAR types

The CHAR type is used to fix a string, and it must be sized with a modifier within the parentheses.

This modifier has a range from 0 to 255.

Values larger than the specified length are truncated, and values smaller than the specified length are padded with spaces.

　　　　

The char type can use the binary modifier, which causes CHAR to participate in a binary operation instead of a traditional case-sensitive method when used for comparison operations.

A variant of the CHAR type is a VARCHAR type, which is a variable-length string type and must also have a modifier ranging from 0 to 255.

The difference between CHAR and VARCHAR is the way the MySQL database handles this modifier, as follows:

1) CHAR is considered to be worth the size, the length is not enough to use a space to complement.

2) The VARCHAR type treats it as the maximum and stores the value using only the length that the string actually needs to be stored (adding an extra byte to store the length of the string itself).

Therefore, a varchar type shorter than the modifier length is not filled by a space in a varchar type, but the value longer than the modifier is still truncated.

Because varchar types can dynamically change the length of stored values based on the timing content, the use of varchar types can greatly conserve disk space and improve storage efficiency when it is not possible to determine how many characters a field requires.

In contrast, the data of the VARCHAR type is less efficient than the CHAR type data.

4.2 TEXT and BLOB types

For cases where the field length requires more than 255, MySQL provides TEXT and BLOB two types.

Depending on the size of the stored data, they all have different subtypes.

These large data are used to store binary data types such as text blocks or images, sound files, and so on.

The TEXT and BLOB types differ in classification and comparison.

BLOB types are case-sensitive, while TEXT is not case-sensitive.

The casing modifier is not used for various blobs and TEXT subtypes.

Values larger than the maximum range supported by the development type are automatically truncated.

5. Time and date (to be supplemented)

6. Collection type

MySQL supports two sets of collection class data types ENUM and set.

An ENUM type allows only one value to be obtained from a collection, whereas a set type allows any number of values to be taken from a collection.

　　

6.1 ENUM types (examples to be added)

The ENUM type allows only one value to be obtained in the collection, which acts like a single option and is often used to process mutually exclusive data, such as the gender of a person.

The ENUM Type field can take a value from the collection or use a null value, except that the input will cause MySQL to insert an empty string in this field.

In addition, if the casing of the inserted value does not match the case of the value in the collection, MySQL automatically converts the case of the inserted value to a value that matches the size of the evil in the collection.

The enum type can be stored as a number inside the system, and is indexed by numbers starting from 1.

An ENUM type can contain up to 65 536 elements, one of which is retained by MySQL to store the error message, which is represented by index 0 or an empty string.

Examples are as follows (to be added ):

6.2 SET Type

The SET type is similar but not the same as the ENUM type.

A set type can take any number of values from a predefined collection, which acts like a check box.

When the ENUM type is the same, any attempt to insert a non-predefined value in the SET Type field causes MySQL to insert an empty string.

If you insert a record that has both a valid element and an illegal element, MySQL retains the legal element and ignores the illegal element.

　　

A SET type can contain up to 64 elements.

The value in the SET element is stored as a separate "bit" sequence, which represents the element corresponding to it.

A bit is a simple and efficient way to create a collection of ordered elements. And it also removes duplicate elements, so it is not possible to include two identical elements in a SET type.

Examples are as follows:

1) Create a MySet data table that contains only one column, which refers to the positioning SET (' A ', ' B ', ' C ', ' d '):

　　　　

CREATE TABLE  SET('a','b','c ','d'));

Create a table

2) Insert the data into the MySet table, respectively, using a different combination form:

INSERT  into VALUES ('a,d'), ('d,a'), ('a,b,a '), ('a,c,d'), ('d,a,d');

Inserting Data

3) After execution of the above statement, the MySet table will add 5 data to obtain the following content through the query:

SELECTCol frommyset; Query results are as follows:+----------+|Col|+----------+|A,d||A,d||A, b||A,c,d||A,d|+----------+

Query Results

4) Insert a row of data again, this time set an unsupported value for the Set column:

INSERT  into VALUES ('a,d,d,s'[Err]1265-  for column'col'1

Insert error value

When executed, a warning message will be displayed stating that the insertion failed.

　　

MySQL data type