MySQL field data type MySQL has three types of columns: numbers, strings, and dates. Numeric type
The number column type is used to store various numeric data, such as price, age, or quantity. There are two types of numeric columns: integer and floating point. All numeric column types can have two options: UNSIGNED and ZEROFILL. UNSIGNED columns cannot have negative numbers. if ZEROFILL is selected, zero is added to the values. The following figure shows the available numeric column types in MySQL.
- TINYINT -- a tiny integer that supports-128 to 127 (SIGNED), 0 to 255 (UNSIGNED), and needs to be stored in 1 byte
- BIT -- same as TINYINT (1)
- BOOL -- same as TINYINT (1)
- SMALLINT -- a small integer, supporting-32768 to 32767 (SIGNED), 0 to 65535 (UNSIGNED), requiring 2 bytes to store MEDIUMINT -- a medium integer, supports-8388608 to 8388607 (SIGNED), 0 to 16777215 (UNSIGNED), and three bytes are required for storage.
- INT -- an integer that supports-2147493648 to 2147493647 (SIGNED), 0 to 4294967295 (UNSIGNED), and needs to be stored in 4 bytes
- INTEGER -- same as INT
- BIGINT -- a large integer that supports-9223372036854775808 to 9223372036854775807 (SIGNED), 0 to 18446744073709551615 (UNSIGNED), and needs to be stored in 8 bytes
- FLOAT (precision) -- A floating point number. Precision <= 24 is used for single-precision floating point; precision is between 25 and 53, and is used for precision floating point. The FLOAT (X) has the same range as the FLOAT and DOUBLE types of the phase sequence, but the display size and decimal places are not defined. Before MySQL3.23, this is not a real floating point value and always has two decimal places. All the calculations in MySQL use dual precision, which may cause unexpected problems.
- FLOAT -- a small menu precision floating point number. Supports-3.402823466E + 38 to-1.175494351E-38,0 and 1.175494351E-38 to 3.402823466E + 38, which requires 4 bytes of storage. If it is UNSIGNED, the range of positive numbers remains unchanged, but negative numbers are not allowed.
- DOUBLE-a double-precision floating point number. Supports-1.7976931348623157E + 308 to-2.225074255072014e-308,0 and 2.225071095072014e-308 to 1.7976931348623157E + 308. If it is FLOAT, UNSIGNED will not change the positive number range, but the negative number is not allowed.
- Double precision -- same as DOUBLE
- REAL -- same as DOUBLE
- DECIMAL -- stores a number as a string, and each character occupies one byte
- DEC -- same as DECIMAL
- NUMERIC -- same as DECIMAL
String column type
The string column type is used to store character data of any type, such as name, address, or newspaper. Below are available string column types in MySQL
- CHAR -- character. A string of fixed length. fill in spaces on the right to reach the specified length. It can be 0 to 155 characters in length. When you search for a value, spaces with the suffix will be deleted.
- VARCHAR -- Variable length character. A variable-length string with the suffix space removed when storing the value. Supports from 0 to 255 characters
- TINYBLOB -- tiny binary object. 255 characters are supported. Storage with a length of more than 1 byte is required. Like TINYTEXT, the search is case sensitive. (0.25KB)
- TINYTEXT -- supports 255 characters. The length must be + 1 byte. Like TINYBLOB, the case sensitivity is ignored during search. (0.25KB)
- BLOB-binary object. 65535 characters are supported. Storage with a length of + 2 bytes is required. (64 KB)
- TEXT -- supports 65535 characters. Requires length + 2 bytes of storage. (64 KB)
- MEDIUMBLOB -- a binary object of medium size. 16777215 characters are supported. Storage with a length of + 3 bytes is required. (16 M)
- MEDIUMTEXT-supports 16777215 characters. Storage with a length of + 3 bytes is required. (16 M)
- LONGBLOB -- a large binary object. 4294967295 characters are supported. Storage with a length of + 4 bytes is required. (4G)
- LONGTEXT -- supports 4294967295 characters. Storage with a length of + 4 bytes is required. (4G)
- ENUM -- enumeration. There can be only one specified value, that is, NULL or "", with a maximum of 65535 values
- SET -- a SET. There can be 0 to 64 values, all from the specified list
Date and Time column types
The date and time column types are used to process time data. data such as the date of the current day or the date of birth can be stored. Format: Y indicates year, M (before M) indicates month, D Indicates day, H indicates hour, M (after M) indicates minute, S indicates second. The following are available date and time column types in MySQL:
- DATETIME -- Format: 'yyyy-MM-DD HH: MM: SS', range: '2017-01-01 00:00:00 'to '2017-12-31 23:59:59 ′
- DATE -- Format: 'yyyy-MM-DD ', range: '2017-01-01' to '2017-12-31 ′
- TIMESTAMP -- Format: 'yyyymmddhhmms', 'yymmddhhmms', 'yyyymmdd', and 'yymmdd'; range: '2017-01-01 00:00:00 'to '2017-01-01 00:00:00 ′
- TIME -- Format: 'hh: MM: SS'
- YEAR -- Format: 'yyyy, range: '000000' to '000000 ′
Numeric type
| Column type |
Required storage capacity |
TINYINT |
1 byte |
SMALLINT |
2 bytes |
MEDIUMINT |
3 bytes |
INT |
4 bytes |
INTEGER |
4 bytes |
BIGINT |
8 bytes |
FLOAT(X) |
4 If X <= 24 or 8 if 25 <= X <= 53 |
FLOAT |
4 bytes |
DOUBLE |
8 bytes |
DOUBLE PRECISION |
8 bytes |
REAL |
8 bytes |
DECIMAL(M,D) |
MByte (D+ 2, ifM < D) |
NUMERIC(M,D) |
MByte (D+ 2, ifM < D) |
Date and time type
| Column type |
Required storage capacity |
DATE |
3 bytes |
DATETIME |
8 bytes |
TIMESTAMP |
4 bytes |
TIME |
3 bytes |
YEAR |
1 byte |
String type
| Column type |
Required storage capacity |
CHAR(M) |
MByte,1 <= M <= 255 |
VARCHAR(M) |
L+ 1 byte, in thisL <= MAnd1 <= M <= 255 |
TINYBLOB,TINYTEXT |
L+ 1 byte, in thisL<2 ^ 8 |
BLOB,TEXT |
L+ 2 bytes.L<2 ^ 16 |
MEDIUMBLOB,MEDIUMTEXT |
L+ 3 bytes.L<2 ^ 24 |
LONGBLOB,LONGTEXT |
L+ 4 bytes.L<2 ^ 32 |
ENUM('value1','value2',...) |
1 or 2 bytes, depending on the number of enumerated values (maximum 65535) |
SET('value1','value2',...) |
1, 2, 3, 4, or 8 bytes, depending on the number of set members (up to 64 members) |
