Shulou(Shulou.com)05/31 Report--

This article mainly introduces "which MySQL field types are divided into several categories". In the daily operation, I believe that many people have doubts about which types of MySQL field types are divided into. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "which types of MySQL field types are divided into?" Next, please follow the editor to study!

The commonly used field types can be roughly divided into three categories: numeric type, string type and date-time type.

1. Numerical type

Numerical types can be divided into three categories: integer type, floating point type and fixed point type.

Integers are mainly used to store integer values, and there are the following field types:

Integers are often used, such as tinyint, int, and 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, which does not mean that int (1) cannot store the value 10. No matter how many characters the display width is set, int occupies 4 bytes, that is, int (5) and int (10) can store the same range.

The smaller the storage bytes, the smaller the footprint. Therefore, in line with the principle of minimizing storage, we should try to select the appropriate integer, for example: to store some status values or people's age can use tinyint; primary key column, no negative number, it is recommended to use int unsigned or bigint unsigned, the numerical value of the estimated field will exceed 4.2 billion, and use the bigint type.

Floating-point type mainly has two float,double, floating-point type stores approximate values in the database, for example, float (6Power3). If you insert a number of 123.45678, what is stored in the actual database is 123.457, but the total number is still based on the actual number, that is, 6 digits, and the maximum integer part is 3 digits. Float and double don't usually use much.

DECIMAL is a fixed-point field type, 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 (MMagne D). NUMERIC is synonymous with DECIMAL, and if the field type is defined as NUMERIC, it is automatically converted to DECIMAL.

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

M is the maximum number of digits (precision), ranging from 1 to 65. Can not be specified, the default value is 10.

D is the number of places to the right of the decimal point (decimal places). The range is 0 to 30 and cannot be greater than M, but is not specified, and the default value is 0.

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

two。 String type

String types are also frequently used. Several commonly used types are shown in the following table:

Among them, char and varchar are the most commonly used. The char type is of fixed length, and MySQL always allocates enough space based on the defined string length. When saving char values, fill in blanks to the right of them to reach the specified length, and when the char value is retrieved, the trailing space is deleted. The varchar type is used to store variable-length strings and does not fill in spaces if the characters do not reach the defined number of digits.

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 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. Varchar is more appropriate for situations where the string is very long or the string to be stored varies in length.

When we define 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 we can use the varchar type. Unless there is a need to store long text data, consider using the text type.

BLOB type is mainly used to store binary large objects, such as pictures, audio and video and other files. It is rarely used every day, and you can consider using it when storing binary strings.

3. Date time type

The date and time types supported by MySQL are YEAR, TIME, DATE, DATETIME, and TIMESTAMP. The comparison of several types is as follows:

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

You can choose DATETIME and TIMESTAMP according to storage requirements. For example, if you require a wider storage range, it is recommended to use DATETIME. If you only store the current timestamp, you can use TIMESTAMP. It is worth noting, however, that the TIMESTAMP field data changes with the system time zone, but the DATETIME field data does not. In general, DATETIME is more widely used.

At this point, the study on "which MySQL field types are divided into several categories" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.