MySQL knowledge tree-supported data types

The main contents of this study note:

Describes the various data types supported by MySQL (commonly used) 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

The integer types supported by MySQL are tinyint, smallint, mediumint, int, bigint (range from small to large).

Zerofill

When we define an integer type, we can specify the display width within the parentheses after the type name, for example, int (5), and when the inserted numeric width is less than 5 bits, MySQL fills the width before the value. for int types, if you do not specify a width manually, the default is int (11).

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 table t_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, its display width would take the default value of 11.

Figure 2

Figure 2, we insert a data into the table and then query it out, although now ID1 and ID2 query out the value is 1, but because id1 in the definition does not specify the display width, so after inserting the value 1, its front 10 bits are filled with the 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 are we using 1 or 00001 as a condition for the query to get the results?

Figure 6

Figure 6, you can see the use of 1 or 00001 as the query criteria, can be detected id2 corresponding values. However, 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 use the Hex () function as a comparison in the query, we can see that the hex () function to get the value is 1, if Hex () to get the value is 3030303031 (string 1 of the 16 is 31, the string 0 is 16 in 30), It is certainly stored in MySQL as a string of 00001, 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 value of 6-bit length to the Id2 111111, MySQL did not report any errors and did not truncate 111111. 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 insertion is successful but MySQL has a warning (when MySQL's SQL mode is strict mode, the insertion behavior will not be able to be completed, and MySQL will report error), we can see the data out of MySQL to the original inserted data interception, The reserved value is 4294967295.

Note: The minimum value for 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, 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 use Zerofill,mysql to define an integer type, we automatically add unsigned to the column (Figure 3, Figure 4 adds Zerofill to the column, and then look at the table's DDL [database definition statement], and we see that the column is more unsigned, see Figure 10). This is because when Zerofill is used, the value of inserting the column cannot be negative, so it is also well-deserved to add unsigned automatically, and unsigned also increases the range of values for the maximum value of the integer type.

Figure 10

Auto_increment

An integer type also has a property that is auto_increment, and this property is unique to the integer type. The role of auto_increment is to keep the column values automatically growing, the value of auto_increment starting from 1, or its initial value can be set manually. When you insert a null value for a column that is set to auto_increment, the actual value inserted is the current maximum value of the column plus 1 (null does not affect the insertion of data that is set to the Auto_increment column, and the column will automatically grow normally).

When a column is set to Auto_increment, it is usually also necessary to set the NOT NULL and primary key for the column (primary key, which is generally set to auto_increment as the primary key, and this is just the case with a non-primary key).

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, floating-point numbers include float (single precision), double (doubles), and fixed-point numbers are only decimal. Both can be specified in the definition of its precision and scale, precision refers to the total number of digits (integer digits + decimal places), the scale refers to the exact number of digits after the decimal point, the form of expression, such as: decimal (15,2), where the accuracy is 15 bits (integer 13 bits, decimal 2 bits), the scale is 2 bits.

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

Figure 11

Figure 12

Figure 13

Figure 11, we create a table, the field ID of the data type is decimal (5,2), 12 in the table to insert more than the scale of the value, although the insert is successful but the data is truncated, here rounding occurs.

Figure 13 We try to insert a value exceeding the precision in the table, does it also have truncation and rounding? 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.

In SQL Mode 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 effective number of float is 7 bits, the effective number of double is 16 bits, 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 represent 7 digits after the decimal point, but only the first 6 bits are accurate, and the 7th bit is likely to cause data errors. For double only the first 15 bits are accurate, and the 16th bit is also likely to cause data errors.

Additional Knowledge points:

The problem of the loss of float and double 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 float and double 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 that decimal will have rounded data in the form of a string in the database, 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 a value that is rounded up when it is deposited as a string.

Bit type

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

Figure 14

Figure 15

We insert more than the number of bits in 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 of value 2 is 10,3 binary is 11, and their second bit is truncated.

In the first query result set in Figure 15, it is necessary to note that using 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 binary data, We use the bin () function to display them in binary form.

Date Time Type

The main differences between date and time types are as follows:

① If you want to use to denote the date and time, the datetime type is generally used;

② If you want to use the date type for the month and year date;

③ if you want to represent a minute or seconds, use the time type generally;

④ If you are only representing the year, you typically use the years type, It is important to note that 5.5. Before the 27 version (not including this version) the year type has both 2-bit and 4-bit format representations, and the year of the 5.5-bit format is no longer supported after the 27.2 version, and the value of year type is displayed in yyyy format.

Year (2) is deprecated primarily because the two-bit display width makes the type representation blurred, ambiguous (the displayed value is only the last two digits), so if you need to use the year data type when designing the database, and MySQL version is less than 5.5.27, it is recommended that the year type is defined as year (4) to avoid problems (starting with the 5.5.27 Version If the year type is defined as year (2) when the table is created, MySQL forces it to be defined as year (4). See Figure 15).

If you upgrade the lower version of MySQL to 5.5.27 Later, you need to do this piece of data processing;

⑤ the timestamp type is generally used if you need to insert the current time of the system frequently or update the time to the current time of the system.

Figure 15

Figure 16

Figure 16, we created a table, column D1 data type is timestamp, by looking at the table definition can see MySQL automatically set timestamp type column default value to Current_timestamp, that is, the system current time, plus the on Update Current_timestamp (when the row's data changes, the value of the column is updated with the current time of the system, as shown in Figure 17, 18).

Figure 17

Figure 17, we add a column for the T_dt_test table i1,int the type display width is 2, the width is not enough with 0 padding.

Figure 18

In Figure 18 We first insert a data for the table, we can see that the inserted D1 value is 2016-10-22 15:25:31, then we change the value of I1 from 1 to 2, and then see D1 found that the original deposit time was updated, the value is 2016-10-22 15:26:27, this is the effect of updating with the current timestamp of the system.

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

Category: MySQL knowledge tree Tags: mysql data type, MySQL data type details, MySQL data type features, MySQL data type comparison, MySQL supported data types, MySQL knowledge tree reprint: http://www.cnblogs.com/ Seker/p/6096024.html

MySQL knowledge tree-supported data types