Preface:

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 covered the use of int and vARCHar types, but we have not covered field types completely. In this article, we will cover the use of common field types.

Commonly used field types can be roughly divided into three categories: numeric type, string type, date and time type. The following categories are introduced in sequence.

1. Value type

The numerical type category can be divided into integer type, floating point type, fixed point type three small classes.

The integer type is mainly used to store integer values. It has the following field types:

Integers are often used, such as tinyint, int, bigint. The default is signed, and you can add an unsigned attribute if you want to store only unsigned values.

The M in int(M) represents the maximum display width. This does not mean that int(1) cannot store the value 10, regardless of how many characters the display width is set, int(5) and int(10) can store the same range.

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: to store some status values or the age of the person can use tinyint; It is recommended to use int unsigned or BigInt unsigned. It is estimated that the value of the field will exceed 4.2 billion. Use bigINT.

Float (6,3); float(6,3); float(6,3); float(6,3); float(6,3); Float and double are not used very often.

The fixed-point field type has a DECIMAL, which is mainly used to store decimals with precision requirements.

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

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

  • M is the largest digit (precision), ranging from 1 to 65. This parameter is optional. The default value is 10.
  • D is the number of digits to the right of the decimal point. The value ranges from 0 to 30 and cannot be greater than M. The default value is 0.

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

2. The value is a string

The string type is also commonly used.

Char and varchar are the most commonly used. The char type is fixed length, MySQL always allocates enough space based on the length of the string defined. When char values are saved, Spaces are padded to the right of them to the specified length, and trailing Spaces are removed when char values are retrieved. The vARCHAR type is used to store variable-length strings that are not followed by a space if the character does not reach the specified number of digits.

The M in char(M) and varchar(M) indicates the maximum number of characters that can be saved. Each letter, digit, and Chinese character occupies one character. Char is good for storing very short strings, or all values close to the same length. For example, char is good for storing the MD5 value of a password because it is a fixed-length value. Varchar is more suitable for long strings or for strings of different sizes to be stored.

When we define the maximum field length, we should allocate it according to need, make a good estimate in advance, and try not to use text type when possible. Use text only when you need to store long text data.

The BLOB type is used to store large binary objects, such as images, audio and video files. It is rarely used in daily use, but can be considered when storing binary strings.

3. Date and time type

MySQL supports the following DATE and TIME types: YEAR, TIME, DATE, DATETIME, TIMESTAMP

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

DATETIME and TIMESTAMP can be selected based on storage requirements. For example, DATETIME is recommended if the storage range is wider. If only the current TIMESTAMP is stored, TIMESTAMP is recommended. However, it is important to note that the TIMESTAMP field data changes with the system time zone but the DATETIME field data does not. DATETIME is more widely used in general.

Conclusion:

This article mainly introduces the common types of fields in MySQL, usually used in the basic types of fields here, with a mind map summary as follows: