Comparison between numeric and decimal types in MySQL

Source: Internet
Author: User
Tags numeric

Decimal (numeric) synonymous with the exact storage of numeric values.

The decimal data type can store up to 38 digits, and all numbers can be placed to the right of the decimal point. The decimal data type stores an accurate (accurate) numeric expression; The approximate value of the value is not stored.

The two characteristics of the columns, variables, and parameters that define decimal are as follows:

P The sum of the digits to the left and right of the decimal point, excluding the decimal point. If 123.45, then p=5,s=2.
Specifies the number of digits that the precision or object can control.

S
Specifies the number of decimal places or digits that can be placed to the right of the decimal point.

P and S must observe the following rules: 0 <= s <= P <= 38.

The default maximum precision value for the numeric and decimal data types is 38. In Transact-SQL, numeric is functionally equivalent to the decimal data type.

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

Convert decimal and numeric data
For decimal and numeric data types, Microsoft®sql Server™ regards each specific combination of precision and scale as a different data type. For example, Decimal (5,5) and decimal (5,0) are treated as different data types.

In Transact-SQL statements, a constant with a decimal point is automatically converted to a numeric data value, and the minimum precision and scale are necessarily used. For example, the constant 12.345 is converted to a numeric value with a precision of 5 and a decimal position of 3.

Conversion from decimal or numeric to float or real can result in loss of precision. converting from int, smallint, tinyint, float, real, money, or smallmoney to decimal or numeric can cause an overflow.

By default, SQL Server uses rounding when converting numbers to a decimal or numeric value with a lower precision and decimal digits. However, if the SET ARITHABORT option is on, SQL Server will receive an error when an overflow occurs. If only the precision and scale are lost, no error is generated.

The author, recently in the study of MySQL data type of decimal, the results of the experimental data are published as follows

Database version: Server version:5.0.45 Source Distribution

1. CREATE TABLE structure

The code is as follows Copy Code


CREATE Table Ta (a float,b decimal (10,5));


2. Inserting data

The code is as follows Copy Code


Insert into TA (a,b) values (1,12345.123423);


The actual B-column data inserted is: 12345.12342

The code is as follows Copy Code


Insert into TA (a,b) values (1,123456.1234);


The actual B-column data inserted is: 99999.99999

Conclusion: Decimal Data type,

1. When the value of the integral part of the insertion exceeds the range of its representation, the value of the decimal part is ignored and filled with the maximum value.

2, when the integer part of the legal, fractional number of redundant digits, directly truncated.

Ii. Numeric (M,D)

When I see the numeric digital type, most of them look like number, and I've made a mistake a few times.

Numeric the same number type as the decimal number type.

Modify column A field type: ALTER TABLE TA change a numeric (10,5);

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 identical, both of which are accurate to m digits.

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: info-contact@alibabacloud.com 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.