可能被你忽略的MySQL隐形数据类型转换
1.创建一张表并插入数据
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `mytest`;
CREATE TABLE `mytest` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`param` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
# 插入数据
INSERT INTO `mytest` VALUES ('1', 'a');
INSERT INTO `mytest` VALUES ('2', '0');
INSERT INTO `mytest` VALUES ('3', 'a123');
INSERT INTO `mytest` VALUES ('4', '123');
2.查询数据,发现问题的现象
# 1)查询param不等于0的数据:varchar类型传入varchar类型
SELECT * FROM mytest WHERE param!='0';
SQL执行结果截图见下图(结果是正确的):
# 2)查询param不等于0的数据:varchar类型传入int类型
SELECT * FROM mytest WHERE param!=0;
SQL执行结果截图见下图(结果是不准确的):
3.什么问题导致的呢?
(1)原因:当MySQL字段类型和传入的数据类型不一致的时候,MySQL会进行隐形的数据类型转换
(2)当列字段类型为varchar类型,传入数据类型与字段类型不一致(例如传入int类型数据)时,若字符串是以数字开头并且全部为数字,则转换为整个字符串(例如:“123”转换为123);若字符串是以数字开头并且并不全部为数字,则只转换数字部分(例如“211abc”转换为211)。
(3)若字符串不是一数字开头,则会被转换为0(例如:“abcd”或“abc123” 都将会转换为0)
关于隐形数据类型转换,还有很多,这只是其中的一丢丢。
有位朋友在面试时曾被面试官问到这样的问题;原题是这样描述的,一张表myparam,其中有一个varchar类型的字段param,向表中插入10条数据,5条param字段为a的数据,5条param字段为0的数据,编写sql语句:select * from myparam where param 不等于0;(问题描述时,并没有指出是param!="0"还是param!=0具体的哪种情况),为什么就是查不出来数据来。下面看一下表结构数据和查询结果:
通过截图,很显然,面试官的所说的sql语句应该是:SELECT * FROM myparam WHERE param !=0;(传入的数据时int类型的0,数据类型不一致)
分析:
1、param类型为varchar类型
2、传入的参数数据为int类型
3、因为param字段varchar类型与传入数据的int类型不一致,所以会有一个隐形的数据类型转换
4、param字段为a的数据,因为开头不是以数字开头的,所以在转换的时候就会被转换为0;param字段为0的数据,是数字,所以在转换的时候会被转换为数字0;因此,表中现存的10条数据,就会转10条param为0的数据了,所以在执行SELECT * FROM myparam WHERE param !=0时,是查不出来任何数据的。
考点:隐形数据类型转换问题