What are the data types of fields in mysql data table?

The data types of fields in the data table are: TINYINT, SMALLINT, MEDIUMINT, INT, INTEGE, DATETIME, DATE, TIMESTAMP, TIME, YEAR, CHAR, VARCHAR, TEXT, TINYBLOB, BLOB, etc.

#The types of data fields defined in MySQL are very important for the optimization of your database.

MySQL supports multiple types, which can be roughly divided into three categories: numerical, date/time and string (character) types. [Recommended tutorial: "mysql tutorial"]

Numeric 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 a synonym for INTEGER, and the keyword DEC is a synonym for DECIMAL.

The 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 types TINYINT, MEDIUMINT and BIGINT. The following table shows the storage and range required for each integer type.

##DECIMALFor DECIMAL(M,D), if M>D, it is M 2 otherwise it is D 2 Depends on the value of M and DDepends on the value of M and DDecimal value

Type	Size	Range (signed)	Range (unsigned)	Use
TINYINT	1 byte	(-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,16 777 215)	Large integer value
INT or INTEGER	4 bytes	( -2 147 483 648, 2 147 483 647)	(0, 4 294 967 295)	Big integer value
BIGINT	8 bytes	(-9,223,372,036,854,775,808, 9 223 372 036 854 775 807)	(0,18 446 744 073 709 551 615)	Maximum integer value
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)	Single 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 315 7 E 30 8)	Double precision Floating point value

Date and time types

The date and time types representing time values ​​are DATETIME, DATE, TIMESTAMP, TIME and YEAR.

Each time type has a valid value range and a "zero" value. The "zero" value is used when specifying an illegal value that MySQL cannot represent.

TIMESTAMP type has proprietary automatic update features, which will be described later.

TypeSizeRangeFormatPurpose DATE31000-01-01/9999-12-31YYYY-MM-DDDate valueTIME3'-838:59:59'/'838:59:59'HH:MM:SSTime value or durationYEAR11901/2155YYYYYear valueDATETIME81000-01-01 00:00:00 /9999-12-31 23:59:59YYYY-MM-DD HH:MM:SSMixed date and time values TIMESTAMP4YYYYMMDD HHMMSSMixed date and time value, timestamp

String type

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

	(bytes)
		1970-01-01 00:00:00/2038 The end time is the 2147483647 second, Beijing time 2038-1-19 11:14:07, January 19, 2038 03:14:07 AM GMT

##MEDIUMTEXT0-16 777 215 bytesMedium length text dataLONGBLOB0-4 294 967 295 bytesVery large text data in binary formLONGTEXT0-4 294 967 295 bytesVery large text data

Type	Size	Use
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	Binary form Long text data
TEXT	0-65 535 bytes	Long text data
MEDIUMBLOB	0-16 777 215 bytes	Medium-length text data in binary form

Note : char(n) and varchar(n) n in the square brackets 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 also differ in terms of their maximum length and whether trailing spaces are preserved. No case conversion is performed during storage or retrieval.

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

BLOB is a binary large object that can hold a variable amount of data. There are 4 BLOB types: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. They differ in the storage range they can accommodate.

There are 4 TEXT types: TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT. The corresponding four BLOB types have different maximum storage lengths, which can be selected according to the actual situation.

The above is the detailed content of What are the data types of fields in mysql data table?. For more information, please follow other related articles on the PHP Chinese website!