# 24.5. 精度数学示例

`mysql> SELECT .1 + .2 = .3;`
`+--------------+`
`| .1 + .2 = .3 |`
`+--------------+`
`|    1 |`
`+--------------+`

`mysql> SELECT .1E0 + .2E0 = .3E0;`
`+--------------------+`
`| .1E0 + .2E0 = .3E0 |`
`+--------------------+`
`|  0 |`
`+--------------------+`

`CREATE PROCEDURE p ()`
`BEGIN`
`  DECLARE i INT DEFAULT 0;`
`  DECLARE d DECIMAL(10,4) DEFAULT 0;`
`  DECLARE f FLOAT DEFAULT 0;`
`  WHILE i < 10000 DO`
`    SET d = d + .0001;`
`    SET f = f + .0001E0;`
`    SET i = i + 1;`
`  END WHILE;`
`  SELECT d, f;`
`END;`

`+--------+------------------+`
`| d      | f|`
`+--------+------------------+`
`| 1.0000 | 0.99999999999991 |`
`+--------+------------------+`

`mysql> SELECT .01 * .01;`
`+-----------+`
`| .01 * .01 |`
`+-----------+`
`| 0.0001    |`
`+-----------+`

`mysql> SELECT ROUND(2.5), ROUND(-2.5);`
`+------------+-------------+`
`| ROUND(2.5) | ROUND(-2.5) |`
`+------------+-------------+`
`| 3  | -3  |`
`+------------+-------------+`

`mysql> SELECT ROUND(2.5E0), ROUND(-2.5E0);`
`+--------------+---------------+`
`| ROUND(2.5E0) | ROUND(-2.5E0) |`
`+--------------+---------------+`
`|    2 |    -2 |`
`+--------------+---------------+`

`mysql> SET SQL_MODE='';`
`Query OK, 0 rows affected (0.00 sec)`
` `
`mysql> CREATE TABLE t (i TINYINT);`
`Query OK, 0 rows affected (0.00 sec)`
` `
`mysql> INSERT INTO t SET i = 128;`
`Query OK, 1 row affected, 1 warning (0.01 sec)`
` `
`mysql> SELECT i FROM t;`
`+------+`
`| i    |`
`+------+`
`|  127 |`
`+------+`
`1 row in set (0.00 sec)`

`mysql> SET SQL_MODE='TRADITIONAL';`
`Query OK, 0 rows affected (0.00 sec)`
` `
`mysql> CREATE TABLE t (i TINYINT);`
`Query OK, 0 rows affected (0.01 sec)`
` `
`mysql> SET sql_mode='STRICT_ALL_TABLES';`
`Query OK, 0 rows affected (0.10 sec)`
` `
`mysql> INSERT INTO t SET i = 128;`
`ERROR 1264 (22003): Out of range value adjusted for column 'i' at row 1`
` `
`mysql> SELECT i FROM t;`
`Empty set (0.00 sec)`

`mysql> SET SQL_MODE='';`
`Query OK, 0 rows affected (0.00 sec)`
` `
`mysql> CREATE TABLE t (i TINYINT);`
`Query OK, 0 rows affected (0.01 sec)`
` `
`mysql> INSERT INTO t SET i = 1 / 0;`
`Query OK, 1 row affected (0.06 sec)`
` `
`mysql> SELECT i FROM t;`
`+------+`
`| i    |`
`+------+`
`| NULL |`
`+------+`
`1 row in set (0.01 sec)`

`mysql> SET SQL_MODE='TRADITIONAL';`
`Query OK, 0 rows affected (0.00 sec)`
` `
`mysql> CREATE TABLE t (i TINYINT);`
`Query OK, 0 rows affected (0.00 sec)`
` `
`mysql> SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';`
`Query OK, 0 rows affected (0.00 sec)`
` `
`mysql> INSERT INTO t SET i = 1 / 0;`
`ERROR 1365 (22012): Division by 0`
` `
`mysql> SELECT i FROM t;`
`Empty set (0.01 sec)`

`mysql> SELECT VERSION();`
`+-----------------+`
`| VERSION()       |`
`+-----------------+`
`| 4.0.25-standard |`
`+-----------------+`
`1 row in set (0.00 sec)`
` `
` `
`mysql> CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;`
` `
`mysql> DESCRIBE t;`
`+-------+-------------+------+-----+---------+-------+`
`| Field | Type| Null | Key | Default | Extra |`
`+-------+-------------+------+-----+---------+-------+`
`| a     | double(3,1) |      |     | 0.0     |       |`
`| b     | double      |      |     | 0       |       |`
`+-------+-------------+------+-----+---------+-------+`

`mysql> SELECT VERSION();`
`+-----------------+`
`| VERSION()       |`
`+-----------------+`
`| 5.1.2-alpha-log |`
`+-----------------+`
`1 row in set (0.00 sec)`
` `
`mysql> CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;`
` `
`mysql> DESCRIBE t;`
`+-------+--------------+------+-----+---------+-------+`
`| Field | Type | Null | Key | Default | Extra |`
`+-------+--------------+------+-----+---------+-------+`
`| a     | decimal(2,1) | NO   |     | 0.0     |       |`
`| b     | double       | NO   |     | 0       |       |`
`+-------+--------------+------+-----+---------+-------+`

`mysql> CREATE TABLE t (i INT, d DECIMAL, f FLOAT);`
`mysql> INSERT INTO t VALUES(1,1,1);`
`mysql> CREATE TABLE y SELECT AVG(i), AVG(d), AVG(f) FROM t;`

`mysql> DESCRIBE y;`
`+--------+--------------+------+-----+---------+-------+`
`| Field  | Type | Null | Key | Default | Extra |`
`+--------+--------------+------+-----+---------+-------+`
`| AVG(i) | double(17,4) | YES  |     | NULL    |       |`
`| AVG(d) | double(17,4) | YES  |     | NULL    |       |`
`| AVG(f) | double       | YES  |     | NULL    |       |`
`+--------+--------------+------+-----+---------+-------+`

`mysql> DESCRIBE y;`
`+--------+---------------+------+-----+---------+-------+`
`| Field  | Type  | Null | Key | Default | Extra |`
`+--------+---------------+------+-----+---------+-------+`
`| AVG(i) | decimal(14,4) | YES  |     | NULL    |       |`
`| AVG(d) | decimal(14,4) | YES  |     | NULL    |       |`
`| AVG(f) | double| YES  |     | NULL    |       |`
`+--------+---------------+------+-----+---------+-------+`