Arithmetic and Bitwise

Arithmetic, Bitwise, and Assignment Operators

The arithmetic operators are +, -, *, /, DIV (integer division) and % or MOD. On exact operands / returns a DECIMAL with four more decimal places than the dividend. Division by zero gives NULL and warning 1365 in a SELECT, but an error in a strict-mode INSERT or UPDATE. The bitwise operators &, |, ^, ~, << and >> work on 64-bit unsigned integers.

MySQL 524 operator precedence, from tightest (top left) to loosest (bottom right)

Assignment uses := or, inside SET only, =. Anywhere else = compares.

Arithmetic on stock and prices, bitwise operators, and an unsigned trapSQL

SET @vat := 0.06;
SELECT price * stock AS value, stock DIV 12 AS boxes, stock % 12 AS loose,
       ROUND(price * (1 + @vat), 2) AS gross, 2 + 3 * 4 AS prec, 7 / 2 AS q, 1 / 0 AS z,
       6 & 3 AS b_and, 6 | 3 AS b_or, 6 ^ 3 AS b_xor, 1 << 4 AS shl, -7 DIV 2 AS idiv
FROM products WHERE sku = 'AC-STK-01';
SELECT stock - 10 FROM products WHERE sku = 'BK-SQL-02';

Output

+--------+-------+-------+-------+------+--------+------+-------+------+-------+-----+------+
| value  | boxes | loose | gross | prec | q      | z    | b_and | b_or | b_xor | shl | idiv |
+--------+-------+-------+-------+------+--------+------+-------+------+-------+-----+------+
| 748.50 |    12 |     6 |  5.29 |   14 | 3.5000 | NULL |     2 |    7 |     5 |  16 |   -3 |
+--------+-------+-------+-------+------+--------+------+-------+------+-------+-----+------+
Warning (Code 1365): Division by 0
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`shop`.`products`.`stock` - 10)'

DIV truncates toward zero, so -7 DIV 2 is -3. The error bites real code: stock is INT UNSIGNED, a subtraction with an unsigned operand is unsigned, and 0 - 10 fails. Write CAST(stock AS SIGNED) - 10. Assigning inside a SELECT (@n := @n + 1, the old running-total trick) draws deprecation warning 1287; use SET or SELECT ... INTO, and window functions for running totals (Frames and Running Totals).

MENU