Data types in MySQL

Data types in MySQL

MySQL supports multiple data types, numeric types, date/time types, and string (character) types that are not case-sensitive. The various data types are explained in more detail below.

1. Data type Description conventions

Several data type descriptions use the following conventions, as shown in table 2-9.

Table 2-9 Data Type Description Management interpretation

Identifier name	With righteousness
M	Represents the maximum display width. Maximum effective display width is 255
D	Applies to floating-point and fixed-point types and represents the number of digits after the decimal point. The maximum possible value is 30, but should not be greater than m –2
Square brackets ('[' and '] ')	Represents an optional section

2. Numeric data types

MySQL supports all standard SQL numeric data types. These types include strict numeric data types (INTEGER, SMALLINT, Decimal, and NUMERIC), as well as 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. Detailed information is shown in table 2-10.

Table 2-10 numeric data types in MySQL

Data type name	Interpretation
bit[(M)]	Bit field type. m represents the number of bits per value, with a range of 1~64. If m is omitted, the default is 1
tinyint[(M)] [UNSIGNED] [Zerofill]	A small integer. The signed range is –128~127. The unsigned range is 0~255
BOOL,BOOLEAN	is a synonym for TINYINT (1). The zero value is considered false. Non- zero values are considered true
mediumint[(M)] [UNSIGNED] [Zerofill]	A medium-sized integer. The signed range is –8 388 608 to 8 388 607. The unsigned range is 0~7 2
int[(M)] [UNSIGNED] [Zerofill]	An integer of normal size. The signed range is –2 147 483 648~2 147 483647. The unsigned range is 0~4 294 967 295

Data type name	Interpretation
integer[(M)] [UNSIGNED] [Zerofill]	This is synonymous with int
bigint[(M)] [UNSIGNED] [Zerofill]	A large integer. The signed range is –9 223 372 036 854 775 808~9 223 372036 854 775 807. The unsigned range is 0~446 744 073 709 551 615
float[(m,d)] [UNSIGNED] [Zerofill]	Small (single-precision 3.402 823 466e+38~–1.175 494351e– 38, 0 and 1.175 494 351e–38~< Span lang= "en-US" >3.402 823 466e+38
double[(m,d)] [UNSIGNED] [Zerofill]	Normal-size (double ) floating-point number.  Allowable values are –1.797 693 134 862 3157e+308~–2.225 073 858 507 2014e–308,0 and 2.225 073858507 201 4e-308~ 1.797 693 134 862 315 7E+308

The above is not a complete list of MySQL numeric data types, please refer to the MySQL reference documentation for full MySQL numeric data type explanations.

3. Date and Time type

Each time type has a valid range of values and a value of "0", which is indicated by the "0" value when an illegal value is specified.
MySQL retrieves values for a given date or time type in the standard output format and tries to interpret the values entered by the user. The following are the valid formats supported by MySQL.

A date with a two-digit year value can be confusing because the century is unclear.
If the value is used in a numeric context, MySQL automatically converts the value of a date or time type to a number, and vice versa.
When MySQL encounters a date or time type that is out of range or is not a valid value for the type (as described at the beginning of this section), it converts the value to the "0" value of the class. One exception is that the time value out of range is cropped to the corresponding endpoint of the time range.

A datetime type represents a value that contains both date and time information. MySQL retrieves and displays the datetime values in the ' yyyy-mm-dd HH:MM:SS ' format. The range of support is ' 1000-01-01 00:00:00 ' ~ ' 9999-12-31 23:59:59 '.

The date type represents a value that has only a date value and no time value. MySQL retrieves and displays the date value in the ' YYYY-MM-DD ' format. The scope of support is ' 1000-01-01 ' ~ ' 9999-12-31 '.

MySQL retrieves and displays the time value in the ' HH:MM:SS ' format (or the ' HHH:MM:SS ' format for large values). The range of time values is ' –838:59:59 ' ~ ' 838:59:59 '. The reason the value of the hour part is large is that the time type can be used not only to represent a day (must be less than 24 hours), but also to indicate an event's past time or the interval between two events (which can be greater than 24 hours, or negative).

Time values can use a variety of presentation formats.

A string in the ' D HH:MM:SS.fraction ' format. You can also use any of the following "non-strict" syntax ' HH:MM:SS.fraction ', ' HH:MM:SS ', ' hh:mm ', ' d HH:MM:SS ', ' d ' hh:mm ', ' d HH ' or ' SS '. Here d is the day, you can take the value of 0~34. Please note that MySQL does not save fractions.
The ' HHMMSS ' format has no spacer string, which is assumed to be a meaningful time. For example, ' 101112 ' is understood as ' 10:11:12 ', but ' 109712 ' is illegal (it has a meaningless minute part) and it will become ' 00:00:00 '.
The value of the HHMMSS format is assumed to be a meaningful time. For example, 101112 is understood as ' 10:11:12 '. The following format can also be understood: SS, MMSS, HHMMSS, Hhmmss.fraction. Please note that MySQL does not save fractions.
The year type is a single-byte type used to represent years. MySQL retrieves and displays the year value in yyyy format. The range is 1901~2155.
You can specify year values in various formats.
A four-bit string with a range of ' 1901 ' ~ ' 2155 '.
A four-digit number with a range of 1901~2155.
A two-bit string with a range of ' 00 ' ~ ' 99 '. The values of ' 00 ' ~ ' 69 ' and ' 70 ' ~ ' 99 ' ranges are converted to the year values of the 2000~2069 and 1970~1999 ranges, respectively.
A two-bit integer with a range of 1~99. The values of the 1~69 and 70~99 ranges are converted to the year values of the 2001~2069 and 1970~1999 ranges, respectively. Note that the two-bit integer range differs slightly from the two-bit string range, because 0 cannot be specified directly as a number and interpreted as 2000. It must be specified as a string ' 0 ' or ' 00 ' or it is interpreted as 0000.

Attention:
The illegal year value is converted to 0000.

4. String type

String types include char, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, enum, and set.

The

 char and varchar types are similar, but they are saved and retrieved in different ways. Their maximum length and trailing spaces are also different in terms of retention. No case conversions are made during the storage or retrieval process. The length of the
 char and varchar type declarations represents the maximum number of characters to save. For example, a CHAR (30) can occupy 30 characters. The length of the
 char column is fixed to the length declared when the table was created. The length can be any value of 0~255. When you save a char value, a space is filled to the right of them to reach the specified length. When a char value is retrieved, the trailing space is removed. No case conversions are made during the storage or retrieval process. The value in the
 varchar column is a variable-length string. The length can be specified as a value of 0~65 535. (The maximum effective length of varchar is determined by the maximum row size and the character set used.) The overall maximum length is 65 532 bytes). In contrast to char, the varchar value is saved with only the required number of characters, plus one byte to record the length (two bytes if the column declaration is longer than 255). The varchar value is not populated when it is saved. The trailing spaces on the value save and retrieve are still preserved, conforming to standard SQL. The
 binary and varbinary data types differ from the CHAR binary and varchar binary data types. For the latter type, the binary property does not treat the column as a binary string column. Instead, it simply uses the two-dollar collation of the column character set, and the column itself contains a non-binary byte string instead of a binary byte string. For example char (5) binary is considered char (5) CHARACTER SET latin1 COLLATE latin1_bin,
 blob is a binary large object that can hold a variable amount of data. There are 4 types of blob, Tinyblob, Blob, Mediumblob, and Longblob. They can only accommodate the maximum length of a value differently. The
 text types are Tinytext, TEXT, Mediumtext, and LONGTEXT4. These correspond to 4 types of blobs, with the same maximum length and storage requirements.

Tinytext Maximum length is 255 (2^8-1) characters.

The TEXT maximum length is 65535 (2^16-1) characters.

Mediumtext Maximum length is 16777215 (2^24-1) characters.

Longtext Maximum length is 4294967295 (2^32-1) characters.

Data types in MySQL