MySQL Data Type

MySQL Data Type

The data field type defined in MySQL is very important for your database optimization.

MySQL supports multiple types, which can be roughly divided into three types: numerical value, date/time, and string (character) type.

  Value 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 the synonym of INTEGER, And the keyword DEC is the synonym of DECIMAL.

BIT Data Type stores BIT field values and supports MyISAM, MEMORY, InnoDB, and BDB tables.

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

Type	Size	Range (Signed)	Range (unsigned)	Purpose
TINYINT	1 byte	(-128,127)	(0,255)	Small integer
SMALLINT	2 bytes	(-32 767)	(535)	Large integer
MEDIUMINT	3 bytes	(-8 388 388 607)	(777 215)	Large integer
INT or INTEGER	4 bytes	(-2 147 483 147 483, 2 647)	(294 967 295)	Large integer
BIGINT	8 bytes	(-9 233 372 036 854 775 223 372 854 775 807)	(446 744 709 073 551 615)	Maximum integer
FLOAT	4 bytes	(-3.402 823 466 E + 38, 1.175 494 351 E-38), 0, (1.175 494 351 E-38, 3.402 823 466 351 E + 38)	0, (1.175 494 351 E-38, 3.402 823 466 E + 38)	Precision Floating point value
DOUBLE	8 bytes	(1.797 693 134 862 315 7 E + 308, 2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E + 308)	0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 7 E + 315)	Double Precision Floating point value
DECIMAL	For DECIMAL (M, D), if M> D is M + 2, otherwise D + 2	Values dependent on M and D	Values dependent on M and D	Small value

  Date and Time Type

The DATE and TIME types of the TIME value are DATETIME, DATE, TIMESTAMP, TIME, and YEAR.

Each time type has a valid value range and a "zero" value. It is used when an invalid MySQL value cannot be expressed.

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

Type	Size (Bytes)	Range	Format	Purpose
DATE	3	April 1000-01-01/9999-12-31	YYYY-MM-DD	Date Value
TIME	3	'-838: 59: 59'/'2014: 59: 59'	HH: MM: SS	Time Value or duration
YEAR	1	1901/2155	YYYY	Year Value
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH: MM: SS.	Mixed Date and Time values
TIMESTAMP	8	2037 00:00:00/	YYYYMMDD HHMMSS	Mixed Date and Time Value, timestamp

  String type

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

Type	Size	Purpose
CHAR	0-255 bytes	Fixed Length string
VARCHAR	0-65535 bytes	Variable-length string
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 data in binary format
TEXT	0-65 535 bytes	Long text data
MEDIUMBLOB	0-16 777 215 bytes	Moderate-length text data in binary format
MEDIUMTEXT	0-16 777 215 bytes	Moderate-length text data
LOGNGBLOB	0-4 294 967 295 bytes	Extremely large text data in binary form
LONGTEXT	0-4 294 967 295 bytes	Large text data

CHAR and VARCHAR types are similar, but they are stored and retrieved in different ways. Their maximum length and whether the trailing space is retained are also different. Case-insensitive conversion is not performed during storage or retrieval.

The BINARY and VARBINARY classes are similar to CHAR and VARCHAR. The difference is that they contain BINARY strings instead of BINARY strings. That is, they contain byte strings instead of character strings. This indicates that they do not have character sets and sort and compare the value values based on the column value byte.

BLOB is a large binary object that can hold a variable amount of data. There are four BLOB types: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. They only have different maximum lengths that can hold values.

There are four types of TEXT: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to four BLOB types, with the same maximum length and storage requirements.

Address: http://www.manongjc.com/mysql/mysql_data_types.html

Related reading:

In-depth study on the performance and efficiency of mysql exists and in

MySQL exists and in usage and differences

Mysql subquery exists and not exists usage and instance

How to Use the mysql where statement

Mysql distinct usage and instance Introduction