4.2.9. MySQL data type

Defining the type of data field in MySQL is very important for optimizing your database.

MySQL supports many types, which can be roughly divided into three types: numeric, date / time, and string (character) types.

Numerical type ¶

MySQL supports all standard SQL numeric data types.

These types include strict numeric data types (INTEGER, SMALLINT, DECIMAL, and NUMERIC) and approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION).

The keyword INT is synonymous with INTEGER, and the keyword DEC is synonymous with DECIMAL.

The BIT data type holds bit field values and supports MyISAM, MEMORY, InnoDB, and BDB tables.

As an extension of the SQL standard, MySQL also supports integer types TINYINT, MEDIUMINT, and BIGINT. The following table shows the storage and range of each integer type required.

Types	Size	Range (signed)	Range (unsigned)	Use
TINYINT	1 Bytes	(- 128127)	(0，255)	Small integer value
SMALLINT	2 Bytes	(- 32 768, 32 767)	(0pr 65 535)	Large integer value
MEDIUMINT	3 Bytes	(- 8 388 608, 8 388 607)	(0pr 16 777 215)	Large integer value
INT or INTEGER	4 Bytes	(- 2 147 483 648 and 2 147 483 647)	(0pr 4 294 967 295)	Large integer value
BIGINT	8 Bytes	(- 9, 223, 372, 036, 752, 036, 854, 775, 807)	(0pr 18 446 744 073 709 551 615)	Maximum integer value
FLOAT	4 Bytes	(- 3.402 823 466 Elaine 38), 0, (1.175 494 351 Elam 38 3.402 823 466 351 Emur38)	0, (1.175 494 351 Emuri 38 jurisdiction 3.402 823 466 Ecolors 38)	Single precision floating point value
DOUBLE	8 Bytes	(- 1.797 693 134 862 315 7 Efu308), 0, (2.225 073 858 507 201 4 EMI 308)	0, (2.225 073 858 507 201 4 Elysee 308 Magistrates 1.797 693 134 862 315 7 Ecolors 308)	Double precision floating point value
DECIMAL	For DECIMAL (M < D), if M > D, if M > D, it is M > D; otherwise, it is M > D.	Values that depend on M and D	Values that depend on M and D	Decimal value

Date and time type ¶

The date and time types that represent time values are DATETIME, DATE, TIMESTAMP, TIME, and YEAR.

Each time type has a range of valid values and a “zero” value, which is used when specifying values that cannot be represented by illegal MySQL.

The TIMESTAMP type has a proprietary automatic update feature, which will be described later.

Types	Size	Range	Format	Use
	(bytes)
DATE	3	1000-01-01 Universe 9999-12-31	YYYY-MM-DD	Date value
TIME	3	‘- 838 rip 59 purl 59 fret racing 838 rig 59 purl 59’	HH:MM:SS	Time value or duration
YEAR	1	1901/2155	YYYY	Year value
DATETIME	8	From 1000-01-01 00 to ‘9999-12-31, 2323 and 59’	YYYY-MM-DD hh:mm:ss	Mixed date and time valu
TIMESTAMP	4	1970-01-01 00 00 01’ UTC to ‘2038-01-19 03 15 15 7’ UTC The end time is the 2147483647 second, Beijing time 2038-1-19 11:14:07 Beijing time, 03:14:07 GMT on January 19, 2038	YYYY-MM-DD hh:mm:ss	Mixed date and time values, timestamp

String type ¶

String types refer to CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM and SET. This section describes how these types work and how to use them in queries.

Types	Size	Use
CHAR	0-255 bytes	Fixed length string
VARCHAR	0-65535 bytes	Variable length string
TINYBLOB	0-255 bytes	A binary string of not more than 255 characters
TINYTEXT	0-255 bytes	Short text string
BLOB	0-65,535 bytes	Long text data 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
LONGBLOB	0-4 294 967 295 bytes	Extremely large text data in binary form
LONGTEXT	0-4 294 967 295 bytes	Extremely large text data

注意 N in parentheses in char (n) and varchar (n) represents the number of characters, not the number of bytes. For example, CHAR (30) can store 30 characters.

CHAR and VARCHAR types are similar, but they are saved and retrieved differently. They are also different in terms of maximum length and whether trailing spaces are retained. No case conversion occurs during storage or retrieval.

BINARY and VARBINARY are similar to CHAR and VARCHAR, except that they contain binary strings rather than non-binary strings. That is, they contain byte strings instead of character strings. This means that they do not have a character set, and sort and compare numeric values based on column value bytes.

BLOB is a large binary object that can hold a variable amount of data. There are four types of BLOB: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. The difference between them is that they can accommodate different storage ranges.

There are four types of TEXT: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. The maximum length of the corresponding four BLOB types is different, which can be selected according to the actual situation.