一、隐式类型转换
1、隐式类型转换
隐式类型转换:SELECT 1 + '1'; 程序可读性差,且依赖数据库的隐式转换规则,如果数据库升级,则程序可能无法正确执行; 有可能会导致索引失效; 有可能会导致意想不到的结果; 显式类型转换:SELECT 1 + CAST('1' AS SIGNED INT); 尽量用显式类型转换;
2、数值型 + 字符型
SELECT 1+'1'; 结果:2 SELECT CONCAT('北京',2008); 结果:北京2008 SELECT '北京' + 2008; 结果:2008 SELECT 'HELLO ' + 'WORLD!'; 结果:0
3、隐式类型转换导致索引失效
## CREATE TABLE teacher( teacher_id VARCHAR(50), teacher_name VARCHAR(50), id_no VARCHAR(50) ); CREATE INDEX idx_teacher_id ON teacher(teacher_id); ## CREATE TABLE student( student_id INT, student_name VARCHAR(50), teacher_id INT ); CREATE INDEX idx_teacher_id ON student(teacher_id); ## SELECT * FROM student a INNER JOIN teacher b ON a.teacher_id = b.teacher_id; 此时不会走索引,因为在teacher表中,teacher_id是varchar类型,而student表中teacher_id是int类型, 会做隐式类型转换,把varchar转为int类型;
4、隐式类型转换导致意想不到的结果
## 依据上面的建表语句,建表并插入以下数据: INSERT INTO teacher VALUES('20180204060001','李斌','530102192005080114'); INSERT INTO teacher VALUES('20180204060002','张成','530102192005080115'); 以下语句会返回两条结果,因为teacher_name是varchar型的,要先转为int型,varchar转int型就变成了0 SELECT COUNT(*) FROM teacher WHERE teacher_name = 0; | |(等价) SELECT COUNT(*) FROM teacher WHERE CAST(teacher_name AS SIGNED INT) = 0; 这种操作还是很危险的,因为当执行删除语句时,可能会删错; DELETE FROM teacher WHERE teacher_name = 0; ## SELECT COUNT(*) FROM teacher WHERE teacher_name = 0; 为什么不是等价于: SELECT COUNT(*) FROM teacher WHERE teacher_name= '0'; 因为隐式类型转换时,转的是左边而不是右边; ## 以下语句会返回两条信息,而不是一条,因为530102192005080114这串数字,已经超过了int类型的范围, 超过了int类型的范围就会转为float类型,等号两边都转为float类型,会丢精度,也就是最后一位数丢了,剩下的就相等了,就全返回了; SELECT COUNT(*) FROM teacher WHERE id_no = 530102192005080114; 等价于: SELECT COUNT(*) FROM teacher WHERE CAST(id_no AS DECIMAL) = CAST(530102192005080114 AS DECIMAL); 在查询时把530102192005080114加上单引号就可以了;
5、其他数据库转换导致意想不到的结果
在Teradata数据库中: SELECT 10/4; 结果:2 解决:可以把分母分子乘以1.00,再运算 SELECT COUNT(*) FROM table1; count返回的是int类型,如果表中数据量超过count出来的数,就报错 解决:在count(*) 外面cast转换一下,转换为能保存结果的类型