MySQLWhat are the supported data types?MySQLDetails of data types in
MySQL data types
The types of data fields defined in MySQL are very important to the optimization of your database.
MySQL supports a variety of types, which can be roughly divided into three categories: numeric, date/time, and string (character) types.
Numeric type
MySQL databaseAll standard SQL numeric data types are supported.
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 holds bit field values and supports MyISAM, MEMORY, InnoDB, and BDB tables.
As an extension to the SQL standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and BIGINT.The table below shows the storage and range required for each integer type.
| Types of | 大小 | range (signed) | range (unsigned) | Use |
|---|---|---|---|---|
| TINYINT | 1 byte | (-128, 127) | (0, 255) | small integer value |
| SMALLINT | 2 byte | (-32 768, 32 767) | (0, 65 535) | large integer value |
| MEDIUMINT | 3 byte | (-8 388 608, 8 388 607) | (0, 16 777 215) | large integer value |
| INT or INTEGER | 4 byte | (-2 147 483 648, 2 147 483 647) | (0, 4 294 967 295) | large integer value |
| BIGINT | 8 byte | (-9 233 372 036 854 775 808, 9 223 372 036 854 775 807) | (0, 18 446 744 073 709 551 615) | very large integer value |
| FLOAT | 4 byte | (-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 byte | (-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 precision floating point value |
| DECIMAL | For DECIMAL(M,D), if M>D, it is M+2 else it is D+2 | depends on the values of M and D | depends on the values of M and D | decimal value |
date and time type
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 an invalid value that MySQL cannot represent.
The TIMESTAMP type has a proprietary auto-update feature that will be described later.
| Types of | 大小 (byte) | range | format | Use |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01 / 9999-12-31 | YYYY-MM-DD | date value |
| TEAM | 3 | ‘-838:59:59'/'838: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 | 4 | 1970-01-01 00:00:00/2037 年某时 | YYYYMMDDHHMMSS | 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 of | 大小 | Use |
|---|---|---|
| Char | 0-255 bytes | fixed-length string |
| VARCHAR | 0-65535 bytes | variable length string |
| TINYBLOB | 0-255 bytes | binary string of up to 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 |
| MEDIUM BLOB | 0-16 777 215 bytes | Medium-length text data in binary form |
| MEDIUM TEXT | 0-16 777 215 bytes | medium length text data |
| LONGBLOB | 0-4 294 967 295 bytes | Very large text data in binary form |
| LONGTEXT | 0-4 294 967 295 bytes | very large text data |
The CHAR and VARCHAR types are similar, but they are stored and retrieved differently.They also differ in terms of their maximum length and whether trailing spaces are preserved.No case conversion is done during storage or retrieval.
The BINARY and VARBINARY classes are similar to CHAR and VARCHAR, except that they contain binary strings instead of non-binary strings.That is, they contain byte strings instead of character strings.This means they have no character set, and sorting and comparison is based on the numeric values of the column value bytes.
A 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 just differ in the maximum length of the value they can hold.
There are 4 TEXT types: TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT.These correspond to the 4 BLOB types, with the same maximum length and storage requirements.
Hope Chen Weiliang Blog ( https://www.chenweiliang.com/ ) shared "What are the data types supported by MySQL? Detailed Explanation of Data Types in MySQL" to help you.
Welcome to share the link of this article:https://www.chenweiliang.com/cwl-466.html
Welcome to the Telegram channel of Chen Weiliang's blog to get the latest updates!
📚 This guide contains huge value, 🌟This is a rare opportunity, don’t miss it! ⏰⌛💨
Share and like if you like!
Your sharing and likes are our continuous motivation!