In-depth understanding of four common and important mysql data types-MySQL

To achieve this goal, we need to fully understand the four common and important data types of mysql.

Next, let's take a look at the important data types of mysql.

1. the numeric type can be divided into integer and floating point decimal types.

The so-called "decimal" refers to decimal and numeric. they are of the same type. Strictly speaking, it is not a numeric type, because they are actually stored as strings, and each numeric value (including the decimal point) occupies a byte storage space, therefore, this type consumes a lot of storage space, but its advantage is that its value will not lose the floating point computing accuracy, it is more suitable for some high computing accuracy requirements, such as price calculation. Float depends on the type of precision, which can be float or double. Their advantages are decimal precision. FLOAT can represent very small values, which can be a minimum value of about 1.17E-38 (0.000... 0117, 37 after the decimal point), can express a smaller number, the minimum number can be about 2.22E-308 (0.000... 0222, decimal point and decimal point followed by 307 zeros. Float and double are 4-byte buckets and 8-byte buckets respectively.

For integer types, there are many different types of integers in MySQL. in the design database table, we can have a byte TINYINT or 8-byte BIGINT, so we should consider which type to use too much, to obtain the minimum storage space without losing any accuracy value.

TINYINT, SMALLINT, MEDIUMINT, INT and BIGINT are 1-byte, 2-byte, 3-byte, 4-byte, and 8-byte. For unsigned integers, the maximum integers of these types are 255, 65535, 16777215,4294967295 18446744073709551615. If we need to save the user's age, TINYINT is enough; if it is an auto-increment ID, we should use MEDIUMINT instead of INT, INT is too big. Many data tables do not reach the MEDIUMINT range.

2. the date and time types entered by date and time are relatively simple.

Such as DATE, TIME, DATETIME, TIMESTAMP, and YEAR. If we only need to care about the Date, but there is no minute or second, we should use Date instead of DATETIME, but DATETIME is the most commonly used one. everything is designed as needed

3. do not assume that the character type is only CHAR

The difference between CHAR and VARCHAR is that CHAR is of a fixed length. If you define a field CHAR (10), then no matter how many bytes of data, this will require 10 bytes of space; for 18-bit ID card numbers, char (18 ),

VARCHAR is variable length. if the value of a field has different lengths, we should use VARCHAR.

4. enumeration and set type enumeration (ENUM)

You can select a maximum of 65,535 different strings.

This may depend on the number of values in one or two bytes of the enumeration type. SET type. a SET can contain up to 64 different members. you can select zero or multiple members. the number of SET members is determined. For example, in SQLServer, you can use the BIT type to indicate gender (male/female), but in MySQL, BIT values are different in different database versions, TINTINT is sometimes a waste. you can use ENUM ('male', 'female ') to save a lot of space.