-
在mysql数据库中,unsigned表面含义是 '无符号’的意思,unsigned既为非负数,用此类型可以增加数据长度.
例如,如果 int最大是2147683647,那 tint unsigned 最大 就可以到 2147683647 * 2 -
与unsigned类似的还有binary,unsigned 属性只针对整型,而binary属性只用于char 和varchar。
-
当设置为unsigned时候,报错BIGINT UNSIGNED value is out of range…如何解决
使用unsigned限制数值范围为正数的时候,如果执行相减操作产生负数;就会报错;解决方法
核心: 使用 cast(targetCol as signed) 将所有涉及到的unsigned字段先转化为signed类型后,再进行运算
-- 需要添加四个计算值计算每日变化数据
-- dayConfirmed: 每日确诊
-- dayRecovered: 每日康复
-- dayDeaths: 每日死亡
-- dayExisting: 当日现存
select n.id,
n.region_id,
n.region_parent_id,
n.recovered,
n.deaths,
n.day_date,
n.confirmed,
(cast(n.confirmed as signed) - cast(m.confirmed as signed)) as dayConfirmed,
(cast(n.recovered as signed) - cast(m.recovered as signed)) as dayRecovered,
(cast(n.deaths as signed) - cast(m.deaths as signed)) as dayDeaths,
(cast(n.confirmed as signed) - cast(n.recovered as signed) - cast(n.deaths as signed)) as dayExisting
from region_data_total as n
join region_data_total as m on date_sub(n.day_date, interval 1 day) = m.day_date
where n.region_id = m.region_id and n.day_date = (select max(day_date) from region_data_total);
更多推荐
mysql 数据库unsigned的用法
发布评论