FLOAT
This page is part of MariaDB's MariaDB Documentation.
The parent of this page is: Data Types for MariaDB Xpand
Topics on this page:
Overview
Single-precision floating-point number.
EXAMPLES
SIGNED and UNSIGNED
The FLOAT data type may be SIGNED (allowing negative values) or UNSIGNED (not allowing negative values).
Example of FLOAT SIGNED (SIGNED is the default):
CREATE TABLE float_signed_example (
description VARCHAR(20),
example FLOAT,
sz6_2 FLOAT(6,2),
sz20_19 FLOAT(20,19) SIGNED
);
SET @pi = 3.1415926535897932384626433832795;
INSERT INTO float_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 float_signed_example;
+-------------+-------------+----------+------------------------+
| description | example | sz6_2 | sz20_19 |
+-------------+-------------+----------+------------------------+
| Pi | 3.14159 | 3.14 | 3.1415927410125732422 |
| Series | 1234.57 | 1234.57 | 1.2345678806304931641 |
| Negative | -1234.57 | -1234.57 | -1.2345678806304931641 |
| Various | 1.23457e+09 | 9999.99 | 10.0000000000000000000 |
+-------------+-------------+----------+------------------------+
Example of FLOAT UNSIGNED:
CREATE TABLE float_unsigned_example (
description VARCHAR(20),
example FLOAT UNSIGNED,
sz6_2 FLOAT(6,2) UNSIGNED,
sz20_19 FLOAT(20,19) UNSIGNED
);
SET @pi = 3.1415926535897932384626433832795;
INSERT INTO float_unsigned_example VALUES
('Pi', @pi, @pi, @pi),
('Series', 1234.567890123, 1234.567890123, 1.234567890123),
('Various', 1234567890, 9999.99, 9.9999999999999999999);
SELECT * FROM float_unsigned_example;
+-------------+-------------+---------+------------------------+
| description | example | sz6_2 | sz20_19 |
+-------------+-------------+---------+------------------------+
| Pi | 3.14159 | 3.14 | 3.1415927410125732422 |
| Series | 1234.57 | 1234.57 | 1.2345678806304931641 |
| Various | 1.23457e+09 | 9999.99 | 10.0000000000000000000 |
+-------------+-------------+---------+------------------------+
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 limits can also vary based on your hardware and operating system. The value is rounded to the nearest valid value.
TRUNCATE float_signed_example;
-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO float_signed_example VALUES
('OK', 3.402823466e38, NULL, NULL),
('Overflow', 4.0e38, NULL, NULL),
('OK', -3.402823466e38, NULL, NULL),
('Underflow', -4.0e38, NULL, NULL),
('OK', NULL, 9999.99, NULL),
('Rounded overflow', NULL, 9999.995, NULL),
('Overflow', NULL, 10000, NULL),
('OK', NULL, -9999.99, NULL),
('Rounded underflow', NULL, -9999.995, NULL),
('Underflow', NULL, -10000, NULL),
('OK', NULL, NULL, 9.9999999999999999),
('Rounded OK', NULL, NULL, 9.99999999999999995),
('Overflow', NULL, NULL, 11),
('OK', NULL, NULL, -9.9999999999999999),
('Rounded OK', NULL, NULL, -9.99999999999999995),
('Underflow', NULL, NULL, -11);
SELECT * FROM float_signed_example;
+-------------------+--------------+-----------+-------------------------+
| description | example | sz6_2 | sz20_19 |
+-------------------+--------------+-----------+-------------------------+
| OK | 3.40282e+38 | NULL | NULL |
| Overflow | 3.40282e+38 | NULL | NULL |
| OK | -3.40282e+38 | NULL | NULL |
| Underflow | -3.40282e+38 | NULL | NULL |
| OK | NULL | 9999.99 | NULL |
| Rounded overflow | NULL | 10000.00 | NULL |
| Overflow | NULL | 10000.00 | NULL |
| OK | NULL | -9999.99 | NULL |
| Rounded underflow | NULL | -10000.00 | NULL |
| Underflow | NULL | -10000.00 | NULL |
| OK | NULL | NULL | 10.0000000000000000000 |
| Rounded OK | NULL | NULL | 10.0000000000000000000 |
| Overflow | NULL | NULL | 11.0000000000000000000 |
| OK | NULL | NULL | -10.0000000000000000000 |
| Rounded OK | NULL | NULL | -10.0000000000000000000 |
| Underflow | NULL | NULL | -11.0000000000000000000 |
+-------------------+--------------+-----------+-------------------------+
TRUNCATE float_unsigned_example;
-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO float_unsigned_example VALUES
('OK', 3.402823466e38, NULL, NULL),
('Overflow', 4.0e38, NULL, NULL),
('Underflow', -1, NULL, NULL);
SELECT * FROM float_unsigned_example;
+-------------+-------------+-------+---------+
| description | example | sz6_2 | sz20_19 |
+-------------+-------------+-------+---------+
| OK | 3.40282e+38 | NULL | NULL |
| Overflow | 3.40282e+38 | NULL | NULL |
| Underflow | 0 | NULL | NULL |
+-------------+-------------+-------+---------+
FLOAT ZEROFILL
While Xpand accepts the FLOAT ZEROFILL type on table create, the qualifier is silently dropped.
CREATE TABLE float_zerofill_example (
example FLOAT ZEROFILL
);
SHOW CREATE TABLE float_zerofill_example\G
*************************** 1. row ***************************
Table: float_zerofill_example
Create Table: CREATE TABLE `float_zerofill_example` (
`example` float
) CHARACTER SET utf8 /*$ SLICES=3 */