• SQL进阶-隐式类型转换


    一、隐式类型转换

    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转换一下,转换为能保存结果的类型
  • 相关阅读:
    如何制作挖空的填空题试卷?
    原型制图工具有哪些?
    书籍推荐?来几本吧
    离线部署ELK+kafka日志管理系统【转】
    Elasticsearch5.0 安装问题集锦【转】
    在Nginx服务器上屏蔽IP
    MySQL Warning: Using a password on the command line interface can be insecure.解决办法
    不老的神器:安全扫描器Nmap渗透使用指南【转】
    MySQL数据库设置为只读及测试【转】
    Linux中切换用户变成-bash4.1-$的解决方法
  • 原文地址:https://www.cnblogs.com/weiyiming007/p/11458840.html
Copyright © 2020-2023  润新知