tl;dr
Don't do calculations in SQL language
Longer
The result scale and precision is well defined here on MSDN. It isn't intuitive, really.
However, in simple terms, precision is lost when the input scales are high because the result scales need to be dropped to 38 with a matching precision drop.
To confirm things
- Your extra CAST in the first example simply add zeroes
- The truncation happens as per my MSDN link (2nd example)
- The 3rd example with constants has implied decimal values that are just enough (5,1) and 18,14).
This means the result scale and precision have no truncation (see blow)
More on the 1st and 3rd cases..
The result scale for a division is max(6, s1 + p2 + 1)
:
- First example, this is 77 which is dropped to 38. Precision is forced down similarly, subject to a minimum of 6 (see this)
- Third example, this is 24 so precision does not need adjusted
You have some options
- calculate in the client code eg .net
- use CLR functions to do .net calculations
- live with the loss of accuracy
- use float and live with 15 significant figures as best
FInally, see this on SO https://stackoverflow.com/questions/423925/t-sql-decimal-division-accuracy/424052#424052
PROBLEM
You need to insert nothing ?
SOLUTION
Then, insert nothing at all ... literally !!!
One of the following will work
INSERT INTO test2 () VALUES (),(),();
INSERT INTO test2 (test) VALUES (NULL),(NULL),(NULL);
INSERT INTO test2 (id) VALUES (NULL),(NULL),(NULL);
SAMPLE DATA
use test
DROP TABLE IF EXISTS test2;
CREATE TABLE test2
(id int not null auto_increment primary key,
test int not null default 0);
SHOW CREATE TABLE test2\G
SELECT * FROM test2;
SAMPLE DATA LOADED
mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS test2;
Query OK, 0 rows affected (0.11 sec)
mysql> CREATE TABLE test2
-> (id int not null auto_increment primary key,
-> test int default 0);
Query OK, 0 rows affected (0.34 sec)
mysql> SHOW CREATE TABLE test2\G
*************************** 1. row ***************************
Table: test2
Create Table: CREATE TABLE `test2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`test` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SELECT * FROM test2;
Empty set (0.00 sec)
mysql>
QUERIES EXECUTED
If id
and test
are the only two columns in the table, the INSERT does not need a column list. If there are other columns in test2
besides id
and test
, name the test
column and insert a NULL. Keep in mind, inserting a NULL into a column that has DEFAULT 0
will become 0
.
mysql> SELECT * FROM test2;
Empty set (0.00 sec)
mysql> INSERT INTO test2 () VALUES (),(),();
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT INTO test2 (test) VALUES (NULL),(NULL),(NULL);
Query OK, 3 rows affected, 3 warnings (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 3
mysql> INSERT INTO test2 (id) VALUES (NULL),(NULL),(NULL);
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test2;
+----+------+
| id | test |
+----+------+
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
| 7 | 0 |
| 8 | 0 |
| 9 | 0 |
+----+------+
9 rows in set (0.00 sec)
mysql>
GIVE IT A TRY !!!
CAVEAT #1 : I did this in Windows 8.1 from the command line
mysql> select * from information_schema.global_variables
-> where variable_name like 'version%';
+-------------------------+------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-------------------------+------------------------------+
| VERSION_COMMENT | MySQL Community Server (GPL) |
| VERSION_COMPILE_MACHINE | x86_64 |
| VERSION_COMPILE_OS | Win64 |
| VERSION | 5.6.15 |
+-------------------------+------------------------------+
4 rows in set (0.07 sec)
mysql>
CAVEAT #2 : I am using MySQL's default SQL mode. Thus, I did not need to disable anything.
Best Answer
A DECIMAL(25,6) stores 25 digits, 6 of them fractional, i.e. a value up to
9,999,999,999,999,999,999.999999
10e18
is10,000,000,000,000,000,000.000000