mysql 精确计算_mysql 5.03以后能够计算decimal 的精确sum值了

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 as .0001 is treated as an exact value rather than as an approximate value, and summing 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 across 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:/

2005-07-26  15:55      

2005-07-26  21:30      

2005-04-27  13:52      

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(*) |

+----------+

|     8192 |

+----------+

1 row in set (0.00 sec)

mysql> select sum(n) from test;

+-----------------+

| sum(n)          |

+-----------------+

| 819199999918.08 |

+-----------------+

1 row in set (0.00 sec)

mysql>

2.10.1. Upgrading from Version 4.1 to 5.0

SQL Changes:

DECIMAL columns now are stored in a more efficient format. To convert a table to use the new DECIMAL type, you should do an ALTER TABLE on it. The ALTER TABLE also will change the table's VARCHAR columns to use the new VARCHAR column type. For information about possible incompatibilities with old applications, see Chapter 23, Precision Math.

MySQL 5.0.3 and up uses precision math when calculating with DECIMAL values (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 in VARCHAR and VARBINARY columns. The maximum length for VARCHAR or VARBINARY now is 65,535 characters or bytes, respectively.

Note: If you create a table with new VARCHAR or VARBINARY columns 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, BIT is a separate data type, not a synonym for TINYINT(1). See Section 11.1.1, “Overview of Numeric Types”.

SQL Changes:

DECIMAL columns now are stored in a more efficient format. To convert a table to use the new DECIMAL type, you should do an ALTER TABLE on it. The ALTER TABLE also will change the table's VARCHAR columns to use the new VARCHAR column type. For information about possible incompatibilities with old applications, see Chapter 23, Precision Math.

MySQL 5.0.3 and up uses precision math when calculating with DECIMAL values (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 in VARCHAR and VARBINARY columns. The maximum length for VARCHAR or VARBINARY now is 65,535 characters or bytes, respectively.

Note: If you create a table with new VARCHAR or VARBINARY columns 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, BIT is a separate data type, not a synonym for TINYINT(1). See Section 11.1.1, “Overview of Numeric Types”.