DECIMAL
This page is part of MariaDB's Documentation.
The parent of this page is: Data Types for MariaDB Enterprise Server
Topics on this page:
Overview
Fixed-point number
DETAILS
SYNONYMS
The following are synonyms for DECIMAL:
DEC
FIXED
NUMBER
NUMERIC
EXAMPLES
SIGNED and UNSIGNED
The DECIMAL
data type may be SIGNED
(allowing negative values) or UNSIGNED
(not allowing negative values).
Example of DECIMAL SIGNED
(SIGNED is the default):
CREATE TABLE decimal_signed_example (
description VARCHAR(20),
sz10_0 DECIMAL,
sz6_2 DECIMAL(6,2),
sz20_19 DECIMAL(20,19) SIGNED
);
SET @pi = 3.1415926535897932384626433832795;
INSERT INTO decimal_signed_example VALUES
('Pi', @pi, @pi, @pi),
('Series', 1234.567890123, 1234.567890123, 1.234567890123),
('Negative', -1234.567890123, -1234.567890123, -1.234567890123),
('Various', 1234567890, 9999.99, 9.9999999999999999999);
SELECT * FROM decimal_signed_example;
+-------------+------------+----------+------------------------+
| description | sz10_0 | sz6_2 | sz20_19 |
+-------------+------------+----------+------------------------+
| Pi | 3 | 3.14 | 3.1415926535897932385 |
| Series | 1235 | 1234.57 | 1.2345678901230000000 |
| Negative | -1235 | -1234.57 | -1.2345678901230000000 |
| Various | 1234567890 | 9999.99 | 9.9999999999999999999 |
+-------------+------------+----------+------------------------+
Example of DECIMAL UNSIGNED
:
CREATE TABLE decimal_unsigned_example (
description VARCHAR(20),
sz10_0 DECIMAL UNSIGNED,
sz6_2 DECIMAL(6,2) UNSIGNED,
sz20_19 DECIMAL(20,19) UNSIGNED
);
SET @pi = 3.1415926535897932384626433832795;
INSERT INTO decimal_unsigned_example VALUES
('Pi', @pi, @pi, @pi),
('Series', 1234.567890123, 1234.567890123, 1.234567890123),
('Various', 1234567890, 9999.99, 9.9999999999999999999);
SELECT * FROM decimal_unsigned_example;
+-------------+------------+---------+-----------------------+
| description | sz10_0 | sz6_2 | sz20_19 |
+-------------+------------+---------+-----------------------+
| Pi | 3 | 3.14 | 3.1415926535897932385 |
| Series | 1235 | 1234.57 | 1.2345678901230000000 |
| Various | 1234567890 | 9999.99 | 9.9999999999999999999 |
+-------------+------------+---------+-----------------------+
Out-of-Range
A value is considered "out-of-range" when it is too small or too large to be stored in a data type. The size specified when creating the column is the strict limit for what values can be represented. When SQL_MODE
is strict (the default) an out-of-range value generates an error and the operation fails. If strict mode is not in effect, the value is rounded to the nearest valid value and a warning is generated (which might be hidden, depending on your warning settings).
A value whose significant digits must be rounded to fit only generates a warning note about data truncation, since it is only an out-of-range value if the rounding causes the value to overflow.
TRUNCATE decimal_signed_example;
-- Disable strict mode
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO decimal_signed_example VALUES
('OK', 9999999999.4, NULL, NULL),
('Rounded overflow', 9999999999.5, NULL, NULL),
('Overflow', 10000000000, NULL, NULL),
('OK', -9999999999.4, NULL, NULL),
('Rounded underflow', -9999999999.5, NULL, NULL),
('Underflow', -10000000000, NULL, NULL),
('OK', NULL, 9999.994, NULL),
('Rounded overflow', NULL, 9999.995, NULL),
('Overflow', NULL, 10000, NULL),
('OK', NULL, -9999.994, NULL),
('Rounded underflow', NULL, -9999.995, NULL),
('Underflow', NULL, -10000, NULL),
('OK', NULL, NULL, 9.99999999999999999994),
('Rounded overflow', NULL, NULL, 9.99999999999999999995),
('Overflow', NULL, NULL, 10),
('OK', NULL, NULL, -9.99999999999999999994),
('Rounded underflow', NULL, NULL, -9.99999999999999999995),
('Underflow', NULL, NULL, -10);
Note (Code 1265): Data truncated for column 'sz10_0' at row 1
Warning (Code 1264): Out of range value for column 'sz10_0' at row 2
Warning (Code 1264): Out of range value for column 'sz10_0' at row 3
Note (Code 1265): Data truncated for column 'sz10_0' at row 4
Warning (Code 1264): Out of range value for column 'sz10_0' at row 5
Warning (Code 1264): Out of range value for column 'sz10_0' at row 6
Note (Code 1265): Data truncated for column 'sz6_2' at row 7
Warning (Code 1264): Out of range value for column 'sz6_2' at row 8
Warning (Code 1264): Out of range value for column 'sz6_2' at row 9
Note (Code 1265): Data truncated for column 'sz6_2' at row 10
Warning (Code 1264): Out of range value for column 'sz6_2' at row 11
Warning (Code 1264): Out of range value for column 'sz6_2' at row 12
Note (Code 1265): Data truncated for column 'sz20_19' at row 13
Warning (Code 1264): Out of range value for column 'sz20_19' at row 14
Warning (Code 1264): Out of range value for column 'sz20_19' at row 15
Note (Code 1265): Data truncated for column 'sz20_19' at row 16
Warning (Code 1264): Out of range value for column 'sz20_19' at row 17
Warning (Code 1264): Out of range value for column 'sz20_19' at row 18
SELECT * FROM decimal_signed_example;
+-------------------+-------------+----------+------------------------+
| description | sz10_0 | sz6_2 | sz20_19 |
+-------------------+-------------+----------+------------------------+
| OK | 9999999999 | NULL | NULL |
| Rounded overflow | 9999999999 | NULL | NULL |
| Overflow | 9999999999 | NULL | NULL |
| OK | -9999999999 | NULL | NULL |
| Rounded underflow | -9999999999 | NULL | NULL |
| Underflow | -9999999999 | NULL | NULL |
| OK | NULL | 9999.99 | NULL |
| Rounded overflow | NULL | 9999.99 | NULL |
| Overflow | NULL | 9999.99 | NULL |
| OK | NULL | -9999.99 | NULL |
| Rounded underflow | NULL | -9999.99 | NULL |
| Underflow | NULL | -9999.99 | NULL |
| OK | NULL | NULL | 9.9999999999999999999 |
| Rounded overflow | NULL | NULL | 9.9999999999999999999 |
| Overflow | NULL | NULL | 9.9999999999999999999 |
| OK | NULL | NULL | -9.9999999999999999999 |
| Rounded underflow | NULL | NULL | -9.9999999999999999999 |
| Underflow | NULL | NULL | -9.9999999999999999999 |
+-------------------+-------------+----------+------------------------+
DECIMAL ZEROFILL
A special type of DECIMAL UNSIGNED
is DECIMAL ZEROFILL
, which pads out the values with leading zeros in SELECT results. The number of leading zeros are just enough to pad the field out to the length of the type's field size (not counting the decimal place), but the zeros are not included in an expression result or in a UNION SELECT column.
Using DECIMAL ZEROFILL
works the same way as DECIMAL UNSIGNED
for most operations except a simple SELECT. For example, with the following test table setup:
CREATE TABLE decimal_zerofill_example (
description VARCHAR(20),
sz10_0 DECIMAL ZEROFILL,
sz6_2 DECIMAL(6,2) ZEROFILL,
sz20_19 DECIMAL(20,19) ZEROFILL
);
SET @pi = 3.1415926535897932384626433832795;
INSERT INTO decimal_zerofill_example VALUES
('Pi', @pi, @pi, @pi),
('Series', 1234.567890123, 1234.567890123, 1.234567890123),
('Various', 1234567890, 9999.99, 9.9999999999999999999);
SELECT * FROM decimal_zerofill_example;
+-------------+------------+---------+-----------------------+
| description | sz10_0 | sz6_2 | sz20_19 |
+-------------+------------+---------+-----------------------+
| Pi | 0000000003 | 0003.14 | 3.1415926535897932385 |
| Series | 0000001235 | 1234.57 | 1.2345678901230000000 |
| Various | 1234567890 | 9999.99 | 9.9999999999999999999 |
+-------------+------------+---------+-----------------------+
CHANGE HISTORY
EXTERNAL REFERENCES
Additional information on this topic may be found in the MariaDB Public Knowledge Base.