The most complete analysis: MySQL field types

This article brings you relevant knowledge about mysql field types and makes a summary. I hope it will be helpful to everyone.

Foreword:

To understand a database, we must understand the data types it supports. MySQL supports a large number of field types, many of which are commonly used. We have also talked about the usage of int and varchar types in previous articles, but we have never fully talked about field types. In this article, we will cover all field types and talk about the usage of common field types.

Commonly used field types can be roughly divided into three categories: Numeric type, String type, Date time type. Below we classify Let’s introduce them in turn.

1. Numerical type

The main categories of numerical types can be divided into integer type,floating point type,fixed point typeThree small categories.

Integers are mainly used to store integer values. There are mainly the following field types:

Integers are often used, such as tinyint, int, bigint. The default is signed. If you only need to store unsigned values, you can add the unsigned attribute.

The M in int(M) represents the maximum display width. It does not mean that int(1) cannot store the value 10. No matter how many characters the display width is set, int will occupy 4 characters. section, that is, the storage range of int(5) and int(10) is the same.

The smaller the storage bytes, the smaller the space occupied. Therefore, in line with the principle of minimizing storage, we should try to choose the appropriate integer type. For example, tinyint can be used to store some status values ​​or people's ages; for primary key columns, there are no negative numbers. It is recommended to use int unsigned or bigint unsigned to estimate field numbers. The value will exceed 4.2 billion, using the bigint type.

Floating point types mainly include float and double. Floating point types store approximate values ​​in the database, such as float(6,3). If you insert a number 123.45678, the actual value stored in the database is 123.457. However, the total number is subject to actual conditions, that is, 6 digits, and the maximum integer part is 3 digits. float and double are not commonly used.

#There is one fixed-point field type: DECIMAL, which is mainly used to store decimals with precision requirements.

DECIMAL was introduced from MySQL 5.1, and the column declaration syntax is DECIMAL(M,D). NUMERIC is synonymous with DECIMAL. If the field type is defined as NUMERIC, it will be automatically converted to DECIMAL.

For the declaration syntax DECIMAL(M,D), the value range of the argument is as follows:

• M is the maximum number of digits (precision), and the range is 1 to 65. Can not be specified, the default value is 10.
• D is the number of digits to the right of the decimal point (decimal places). The range is 0 to 30, and cannot be larger than M. It does not need to be specified. The default value is 0.

For example, the field salary DECIMAL(5,2) can store any value with five digits and two decimal places, so the range of values ​​that can be stored in the salary column is from -999.99 to 999.99 .

2. String type

The string type is also often used. The commonly used types are as follows:

where char and varchar are the most commonly used. The char type is fixed-length, and MySQL always allocates enough space according to the defined string length. When char values ​​are saved, they are padded to the right with spaces to the specified length, and when char values ​​are retrieved, trailing spaces are removed. The varchar type is used to store variable-length strings. During storage, if the characters do not reach the defined number of digits, spaces will not be added at the end.

The M in char(M) and varchar(M) represents the maximum number of characters saved. A single letter, number, Chinese, etc. all occupy one character. char is suitable for storing very short strings, or when all values ​​are close to the same length. For example, char is ideal for storing the MD5 value of a password because it is a fixed-length value. For situations where the string is very long or the strings to be stored are of different lengths, varchar is more suitable.

When defining the maximum length of a field, we should allocate it as needed, make an estimate in advance, and try not to use the text type if the varchar type can be used. Unless there is a need to store long text data, consider using the text type.

The BLOB type is mainly used to store binary large objects, such as pictures, audio and video files. Rarely used in daily life, you can consider using it when storing binary strings.

3. Date and time types

The date and time types supported by MySQL are YEAR, TIME, DATE,DATETIME, TIMESTAMP, several types are compared as follows:

When it comes to date and time field type selection, just select the appropriate type according to storage requirements.

How to choose between DATETIME and TIMESTAMP types can be based on storage requirements. For example, if a wider storage range is required, it is recommended to use DATETIME. If you only store the current timestamp, you can use the TIMESTAMP type. However, it is worth noting that TIMESTAMP field data will change with the system time zone but DATETIME field data will not. In general, DATETIME is more widely used.

Summary:

This article mainly introduces the commonly used field types in MySQL. The field types commonly used are basically here, with a thinking guide The figure is summarized as follows:

Recommended learning: mysql video tutorial

The above is the detailed content of The most complete analysis: MySQL field types. For more information, please follow other related articles on the PHP Chinese website!