mysql在比较时，比较的字段为varchar写成int型会出现什么结果

忽然想起来一个面试的坑，记录下

场景是一个查询的语句查询条件的类型为varchar,如果传int型会出现什么情况

copy个例子

CREATE TABLE `t_test`  (
  `id` int(11) NOT NULL,
  `no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of t_test
-- ----------------------------
INSERT INTO `t_test` VALUES (1, '1', '张三');
INSERT INTO `t_test` VALUES (2, '1a', '李四');
INSERT INTO `t_test` VALUES (3, '1aa', '王五');
INSERT INTO `t_test` VALUES (4, 'bb', 'aaa');
INSERT INTO `t_test` VALUES (5, 'aa1', 'bb');
INSERT INTO `t_test` VALUES (6, '22b', 'cc');
INSERT INTO `t_test` VALUES (7, '200', 'dd');

SELECT * FROM t_test t1 where t1.no  = 1;
SELECT * FROM t_test t1 where t1.no  = '1';

查出来结果是否一样？

我之前跟copy 的作者一样觉得只出现张三这一行，结果却出乎我的预料

mysql官网说的是字符串和数字在比较和运算会相互转换，最后的转换结果是

SELECT id,CAST(no AS signed) no,name FROM test WHERE no = 1

而且转换以后不能使用索引检索