MySQL Data Types

The following sections describe the data types supported by MySQL.

MySQL data types

MySQL supports three types of data types:Strings,NumbersandDate/Timedata type.

String data type

String data types are commonly used to store names, addresses, descriptions, or any values containing letters and numbers, including binary data, such as images or audio files.

CHAR and VARCHAR types

CHAR data type allows storing up to255characters for fixed-length strings. The VARCHAR data type allows storing up to65535characters for variable-length strings (in MySQL 5.0.3was previously limited to255characters).

CHAR and VARCHAR data type declarations indicate the maximum number of characters that can be stored. For example, CHAR5) can accommodate up to5characters.

The main difference between CHAR and VARCHAR data types is the way they store data. When values are stored in a CHAR column, they are right-padded with spaces to the specified length, but no padding is performed when storing values in a VARCHAR column. This means that if the value 'ab' is stored as CHAR, it can accommodate up to4) stored in VARCHAR(4) in the column, then the value will be stored as 'ab', while the same value will be stored as 'ab'.

BINARY and VARBINARY types

BINARY and VARBINARY types are similar to CHAR and VARCHAR, but the difference is that they contain binary strings instead of non-binary strings. 　 The maximum allowed length of BINARY and VARBINARY is the same as that of CHAR and VARCHAR, but the difference is that the length of BINARY and VARBINARY is in bytes, not in characters.

TEXT and BLOB types

TEXT and BLOB data types are specifically used to save large amounts of data. TEXT data type is used to store long strings, such as descriptions, blog comments, etc.

BLOB is a binary large object that can contain a variable amount of data. This feature is particularly useful when you need to store binary media files in a database (such as image or audio files).

The four BLOB types are TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. The difference between them lies only in the maximum length of the values they can contain. Similarly, the four text types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to the four BLOB types, with the same maximum length and storage requirements.

The following table lists paired MySQL string data types. Regardless of whether the type is binary or non-binary, the maximum length is in bytes.

ENUM type

ENUM data type allows you to specify a list of possible values that can be stored in a column. For example, specify gender ENUM ('male', 'female') NOT NULL columns can have any of the following values: '', 'male', or 'female'. You can specify up to65535different values. If an invalid value is inserted into an ENUM column (i.e., a string that does not exist in the allowed value list), an empty string will be inserted.

SET type

SET data type allows specifying a list of values to be inserted into the column, such as ENUM. However, unlike the ENUM data type, which allows only one value to be selected, the SET data type allows multiple values to be selected from the specified value list.

For example, a column specified as option SET('one', 'two') NOT NULL can store the following values: 'one', 'two', or 'one,two'. Multiple values separated by commas (,) can be specified. For the SET data type, up to64different values.

numerical data types

numerical data types are commonly used to store data such as prices, salaries, etc.

INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT types

MySQL supports SQL standard integer types INTEGER (or INT) and SMALLINT. MySQL also supports integer types TINYINT, MEDIUMINT, and BIGINT as extensions to the SQL standard. The following table shows the range of each integer type.

numerical data types have additional attributes of SIGNED (signed) and UNSIGNED (unsigned). Numerical data types are signed by default, ranging from negative to positive. Adding the UNSIGNED attribute to a numerical column does not allow the use of negative numbers and shifts the range up, starting from 0 instead of a negative number.

DECIMAL, NUMERIC type

DECIMAL and NUMERIC data types are used to store accurate numerical values. These data types are also known as 'fixed-point' or 'exact value' types and are typically used when precise accuracy is required, such as storing monetary data like product prices. In MySQL, NUMERIC is implemented as DECIMAL.

When declaring DECIMAL or NUMERIC columns, you can specify precision and scale, for example DECIMAL(P, S) or NUMERIC(P, S), where P is the precision and S is the scale. precision specifies the maximum number of digits that can be stored in the column (including the number of digits after the decimal point), while the number of decimal places indicates the number of digits that can be stored after the decimal point. For example, the price DECIMAL(62column can store any value with six digits and two decimal places, that is-9999.99to9999.99values.

FLOAT, DOUBLE type

FLOAT and DOUBLE types represent approximate numerical data values. This is why these data types are also called 'floating-point' or 'approximate' types

MySQL supports syntax: FLOAT(M, D) or DOUBLE PRECISION(M, D). 73,-9999.999“” 　 MySQL rounds off values when storing them, so if you convert9999.0009Insert FLOAT (73“) column9999.001“”

Date (DATE) and Time (TIME) data types

Date and time data types are usually used to store data such as birth date, employment date, and the date and time when a record is created or updated in a table, etc.

DATE type

The DATE data type is used to store dates. MySQL DATE stores and retrieves values in 'YYYY-MM- 1000-01-01, 9999-12-31“”

Time (TIME) type

The TIME data type can be used to store days or time intervals. MySQL TIME stores and retrieves values in the following 'HH:MM:SS' format, where HH, MM, and SS are the hour, minute, and second parts of the time (or 'HHH:MM:SS' for the format of hours). The supported TIME value range is '-8385959” to '8385959”。

The hour part may be very large because in MySQL, the TIME type can not only be used to store time in a day (which must be less than24hours), and can also be used for elapsed time or the time interval between two events (which may be much greater than24hours, even negative ones).

DATETIME and TIMESTAMP types

DATETIME and TIMESTAMP data types are used to represent 'YYYY-MM-DD HH:MM:SS' format to store combined date and time values. These data types are usually used to store dates and times when distribution orders are issued, when a row is created or modified in a table, and so on.

These two data types are similar in many aspects, but there are some differences. DATETIME supports a range of '1000-01-01 00:00:00' to '9999-12-31 23:59:59However, the timestamp range is '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

TIMESTAMP and DATETIME also have special automatic initialization and automatic update properties, but these properties do not apply to MySQL 5.6.5previous DATETIMEM.

Year (YEAR) Type

YEAR data type is used to store four-digit year values in YYYY format.

It can be declared as YEAR or YEAR(4) Supported YEAR value range is1901to2155. Invalid YEAR values will be converted to 0000.