CAST and Type Affinity

MySQL 8.4 has fixed column types, not affinity — CAST, CONVERT, and the implicit coercion rules that bite when you skip them.

SyntaxCAST(expr AS type) -- CONVERT(expr, type) is the identical call

MySQL columns have fixed declared types, so there is no SQLite-style affinity. What there is instead is implicit coercion: when the two sides of a comparison do not match, MySQL converts one of them, and the rule it uses is often not the one you assumed.

Explicit conversion

CAST(expr AS type) and CONVERT(expr, type) are the same operation with different spellings. Permitted target types include BINARY, CHAR, DATE, DATETIME, DECIMAL, DOUBLE, FLOAT, JSON, NCHAR, REAL, SIGNED, TIME, UNSIGNED and YEAR. There is no INT or VARCHAR here — you write SIGNED and CHAR. A second form, CONVERT(expr USING transcoding_name), changes character set rather than type.

Implicit conversion

If both arguments are strings they compare as strings; if both are integers, as integers. In most other cases — including string against number — they compare as double-precision floats. A string converts from its leading digits, so '6x' becomes 6 and 'x6' becomes 0. That is why SELECT 0 = 'x6' returns 1.

Strict mode

The default sql_mode in MySQL 8.4 includes STRICT_TRANS_TABLES, which rejects invalid values in INSERT and UPDATE instead of silently adjusting them. It does not change SELECT: invalid values there still produce a warning, not an error, so CAST('abc' AS SIGNED) returns 0.

Example

Example · sql
-- Legacy import table where everything arrived as text
CREATE TABLE staging_orders (
  ref        VARCHAR(32),
  qty_text   VARCHAR(16),
  total_text VARCHAR(32),
  placed_text VARCHAR(32)
);

INSERT INTO orders (ref, qty, total, placed_at)
SELECT
  s.ref,
  CAST(s.qty_text   AS UNSIGNED),          -- not 'INT': the keyword is UNSIGNED
  CAST(s.total_text AS DECIMAL(12,2)),     -- exact, so money arithmetic is exact
  CAST(s.placed_text AS DATETIME)          -- CONVERT(s.placed_text, DATETIME) is the same
FROM staging_orders s
-- Guard the rows first: in a SELECT a bad value casts to 0 with a warning,
-- but this INSERT runs under STRICT_TRANS_TABLES and would error on it
WHERE s.qty_text    REGEXP '^[0-9]+$'
  AND s.total_text  REGEXP '^[0-9]+(\\.[0-9]{1,2})?$'
  AND CAST(s.placed_text AS DATETIME) IS NOT NULL;

When to use it

  • Load a text staging table into typed columns after an import
  • Sort or compare numbers that are stored in a VARCHAR column
  • Force DECIMAL arithmetic on a value that would otherwise be treated as a float
  • Resolve an illegal mix of collations error between two tables created under different defaults

More examples

The coercion trap

Every line here is a bug someone has shipped. The first pair shows partial numeric conversion, the middle pair shows that string-versus-string and string-versus-number use different rules, and the last shows the index-killing form. Quote literals that are compared against character columns, always.

Example · sql
SELECT 1 > '6x',   -- 0: '6x' becomes 6
       7 > '6x',   -- 1
       0 = 'x6',   -- 1: 'x6' becomes 0
       '10' < '9', -- 1: both strings, compared as text
       10 < '9';   -- 0: string converted to a number

-- So this matches every row whose code is not numeric, and scans the table:
SELECT * FROM products WHERE code = 0;
-- Write this instead:
SELECT * FROM products WHERE code = '0';

Sorting numbers that live in a VARCHAR

A cast in ORDER BY fixes the result but is computed per row. If the sort matters often, store the cast as a generated column and index that, so the ordering becomes a cheap index read instead of a filesort.

Example · sql
-- Text order puts '10' before '9'
SELECT version FROM releases ORDER BY version;

-- Numeric order, via an explicit cast
SELECT version FROM releases
ORDER BY CAST(version AS UNSIGNED), version;

-- The permanent fix: a generated column that can be indexed
ALTER TABLE releases
  ADD COLUMN version_num INT UNSIGNED
    AS (CAST(version AS UNSIGNED)) STORED,
  ADD INDEX (version_num);

Fixing a collation mismatch in a join

CONVERT(expr USING charset) is the character-set form of the function, not the type form, and it is the quick unblock when two tables were created at different times under different defaults. Treat it as a stopgap: converting the column in the join predicate prevents use of the index on it, so the real fix is an ALTER TABLE on the older table.

Example · sql
-- ERROR 1267: Illegal mix of collations when the two columns differ
SELECT *
FROM legacy_users l          -- utf8mb3_general_ci
JOIN accounts a              -- utf8mb4_0900_ai_ci
  ON a.login = l.login;

-- Make the character sets agree for the comparison
SELECT *
FROM legacy_users l
JOIN accounts a
  ON a.login = CONVERT(l.login USING utf8mb4);

Related

sql data typessql comparisonsql numeric functionssql generated columnssql sargable

Discussion

  • Be the first to comment on this lesson.