浅析SQL数据类型的隐式转换与显式转换以及隐式转换可能导致的问题

一、隐式类型转换问题

1、隐式类型转换：

　　比如：SELECT 1 + '1';

2、隐式类型转换的问题：

（1）程序可读性差，且依赖数据库的隐式转换规则，效率差，会增加处理时间；且如果数据库升级，则程序可能无法正确执行；

（2）有可能会导致索引失效；

（3）有可能会导致意想不到的结果；

3、显式类型转换：SELECT 1 + CAST('1' AS SIGNED INT);

4、原则：尽量用显式类型转换。

5、隐式转换问题示例：

（1）数值型 + 字符型

SELECT 1+'1';           -- 结果：2
SELECT CONCAT('北京',2008);    -- 结果：北京2008
SELECT '北京' + 2008;       -- 结果： 可能2008，可能报错
SELECT 'HELLO ' + 'WORLD!';    -- 结果：可能 0，可能报错

（2）隐式类型转换导致索引失效

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类型；

（3）隐式类型转换导致意想不到的结果

SELECT 10/4;       -- 结果：2      解决：可以把分母分子乘以1.00，再运算
    
SELECT COUNT(*) FROM table1;    -- count返回的是int类型，如果表中数据量超过count出来的数，就报错
-- 解决：在count(*) 外面cast转换一下，转换为能保存结果的类型

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加上单引号就可以了；

二、SQL 数据类型转换

　　数据类型转换分为隐式转换和显式转换。

1、显式转换：顾名思义就是使用函数进行数据类型转化，如cast、convert

2、隐式转换问题

-- 例子1
SELECT 1+ ‘1’ –返回值为2

-- 例子 2
SELECT
CASE
WHEN 1 > 1 THEN 10
WHEN 1 = 1 THEN ‘10’
WHEN 1 < 1 THEN 10.2
END    -- 返回值为10.2

-- 例子3
SELECT
CASE
WHEN 1 > 1 THEN 10
WHEN 1 = 1 THEN ‘abc’
WHEN 1 < 1 THEN 10.2
END     -- 语法错误

　　在第一个例子中，'1' 被转换为 int 的 1 ；

　　在第二个例子中，不管那个后面的条件成立，结果都被转换为decima；

　　第三个例子由于‘ab’转换为decimal失败，所以报错。

　　为什么呢？ —— 这是因为 sql server 中有多个数据类型在一个表达式中时会存在隐式的转换，各个数据类型的优先级如下：

1、用户定义数据类型（最高）
2、sql_variant
3、xml
4、datetime
5、smalldatetime
6、float
7、real
8、decimal
9、money
10、smallmoney
11、bigint
12、int
13、smallint
14、tinyint
15、bit
16、ntext
17、text
18、image
19、timestamp
20、uniqueidentifier
21、nvarchar（包括 nvarchar(max)）
22、nchar
23、varchar （包括 varchar(max)）
24、char
25、varbinary（包括 varbinary(max)）
26、binary（最低）