DECIMAL
This page is part of MariaDB's Documentation.
The parent of this page is: Data Types for MariaDB Xpand
Topics on this page:
Overview
Fixed-point number.
USAGE
DECIMAL[(<precision>[,<scale>])] [SIGNED | UNSIGNED] [ZEROFILL]
DETAILS
Data Type | Precision | Scale |
|---|---|---|
| 0..255if omitted or
0, the default is 10 | 0..31the default is
0 |
|
SYNONYMS
The following are synonyms for DECIMAL:
DEC
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 (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 limit for what values can be represented. The value is rounded to the nearest valid value.
TRUNCATE decimal_signed_example;
-- Disable strict mode or the inserts will fail
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);
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
While Xpand accepts the DECIMAL ZEROFILL type on table create, the qualifier is ignored and silently dropped:
CREATE TABLE decimal_zerofill_example (
example DECIMAL ZEROFILL
);
SHOW CREATE TABLE decimal_zerofill_example\G
*************************** 1. row ***************************
Table: decimal_zerofill_example
Create Table: CREATE TABLE `decimal_zerofill_example` (
`example` decimal(10,0)
) CHARACTER SET utf8 /*$ SLICES=3 */