Mysql 数值类型

一、概述

Mysql 中有多种数据类型可以用于数值的表示,不同的版本可能会存在差异,下面的表格中列出了 Mysql 8.x 中所支持的数值类型

整数类型	存储占用磁盘空间大小	取值范围(无符号)	取值范围(有符号)
TINYINT	1 个字节	0 ~ 255	-128 ~ 127
SMALLINT	2 个字节	0 ~ 65535	-32768 ~ 32767
MEDIUMINT	3 个字节	0 ~1677215	-8388608 ~ 8388607
INT / INTEGER	4 个字节	0 ~ 4294967295	-2147483648 ~ 21474836487
BIGINT	8 个字节	0 ~ 18446744073709551615	-9223372036854775808 ~9223372036854775807
定点数类型	存储占用磁盘空间大小	取值范围
DEC(M,D) / DECIMAL(M,D)	M+2 个字节	最大值、最小值极大,给定的 DECIMAL 的有效取值范围由 M 和 D 决定

二、整数类型

在整数类型中,按照取值范围和存储方式的不同,可以分为 tinyint、smallint、mediumint、int(integer)、bigint 这 5 个类型,如果超出类型范围,会发生"Out of range" 错误提示

下面就演示插入值超过上限的报错

创建表 test1

CREATE TABLE `test1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `column1` tinyint NOT NULL DEFAULT '0',
  `column2` smallint NOT NULL DEFAULT '0',
  `column3` mediumint NOT NULL DEFAULT '0',
  `column4` int NOT NULL DEFAULT '0',
  `column5` bigint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

往 test1 表中插入数据

Mysql 还支持在类型名称后面的小括号内指定显示宽度,例如 int(5) 表示当数值宽度小于 5 位时在数字前面填满宽度,一般配合 zerofill 使用,zerofill 就是用 0 填充的意思,也就是在数字位数不够的空间使用字符 0 填满,如果类型后面不显示指定宽度,那么会有默认值,例如 int 类型的默认值就是 int(10)

创建表 test2

CREATE TABLE `test2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `col1` tinyint unsigned zerofill NOT NULL DEFAULT '000',
  `col2` tinyint(2) unsigned zerofill NOT NULL DEFAULT '00',
  `col3` int unsigned zerofill NOT NULL DEFAULT '0000000000',
  `col4` int(8) unsigned zerofill NOT NULL DEFAULT '00000000',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我们建表时没有给 col1 列加上宽度,但是在创建表时 Mysql 为我们加上了默认的宽度 tiny(3),同理 col3 列 int 类型的默认宽度为 int(10),并且都添加了无符号标识(unsigned)

下面就插入一组数据看一下实际显示的效果,可以看出 0 填充了未被占满的宽度

如果插入的数据超过了宽度会怎么办呢(注意可以超过宽度,但是不能超过该类型的取值范围)

col2 的宽度为 2,我们插入的数据 123 宽度为 3,col4 的宽度为 8,而我们插入的数据 2222222222 宽度为 10,从上面的结果可以看出,超过了设置的宽度之后,不会对插入的数据有任何影响,还是按照类型的实际精度来进行保存,这个时候的宽度实际上已经失去了意义

另外有一个需要注意的地方,我们主键一般设置的都是 AUTO_INCREAMENT,PRIMARY_KEY,当插入 null 值到主键列时,Mysql 会插入一个比主键列中当前最大值大 1 的列,虽然如此,但是还是建议将主键列定义为 NOT NULL

三、定点数类型

定点数类型一般用于表示货币等高精度的数据,当然有时候也会用来保存地理位置信息,例如 精度、纬度

Mysql 中使用 DEC(M,D) / DECIMAL(M,D) 来表示定点数,M 称为标度,表示可以显示 M 位数字,D 称为精度,表示可以有几位小数

例如表中某一个字段 col1 的类型为 DEC(6,3),那么它就表示 col1  可以显示为 999.999

创建表 test3

create table test3(
	id int auto_increment,
	col1 DEC default 0 not null,
	col2 DEC(6,2) default 0 not null,
	col3 DECIMAL default 0 not null,
	col4 DECIMAL(8,3) default 0 not null,
	constraint test3_pk primary key (id)
);

查看表结构

可以很明显的看出 col1 建表时使用的是 DEC,并且没有指定精度和标度,Mysql 会给出默认的精度和标度(10,0),而且类型变成了 decimal,也就是说 dec(M,D) 和 decimal(M,D) 是等价的

现在测试插入一组数据

col1 列默认的标度是 10 ,当插入 11 位的数字 99999999999,会报错 "Out of range"

插入给定的标度和精度范围内的值会按照字段的标、精度显示,例如 col2 插入的是 1,显示为 1.00, col4 插入的是 1 ,显示为 1.000