Understanding MySQL Implicit Type Conversion and Its Pitfalls

1. Background

A colleague needed a quick data report and wrote a simple JOIN query, mistakenly joining user.id (int) with order.order_code (varchar). The query ran without error, consumed high CPU, and returned unexpected results, prompting an investigation.

2. Reproducing the Scenario

Two tables are created:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `user` VALUES (1, '张三', 28, '2022-09-06 07:40:56', '2022-09-06 07:40:59');

CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `order_code` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `money` decimal(20,0) DEFAULT NULL,
  `title` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `order` VALUES (1, 2, '1d90530e-6ada-47c1-b2fa-adba4545aabd', 100, 'xxx购买两件商品', '2022-09-06 07:42:25', '2022-09-06 07:42:27');

The intended query is:

select o.* from `user` u 
left JOIN `order` o on u.id = o.user_id;

but the mistaken query uses:

select o.* from `user` u 
left JOIN `order` o on u.id = o.order_code;

Even though u.id is an integer and o.order_code is a varchar, MySQL executes the statement and returns rows.

3. Root Cause

MySQL implicitly converts the varchar order_code to an unsigned integer because the comparison value is numeric. The conversion stops at the first non‑digit character, so the string '1d90530e…' becomes 1 , matching the integer 1 .

select cast('1d90530e-6ada-47c1-b2fa-adba4545aabd' as unsigned);

Other examples show the conversion rule: leading digits are kept, conversion stops at the first non‑digit, and if the first character is non‑numeric the result is 0 .

select cast('223kkk' as unsigned);   -- returns 223
select cast('k223kkk' as unsigned); -- returns 0

4. Implicit Conversion Rules

When operands have different types, MySQL performs implicit conversion according to a hierarchy:

If either operand is NULL , the result is NULL (except for the <=> operator).

String‑string comparisons stay as strings.

Integer‑integer comparisons stay as integers.

Hexadecimal values compared with non‑numeric strings are treated as binary strings.

When a TIMESTAMP or DATETIME is compared with a constant, the constant is converted to a timestamp.

Decimal compared with integer is converted to decimal; compared with floating‑point converts decimal to floating‑point.

All other cases convert both operands to floating‑point numbers.

5. Avoiding Implicit Conversion

To prevent unexpected results and performance loss, always match column types with the literal values in predicates. Using a numeric literal against a varchar column forces MySQL to cast the column, which disables index usage and may cause full table scans.

select * from `order` where order_code = 1;

Instead, write the predicate with a string literal:

select * from `order` where order_code = '1';

By keeping types consistent, you avoid hidden conversions, preserve index efficiency, and eliminate subtle bugs.