隐式转换导致结果输出异常
本帖最后由 ShawnYan 于 2023-12-13 17:54 编辑(Wed Dec 13 17:24:51 2023)>select version();
+-----------+
| version() |
+-----------+
| 8.0.32-24 |
+-----------+
1 row in set (0.00 sec)
test case:
create table t1 (
id int not null,
name varchar(255) default null,
primary key(id)
);
insert into t1 values (1,'123456789012345611');
insert into t1 values (2,'123456789012345612');
insert into t1 values (3,'123123');
create table t2 (
id int not null,
name bigint default null,
primary key(id)
);
insert into t2 values (1,'123456789012345611');
insert into t2 values (2,'123456789012345611');
insert into t2 values (3,'123123');
results:
(Wed Dec 13 17:24:04 2023)>select t1.id id1, t1.name name1, t2.id id2, t2.name name2
-> from t1,t2
-> where t1.name = t2.name;
+-----+--------------------+-----+--------------------+
| id1 | name1 | id2 | name2 |
+-----+--------------------+-----+--------------------+
| 2 | 123456789012345612 | 1 | 123456789012345611 |
| 1 | 123456789012345611 | 1 | 123456789012345611 |
| 2 | 123456789012345612 | 2 | 123456789012345611 |
| 1 | 123456789012345611 | 2 | 123456789012345611 |
| 3 | 123123 | 3 | 123123 |
+-----+--------------------+-----+--------------------+
5 rows in set (0.01 sec)
(Wed Dec 13 17:53:57 2023)>select t1.id id1, t1.name name1, t2.id id2, t2.name name2 from t1,t2 where cast(t1.name as decimal(20)) = t2.name;
+-----+--------------------+-----+--------------------+
| id1 | name1 | id2 | name2 |
+-----+--------------------+-----+--------------------+
| 1 | 123456789012345611 | 1 | 123456789012345611 |
| 1 | 123456789012345611 | 2 | 123456789012345611 |
| 3 | 123123 | 3 | 123123 |
+-----+--------------------+-----+--------------------+
3 rows in set (0.00 sec)
(Wed Dec 13 17:24:42 2023)>select t1.id id1, t1.name name1, t2.id id2, t2.name name2
-> from t1,t2
-> where t1.name = cast(t2.name as binary);
+-----+--------------------+-----+--------------------+
| id1 | name1 | id2 | name2 |
+-----+--------------------+-----+--------------------+
| 1 | 123456789012345611 | 1 | 123456789012345611 |
| 1 | 123456789012345611 | 2 | 123456789012345611 |
| 3 | 123123 | 3 | 123123 |
+-----+--------------------+-----+--------------------+
3 rows in set (0.00 sec)
类型隐式转换在MySQL源头就存在，以上结果也是合规的，详见MySQL关于类型隐式转换规则 https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html
页:
[1]