Mysql – Does MySQL or MariaDB support double literals (double precision)

datatypesfloating pointmariadbMySQLsyntax

MySQL seems to support double precision

The FLOAT and DOUBLE types represent approximate numeric data values. MySQL uses four bytes for single-precision values and eight bytes for double-precision values.

Is there a method to declare that a literal is a double precision and not a float, decimal or anything else? I don't see this mentioned in the docs on Numeric Literals. In PostgreSQL, I could coerce a literal to a double precision with 

SELECT 1::double precision,
  double precision '1',
  CAST(1 AS double precision);

Best Answer

Though not in the docs explicitly on Numeric Literals. You can make a double precision literal with scientific notation (using e0) which seems to work,

MariaDB [test]> CREATE TABLE foo AS SELECT 1.0e0 AS "se_decimal", 1e0 AS "se_nodecimal", 1.0 AS "decimal";
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [test]> SHOW CREATE TABLE foo;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| foo   | CREATE TABLE `foo` (
  `se_decimal` double NOT NULL,
  `se_nodecimal` double NOT NULL,
  `decimal` decimal(2,1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

This is actually supported by the SQL Spec, which shifts the literal from <exact numeric literal> to <approximate numeric literal> when it sees the e or E. From SQL 2011n spec,

<approximate numeric literal> ::=
  <mantissa> E <exponent>

This is what the docs on Numeric Literals presumably refer to when it says,

Approximate-value numeric literals are represented in scientific notation with a mantissa and exponent. Either or both parts may be signed. Examples: 1.2E3, 1.2E-3, -1.2E3, -1.2E-3.

Though the docs don't say the type, it's double (see above).

See also,

Related Question