Numbers

Numbers are divided into decimal and hexadecimal types.

Decimal numbers

Decimal numbers are divided into exact numbers (integers and decimals) and approximate numbers. You can use a decimal point . to separate decimal numbers, and you can add a negative sign - before a number to indicate a negative value.

The DECIMAL data type is an exact type for precise calculations. The FLOAT and DOUBLE data types are approximate types for approximate calculations.

Exact numbers have an integer part, a fractional part, or both. For example: 1, .2, 3.4, -5, -6.78, and +9.10.

Approximate numbers are represented in scientific notation with a mantissa and an exponent. For example: 1.2E3, 1.2E-3, -1.2E3, and -1.2E-3.

Two similar-looking numbers may be treated differently. For example, 2.34 is an exact (fixed-point) number, and 2.34E0 is an approximate (floating-point) number.

Hexadecimal numbers

Hexadecimal numbers support only integers and start with the prefix X or 0x. They allow the use of the letters A to F, and all letters are case-insensitive.

The following are valid hexadecimal numbers:

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

The following are invalid hexadecimal numbers:

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

A value with the prefix X must contain an even number of digits. Otherwise, a syntax error occurs. To avoid this issue, pad the value with 0. For example: X'0FFF'.

A value with the prefix 0x with an odd number of digits is interpreted as having an additional leading 0. For example, 0xaaF is interpreted as 0x0aaF.

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

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

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

obclient> SELECT X''+0;
+-------+
| X''+0 |
+-------+
|     0 |
+-------+
1 row in set

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

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

obclient> SELECT X'646F67';
+-----------+
| X'646F67' |
+-----------+
| dog       |
+-----------+
1 row in set