MySQL Data Types

MySQL Data Types
MySQL Data Type

MySQL supports multiple data types, including numeric, date/time, and string.

1. Numeric data type: It can be integer TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT and DOUBLE, or DECIMAL.
2. DATE/TIME type: Includes YEAR, TIME, DATE, DATETIME, and TIMESTAMP.

3. String type: including CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET.

   1.1 Integer type

   Numeric data types are mainly used to store numbers. MYSQL provides multiple numeric data types. Different databases have different value ranges. A larger value range can be stored, the larger the control it stores. MySQL provides the following integer types: TINYTINT, SMALLINT, MEDIUMINT, INT, and BIGINT. You can add the AUTO_INCREMENT auto-increment constraint for fields of Integer type attributes. The following table lists the numeric types in MySQL.

Type name	Description	Storage Requirements
TINYINT	Small integer	1 byte
SMALLINT	Small integer	2 bytes
MEDIUMINT	An integer of medium size	3 bytes
INT	Integer of normal size	4 bytes
BIGINT	Big integer	8 bytes

From the table, we can see that the number of bytes required for different types of integer storage is different. The smallest number of bytes is TINYINT type, and the largest is BIGINT type, the larger the value range that can be expressed by the type that occupies more bytes. You can obtain the value range of each data type based on the number of bytes occupied. For example, TINYINT requires one byte (8 bits) for storage, the maximum value of the TINYINT unsigned number is 2 ^ 8-1, that is, 255. the maximum value of the TINYINT unsigned number is 2 ^ 7-1, that is, 127. Other types are shown in the following table:

Data Type	Signed	Unsigned
TINYINT	-128 ~ 127	0 ~ 255
SMALLINT	-32768 ~ 32767	0 ~ 65535
MEDIUMINT	-8388608 ~ 8388607	0 ~ 16777215
INT	-2147483648 ~ 2147483647	0 ~ 4294967295
BIGINT	-9223372036854775808 ~ 9223372036854775807	0 ~ 18446744073709551615

1.2 floating point type and fixed point type

MySQL uses floating point numbers and fixed points to represent decimals. There are two floating point types: FLOAT and DOUBLE ). There is only one fixed point type: DECIMAL. Both the floating point type and the fixed point type can be expressed by (M, N), where M is called precision, indicating the total number of digits; N is called scale, indicating the number of decimal digits. The following table lists the requirements for Fractional storage.

Type name	Description	Storage Requirements
FLOAT	Single-precision floating point number	4 bytes
DOUBLE	Double-precision floating point number	8 bytes
DECIMAL (M, D), DEC	Number of strictly compressed points	M + 2 bytes

DECIMAL is different from FLOAT and DOUBLE. DECIMAL is actually stored in strings. the maximum possible value range of DECIMAL is the same as that of DOUBLE, but its valid value range is determined by the values of M and D. If M is changed and D is fixed, the value range increases with M.

1.3 date/time type

MySQL has multiple data types that indicate DATE, including DATETIME, DATE, TIMESTAMP, TIME, and YEAR. The following table lists the MySQL date/time types:

Type name	Date Format	Date range	Storage Requirements
YEAR	YYYY	1901 ~ 2155	1 byte
TIME	HH: MM: SS	-838: 59: 59 ~ 838: 59: 59	3 bytes
DATE	YYYY-MM-DD	1000-01-01 ~ 9999-12-3	3 bytes
DATETIME	YYYY-MM-DD HH: MM: SS.	1000-01-01 00:00:00 ~ 23:59:59, 9999-12-31	8 bytes
TIMESTAMP	YYYY-MM-DD HH: MM: SS.	1970-01-01 UTC ~ 2038-01-19 03:14:07 UTC	4 bytes

1.4 string type

The string type is used to store string data. In addition to strings, it can also store other data, such as binary data of slices and sounds. Strings can be compared in case-insensitive or case-insensitive strings. In addition, pattern matching can be performed. In MySQL, string types include CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET. The following table lists the string data types.

Type name	Description	Storage Requirements
CHAR (M)	Non-binary string with Fixed Length	M bytes, 1 <= M <= 255
VARCHAR (M)	Variable Length non-binary string	L + 1 byte, where L <= M and 1 <= M <= 255
TINYTEXT	Very small non-binary string	L + 1 byte, where L <2 ^ 8
TEXT	Small non-binary string	L + 2 bytes, here L <2 ^ 16
MEDIUMTEXT	Medium-size non-binary string	L + 3 bytes, here L <2 ^ 32
LONGTEXT	Large non-binary string	L + 4 bytes, here L <2 ^ 24
ENUM	Enumeration type. Only one enumerated string value is allowed.	1 or 2 bytes, depending on the number of enumerated values (maximum 65535)
SET	One setting allows a String object to have 0 or more SET members.	1, 2, 3, 4, or 8 bytes, depending on the number of set members (up to 64 members)

1.5 binary type

MySQL supports two types of plain data: text strings and binary strings. MySQL stores BINARY string data types: BIT, BINARY, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. The following table lists the binary data types:

Type name	Description	Storage Requirements
BIT (M)	Bit Field Type	About (M + 7)/8 bytes
BINARY (M)	Fixed-length binary string	MB bytes
VARBINARY (M)	Variable-length binary string	M + 1 byte
TINYBLOB (M)	Very small BLOB	L + 1 byte, where L <2 ^ 8
BLOB (M)	Small BLOB	L + 2 bytes, here L <2 ^ 16
MEDIUMBLOB (M)	BLOB of medium size	L + 3 bytes, here L <2 ^ 24
LONGBLOB (M)	Very large BLOB	L + 4 bytes, here L <2 ^ 32

2. How to Select a data type

MySQL provides a large number of data types. In order to optimize storage and improve database performance, precise types should be used under any circumstances. That is, among all types that can represent the column value, this type uses the least storage.

2.1 integer and floating point number

If the decimal part is not required, an integer is used to save the data. If the decimal part is required, a floating point number is used to represent the data. For floating-point data columns, the saved values round the decimal places defined in the column. For example, if the value range of a column is 1-99999 and an integer is used, mediumint unsigned is the best type. If you want to store decimals, FLOAT is used.
FLOAT and DOUBLE types are supported. The precision of the DOUBLE type is higher than that of the FLOAT type. Therefore, if the storage precision is required to be higher, DOUBLE type should be selected.

2.2 floating point number and fixed point number

The advantage of FLOAT and DOUBLE over DECIMAL of a fixed point is that when the length is certain, floating point numbers can indicate a larger data range. However, since floating-point numbers are prone to errors, it is recommended that DECIMAL be used for storage when the precision requirement is high. DECIMAL is stored as a string in MySQL and is used to define data with high precision requirements, such as currencies. DECIMAL is stored as a string in MySQL. In data migration, float (M, D) is not defined as a standard SQL statement, and database migration may have problems. It is best not to use it like this. The other two floating point numbers are prone to problems when performing subtraction and comparison operations. Therefore, be careful when performing calculations. We recommend that you use the DECIMAL type for numerical comparison.

2.3 date/time type

MySQL has multiple data types for different types of date and TIME, such as YEAR and TIME. If you only need to record the YEAR, you can use YEAR. If you only record the TIME, the TIME type is used.
If you want to record both the date and time, you can use the TIMESTAMP or DATETIME type. Since the value range of the TIMESTAMP column is less than the value range of DATETIME, it is best to use DATETIME for dates with a large storage range.
TIMESTAMP also has an attribute not available in DATETIME. By default, when a record is inserted but the TIMESTAMP value is not specified, MySQL sets the TIMESTAMP column to the current time. Therefore, it is convenient to use TIMESTAMP when inserting records at the same time. In addition, TIMESTAMP is more effective than DATETIME in space.

2.4 features and selection between CHAR and VARCHAR

Differences between CHAR and VARCHAR:
CHAR is a fixed-length character, VARCHAR is a variable-length character, CHAR automatically deletes the trailing space of the inserted data, VARCHAR does not delete the trailing space.
CHAR is a fixed length, so it processes faster than VARCHAR, but its disadvantage is that it wastes storage space. Therefore, it is not suitable for storage, but the CHAR type can be used for speed requirements. Otherwise, the VARCHAR type can be used for implementation.

Impact of storage engine on CHAR and VARCHAR:

For the MyISAM storage engine, it is best to use a fixed-length data column instead of a variable-length data column. In this way, the entire table can be static, so that data retrieval is faster, and the storage space is used for query time.
For the InnoDB Storage engine: a variable-length data column is used. Because the storage format of the InnoDB data table is not fixed or variable-length, CHAR is not necessarily better than VARCHAR, however, because VARCHAR is stored according to the actual storage length, it saves space, so the total disk I/O and data storage is better.

2.5 ENUM and SET

ENUM can only obtain a single value. Its data column represents an enumeration set. Its valid value list can contain a maximum of 65535 members. Therefore, you can use ENUM to select one from multiple values. For example, the gender field is suitable for defining the ENUM type. Each time, only one value can be obtained from 'male' or 'femal.
SET can be multiple values. Its valid value list allows a maximum of 64 members. A null string is also a valid SET value. When multiple values are required, the SET type is suitable. For example, to store a hobby, it is best to use the SET type.
The values of ENUM and SET appear as strings, but MySQL stores them as numbers internally.

2.6 BLOB and TEXT

BLOB is a binary string, TEXT is a non-binary string, both of which can store large amounts of information. BLOB mainly stores images and audio information, while TEXT can only store plain TEXT files. The storage relationship between the two should be distinguished.