Table Column Types for Exact Numbers

This section describes 4 types of table columns to store exact numbers: NUMERIC, DECIMAL, INTEGER and SMALLINT.

There are 4 types of table columns to store exact numbers: NUMERIC, DECIMAL, INTEGER and SMALLINT.

"NUMERIC(precision, scale)" - Exact numeric value with specified scale and specified precision.

"DECIMAL(precision, scale) or DEC(precision, scale)" - Exact numeric value with specified scale and specified precision or higher.

"INTEGER" - Exact number value with scale of 0. Precision is defined by the implementation.

"SMALLINT" - Exact number value with scale of 0. Precision is defined by the implementation and should be lower than INTEGER.

Examples of exact number column types, ExactNumberColumns.sql:

```-- ExactNumberColumns.sql
--
CREATE TABLE ExactNumber (
Line VARCHAR(8),
Price NUMERIC(8,2), -- (precision, scale)
Rate DECIMAL(8,2),
Population INTEGER,
Quantity SMALLINT
);
INSERT INTO ExactNumber VALUES (
'1',
1.11,
2.22,
33,
4
);
INSERT INTO ExactNumber VALUES (
'2',
11.111,
22.222,
3333.3,
44.4
);
INSERT INTO ExactNumber VALUES (
'3',
1111.1,
2222.2,
333333,
444
);
INSERT INTO ExactNumber VALUES (
'4',
111111.119,
222222.229,
33333333,
4444
);
INSERT INTO ExactNumber VALUES (
'5',
11111111.119,
22222222.229,
3333333333,
44444
);
SELECT * FROM ExactNumber;
DROP TABLE ExactNumber;
```

Run ExactNumberColumns.sql on MySQL 5.7 server, you will get:

```C:\herong>set "MySQL=\Program Files\MySQL\MySQL Server 5.7"
< ExactNumberColumns.sql

ERROR 1264 (22003) at line 39: Out of range value for column 'Price'
at row 1
```

Note that MySQL 5.7 is rejecting numeric literals that have more digits than their column precisions.

But run ExactNumberColumns.sql on MySQL 5.6, 5.0, and 4.0 servers, you will get:

```Line    Price   Rate    Population      Quantity
1       1.11    2.22    33      4
2       11.11   22.22   3333    44
3       1111.10 2222.20 333333  444
4       111111.12       222222.23       33333333        4444
5       9999999.99      9999999.99      2147483647      32767
```

Note that:

• Line 2 tells us that extra fractional digits will be rounded down or up.
• Line 3 tells us that fractional digits will be padded to match the specified scale.
• Line 5 tells us that when a number reaches the specified precision, it will be replaced with the maximum value of the specified precision.
• I don't see any differences between NUMERIC and DECIMAL.

Last update: 2015.