Three commonly used MySQL data types

Defining the type of data fields in MySQL is very important for optimizing your database.

MySQL supports a variety of types, which can be roughly divided into three categories: numeric, date/time, and string (character) types.

Numeric Types

MySQL supports all standard SQL numeric data types.

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 stores 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 following table shows the storage and range required for each integer type.

Date and time types

The 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 you specify an illegal value that MySQL cannot represent.

The TIMESTAMP type has a special automatic update feature, which will be described later.

String Type

String types include CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET. This section describes how these types work and how to use them in queries.

Notice:

The n in the brackets of char(n) and varchar(n) represents the number of characters, not the number of bytes. For example, CHAR(30) can store 30 characters.

The CHAR and VARCHAR types are similar, but they are stored and retrieved differently. They also differ in terms of maximum length and whether trailing spaces are preserved. No case conversion is performed during storage or retrieval.

BINARY and VARBINARY are similar to CHAR and VARCHAR, except that they contain binary strings rather than nonbinary strings. That is, they contain byte strings rather than character strings. This means that they have no character set, and sorting and comparison are based on the numeric value of the column value bytes.

A BLOB is a binary large object that can hold a variable amount of data. There are four BLOB types: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. The difference between them is the storage capacity.

There are 4 TEXT types: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. The corresponding 4 BLOB types have different maximum storage lengths, which can be selected according to actual conditions.

The above are the details of three commonly used MySQL data types. For more information about MySQL data types, please pay attention to other related articles on 123WORDPRESS.COM!