Data types, strict mode and implicit conversion
MySQL converts between strings and numbers silently. Know when it does, what strict mode refuses, and how a quoted number can switch an index off.
After this lesson you can
- Predict the result of comparing a string with a number in MySQL
- Explain what strict SQL mode changes about bad inserts
- Spot a type mismatch that turns an index lookup into a scan
- Validate numeric text explicitly instead of trusting CAST
MySQL is generous with types. When an expression mixes a string and a number, it converts the string to a number and carries on. The rules are simple once written down, and surprising until then.
String meets number
When one side of a comparison is a number and the other a string, both are
compared as numbers. A string becomes a number by reading its leading
digits and stopping at the first character that cannot be part of one; a
string with no leading number becomes 0. Two strings are compared as
strings, character by character, under the column's collation.
SELECT '1abc' = 1 AS str_vs_num, 'abc' = 0 AS no_digits_is_zero, '10' < '9' AS two_strings, 10 < '9' AS num_vs_str, 0.1 + 0.2 = 0.3 AS decimal_sum, CAST(0.1 AS DOUBLE) + CAST(0.2 AS DOUBLE) = 0.3 AS double_sum;'1abc' = 1 is true, 'abc' = 0 is true, and '10' < '9' is true because
'1' sorts before '9'. The last two columns show a separate point: a
literal like 0.1 is an exact DECIMAL in MySQL, so 0.1 + 0.2 = 0.3
holds, while the same sum in DOUBLE misses by a rounding error. Money
belongs in DECIMAL(p, s), never in FLOAT or DOUBLE.
Strict mode decides whether bad data is an error
MySQL 8.4's default sql_mode includes STRICT_TRANS_TABLES. In strict
mode an INSERT or UPDATE that would have to change a value to store it
(300 into a TINYINT, eight characters into a VARCHAR(5)) fails. With
strict mode off, the same statement succeeds and quietly stores something
else.
CREATE TABLE t (n TINYINT, s VARCHAR(5));-- A helper that runs a statement and records the error instead of stopping.CREATE TABLE attempts (n INT AUTO_INCREMENT PRIMARY KEY, statement TEXT, outcome TEXT);CREATE PROCEDURE try_sql(IN stmt TEXT)BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @err_no = MYSQL_ERRNO, @err_msg = MESSAGE_TEXT; INSERT INTO attempts (statement, outcome) VALUES (stmt, CONCAT('error ', @err_no, ': ', REPLACE(@err_msg, CONCAT(DATABASE(), '.'), ''))); END; SET @sql = stmt; PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s; INSERT INTO attempts (statement, outcome) VALUES (stmt, 'ok');END;-- Strict mode is on by default.CALL try_sql('INSERT INTO t VALUES (300, ''abcdefgh'')'); -- Now switch it off for this session and run the same insert directly.SET SESSION sql_mode = '';INSERT INTO t VALUES (300, 'abcdefgh'); SELECT 'strict (default)' AS sql_mode_used, outcome, NULL AS stored_n, NULL AS stored_sFROM attemptsUNION ALLSELECT 'empty', 'ok', n, sFROM t;Without strict mode, 300 became 127 (the TINYINT maximum) and the
string was cut to five characters, each with only a warning nobody reads.
Strict mode affects data-changing statements; a SELECT that converts
'12abc' still just returns 12 with a warning. Some legacy applications
switch strict mode off at connect time, so check @@sql_mode before you
trust what a table contains.
A type mismatch can disable an index
Store phone numbers in a VARCHAR and compare with a number, and MySQL
must convert every row's value to a number to compare it. The index is
ordered by string, not by numeric value, so it cannot be searched for
5551234; many different strings ('5551234', '05551234',
' 5551234') convert to that same number.
CREATE TABLE customers ( id INT PRIMARY KEY, phone VARCHAR(20) NOT NULL, KEY idx_phone (phone));INSERT INTO customers VALUES (1, '5551234'), (2, '05551234'), (3, '5559999'), (4, '5550000'), (5, '5551111'), (6, '5552222');ANALYZE TABLE customers;EXPLAIN FORMAT=JSON INTO @num SELECT id FROM customers WHERE phone = 5551234;EXPLAIN FORMAT=JSON INTO @str SELECT id FROM customers WHERE phone = '5551234'; SELECT 'phone = 5551234' AS predicate, JSON_UNQUOTE(JSON_EXTRACT(@num, '$.query_block.table.access_type')) AS access_type, (SELECT GROUP_CONCAT(id ORDER BY id) FROM customers WHERE phone = 5551234) AS matching_idsUNION ALLSELECT 'phone = ''5551234''', JSON_UNQUOTE(JSON_EXTRACT(@str, '$.query_block.table.access_type')), (SELECT GROUP_CONCAT(id ORDER BY id) FROM customers WHERE phone = '5551234');The numeric comparison reads the entire index (access_type index, a full
index scan) and also returns the wrong rows: '05551234' converts to the
same number. The quoted comparison is a ref lookup, an equality search in
the index, and returns exactly one row. The fix is always on the query side:
compare a string column with a string.
Common mistakes
- Comparing a
VARCHARcolumn with a number. You get a full scan and matches you did not ask for, asphone = 5551234shows above. - Trusting
CAST(x AS UNSIGNED)to validate input. It reads the leading digits and ignores the rest, so'12abc'becomes 12 and'abc'becomes 0. Check the text with a pattern first; the exercise is exactly that. - Turning strict mode off to make an import "work". The import then succeeds by storing values that are not what the file said.
- Storing money in
DOUBLE. Binary floating point cannot represent0.1exactly;DECIMALcan.
What interviewers ask
- What does
SELECT '5 apples' = 5return in MySQL, and why? : the string is converted to a number by reading its leading digits.
Try it yourself
2 visible tests · 2 hidden testsTable imports(id, raw_qty) holds quantities exactly as they arrived in a
CSV file, as VARCHAR. A quantity is valid only if raw_qty consists
of one or more digits 0-9 and nothing else: no spaces, signs,
letters, decimal points or exponents. '007' and '0' are valid.
Return id and qty (the value as an integer) for every valid row,
ordered by id.
CREATE TABLE imports (id INT PRIMARY KEY, raw_qty VARCHAR(20) NOT NULL);INSERT INTO imports VALUES (1, '12'), (2, 'abc'), (3, '007'), (4, '40');Sign up to run the tests, check the hidden ones and save your progress. Sign up