MySQL Knowledge Tree-supported data types, mysql Data Types

MySQL Knowledge Tree-supported data types, mysql Data Types

The main content of this study note:

Describes various data types (commonly used) supported by MySQL and its main features.

 

MySQL supports multiple data types, including numeric, date and time, and string.

 

Value Type

MySQL numeric types include integer, floating point, point, and bit.

 

Integer type

MySQL supports tinyint, smallint, mediumint, int, and bigint integers (ranging from small to large ).

 

Zerofill

When defining the integer type, you can specify the display width in parentheses after the type name, for example, int (5). When the width of the inserted value is less than 5 characters, mySQL fills in the width before the value. If you do not manually specify the width of the int type, the default value is int (11 ).

The display width is generally used in combination with zerofill. That is, when the number of inserted values does not reach the specified display width, a few digits before the value will be filled with a few zeros.

Figure 1

Figure 1. We create table t_1 with two fields id1 and id2, both of which are int type. In id2, we specify the display width as 5, while id1 does not manually specify the display width. Therefore, the display width defaults to 11.

 

Figure 2

Figure 2: Insert a piece of data into the table and then query it. Although id1 and id2 both query 1, the display width is not specified during id1 definition, therefore, after the value 1 is inserted, the first 10 digits are filled with the width. Because id2 specifies the display width, only four digits are filled in front of the display width.

 

Figure 3

Figure 4

Figure 3 and figure 4 make a slight modification to the definition of id1 and id2 to display the filling width more intuitively, and use zerofill to fill the width.

 

Figure 5

Figure 5. After zerofill is used, we can see that the value is filled with a width of 0 before it. So can we get the result by using 1 or 00001 as the condition?

 

Figure 6

Figure 6 shows the corresponding id2 value when 1 or 00001 is used as the query condition. However, it should be noted that the actual storage value in MySQL is still 1, not 00001, because 00001 is not an integer representation, but a string representation, figure 7 below demonstrates this problem.

 

Figure 7

Figure 7: we use the hex () function for comparison during the query. We can see that the value obtained by using the hex () function is 1. If hex () the obtained value is 3030303031 (The hexadecimal value of string 1 is 31, and the hexadecimal value of string 0 is 30). Therefore, MySQL certainly stores the value in the form of a 00001 string, but obviously it is not.

 

Note: The hex () function can convert a number or string to a string in hexadecimal format.

 

When id2 is defined as int (5), what if the value of the display width is exceeded?

 

Figure 8

Figure 8. When a 6-digit value of 111111 is inserted into id2, MySQL does not report any error or truncate 111111. Therefore, it means that the display width does not limit the length of the inserted value, and there is no relationship between the two, unless the inserted value exceeds the data type range, as shown in figure 9.

 

Figure 9

 

Figure 9 shows that MySQL has a warning even if the insert operation is successful (when the SQL Mode of MySQL is in strict Mode, the insert operation cannot be completed, and MySQL reports an ERROR ), when we query the data, we can see that MySQL has intercepted the originally inserted data with a reserved value of 4294967295.

 

Note: The signed minimum value of the int data type is-2147483648, the maximum value is 2147483647, the unsigned minimum value is 0, and the maximum value is 4294967295.

 

Knowledge POint Description:

In fact, the display width is meaningful only when zerofill is used together. Otherwise, the display width can be set to the default value. Do not consider that specifying the display width affects the value range of the integer type. There is no relationship between the two, but that the value range of the integer type is only unsigned.

 

 

Unsigned

When zerofill is used to define the integer type, MySQL automatically adds unsigned to this column (after zerofill is added to columns in figure 3 and figure 4, check the DDL [database Definition Statement] of the table and you will find that the column has an unsigned value. For details, see Figure 10 ). This is because when zerofill is used, the value inserted into this column cannot be a negative number, so it is appropriate to automatically add unsigned, and unsigned also increases the value range of the maximum integer type.

 

Figure 10

 

 

Auto_increment

Another attribute of the integer type is auto_increment, which is unique to the integer type. Auto_increment is used to keep the column value increasing automatically. The auto_increment value starts from 1 by default. You can also manually set its initial value. When a null value is inserted to a column set to auto_increment, the actual inserted value is the current maximum value of the column plus 1 (null does not affect data insertion in the column set to auto_increment, columns will automatically grow normally ).

When a column is set to auto_increment, you usually need to set not null and primary key for this column (primary key, which is generally set to auto_increment) as the primary key, ).

Note that only one field in a table can be set to auto_increment.

 

 

Floating Point Number and fixed point number

Both are used to represent decimal places. Floating Point Numbers include float (single precision) and double (double Precision), and the number of points is only decimal. When defining the two, you can specify the precision and scale. The precision refers to the total number of digits displayed (integer + decimal place). The scale refers to the number of digits accurate to the decimal point. The format is as follows: decimal (), where the precision is 15 digits (integer 13 digits, decimal 2 digits), and the scale is 2 digits.

It should be noted that the number of fixed points is saved in MySQL in the form of a string, which is an accurate storage, but represents a decimal number, which is more accurate than a floating point number.

 

Figure 11

Figure 12

Figure 13

Figure 11: Create a table where the data type of the field id is decimal (5, 2). 12 when inserting a value that exceeds the scale in the table, although the insertion was successful, the data at the time of insertion was truncated. Here, the data is rounded down.

Figure 13 if we try to insert a value that exceeds the precision in the table, will it also be truncated and rounded down? Are the two values displayed as 123.12 and 124.12 respectively? Obviously not from the results, our guesses are completely false. When the precision is exceeded, although the insertion is successful, the inserted value is the maximum value under the specified precision and scale. For example, the maximum value under (5, 2) is 999.99.

In the SQL Mode strict Mode, these insert operations cannot be successfully executed and MySQL reports an ERROR.

 

Additional knowledge points:

The difference between single precision and Double Precision cannot be understood as that single precision is accurate to the first digit after the decimal point, while double precision is accurate to the second digit after the decimal point, which is obviously wrong. In fact, because float has 7 valid digits and double has 16 valid digits, single precision and double Precision actually refer to the valid digits here.

In addition, it should be noted that the number of valid digits is not equal to the exact number of digits. Even though float can represent 7 digits after the decimal point, only the first 6 digits are accurate, and 7th digits are likely to cause data errors. For double, only the first 15 digits are accurate, and 16th digits may also cause data errors.

 

Additional knowledge points:

The loss of float and double precision is actually extended or truncated because of inconsistent access time scales. When data is input, if the data scale is inconsistent with the scale set when defining the column data type, it will be stored with an approximate value during storage, this leads to the loss of precision we mentioned above.

Under what circumstances will float and double Precision be lost? In fact, based on the above problems, we can think that when the data scale is the same as the type scale (the data input scale is the same as the scale set when defining the column data type ), there will be no loss of precision.

In view of this, we often choose the decimal type. Data smaller than or equal to its scale can be correctly entered without loss of precision, because it stores data in the database as strings, this ensures accuracy. But it does not mean that decimal will not lose precision, although it will not have precision expansion, but it will have precision truncation. For example, when the scale of input data is greater than the scale set by the column data type, it is still rounded.

Although we say that decimal stores data in the database as strings, there will be a problem of precision truncation (rounding). It seems that there is a conflict between the two in terms of text description, but it is not. We can understand this as follows: decimal stores the rounded data into the database in the form of strings, but represents decimals (one is the storage form and the other is the representation form ), the precision of this decimal point will not change any more. No matter what precision is used to obtain this value, it is the value rounded up and saved as a string.

 

 

Bit type

BIT refers to bit, which is used to store binary data. BIT (1) indicates the binary data with a length of 1 bit.

Figure 14

Figure 15

We insert data that exceeds the number of digits into the table in Figure 14. From the second query result set in Figure 15, we can find that the data has been truncated. The binary value 2 is 10, and the binary value 3 is 11, their second digits are truncated.

In the first query result set in Figure 15, it must be noted that in the MySQL command line window, the data we need cannot be seen using select * from t_bit_test, you can only see two smiling faces. Since bit stores binary data, we use the bin () function to display them in binary format.

 

--------------------- To be continued ---------------------