MySQL knowledge tree-supported data types

The main contents of this study note:

Introduction MySQL supports a variety of data types (common) and explains its main features.

MySQL supports a variety of data types, mainly including numeric types, date and time types, and string types.

Numeric type

MySQL 's numeric types include integer type, floating-point type, fixed-point number type, and bit type.

Integer type

MySQL supports integer types such as tinyint,smallint,mediumint,int ,bigint(range from small to large).

Zerofill

When defining an integer type, we can specify the display width within the parentheses after the type name, for example , int (5), and when the width of the inserted value is less than 5 bits,theMySQL The width is filled in front of the value. for int types, if you do not specify a width manually, the default is int (one).

The display width is generally used in conjunction with the Zerofill , that is, when the number of digits inserted does not reach the specified display width, the missing few will fill a few 0 before the value.

Figure 1

Figure 1, we create the tablet_1, two fields are id1 and id2, each of which is of type int . where id2 We specified a display width of 5, and id1 did not manually specify the display width, so its display width would take the default value one by one.

Figure 2

Figure 2, we insert a data into the table and then query it out, although now id1 and id2 query out the values are 1 , but because ID1 The display width is not specified at the time of definition, so the value is inserted 1 upon the front of its Ten bits are filled with width. id2 , however, has only 4 bits filled in front of it because it specifies the display width .

Figure 3

Figure 4

Figure 3,figure 4, in order to more intuitively see the effect of the fill width, we will id1 and id2 the definition of slightly modified, using zerofill to fill the width.

Figure 5

In figure 5, after using zerofill , we can see the effect that the value is preceded by a 0 padding width. So do we get the results using 1 or 00001 as a condition in the query ?

Figure 6

Figure 6, you can see the use of 1 or 00001 as the query criteria, can be detected id2 corresponding values. But it is important to note that the actual value stored in MySQL is still 1, not 00001, because 00001 is not a representation of an integer, but rather a representation of a string, as shown in Figure 7 below .

Figure 7

Figure 7, we used theHex ()function as a comparison, you can see the useHex ()function to get the value is1, ifHex ()The resulting value is3030303031(String1of the -into the system to, the string0of the -into the system -), you can be sure that theMySQLis in the00001is stored in string form, but it is obviously not.

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

for the practice of specifying the display width , what happens if you insert a value that exceeds the display width when ID2 is defined as int (5) ?

Figure 8

Figure 8, when inserting a numeric 111111 length of 6 bits to the id2 ,theMySQL no errors were reported and 111111 were not truncated. As a result, the display width does not have a limit on the length of the inserted numeric value, unless the inserted value exceeds the range of the data type, as shown in Figure 9.

Figure 9

Figure 9, you can see that although the insert succeeds but MySQL has a warning (when MySQL 's SQL mode is in strict mode, The insertion behavior will not be completed, and MySQL will report the error, we can see the data when the MySQL The data that was originally inserted is intercepted and the reserved value is 4294967295.

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

Knowledge Point Description:

In fact, for the display width, only with the use of Zerofill, the display width is meaningful, otherwise let the display width is the default value can be. Do not assume that specifying the display width has any effect on the range of integer types, and that there is no relationship between them, whereas the range of values for integer types is only unsigned .

Unsigned

when we define an integer type, we use theZerofill,MySQLwill automatically add to this column for usunsigned(Figure3, Figure4The column has been addedZerofill, and then view the table'sDDL [Database Definition Statements], you'll find more columns.unsigned, see figureTen). This is because when using theZerofill, the value inserted into the column cannot be negative, so it is automatically addedunsignedIt is also a well-deservedunsignedalso increases the range of values for the maximum value of the integer type.

Figure 10

Auto_increment

integer type there is also a property that is auto_increment auto_increment auto_increment 1 Start, you can also set their initial values manually. Inserts a column that is set to auto_increment When span style= "Font-family:calibri" >null 1 ( null auto_increment column data insertion, the column will normally automatically grow).

When a column is set to auto_increment , it is also usually necessary to set not null and primary keyfor the column(primary key, The columns that are generally set to auto_increment are used as primary keys, which are just general and non-primary key cases.

It is also necessary to note that there can be at most one field in a table that is set to auto_increment.

Floating point and fixed point number

Both are used to represent decimals, and floating-point numbers includeFloatSingle Precision),Double (Double Precision), the fixed-point number is onlydecimal. Both can specify their precision and scale when they are defined, and the precision is the number of digits that are displayed altogether.(integer digits+Decimal digits), the scale refers to the exact number of digits after the decimal point, the form of expression such as:decimal (15,2), the accuracy here is thebit(integer -bits, decimals2bit), the scale is2bit.

It is necessary to note that the fixed-point number MySQL is stored in the form of a string, which belongs to the exact storage, but it shows a decimal number, which is more accurate than the floating-point number.

Figure 11

Figure 12

Figure 13

FigureOne, we create a table, The data type of the field ID is decimal (5,2), when inserting a value exceeding the scale in the table, Although the insertion was successful but the data was truncated at the time of insertion, rounding took place.

When we try to insert a value that exceeds the precision in the table, will it also be truncated and rounded? Will two values be displayed as 123.12 and 124.12 , respectively ? It is obvious from the result that our guess is completely wrong. In cases where precision is exceeded, the insertion succeeds but the inserted value is the maximum value under the specified precision and scale, for example , the maximum value under (5,2) is 999.99.

If in SQL mode in strict mode, these insertions will not be successful and MySQL will report ERROR.

Additional Knowledge points:

The difference between single precision and double precision, the difference between the two can not be understood as the single precision is accurate to a decimal place, and the double precision is accurate to two digits after the decimal point, which is obviously wrong. In fact, because the valid digits of float are 7 bits,the effective number of double is So the single-precision, double-precision is actually refers to the number of significant digits here.

It is also important to note that the number of significant digits is not equal to the exact number of digits, even though float can be expressed to 7 digits after the decimal point, but only the first 6 bits are accurate, the first 7 bits are likely to cause data errors. For double only The first position is accurate, and the first digit is also likely to cause data errors.

Additional Knowledge points:

The problem of the loss of floatanddouble precision is actually being extended or truncated because of the inconsistency of the scale of the access. When data is entered, if the scale of the data is inconsistent with the scale set when defining the column data type, it will result in the storage being stored at an approximate value, which results in the loss of precision as mentioned above.

under what circumstances is the precision of the floatanddouble not lost? In fact, according to the above problems, we can think of when the data scale and type scale (the scale of the input data and the definition of the column data type is set to the same scale), there will be no loss of precision.

In view of this, we often choose the decimal type, less than equal to the scale of the data can be correctly entered, no loss of precision, because it is the data in the form of strings into the database, which guarantees the accuracy. However , it does not mean that decimal does not lose precision, although it does not occur with precision scaling, but precision truncation occurs. For example, rounding occurs when the scale of the input data is greater than the scale set by the column data type.

Although we say that decimal stores data as a string in the database, and there is the problem of precision truncation (rounding), it seems that the two have a literal description of the conflict, it is not. We understand this:decimal will take place the rounded data is stored in the database as a string, but it shows a decimal (one is a storage form, one is a representation), and the precision of this decimal is no longer changed. Regardless of the precision to get this value, it is rounded after the value of the deposit as a string.

Bit type

bit type refers to the Bit, which is used to store binary data,bit (1) represents binary data with a length of 1 bits.

Figure 14

Figure 15

We insert more than the number of digits in the table of Figure 14 15 The second query result set can find that the data has been truncated, the value 2 10 3 11

in the first query result set in Figure X, it is necessary to specify the use of the select * from T_bit_test in the MySQL command line window is not able to see the data we need, you can only see that there are two smiley faces are displayed, since bit is stored in binary data, we use the bin () function in binary form to display them.

---------------------to Be Continued---------------------

MySQL knowledge tree-supported data types