MySQL 5.03 and later can calculate the exact sum value of decimal

Chapter 23. Precision math
Table of contents

23.1. Types of numeric values
23.2. decimal data type changes
23.3. Expression handling
23.4. Rounding Behavior
23.5. Precision math examples
MySQL 5 introduces precision math, that is, numeric value handling that results in more accurate results and more control over invalid values than in earlier versions of MySQL. Precision math is based on two implementation changes:

The introduction of new SQL modes in MySQL 5.0.2 that control how strict the server is about accepting or rejecting invalid data.

The introduction in MySQL 5.0.3 of a library for fixed-point arithmetic.

These changes have several implications for numeric operations:

More precise calculations.

For exact-value numbers, calculations do not introduce floating-point error. instead, exact precision is used. for example, a number such. 0001 is treated as an exact value rather than as an approximate value, and suming it 10,000 times produces a result of 1, not a value "close" to 1.

Well-defined rounding behavior.

For the exact-value numbers, the result of round () depends on its argument, not on factors such as how the underlying C library works.

Improved platform independence.

Operations on exact numeric values are the same authentication SS different platforms such as windows and UNIX.

Control over invalid value handling.

Overflow and division by zero are detectable and can be treated as errors. for example, you can treat a value that is too large for a column as an error rather than having the value truncated to lie within the range of the column's data type. similarly, you can treat division by zero as an error rather than as an operation that produces a result of null. the choice of which approach to take is determined by the setting of the SQL _mode system variable.

An important result of these changes is that MySQL provides improved compliance with standard SQL.

The following discussion covers several aspects of how precision math works (including possible incompatibilities with older applications ). at the end, some examples are given that demonstrate how MySQL 5 handles numeric operations more precisely than before.

Microsoft Windows 2000 [version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.

C:/Documents and Settings/Administrator> D:

D:/> dir my *
Volume in drive D is local disk
Volume serial number is D492-84BA

Directory of D :/

<Dir> mydocs
<Dir> mysql-5.0.9-beta-win32
<Dir> mysql5
0 file (s) 0 bytes
3 Dir (s) 2,707,423,232 bytes free

D:/>
D:/> Cd * Win32

D:/mysql-5.0.9-beta-win32> Cd Bin

D: // mysql-5.0.9-beta-win32/bin> mysql-u Root
Welcome to the MySQL monitor. commands end with; or/g.
Your MySQL connection ID is 2 to server version: 5.0.9-beta-NT-max

Type 'help; 'or'/H' for help. type'/C' to clear the buffer.

Mysql> Create Table Test (N decimal (15, 2 ));
Error 1046 (3d000): No Database Selected
Mysql> use test
Database changed
Mysql> Create Table Test (N decimal (15, 2 ));
Query OK, 0 rows affected (0.05 Sec)

Mysql> Insert table test values (99999999.99 );
Error 1064 (42000): You have an error in your SQL syntax; check the manual that
Corresponds to your MySQL Server version for the right syntax to use near 'table
Test values (99999999.99) 'at line 1
Mysql> insert into test values (99999999.99 );
Query OK, 1 row affected (0.00 Sec)

Mysql> insert into test select * from test;
Query OK, 1 row affected (0.00 Sec)
Records: 1 duplicates: 0 Warnings: 0

Mysql> insert into test select * from test;
Query OK, 2 rows affected (0.00 Sec)
Records: 2 duplicates: 0 Warnings: 0

Mysql> insert into test select * from test;
Query OK, 4 rows affected (0.00 Sec)
Records: 4 duplicates: 0 Warnings: 0

Mysql> insert into test select * from test;
Query OK, 8 rows affected (0.00 Sec)
Records: 8 duplicates: 0 Warnings: 0

Mysql> insert into test select * from test;
Query OK, 16 rows affected (0.00 Sec)
Records: 16 duplicates: 0 Warnings: 0

Mysql> insert into test select * from test;
Query OK, 32 rows affected (0.00 Sec)
Records: 32 duplicates: 0 Warnings: 0

Mysql> insert into test select * from test;
Query OK, 64 rows affected (0.00 Sec)
Records: 64 duplicates: 0 Warnings: 0

Mysql> insert into test select * from test;
Query OK, 128 rows affected (0.00 Sec)
Records: 128 duplicates: 0 Warnings: 0

Mysql> insert into test select * from test;
Query OK, 256 rows affected (0.00 Sec)
Records: 256 duplicates: 0 Warnings: 0

Mysql> insert into test select * from test;
Query OK, 512 rows affected (0.00 Sec)
Records: 512 duplicates: 0 Warnings: 0

Mysql> insert into test select * from test;
Query OK, 1024 rows affected (0.00 Sec)
Records: 1024 duplicates: 0 Warnings: 0

Mysql> insert into test select * from test;
Query OK, 2048 rows affected (0.02 Sec)
Records: 2048 duplicates: 0 Warnings: 0

Mysql> insert into test select * from test;
Query OK, 4096 rows affected (0.00 Sec)
Records: 4096 duplicates: 0 Warnings: 0

Mysql> select count (*) from test;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 8192 |
+ ---------- +
1 row in SET (0.00 Sec)

Mysql> select sum (n) from test;
+ ----------------- +
| Sum (n) |
+ ----------------- +
| 1, 819199999918.08 |
+ ----------------- +
1 row in SET (0.00 Sec)

Mysql>

2.10.1. Upgrading from version 4.1 to 5.0

SQL changes:

• DECIMALColumns now are stored in a more efficient format. to convert a table to use the newDECIMALType, you shoshould doALTER TABLEOn it.ALTER TABLEAlso will change the table'sVARCHARColumns to use the newVARCHARColumn type. For information about possible incompatibilities with old applications, see Chapter 23,Precision math.

• MySQL 5.0.3 and up uses precision math when calculatingDECIMALValues (64 decimal digits) and for rounding exact-value numbers. See chapter 23,Precision math.

• As of MySQL 5.0.3, trailing spaces no longer are removed from values stored inVARCHARAndVARBINARYColumns. The maximum lengthVARCHAROrVARBINARYNow is 65,535 characters or bytes, respectively.

  Note: If you create a table with newVARCHAROrVARBINARYColumns in MySQL 5.0.3 or up, the table will not be usable if you downgrade to a version older than 5.0.3. Dump the table before downgrading and then reload it after downgrading.

• As of MySQL 5.0.3,BITIs a separate data type, not a synonymTINYINT(1). See section 11.1.1, "Overview of numeric types ".

SQL changes:

• DECIMALColumns now are stored in a more efficient format. to convert a table to use the newDECIMALType, you shoshould doALTER TABLEOn it.ALTER TABLEAlso will change the table'sVARCHARColumns to use the newVARCHARColumn type. For information about possible incompatibilities with old applications, see Chapter 23,Precision math.

• MySQL 5.0.3 and up uses precision math when calculatingDECIMALValues (64 decimal digits) and for rounding exact-value numbers. See chapter 23,Precision math.

• As of MySQL 5.0.3, trailing spaces no longer are removed from values stored inVARCHARAndVARBINARYColumns. The maximum lengthVARCHAROrVARBINARYNow is 65,535 characters or bytes, respectively.

  Note: If you create a table with newVARCHAROrVARBINARYColumns in MySQL 5.0.3 or up, the table will not be usable if you downgrade to a version older than 5.0.3. Dump the table before downgrading and then reload it after downgrading.

• As of MySQL 5.0.3,BITIs a separate data type, not a synonymTINYINT(1). See section 11.1.1, "Overview of numeric types ".