Type Conversion
CONVERT(expr, type [ARRAY])
CAST(expr AS type [ARRAY]) |
| type can be BINARY [(N)], CHAR [(N)],
DATE, DATETIME, DECIMAL [(M[,D])], TIME,
or UNSIGNED [INTEGER]. The optional ARRAY keyword casts
to a JSON array of the target type, for use with a multi-valued
index. |
Explicit Conversion
SELECT 2 + '2';
SELECT CONCAT(2, '2'); |
4 (the string is coerced to a number for +); '22' (CONCAT()
always coerces its arguments to strings).
| SELECT CONVERT(2, CHAR) + CAST(2 AS UNSIGNED INT); |
4 — CONVERT(2, CHAR) yields the string '2', which is
then coerced right back to a number for +.
SELECT CAST(22 AS YEAR);
SELECT CONVERT(88, YEAR); |
2022; 1988 — two-digit years 00–69 map to
2000–2069, and 70–99 map to 1970–1999.
| SELECT CONVERT('xyz' USING 'utf8'); |
'xyz' — re-encodes a string into a different character
set.
SELECT 2 + '2';
SELECT CONCAT(2, '2');
SELECT CONVERT(2, CHAR) + CAST(2 AS UNSIGNED INT);
SELECT CAST(22 AS YEAR);
SELECT CONVERT(88, YEAR);
SELECT CONVERT('xyz' USING 'utf8');
SELECT 2 + '2';
SELECT CONCAT(2, '2');
SELECT CONVERT(2, CHAR) + CAST(2 AS UNSIGNED INT);
SELECT CAST(22 AS YEAR);
SELECT CONVERT(88, YEAR);
SELECT CONVERT('xyz' USING 'utf8');
SELECT 2 + '2':
SELECT CONCAT(2, '2'):
SELECT CONVERT(2, CHAR) + CAST(2 AS UNSIGNED INT):
| CONVERT(2, CHAR) + CAST(2 AS UNSIGNED INT) |
|---|
| 4 |
SELECT CAST(22 AS YEAR):
SELECT CONVERT(88, YEAR):
SELECT CONVERT('xyz' USING 'utf8'):
| CONVERT('xyz' USING 'utf8') |
|---|
| xyz |
Implicit Conversion
Implicit casts occur whenever MySQL encounters an expression or
comparison whose operands have different data types; the server
converts one or both operands to a compatible type automatically.
NULL — any comparison against NULL yields NULL, not a
boolean; see NULL Handling.
| SELECT 1 = 0x01, '1' = 0x01; |
1, 0 — 0x01 is compared numerically against the bare
integer 1 (equal), but as a binary string against the string '1'
(not equal, since the byte 0x01 differs from the ASCII character
'1').
| SELECT 2 > '5x', 0 = 'x5'; |
0, 1 — '5x' converts to the number 5 for the numeric
comparison (2>5 is false); 'x5' has no leading digits and
converts to 0 (0=0 is true).
| SELECT 'a' = 'A', BINARY 'a' = 'A'; |
1, 0 — the default collation compares letters
case-insensitively; forcing a BINARY cast compares raw bytes,
where lowercase and uppercase differ.
SELECT 1 > NULL;
SELECT 1 = 0x01, '1' = 0x01;
SELECT 2 > '5x', 0 = 'x5';
SELECT 'a' = 'A', BINARY 'a' = 'A';
SELECT 1 > NULL;
SELECT 1 = 0x01, '1' = 0x01;
SELECT 2 > '5x', 0 = 'x5';
SELECT 'a' = 'A', BINARY 'a' = 'A';
SELECT 1 > NULL:
SELECT 1 = 0x01, '1' = 0x01:
SELECT 2 > '5x', 0 = 'x5':
SELECT 'a' = 'A', BINARY 'a' = 'A':
| 'a' = 'A' | BINARY 'a' = 'A' |
|---|
| 1 | 0 |
SELECT * FROM products WHERE price = '10.99';
SELECT * FROM orders WHERE order_date > '2023-01-01';
SELECT CONCAT('Product ', product_id) AS description FROM products; |
String literals are implicitly converted to numeric, date, and string context respectively, without an explicit CAST/CONVERT.
CREATE TABLE products (product_id INT, price DECIMAL(10,2));
INSERT INTO products VALUES (1, 10.99);
-- Implicit conversion in a WHERE clause: the string '10.99' is
-- converted to a DECIMAL to compare against price
SELECT * FROM products WHERE price = '10.99';
CREATE TABLE products (product_id INT, price DECIMAL(10,2));
INSERT INTO products VALUES (1, 10.99);
-- Implicit conversion in a WHERE clause: the string '10.99' is
-- converted to a DECIMAL to compare against price
SELECT * FROM products WHERE price = '10.99';