mysql 5.03以后能够计算decimal 的精确sum值了
来源:互联网 发布:十字军之王2 优化 编辑:程序博客网 时间:2024/06/04 18:03
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 <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: MySQL 5.0.3 and up uses precision math when calculating with As of MySQL 5.0.3, trailing spaces no longer are removed from values stored in Note: If you create a table with new As of MySQL 5.0.3, 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. DECIMAL
values (64 decimal digits) and for rounding exact-value numbers. See Chapter 23, Precision Math. VARCHAR
and VARBINARY
columns. The maximum length for VARCHAR
or VARBINARY
now is 65,535 characters or bytes, respectively. 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. 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 newDECIMAL
type, you should do anALTER TABLE
on it. TheALTER TABLE
also will change the table'sVARCHAR
columns to use the newVARCHAR
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
andVARBINARY
columns. The maximum length forVARCHAR
orVARBINARY
now is 65,535 characters or bytes, respectively.Note: If you create a table with new
VARCHAR
orVARBINARY
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 forTINYINT(1)
. See Section 11.1.1, “Overview of Numeric Types”.
- mysql 5.03以后能够计算decimal 的精确sum值了
- 解决Python中浮点数计算结果不够精确的问题:decimal模块
- 阶乘的精确值计算
- 计算分数的精确值
- 计算分数的精确值
- 计算分数的精确值
- 计算分数的精确值
- MySQL的decimal类型
- 三年了,终于找到了个能够随时上网的公司了!以后经常写博客了
- 我们大大地高估了自己一年以后能够做到的事
- 我们大大地高估了自己一年以后能够做到的事
- 如何计算分数的精确值
- java的精确计算
- BigDecimal的精确计算
- 计算分数精确值
- 计算分数精确值
- 计算分数精确值
- 计算分数精确值
- struts 的radio标记的增强
- 使用镶套填充TreeView
- 如何解决VS.Net生成时的“不可恢复的生成错误”
- 用java配置并调用Windows的At命令
- [DNN模块开发]如何写dnn文件
- mysql 5.03以后能够计算decimal 的精确sum值了
- 使用NotifyIcon轻松创建系统任务栏小图标
- 巧借Excel另存功能,在VFP中转换表格为CSV格式
- VB+VFP联合应用
- VS .net 2003调试javascript中两个杂症的解决
- 坦然地接受你所面对的每一个人
- 搜索引擎技术及趋势
- 使用Oracle Developer Tools For Visual Studio .NET
- 好站链接,共同学习,共同进步