Comparison between numeric and decimal types in MySQL

Decimal (numeric) synonymous, used to store numeric values accurately.

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 (precise) representation of the number, and does not store the approximate value of the value.

The two characteristics of a column, variable, and parameter that define a decimal are as follows:

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

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

P and S must comply with 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 exactly as specified, a decimal data type with decimals can be used to store numbers.

Convert decimal and numeric data
For decimal and numeric data types, Microsoft®sql Server™ considers 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, constants with decimal points are automatically converted to numeric data values, and the minimum precision and scale must be used. For example, a constant of 12.345 is converted to a numeric value with a precision of 5 and a decimal bit of 3.

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

By default, when you convert a number to a decimal or numeric value that has a lower precision and scale, SQL Server uses the round-off method. 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 the MySQL decimal data type, now the results of the data 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 column B data actually inserted is: 12345.12342

The code is as follows	Copy Code
INSERT into TA (A, B) values (1,123456.1234);

The column B data actually inserted is: 99999.99999

Conclusion: The decimal data type,

1. When the value of the inserted integer part exceeds its representation, the value of the fractional part is ignored and populated with the maximum value.

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

Second, numeric (m,d)

When I see the numeric number http://www.111cn.net/database/mysql/55524.htm type, most of them will be counted, and I have been mistaken several times.

Numeric the same number type as the decimal number type

Modify the Column A field type: ALTER TABLE TA change a 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 |

As can be seen, in myslq5.0, the numeric and decimal data types are consistent, both precision is m-digit

Comparison between numeric and decimal types in MySQL