BIGINT UNSIGNED value is out of range in..的解决方法

今天执行一个sql出现BIGINT UNSIGNED value is out of range in的错误。sql如下

SELECT
cac.id,
cac.teacher_user_id,
cd.teacher_into_time – cac.start_time b
FROM
class_appoint_course cac
LEFT JOIN course_details cd ON (
cac.id = cd.class_appoint_course_id
AND cd.type = 1
)
WHERE
cac.late_status = 1
OR (
cac.late_status = 0
AND cd.teacher_into_time > cac.start_time
)

出现错误提示[Err] 1690 – BIGINT UNSIGNED value is out of range in ‘(`newuuabc`.`cd`.`teacher_into_time` – `newuuabc`.`cac`.`start_time`)’

出现这个错误的原因是什么呢？是相减的两个字段都是unsigned int类型的，相减的结果mysql默认也是unsigned，如果要避免这样的错误，需要把数据进行类型转换，比如转换为signed类型

SELECT
cac.id,
cac.teacher_user_id,
cast(cd.teacher_into_time as signed) – cast(cac.start_time as signed) b
FROM
class_appoint_course cac
LEFT JOIN course_details cd ON (
cac.id = cd.class_appoint_course_id
AND cd.type = 1
)
WHERE
cac.late_status = 1
OR (
cac.late_status = 0
AND cd.teacher_into_time > cac.start_time
)

使用cast方法进行类型转换。这样就不会出现错误了