Mysql数据类型之整数型的一些细节你get到了吗？

Mysql数据类型之整数型篇

数据类型概述

Mysql里的数据类型主要有数值型(整形、浮点型)、时间类型、字符串类型、JSON类型、坐标类型、混合类型(整数或字符串)。

数值型范围表格

数据类型	存储位数	数据范围
tinyint	1	有符号: -128–127  无符号: 0–255
smallint	2	有符号: -32768–32767 无符号: 0–65535
mediumint	3	有符号: -8388608–8388607 无符号: 0–16777215
int	4	有符号:-2147483648–2147483647 无符号: 0–4294967295
bigint	8	有符号:-2^63 - 2^63-1 无符号: 0–2^64-1
decimal(M, N)	1–29	M 1到65 N 0到38，M大于等于N
float	4	总位数65（其中小数部分38）
double	8	总位数65（其中小数部分38）
bit(M)	1-8	1-64 bits

例如:

1 decimal(5,2) 即小数点后面是2位，总5位（整数部分3位），所以范围是-999.99-999.99。

2 针对float不能用等于或者不等于比较符。

3 bit类型的存储字节与其位数有关，FLOOR((M+7)/8) 字节。

范围代码验证

CREATE TABLE tb_datatype(
smallintcol  smallint signed
)
-- 1 仅有smallint时等价于smallint signed 符号类型
-- 2 指定unsigned时数据存的是正整数
-- 3 以有符号smallint为例验证数据范围(-32768到32767)
INSERT INTO tb_datatype VALUES(-32768)
INSERT INTO tb_datatype VALUES(32767)
SELECT * FROM tb_datatype

--  1 反例验证smallint范围
INSERT INTO tb_datatype VALUES(-32769)
> 1264 - Out of range value for column 'smallintcol' at row 1

INSERT INTO tb_datatype
VALUES(32768)
> 1264 - Out of range value for column 'smallintcol' at row 1
> 时间: 0s

-- 2 反例验证decimal范围
#2.1 整数部分最多65位
ALTER TABLE tb_datatype ADD decimalcol decimal(66,29)
> 1426 - Too big precision 66 specified for 'decimalcol'. Maximum is 65
> 时间: 0s

-- #2.2 小数点最多38位
ALTER TABLE tb_datatype ADD decimalcol decimal(65,39)
> 1425 - Too big scale 39 specified for 'decimalcol'. Maximum is 38
> 时间: 0s

数值类型的属性

设置显示位数

在数值类型定义时指定显示的宽度(位数)M。

注意：这里的M是用于显示的，并不能限制数值类型的存储范围。比如我们定义了字段smallintcol2并指定它的显示宽度是3，但我们也可以存超过3位数的数字。

ALTER TABLE tb_datatype ADD smallint(3)
UPDATE tb_datatype SET SMALLINTcol2 = 9999 WHERE id=1 ;
UPDATE tb_datatype SET SMALLINTcol2 = 99 WHERE id=2 ;

设置零填充(ZEROFILL)

数值显示宽度M是和ZEROFILL结合一起使用的，即如果实际存储的数值比定义的位数M小则左边用”0”填充。比如我们定义了字段smallintcol3的显示宽度M是3，并且是ZEROFILL的。

ALTER TABLE tb_datatype ADD smallintcol3 smallint(3) ZEROFILL
UPDATE tb_datatype SET SMALLINTcol3 = 9999 WHERE id=1 ;
UPDATE tb_datatype SET SMALLINTcol3 = 99 WHERE id=2 ;

此时我们可以看到当99存进去时，显示的是三位的”099”,当实际存储位大于指定的宽度M时照常显示。

注意事项：

1 表达式或者UNION对属性为ZEROFILL的字段”免疫”，即还是当成普通的数值类型。详见下例：

SELECT * FROM tb_datatype WHERE SMALLINTcol3 < 100

SELECT SMALLINTcol3 FROM tb_datatype WHERE id = 2
UNION
SELECT 111
结果为
99
111

2 如果指定ZEROFILL则mysql数据库系统自动加上UNSIGNED属性。
3 ZEROFILL 属性会被遗弃，建议用其它方法替换，比如LPAD，见下例;

SELECT LPAD('99',3,'0')

有符号类型

有符号类型属性是指定存储的都是正整数即不能是负的。

ALTER TABLE tb_datatype ADD smallintcol4 smallint UNSIGNED

UPDATE tb_datatype SET SMALLINTcol4 = 1999 WHERE id=1
> Affected rows: 1
> 时间: 0.331s

-- 插入负整数时报超出数据范围的错。
UPDATE tb_datatype SET SMALLINTcol4 = -99 WHERE id=2
> 1264 - Out of range value for column 'smallintcol4' at row 2
> 时间: 0s

自增 AUTO_INCREMENT

字段自增(AUTO_INCREMENT)即指定字段的内容由数据库来维护其值和增幅（步长），默认值是1，增幅为1。

alter table tb_datatype add column seq int auto_increment not null, add primary key(seq);

此时我们可以看到seq自动有了值1和2，因为当前表有2行。

注意事项：

1 不能将NULL插入含自增的字段，插入失败后自增对应的系统序列(sequence)会+1（步长）。

2 一般把0插入到自增字段里，除非打开NO_AUTO_VALUE_ON_ZERO模式（不建议）。

3 自增列不支持负数。

4  检查(CHECK)约束里不能使用含自增的字段。

5 自增属性一般只针对正整数类型的字段，FLOAT和DOUBLE类型的字段将不再支持。

完整代码

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tb_datatype
-- ----------------------------
DROP TABLE IF EXISTS `tb_datatype`;
CREATE TABLE `tb_datatype`  (
  `id` int(11) NULL DEFAULT NULL,
  `smallintcol` smallint(6) NULL DEFAULT NULL,
  `smallintcol2` smallint(3) NULL DEFAULT NULL,
  `decimalcol` decimal(65, 38) NULL DEFAULT NULL,
  `smallintcol3` smallint(3) UNSIGNED ZEROFILL NULL DEFAULT NULL,
  `smallintcol4` smallint(5) UNSIGNED NULL DEFAULT NULL,
  `seq` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`seq`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_datatype
-- ----------------------------
INSERT INTO `tb_datatype` VALUES (1, -32768, 9999, NULL, 9999, 1999, 1);
INSERT INTO `tb_datatype` VALUES (2, 32767, 99, NULL, 099, NULL, 2);

SET FOREIGN_KEY_CHECKS = 1;