Comparison of NUMERIC and DECIMAL types in Mysql, numericdecimal

Comparison of NUMERIC and DECIMAL types in Mysql, numericdecimal

Decimal (numeric) is synonymous and used to precisely store values.

A decimal data type can store up to 38 numbers. All numbers can be placed on the right of the decimal point. The decimal data type stores an accurate (accurate) numeric expression, and does not store approximate values.

Two features of defining decimal columns, variables, and parameters are as follows:

P indicates the sum of the numbers on the left and right of the decimal point, excluding the decimal point. For example, 123.45, p = 5, s = 2.
Specifies the number of numbers that can be controlled by precision or object.

S
Specify the number of decimal places or numbers to the right of the decimal point.

P and s must comply with the following rules: 0 <= s <= p <= 38.

The default maximum precision of numeric and decimal data types is 38. In Transact-SQL, numeric and decimal data types are functionally equivalent.

When data values must be precisely stored as specified, decimal data types with decimal numbers can be used to store numbers.

Convert decimal and numeric data
For decimal and numeric data types, Microsoft®SQL Server™Each specific combination of precision and decimal places is considered as a different data type. For example, decimal (5, 5) and decimal (5, 0) are treated as different data types.

In a Transact-SQL statement, constants with decimal points are automatically converted to numeric data values, and the minimum precision and decimal places must be used. For example, constant 12.345 is converted to the numeric value, with a precision of 5 and a decimal point of 3.

Conversion from decimal or numeric to float or real results in loss of precision. Conversion from int, smallint, tinyint, float, real, money, or smallmoney to decimal or numeric may cause overflow.

By default, SQL Server uses the round-robin method when converting a number to a decimal or numeric value with lower precision and decimal places. However, if the set arithabort option is ON, SQL Server will encounter an error when overflow occurs. If only precision and decimal places are lost, no error is generated.

I have recently studied the decimal data type of mysql. The data experiment results are published as follows:

Database version: Server version: 5.0.45 Source distribution

1. Create a table structure

The Code is as follows:	Copy code
Create table ta (a float, B decimal (10, 5 ));

2. Insert data

The Code is as follows:	Copy code
Insert into ta (a, B) value (1, 12345.123423 );

Actually inserted Column B data: 12345.12342

The Code is as follows:	Copy code
Insert into ta (a, B) value (1, 123456.1234 );

Actually inserted Column B data: 99999.99999

Conclusion: decimal data type,

1. When the value of the inserted integer part exceeds the expressed range, the value of the fractional part is ignored and filled with the maximum value.

2. When the integer part is valid and the decimal part is redundant, it is directly truncated.

Ii. numeric (M, D)

When you see the numeric digital http://www.111cn.net/database/mysql/55524.htm type, most of them will be seen as number, and I have also made a mistake several times.

The numeric type is the same as the decimal numeric type.

Modify the field type of column a: alter table ta change a numeric );

The Code is as follows:	Copy code
Show create table ta; | Ta | create table 'ta '( 'A' decimal (10, 5) default NULL, 'B' decimal (10, 5) default NULL ) ENGINE = MyISAM default charset = utf8 |

It can be seen that in myslq5.0, the numeric and decimal data types are consistent, and the accuracy of both data types is accurate to M-bit numbers.