MySQL CAST: CAST, CONVERT and Implicit Casting

MySQL converts values between types automatically when they do not match. This is convenient and also a source of surprising results.

Implicit conversion

query.sql

SQL

SELECT '10' + 5 AS text_plus_number, '3 apples' + 2 AS leading_number, 'abc' + 1 AS no_number;

Output

OUTPUT

+------------------+----------------+-----------+
| text_plus_number | leading_number | no_number |
+------------------+----------------+-----------+
|               15 |              5 |         1 |
+------------------+----------------+-----------+
1 row in set

Text is converted to a number by reading its beginning. '3 apples' becomes 3 (with a warning), and 'abc' becomes 0. In SELECT, this only gives a warning. In an UPDATE or INSERT with a number column, the strict mode refuses it (SQL Mode).

Comparing different types

When you compare a number with text, MySQL turns both into numbers, which can match more than you expect:

query.sql

SQL

SELECT '1abc' = 1 AS text_equals_one, 'abc' = 0 AS abc_equals_zero, '0.5' = 0.5 AS decimal_text;

Output

OUTPUT

+-----------------+-----------------+--------------+
| text_equals_one | abc_equals_zero | decimal_text |
+-----------------+-----------------+--------------+
|               1 |               1 |            1 |
+-----------------+-----------------+--------------+
1 row in set

'abc' = 0 is true, because 'abc' becomes 0. This is a real source of bugs and, in login code that compares a text password to a number, of security problems.

An index can be lost

When a column and a value have different types, MySQL may convert the column, and a conversion of every row cannot use an index:

query.sql

SQL

1
2

CREATE TABLE phones (id INT AUTO_INCREMENT PRIMARY KEY, number VARCHAR(15) NOT NULL, KEY idx_number (number));
INSERT INTO phones (number) VALUES ('9845012345'), ('9845012346');

query.sql

SQL

1
2

EXPLAIN FORMAT=TRADITIONAL SELECT id FROM phones WHERE number = '9845012345';
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM phones WHERE number = 9845012345;

Output

OUTPUT

+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | phones | NULL       | ref  | idx_number    | idx_number | 62      | const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
1 row in set

+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | phones | NULL       | index | idx_number    | idx_number | 62      | NULL |    2 |    50.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
1 row in set

The first query has type = ref: MySQL jumps straight to the matching entries in the index. The second has type = index and rows = 2: it reads the whole index and converts every value to a number before comparing. On a table with millions of rows, that is the difference between milliseconds and seconds. (The EXPLAIN columns are explained in EXPLAIN.)

Match the type of the column: quote text values, and pass numbers as numbers. In application code with prepared statements, bind the parameter with the correct type.

Explicit conversion: CAST and CONVERT

query.sql

SQL

SELECT CAST('42' AS UNSIGNED) AS to_number,
       CAST(3.789 AS SIGNED) AS rounded,
       CAST(42 AS CHAR) AS to_text,
       CAST('2026-07-15 10:30:00' AS DATE) AS to_date,
       CAST('2026-07-15' AS DATETIME) AS to_datetime,
       CONVERT('7', SIGNED) AS convert_form,
       CAST(1234.5 AS DECIMAL(10,2)) AS to_decimal;

Output

OUTPUT

+-----------+---------+---------+------------+---------------------+--------------+------------+
| to_number | rounded | to_text | to_date    | to_datetime         | convert_form | to_decimal |
+-----------+---------+---------+------------+---------------------+--------------+------------+
|        42 |       4 | 42      | 2026-07-15 | 2026-07-15 00:00:00 |            7 |    1234.50 |
+-----------+---------+---------+------------+---------------------+--------------+------------+
1 row in set

Target	Writes
`SIGNED`, `UNSIGNED`	Integers
`DECIMAL(p,s)`	Exact numbers
`CHAR`, `CHAR(n)`	Text
`DATE`, `TIME`, `DATETIME`	Date and time
`JSON`	JSON documents
`BINARY`	Bytes

CAST(x AS type) is standard SQL. CONVERT(x, type) is the same thing in a different form. CONVERT(x USING charset) changes the character set of text.

CAST(3.789 AS SIGNED) rounds (to 4 here), it does not cut. Use FLOOR() or TRUNCATE() when you need other behaviour (Numeric Functions).

Failed conversion

query.sql

SQL

1
2

SELECT CAST('abc' AS UNSIGNED) AS bad_number;
SHOW WARNINGS;

Output

OUTPUT

+------------+
| bad_number |
+------------+
|          0 |
+------------+
1 row in set

+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'abc' |
+---------+------+------------------------------------------+
1 row in set

Habits that avoid trouble

Use the right column type to begin with.
Use CAST when you mean to convert, so the reader sees it.
Bind query parameters with their real type.
Never rely on 'abc' = 0 style behaviour. Treat a conversion warning as a bug.
Keep STRICT_TRANS_TABLES on.

warning

Comparing an ID column with text

WHERE id = '5 apples' quietly matches id = 5. User input must be validated and bound as a number, not pasted into the query.