Numeric values

Numeric values can be decimal or hexadecimal.

Decimal values

Decimal values can be exact (integers and fixed-point values) or approximate (floating-point values). Decimal values can use a decimal point . as a decimal separator and can be preceded by a minus sign - to indicate a negative value.

The DECIMAL data type is a fixed-point type and is used for exact calculations. The FLOAT and DOUBLE data types are floating-point types and are used for approximate calculations.

info

The DECIMAL type supports shrinking (for example, DECIMAL(10, 2) can be changed to DECIMAL(9, 2)).

Exact numeric values have an integer part, a fractional part, or both. Examples: 1, .2, 3.4, -5, -6.78, +9.10.

Approximate numeric values are represented in scientific notation with a mantissa and an exponent. Examples: 1.2E3, 1.2E-3, -1.2E3, -1.2E-3.

Two similar-looking numeric values may be treated differently. For example, 2.34 is an exact (fixed-point) value, while 2.34E0 is an approximate (floating-point) value.

Hexadecimal values

Hexadecimal values support only integers and start with the prefix X or 0x. The letters A to F are allowed, and all letters are case-insensitive.

The following are valid hexadecimal values:

X'01AF'
X'01af'
x'01AF'
x'01af'
0x01AF
0x01af

The following are invalid hexadecimal values:

X'0H' (H is not a hexadecimal digit)

Values prefixed with X must have an even number of digits, otherwise a syntax error will occur. To avoid this, pad the value with 0, for example: X'0FFF'.

If a value prefixed with 0x has an odd number of digits, it will be considered to have an additional leading 0. For example, 0xaaF is interpreted as 0x0aaF.

By default, hexadecimal values are binary strings, where each pair of hexadecimal digits represents a character:

SELECT 0x5461626c65, CHARSET(0x5461626c65);
+--------------+-----------------------+
| 0x5461626c65 | CHARSET(0x5461626c65) |
+--------------+-----------------------+
| Table        | binary                |
+--------------+-----------------------+
1 row in set (0.001 sec)

An empty hexadecimal value (X' ') is calculated as a binary string of zero length. When converted to a number and output, it is 0:

SELECT X''+0;
+-------+
| X''+0 |
+-------+
|     0 |
+-------+
1 row in set (0.001 sec)

To convert a string or number to a hexadecimal string, use the HEX() function:

SELECT HEX('dog');
+------------+
| HEX('dog') |
+------------+
| 646F67     |
+------------+
1 row in set (0.001 sec)

SELECT X'646F67';
+-----------+
| X'646F67' |
+-----------+
| dog       |
+-----------+
1 row in set (0.001 sec)