MENU


Built In Functions and Operators

Most of what a PHP page does to a value (rounding a price, trimming a name, grouping orders by month) can happen in the query instead, before the rows cross the network. The examples use SELECT to show each result, but every expression works equally in WHERE, ORDER BY, GROUP BY, UPDATE and generated columns (Section 3.4.8). All output comes from MySQL 9.7.2 against the shop database. Several functions from older tutorials are gone, and each removal is named where it matters.

3.7.1 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.Figure 3.6: MySQL operator precedence, from tightest (top left) to loosest (bottom right)Assignment uses := or, inside SET only, =. Anywhere else = compares.

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';

Listing 3.59: Arithmetic on stock and prices, bitwise operators, and an unsigned trap

+--------+-------+-------+-------+------+--------+------+-------+------+-------+-----+------+

| 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)'

Output of Listing 3.59 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 (Section 3.8.7).

3.7.2 Comparison Operators, IN, and BETWEEN

Comparisons (=, <> or !=, <, <=, >, >=) return 1, 0 or NULL; <=> is the NULL-safe equal, and IS [NOT] TRUE, FALSE or UNKNOWN tests a truth value. x BETWEEN a AND b means x >= a AND x <= b, IN tests a list, and row constructors compare left to right, the way a composite index sorts. The logical operators are AND, OR, NOT and XOR.

SELECT id, rating, body IS NULL AS no_text, rating <=> 4 AS is4,

rating BETWEEN 3 AND 4 AS mid, product_id IN (1, 6) AS in_list,

(product_id, rating) > (2, 3) AS row_gt, (rating > 3) XOR (body IS NULL) AS one_of,

customer_id NOT IN (3, NULL) AS not_in_null

FROM reviews WHERE customer_id IN (1, 2);

Listing 3.60: Comparison, IN, BETWEEN, row constructors and XOR on the reviews table

+----+--------+---------+-----+-----+---------+--------+--------+-------------+

| id | rating | no_text | is4 | mid | in_list | row_gt | one_of | not_in_null |

+----+--------+---------+-----+-----+---------+--------+--------+-------------+

| 1 | 5 | 0 | 0 | 0 | 1 | 0 | 1 | NULL |

| 5 | 4 | 1 | 1 | 1 | 1 | 1 | 0 | NULL |

| 2 | 4 | 0 | 1 | 1 | 0 | 1 | 1 | NULL |

| 3 | 3 | 0 | 0 | 1 | 0 | 1 | 0 | NULL |

+----+--------+---------+-----+-----+---------+--------+--------+-------------+

Output of Listing 3.60 No row belongs to customer 3, yet NOT IN (3, NULL) is NULL for all of them, so a WHERE with it keeps nothing: the classic NOT IN (SELECT ...) bug (Section 3.6.3). Under utf8mb4_0900_ai_ci, 'abc' = 'ABC' is 1, but, because the collation is NO PAD, 'abc' = 'abc ' is 0. &&, || and ! raise deprecation warning 1287; write AND, OR and NOT.

3.7.3 LIKE and Wildcards

LIKE matches a whole string against a pattern where % is any run of characters (even none) and _ exactly one. Backslash escapes a literal % or _, and ESCAPE picks another escape character. Case sensitivity follows the collation.

SELECT sku, title FROM products

WHERE title LIKE '%sql%' OR sku LIKE 'AC-___-01';

SELECT 'Mug' LIKE 'mug' AS ci, 'Mug' LIKE 'mug' COLLATE utf8mb4_0900_as_cs AS cs,

'50% off' LIKE '50\% %' AS esc, '50% off' LIKE '50!%%' ESCAPE '!' AS esc2,

'abc ' LIKE 'abc' AS trail, 'BK-PHP-01' NOT LIKE 'BK-%' AS nl;

Listing 3.61: Wildcards, escapes and case sensitivity with LIKE

+-----------+------------------------+

| sku | title |

+-----------+------------------------+

| BK-SQL-01 | SQL Queries That Scale |

| AC-MUG-01 | Semicolon Coffee Mug |

| AC-STK-01 | Stack Sticker Pack |

+-----------+------------------------+

+----+------+-----+------+-------+----+

| ci | cs | esc | esc2 | trail | nl |

+----+------+-----+------+-------+----+

| 1 | 0 | 1 | 1 | 0 | 0 |

+----+------+-----+------+-------+----+

Output of Listing 3.61 LIKE never pads, so 'abc ' does not match 'abc'. A fixed prefix such as sku LIKE 'BK-%' becomes a range on the sku index (EXPLAIN shows a range scan), but '%-01' forces a scan of every entry, the usual cause of a slow search box; use a full-text index (Section 3.9.7). Escape %, _ and \ in user input before binding it (Section 4.16.8), or a lone % matches every row.

3.7.4 Regular Expressions with REGEXP_LIKE and Its Companions

Since MySQL 8.0 regular expressions run on the Unicode-aware ICU library. REGEXP and RLIKE are synonyms for REGEXP_LIKE(), and three companions return where (REGEXP_INSTR), what (REGEXP_SUBSTR) or a rewritten string (REGEXP_REPLACE). A regex matches anywhere unless anchored with ^ and $, and a match type of 'c' (case-sensitive), 'i', 'm' (multiline) or 'n' overrides the collation.

SELECT sku, REGEXP_SUBSTR(sku, '[A-Z]{2,3}', 1, 2) AS topic,

REGEXP_INSTR(title, ' ') AS first_space, REGEXP_REPLACE(title, '\\s+', '_') AS snake

FROM products WHERE sku REGEXP '^BK-(SQL|LNX)-';

SELECT REGEXP_LIKE('CamelCase', 'camelcase') AS ci,

REGEXP_LIKE('CamelCase', 'camelcase', 'c') AS cs,

REGEXP_SUBSTR('Order 9 of 2026', '\\d+', 1, 2) AS second_num,

REGEXP_LIKE('1+2', '1\+2') AS one_slash, REGEXP_LIKE('1+2', '1\\+2') AS two_slash;

CREATE TABLE sku_rule (sku VARCHAR(20)

CHECK (REGEXP_LIKE(sku, '^[A-Z]{2}-[A-Z]{2,3}-[0-9]{2}$', 'c')));

INSERT INTO sku_rule VALUES ('BK-PHP-01'), ('bk-php-1');

Listing 3.62: Extracting, replacing and validating with the REGEXP functions

+-----------+-------+-------------+---------------------------+

| sku | topic | first_space | snake |

+-----------+-------+-------------+---------------------------+

| BK-SQL-01 | SQL | 4 | SQL_Queries_That_Scale |

| BK-SQL-02 | SQL | 9 | Indexing_Deep_Dive |

| BK-LNX-01 | LNX | 4 | The_Linux_Server_Handbook |

+-----------+-------+-------------+---------------------------+

+----+----+------------+-----------+-----------+

| ci | cs | second_num | one_slash | two_slash |

+----+----+------------+-----------+-----------+

| 1 | 0 | 2026 | 0 | 1 |

+----+----+------------+-----------+-----------+

ERROR 3819 (HY000): Check constraint 'sku_rule_chk_1' is violated.

Output of Listing 3.62 After the pattern come start position, occurrence and match type, so topic is the second letter group. Double every backslash: the string parser eats one, so '1\+2' reaches ICU as 1+2, "one or more 1s, then 2". regexp_time_limit and regexp_stack_limit make a runaway pattern fail with an error instead of hanging the connection.

3.7.5 String Functions

String positions start at 1. LENGTH() counts bytes and CHAR_LENGTH() characters, which differ once text leaves ASCII. The workhorses are CONCAT(), CONCAT_WS() (with a separator, skipping NULLs), LOWER(), UPPER(), TRIM(), LPAD(), LEFT(), SUBSTRING(), SUBSTRING_INDEX(), LOCATE(), REPLACE() and FIELD(). The first query builds a URL slug from each title.

SELECT title,

TRIM(BOTH '-' FROM REGEXP_REPLACE(LOWER(title), '[^a-z0-9]+', '-')) AS slug

FROM products WHERE category_id = 2;

SELECT CONCAT_WS(' / ', name, NULL, country) AS label,

SUBSTRING_INDEX(email, '@', -1) AS domain, CONCAT('C-', LPAD(id, 5, '0')) AS acct,

UPPER(LEFT(name, 3)) AS code, CONCAT(name, NULL) AS cat,

LENGTH('Café') AS bytes, CHAR_LENGTH('Café') AS chars

FROM customers WHERE id = 3;

Listing 3.63: Slugs from product titles, and labels built from a customer row

+----------------------------+----------------------------+

| title | slug |

+----------------------------+----------------------------+

| Modern PHP in Practice | modern-php-in-practice |

| Laravel From the Ground Up | laravel-from-the-ground-up |

| The Linux Server Handbook | the-linux-server-handbook |

+----------------------------+----------------------------+

+---------------+-------------+---------+------+------+-------+-------+

| label | domain | acct | code | cat | bytes | chars |

+---------------+-------------+---------+------+------+-------+-------+

| Chen Wei / MY | example.com | C-00003 | CHE | NULL | 5 | 4 |

+---------------+-------------+---------+------+------+-------+-------+

Output of Listing 3.63 The slug lowercases, turns each run of other characters into one hyphen, and trims hyphens from the ends. It would drop the "é" of "Café", so transliterate accented titles in PHP (Section 4.6.10) or use Laravel's Str::slug(). VARCHAR(n) counts characters, so validate with CHAR_LENGTH(). CONCAT() returns NULL if any part is NULL, which is why optional parts belong in CONCAT_WS(). ORDER BY FIELD(status, 'pending', 'paid', 'shipped') sorts in a custom order.

3.7.6 Numeric and Rounding Functions

ROUND(x, d) rounds to d places (a negative d rounds left of the point), TRUNCATE() cuts, FLOOR() and CEIL() go down and up, and ABS(), MOD(), POW(), SQRT() and RAND() do what their names say. FORMAT(x, d, locale) returns a display string. CAST(expr AS type) or CONVERT(expr, type) converts to SIGNED, UNSIGNED, DECIMAL(m,d), DOUBLE, FLOAT, CHAR, BINARY, DATE, DATETIME, TIME, YEAR or JSON.

SELECT FORMAT(SUM(price * stock), 2) AS inventory,

FORMAT(SUM(price * stock), 2, 'de_DE') AS de, ROUND(SUM(price * stock), -2) AS hundreds,

ROUND(2.5) AS r_dec, ROUND(2.5E0) AS r_dbl, TRUNCATE(4.99, 1) AS t, FLOOR(-4.2) AS fl

FROM products;

SELECT 0.1 + 0.2 = 0.3 AS dec_eq, 0.1E0 + 0.2E0 AS dbl_sum,

CAST(stock AS SIGNED) - 10 AS sold, CAST('39.90' AS DECIMAL(6,2)) * 3 AS dec3,

CAST(2.99 AS UNSIGNED) AS u, CAST(price AS CHAR) AS txt,

CONVERT('2026-09-23', DATE) + INTERVAL 1 DAY AS tmrw

FROM products WHERE sku = 'BK-SQL-02';

Listing 3.64: Formatting inventory value, rounding exact and approximate values, and casting

+-----------+----------+----------+-------+-------+-----+----+

| inventory | de | hundreds | r_dec | r_dbl | t | fl |

+-----------+----------+----------+-------+-------+-----+----+

| 4,528.00 | 4.528,00 | 4500 | 3 | 2 | 4.9 | -5 |

+-----------+----------+----------+-------+-------+-----+----+

+--------+---------------------+------+--------+---+-------+------------+

| dec_eq | dbl_sum | sold | dec3 | u | txt | tmrw |

+--------+---------------------+------+--------+---+-------+------------+

| 1 | 0.30000000000000004 | -10 | 119.70 | 3 | 29.00 | 2026-09-24 |

+--------+---------------------+------+--------+---+-------+------------+

Output of Listing 3.64 An exact value (DECIMAL, or a literal like 2.5) rounds half away from zero; an approximate one (2.5E0, a DOUBLE) follows the C library's round-half-to-even. Keep money in DECIMAL (Section 3.3.3) and sums stay exact, as dec_eq shows. An integer cast rounds as well, so CAST(2.99 AS UNSIGNED) is 3. FORMAT() output no longer sorts or sums as a number, so format last, or in PHP when the locale comes from the visitor.

3.7.7 Date and Time Functions and Format Specifiers

NOW() is fixed when the statement starts, while SYSDATE() reads the clock at each call. DATE_ADD(), DATE_SUB() and + INTERVAL n unit do arithmetic, DATEDIFF() counts days and TIMESTAMPDIFF(unit, a, b) any unit. DATE_FORMAT() and STR_TO_DATE() share specifiers: %Y year, %m and %b month, %d and %D day ("11th"), %H and %l hour, %i minutes, %p AM/PM, %a weekday.

SELECT DATE_FORMAT(ordered_at, '%Y-%m') AS month, COUNT(*) AS orders,

GROUP_CONCAT(DISTINCT YEARWEEK(ordered_at, 3)) AS iso_weeks,

MIN(DATE(ordered_at) - INTERVAL WEEKDAY(ordered_at) DAY) AS first_monday,

DATE_FORMAT(MIN(ordered_at), '%a %D %b, %l:%i %p') AS first_order

FROM orders WHERE ordered_at < '2026-06-01' GROUP BY month ORDER BY month;

Listing 3.65: Orders bucketed by month and ISO week, with each month's first order formatted

+---------+--------+---------------+--------------+-----------------------+

| month | orders | iso_weeks | first_monday | first_order |

+---------+--------+---------------+--------------+-----------------------+

| 2026-03 | 2 | 202610,202611 | 2026-03-02 | Mon 2nd Mar, 10:15 AM |

| 2026-04 | 1 | 202615 | 2026-04-06 | Sat 11th Apr, 7:30 AM |

| 2026-05 | 2 | 202619,202621 | 2026-05-04 | Tue 5th May, 12:00 PM |

+---------+--------+---------------+--------------+-----------------------+

Output of Listing 3.65 YEARWEEK(d, 3) numbers Monday-based ISO weeks; the default mode 0 starts on Sunday. Subtracting WEEKDAY() days gives the week's Monday, a friendlier label. Month arithmetic clamps: '2026-01-31' + INTERVAL 1 MONTH is 2026-02-28. CONVERT_TZ() with a named zone such as 'Asia/Kuala_Lumpur' returns NULL until the zone tables are loaded (mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql), while offsets such as '+08:00' always work. In WHERE, keep the column bare (ordered_at >= '2026-03-01', not MONTH(ordered_at) = 3) so an index applies (Section 3.12.9).

3.7.8 COALESCE, IFNULL, NULLIF, GREATEST, and LEAST

These flow-control functions sit beside CASE and IF() (Section 3.5.12). COALESCE() returns its first non-NULL argument and IFNULL(a, b) is the two-argument form. NULLIF(a, b) is NULL when a = b, which turns a zero divisor into a safe NULL. GREATEST() and LEAST() clamp, and INTERVAL(n, b1, b2, ...) counts the sorted bounds n has reached, a quick banding tool.

SELECT p.sku, IFNULL(ROUND(AVG(r.rating), 1), 'none') AS avg,

COALESCE(LEFT(MAX(r.body), 14), p.attributes->>'$.format', 'n/a') AS blurb,

ROUND(p.price / NULLIF(p.stock, 0), 2) AS per_unit,

GREATEST(CAST(p.stock AS SIGNED) - 20, 0) AS surplus, LEAST(p.price, 30) AS capped,

INTERVAL(p.price, 10, 30, 45) AS band, GREATEST(p.price, NULL) AS g_null,

GREATEST(10, '9') AS g_mixed

FROM products AS p LEFT JOIN reviews AS r ON r.product_id = p.id

WHERE p.id IN (3, 6, 8) GROUP BY p.id;

Listing 3.66: Fallbacks, a safe division, clamping and price bands on the product list

+-----------+------+----------------+----------+---------+--------+------+--------+---------+

| sku | avg | blurb | per_unit | surplus | capped | band | g_null | g_mixed |

+-----------+------+----------------+----------+---------+--------+------+--------+---------+

| BK-SQL-02 | 3.0 | Good on B-tree | NULL | 0 | 29.00 | 1 | NULL | 9 |

| BK-LNX-01 | 4.0 | paperback | 4.67 | 0 | 30.00 | 2 | NULL | 9 |

| AC-STK-01 | none | n/a | 0.03 | 130 | 4.99 | 0 | NULL | 9 |

+-----------+------+----------------+----------+---------+--------+------+--------+---------+

Output of Listing 3.66 The out-of-stock ebook gets a NULL unit price, not a warning, and the Linux book, whose only review has no body, falls back to its JSON format. GREATEST() and LEAST() return NULL if any argument is NULL, and a mix of numbers and strings compares as strings, so GREATEST(10, '9') is '9'. IFNULL(AVG(...), 'none') also makes the column text; return the NULL and render "none" in PHP when the value feeds a chart.

3.7.9 Hashing, Encryption, Compression, UUIDs, and Server Information

The list has shrunk. PASSWORD() went in MySQL 8.0.11, and ENCRYPT(), ENCODE(), DECODE(), DES_ENCRYPT() and DES_DECRYPT() in 8.0.3. MD5() and SHA1() (with its alias SHA()) were deprecated in 9.4.0 and moved out of the server in 9.6.0, so 9.7 LTS has only SHA2() built in; an administrator can restore the old two with INSTALL COMPONENT 'file://component_classic_hashing'. AES_ENCRYPT() defaults to aes-128-ecb; set block_encryption_mode to CBC and pass a random IV.

SELECT MD5('ana@example.com');

SET block_encryption_mode = 'aes-256-cbc';

SET @key = UNHEX(SHA2('passphrase kept outside the database', 256)), @iv = RANDOM_BYTES(16);

SET @ct = AES_ENCRYPT('4111 1111 1111 1111', @key, @iv);

SELECT LEFT(SHA2('ana@example.com', 256), 16) AS sha256_start, LENGTH(@ct) AS ct_bytes,

CAST(AES_DECRYPT(@ct, @key, @iv) AS CHAR) AS plain,

AES_DECRYPT(@ct, UNHEX(SHA2('wrong', 256)), @iv) AS wrong_key,

LENGTH(COMPRESS(REPEAT('shop ', 1000))) AS packed_5000;

INSERT INTO customers (email, name, country) VALUES ('ivy@example.com', 'Ivy Tan', 'SG');

SELECT LAST_INSERT_ID() AS new_id, VERSION() AS version, DATABASE() AS db,

CURRENT_USER() AS me, UUID() AS uuid;

Listing 3.67: SHA-2, AES-256-CBC, compression, and what the server says about the session

ERROR 1305 (42000): FUNCTION shop.MD5 does not exist

+------------------+----------+---------------------+-----------+-------------+

| sha256_start | ct_bytes | plain | wrong_key | packed_5000 |

+------------------+----------+---------------------+-----------+-------------+

| 8e43ca37701228e7 | 32 | 4111 1111 1111 1111 | NULL | 42 |

+------------------+----------+---------------------+-----------+-------------+

+--------+---------+---------+----------------+--------------------------------------+

| new_id | version | db | me | uuid |

+--------+---------+---------+----------------+--------------------------------------+

| 9 | 9.7.2 | shop | root@localhost | 3181c3fc-b724-11f1-98d6-00155d5f9c3f |

+--------+---------+---------+----------------+--------------------------------------+

Output of Listing 3.67 A wrong key usually gives NULL, but not always. SQL-side encryption sends key and plaintext to the server, where logs and SHOW PROCESSLIST can expose them: encrypt in PHP with Sodium (Section 4.17.12), and hash passwords with password_hash() (Section 4.17.9). UUID() is version 1, time-based; store it as BINARY(16) via UUID_TO_BIN(u, 1), whose swap flag puts the time bits first so keys insert in index order. LAST_INSERT_ID() is per connection, so concurrent inserts never see each other's ids.