NULL means "unknown", so almost any expression with a NULL is NULL, even NULL = NULL: test with IS NULL or <=>. Defaults may be expressions in parentheses.
SELECT NULL = NULL AS eq, NULL <=> NULL AS null_safe, CONCAT('a', NULL) AS cat,
COUNT(body) AS bodies, COUNT(*) AS all_rows, '5 apples' + 1 AS a, '1e3' + 0 AS b,
'abc' = 0 AS c, '10' > '9' AS d, 10 > '9' AS e FROM reviews;
INSERT INTO customers (email, country) VALUES ('ivy@example.com', 'US');
CREATE TABLE defs (id BINARY(16) DEFAULT (UUID_TO_BIN(UUID())), qty INT NOT NULL DEFAULT 1,
due DATE DEFAULT (CURRENT_DATE + INTERVAL 14 DAY));
INSERT INTO defs () VALUES ();
SELECT LENGTH(id) AS id_bytes, qty, due > CURRENT_DATE AS future FROM defs;Output
+------+-----------+------+--------+----------+---+------+---+---+---+ | eq | null_safe | cat | bodies | all_rows | a | b | c | d | e | +------+-----------+------+--------+----------+---+------+---+---+---+ | NULL | 1 | NULL | 6 | 7 | 6 | 1000 | 1 | 0 | 1 | +------+-----------+------+--------+----------+---+------+---+---+---+ Warning (Code 1292): Truncated incorrect DOUBLE value: '5 apples' Warning (Code 1292): Truncated incorrect DOUBLE value: 'abc' ERROR 1364 (HY000): Field 'name' doesn't have a default value +----------+-----+--------+ | id_bytes | qty | future | +----------+-----+--------+ | 16 | 1 | 1 | +----------+-----+--------+
A string meeting a number becomes a DOUBLE read from its leading digits, so 'abc' = 0 is true; two strings compare as text, so '10' > '9' is false. Only column writes are strict: setting stock = '12 units' fails with ERROR 1265. For WHERE sku = 123, EXPLAIN warns "Cannot use ref access on index 'sku' due to type or collation conversion". Pass values in the column's type (Placeholders) and CAST() deliberately.