Numbers can be decimal or hexadecimal.
Decimal numbers
Decimal numbers can be exact (integers and fixed-point numbers) or approximate (floating-point numbers). You can use a decimal point . to separate the integer and fractional parts of a number. You can also add a minus sign - before a number to indicate a negative value.
The DECIMAL data type is a fixed-point type that is used for exact calculations. The FLOAT and DOUBLE data types are floating-point types that are used for approximate calculations.
Note
You can reduce the precision of the DECIMAL type. For example, you can change DECIMAL(10, 2) to DECIMAL(9, 2).
Exact numbers have integer parts, fractional parts, or both. For example: 1, .2, 3.4, -5, -6.78, and +9.10.
Approximate numbers are represented in scientific notation, which consists of a mantissa and an exponent. For example: 1.2E3, 1.2E-3, -1.2E3, and -1.2E-3.
Two numbers that look similar can 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. They 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 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 that starts with the X prefix must contain an even number of digits. Otherwise, a syntax error occurs. To avoid this issue, pad the value with 0 to make the number of digits even. For example, X'0FFF'.
A value that starts with the 0x prefix and contains 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 interpreted as a binary string of zero length. When you convert it to a number and output it, it is 0:
obclient> SELECT X''+0;
+-------+
| X''+0 |
+-------+
| 0 |
+-------+
1 row in set
To convert a string or a 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