Shulou(Shulou.com)06/01 Report--

The following mainly brings you the numerical detailed tutorials in the MySQL column types. I hope these contents can bring you practical use, which is also the main purpose of this article when I edit the numerical detailed tutorials in the MySQL column types. All right, don't talk too much nonsense, let's just read the following.

Column type (data type)

The so-called column type, in fact, refers to the data type, that is, a unified classification of data, from a system point of view is to be able to use a unified way of management, better use of limited space.

In SQL, data types are divided into three categories: numerical type, string type and date-time type.

For numerical data, it can be further divided into integer type and decimal type.

Integer type

In SQL, because the problem of saving disk space is considered, the system subdivides integers into five categories, which are:

Tinyint: mini integer, using 1 byte to store data (commonly used)

Smallint: small integers that use 2 bytes to store data

Mediumint: medium integer, using 3 bytes to store data

Int: standard integer, using 4 bytes to store data (commonly used)

Bigint: a large integer that uses 8 bytes to store data.

Next, enter the following SQL statement to test:

Create an integer table create table my_int (int_1 tinyint, int_2 smallint, int_3 int, int_4 bigint) charset utf8

As shown in the figure above, we have successfully created the my_int table and then inserted the data:

-- insert data insert into my_int values (1, 2, 3, 4); insert into my_int values ('afield, 2, 5, 3, 4, 4); insert into my_int values, 2, 4, 2, 4, 2, 4, 2, 4, 2, 5, 4)

As shown in the figure above, through the column type, we can define the type and length range of the inserted data.

As for why an out-of-range error is reported when assigning a value to int_1, it is because the numeric type is signed by default in SQL, that is, dividing the positive and negative values. If we need to use unsigned data, we need to declare the data type ourselves, that is, when we declare the data type, append the unsigned keyword. For example:

-- in the my_int table, add the int_5 field and set its data type to tinyint unsignedalter table my_int add int_5 tinyint unsigned

As shown in the figure above, the int_5 field has been added successfully, and continue to insert data:

-- insert data insert into my_int values (1, 2, 3, 4, 255)

As shown in the figure above, when we limit tinyint to unsigned, we can insert any integer between 0,255 and 255! But, looking back, let's take a closer look at the following picture:

By looking at the picture above, we will find that the data type of each field is followed by a pair of parentheses and contains numbers. In fact, these numbers do not have any special meaning, but only indicate the display width of the data. In fact, we can change the width of the display, but this modification does not change the size of the data itself.

The significance of the display width: when the data is not enough to display the width, it will automatically make the data become the corresponding display width, usually with a leading 0 to increase the width, which does not change the size of the data value, that is, zero filling with zerofill, and zero filling will automatically cause the value to become unsigned.

Next, execute the following SQL statement:

-- in the my_int table, add the int_6 field and set its data type to tinyint zerofillalter table my_int add int_6 (3) tinyint zerofill

Then insert the data and test it:

-- insert data insert into my_int values (1, 2, 3, 4, 5, 6)

As shown in the figure above, the meaning of zero padding is to ensure the format of the data.

Decimal type

A decimal type, that is, a numeric type with a decimal point or a range beyond the integer.

In SQL, the decimal type is subdivided into floating point type and fixed point type, where:

Floating point: floating decimal point, limited precision, easy to lose precision

Fixed point type: fixed decimal point, fixed precision, will not lose precision.

Type 1: floating point type

Floating-point data is a kind of precision data, because beyond the specified range, it loses precision and is automatically rounded. In theory, floating-point types are divided into two types of precision:

Float: single precision, takes 4 bytes to store data, and the precision range is about 7 bits.

Double: double precision, takes up 8 bytes to store data, and the precision range is about 15 bits.

Floating-point use: if you use float directly, it means there is no decimal part; if you use float, where M represents the total length, D represents the length of the decimal part, and Mmerd represents the length of the integer part.

Execute the following SQL statement to create a floating-point table for testing:

-- create a floating point table create table my_float (F1 float, f2 float (10magin2), f3 float (6magin2)) charset utf8

When we insert data into the floating-point table my_float, we can insert decimals directly or data expressed in scientific notation. In addition, when inserting floating-point data, the integer part cannot exceed the length range, but the decimal part can exceed the length range, and the system will automatically round it. In particular, if the floating point number is due to the system rounding (rounding) that causes the integer part to exceed the specified length, then the system is allowed.

-- insert test data insert into my_float values (2.15e4) 20.15 9999.99); insert into my_float values (20151120) 123456789.99 9999.99); insert into my_float values (5211314 123456.999999)

As shown in the figure above, our conclusion has been verified.

Type 2: fixed point type

Fixed-point data, absolutely guarantee that the integer part will not be rounded, that is to say, will not lose accuracy, but the fractional part may lose accuracy, although in theory, the decimal part will not lose accuracy.

Execute the following SQL statement to create a fixed-point table and compare it with floating-point numbers for testing:

-- create a fixed-point number table create table my_decimal (F1 float (10Magazine 2), D1 decimal (10Magne2) charset utf8

When we insert data, the integer part of the fixed point must not exceed the length range (rounding is also not allowed), the length of the decimal part can be exceeded at will, there is no limit, the system will automatically round:

-- insert test data insert into my_decimal values (99999999.99); insert into my_decimal values (123456789.99) 2015.1314; insert into my_decimal values (123456.99 2015.1314)

As shown in the figure above, our conclusion has also been verified.

For the above detailed tutorials on numerical values in MySQL column types, do you think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.