mysql中signed是什么类型_mysql|unsigned 与 signed 类型

通过mysql的数值类型设置，控制数值的正负

1，如何使用

在mysql的编辑器中，可以直接定义 bigint(20) unsigned

2, 发挥的作用

一般默认定义的数据类型为signed(有符号类型)，取值返回包含有负数范围，一般正负值的差依然等于无符号类型的范围的上限值。

当定义为unsigned(无符号类型)时， 取值范围仅为正数范围，下限值为0；

3，当设置为unsigned时候，报错BIGINT UNSIGNED value is out of range….如何解决

使用unsigned限制数值范围为正数的时候，如果执行相减操作产生负数；就会报错；解决方法

--需要添加四个计算值计算每日变化数据--dayConfirmed: 每日确诊--dayRecovered: 每日康复--dayDeaths: 每日死亡--dayExisting: 当日现存

selectn.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)) asdayConfirmed,

(cast(n.recovered as signed) - cast(m.recovered as signed)) asdayRecovered,

(cast(n.deaths as signed) - cast(m.deaths as signed)) asdayDeaths,

(cast(n.confirmed as signed) - cast(n.recovered as signed) - cast(n.deaths as signed)) asdayExistingfrom region_data_total asnjoin region_data_total as m on date_sub(n.day_date, interval 1 day) =m.day_datewhere n.region_id = m.region_id and n.day_date = (select max(day_date) from region_data_total);

核心： 使用 cast(targetCol as signed) 将所有涉及到的unsigned字段先转化为signed类型后，再进行运算

相关内容补充：

MYSQL的数据类型(菜鸟教程)：

https://www.runoob/mysql/mysql-data-types.html

mysql|unsigned 与 signed 类型