Numeric values are divided into decimal and hexadecimal values.
Decimal values
Decimal values are divided into exact values (integer and decimal values) and approximate values. You can use the decimal point . as a decimal separator. You can also add a minus sign - before a value 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
The DECIMAL type supports reducing the precision (for example, DECIMAL(10, 2) can be changed to DECIMAL(9, 2)).
Exact values have an integer part, a fractional part, or both. Examples: 1, .2, 3.4, -5, -6.78, and +9.10.
Approximate values are represented in scientific notation with a mantissa and an exponent. Examples: 1.2E3, 1.2E-3, -1.2E3, and -1.2E-3.
Two values that appear similar may be treated differently. For example, 2.34 is an exact (fixed-point) value, and 2.34E0 is an approximate (floating-point) value.
Hexadecimal values
Hexadecimal values support only integer values and start with the prefix X or 0x. The hexadecimal digits A to F 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)
A value that starts with the prefix X must contain an even number of digits. Otherwise, a syntax error occurs. To avoid this error, pad the value with 0 as needed. For example: X'0FFF'.
A value that starts with the prefix 0x and contains an odd number of digits is treated as having 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:
obclient> SELECT 0x5461626c65, CHARSET(0x5461626c65);
+--------------+-----------------------+
| 0x5461626c65 | CHARSET(0x5461626c65) |
+--------------+-----------------------+
| Table | binary |
+--------------+-----------------------+
1 row in set
An empty hexadecimal value (X' ') is treated 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