Numbers are divided into decimal and hexadecimal numbers.
Decimal numbers
Decimal numbers are divided into exact numbers (integers and decimal numbers) and approximate numbers. Decimal numbers can be separated by a decimal point . and can be negative by adding a minus sign - in front.
The DECIMAL data type is a fixed-point type, which is used for exact calculations. The FLOAT and DOUBLE data types are floating-point types, which are used for approximate calculations.
Note
The DECIMAL type supports reducing the precision (for example, from DECIMAL(10, 2) to DECIMAL(9, 2)).
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, while 2.34E0 is an approximate (floating-point) number.
Hexadecimal numbers
Hexadecimal numbers support only integer values and start with the prefix X or 0x. They allow the use of the letters A to F, which 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)
Values with the prefix 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'.
Values with the prefix 0x that have an odd number of digits are treated 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