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

最新推荐文章于 2025-09-10 04:05:29 发布

原创最新推荐文章于 2025-09-10 04:05:29 发布 · 1.1w 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #duplicates #warnings #insert #table #query

本文介绍了 MySQL 5 引入的精确数学运算，包括新 SQL 模式和定点算术库的引入，带来更精确计算、明确的舍入行为、更好的平台独立性和对无效值处理的控制。还给出了创建表、插入数据等操作示例，以及从 4.1 升级到 5.0 的 SQL 变化。

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.

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       <DIR>          MyDocs
2005-07-26 21:30       <DIR>          mysql-5.0.9-beta-win32
2005-04-27 13:52       <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(*) |
+----------+
| 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”.