Shulou(Shulou.com)06/01 Report--

This article is about which SQL numeric data types MySQL supports. Xiao Bian thinks it is quite practical, so share it for everyone to make a reference. Let's follow the editor and have a look.

The types of data fields defined in MySQL are important for optimizing your database.

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

I. 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 synonymous with INTEGER and the keyword DEC is synonymous with DECIMAL.

BIT data types hold bit field values and support MyISAM, MEMORY, InnoDB, and BDB tables.

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

Type Size Range (Signed) Range (Unsigned) Purpose TINYINT1 byte (-128,127)(0,255) Small integer value SMALLINT2 bytes (-32 768，32 767)(0, 65 535) Large integer value MEDIUMINT3 bytes (-8 388 608，8 388 607)(0, 16 777 215) Large integer value INT or INTEGER4 bytes (-2 147 483 648，2 147 483 647)(0, 4 294 967 295) Large integer value BIGINT8 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 FLOAT4 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 DOUBLE8 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+308) Double

Floating-point value DECIMAL versus DECIMAL(M,D) M+2 if M>D otherwise D+2 depending on M and D values depending on M and D fractional values

II. Date and time types

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 value of "zero," which is used when specifying a value that illegal MySQL cannot represent.

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

type size

(Byte) Range Format Usage DATE31000-01-01/9999-12-31 Y-MM-DD Date Value TIME3'-838: 59:59'/'838:59:59'HH:MM:SS Time Value or Duration YEAR11901/2155YYYY Year Value DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59 Y-MM-DD HH:MM:SS Mixed Date and Time Value TIMESTAMP4

1970-01-01 00:00:00/2038

End time is 2147483647 seconds, Beijing time 2038-1-19 11:14:07, GMT January 19, 2038 03:14:07 am

YMMDD HHMMSS Mixed Date and Time Values, Timestamps

III. String types

String types are 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 CHAR0-255 bytes fixed-length string VARCHAR0-65535 bytes variable-length string TINYBLOB0- 255 bytes binary string of no more than 255 characters TINYTEXT0-255 bytes short-text string BLOB0- 65 535 bytes long text data in binary form TEXT0- 65 535 bytes long text data MEDIUMBLOB0-16 777 215 bytes medium-length text data in binary form MEDIUMTEXT0- 16 777 215 bytes medium length text data LONGBLOB0-4 294 967 295 bytes very large text data in binary form LONGTEXT0-4 294 967 295 bytes very large text data

CHAR and VARCHAR types are similar, but they are saved and retrieved differently. They also differ in maximum length and whether trailing spaces are preserved. Case conversion is not 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 they have no character set and sort and compare numeric values based on column value bytes.

BLOB is a binary large object that can hold variable amounts of data. There are 4 BLOB types: TINY BLOB, BLOB, MEDIUM BLOB and LONG BLOB. They differ in the range of storage they can accommodate.

There are four types of TEXT: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. Corresponding to these four BLOB types, the maximum length that can be stored is different, and can be selected according to the actual situation.

Thank you for reading! About MySQL support which SQL numerical data types to share here, I hope the above content can be of some help to everyone, so that everyone can learn more knowledge. If you think the article is good, you can share it so that more people can see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.