Numbers can be decimal or hexadecimal.
Decimal values
Decimal numbers can be exact values (integers and fixed-point numbers) or floating-point numbers. A decimal point . can be used as the decimal separator, or a negative sign - can be added to the front to indicate a negative value.
The DECIMAL data type is a fixed-point type that performs exact calculations. The FLOAT and DOUBLE data types are floating-point types that perform approximate calculations.
Note
The DECIMAL type supports reduction in scale (for example, changing DECIMAL(10, 2) to DECIMAL(9, 2)).
Exact values are numbers that have an integer part or a decimal part, or both. For example: 1, .2, 3.4, -5, -6.78, +9.10.
Approximate values are expressed in scientific notation, consisting of a mantissa and an exponent. For example: 1.2E3, 1.2E-3, -1.2E3, -1.2E-3.
Two seemingly similar numbers may be treated differently. For example, 2.34 is a precise (fixed-point) number, while 2.34E0 is an approximate (floating-point) number.
Hexadecimal values
Hexadecimal numeric values can only be integers. They must start with the prefix X or 0x, and can contain letters from A to F. 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' (where H is not a hexadecimal digit)
A value prefixed with X must have an even number of digits; otherwise, a syntax error occurs. To avoid this issue, pad the value with 0, for example: X'0FFF'.
If a value prefixed with 0x has an odd number of digits, it is considered to have an additional leading 0. For example, 0xaaF is interpreted as 0x0aaF.
By default, a hexadecimal value is a binary string in which each pair of hexadecimal digits represents one character:
obclient> SELECT 0x5461626c65, CHARSET(0x5461626c65);
+--------------+-----------------------+
| 0x5461626c65 | CHARSET(0x5461626c65) |
+--------------+-----------------------+
| Table | binary |
+--------------+-----------------------+
1 row in set
An empty hexadecimal value (X' ') is evaluated as a binary string of zero length. 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