Comparison of NUMERIC and DECIMAL types in Mysql, numericdecimal

Source: Internet
Author: User

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.

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 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.