MYSQL的数据类型_mysql支持的数据类型-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_74742634/article/details/142332600

一.MYSQL支持的数据类型.

1.2.2char与varchar的区别.

1.2.3总结一下int(M) char(M) varchar(M);

1.3 日期时间类型

1.3.1日期时间类型

1.3.2 default current_timestamp属性

1.3.3. on update current_timestamp :

一.MYSQL支持的数据类型.

MYSQL支持所有标准的SQL数据类型,其中包括数值类型,字符串类型,以及日期和时间类型.

1.1.数值类型

整数类型	字节数	取值范围(有符号)
TINYINT	1	-128~127
SMALLINT	2	-32768~32767
MEDIUMINT	3	-2^23~2^23-1
INT	4	-2^31~2^31-1
BIGINT	8	-2^63~2^63-1

浮点数类型	字节数	取值范围
FLOAT	4	-1.175494351E-38~+3.402823466E+38
DOUBLE	8	-2.2230738585072014E-308~+1.7976931348623157E+308

定点数类型	字节数	描述
DECIMAL(M,D)	M+2	M表示整数位,D表示小数位

位类型	字节数	取值范围
BIT(M)	1~8	BIT(1)~BIT(64) (M表示二进制的位数1~64位表示1~8个字节)

1.1.1整数类型

在整数类型中,MYSQL还支持在数据类型后面指定显示的宽度即位宽如int(5),这个5是指当插入的数值宽度小于5的时候 , 在该数字前面用指定的数去填满宽度 ,当然如果不指定宽度会默认为int(11).位宽一般配合zerofill使用,zerofill就是用"0"来填充的意思,也就是在指定位宽的情况下,数字位数不够用0来填充,举个例子:

(1)创建一个表名为emp ,emp表中有三列如id1 设置数值宽度为5但没有设置zerofill, id2 默认宽度,id3 数值宽度为5,并配合zerofill.

(2)插入数据在每一列都插入数值1 ,可以看到id3 前面有四个0来填充.

(3)修改id2 的字段类型为 id2 int zerofill可以看到id2前面有10个0来填充说明默认情况下int数值宽度为11.

可以想象如果插入的数值宽度超过了指定的宽度,它会不会进行截断或插入不成功呢?答案是:只要没超过它的取值范围,不会对他的插入有任何影响.不过这时指定的宽度已经没有任何的意义了..

整数类型有两个属性,一个是unsigned无符号类型,如果需要设置的列不为负数或者需要较大的上限时可以去指定它,它的取值范围下限为0,上限为原取值范围的2倍.如TINYINT有符号取值范围是-128~127,无符号取值范围是0~255;如果指定一个列的类型时加上zerofill参数,则MYSQL自动为该列添加unsigned属性.

另一个属性是auto_increment,加入该参数的列是自动增长列,每一次插入数据,MYSQL会自动插入一个比该列中当前最大值大1的数,因此在需要产生唯一标识符(如学生在班级中的序号)或顺序值时可以添加该参数但只能用于整数类型.一个表只能有一个auto-increment列因为一个表中只会有一个唯一标识符(即你的身份证号是唯一的,标识你的身份),对想用auto-increment需要在该列定义为NOT NULL,并添加主键(primary key)或者唯一键(unique)

主键是用来标识身份用的,唯一键表示该列中所有插入的数据是唯一的不可重复的,它是为了约束除主键外的列的不可重复性.如:

创建一个表名emp3的表,表中有两个列 id 列指定它为auto_increment not null 以及是主键和一个class 列为默认列.

创建好了之后添加记录,添加的时候只给class 列添加数据观察 id 列是否会自动增长列

给emp3 表添加了五条记录 id列是自动顺序增长的.

1.1.2浮点数和定点数类型

浮点数分为float 单精度和double 双精度.定点数只有decimal一种,它在MYSQL内部以字符串进行存储,比浮点数更加精确,适合精度更高的数据.

这里先介绍一下decimal(M,D) 中的 M 是代表了该值一共有多少个整数位(包含小数) ,D 就代表了一共有多少个小数位,因此M 是包含D的,可以在mysql上验证一下如

创建一个有一个列的类型位 decimal(5,2)的表

在添加数值位2222.3的时候报错说明了该值超出了它的取值范围,而同样是5 个整数位123.45添加成功说明它的整数有(5-2) 3位,小数有2 位.

在添加有六个整数位333.345时虽然添加成功但发出警告说添加的数的小数位是被截断的 ,因此说明了decimal(M,D)定点数类型的数整数部分是M -D 位 ,超过 M-D位是会报错的,小数位是D位 ,添加数值的时候是可以超出D位的但会被截断其超出的位数并进行四舍五入对其存值.如在上述添加333.345时 MYSQL会存入333.35的值第三位小数位进行了四舍五入.

1.1.3 bit(N)位类型

首先 bit(N) 中的N代表的是有 N 个二进制位比如N=1时 bit 的取值范围是2^1-1. N=2时 bit(2)的取值范围时 0 ~ 2^2-1. 有多少个二进制位说明它的最大值就是2^N-1.另外,N的取值范围时1~64.

1.2 字符串类型

1.2.1 char与varchar类型

常用的字符串类型有 char,varchar,blob,text,tinyblob,tinytext,mediumblob,mediumtext这几种类型.

字符串类型听起来就是存储字符串的类型的. 首先介绍一下char 和 vechar .看起来这两个长得很相似但实际上有很大的不同.

char(M)类型是定长字符串,,M代表了该类型最多能存储M个字符(记住是字符而不是字节),M的取值范围是0~255,说明了 char 类型最多只能存储255个字符, char类型是不受字节和字符编码限制的.在添加记录时如果存储的字符串的字符超过了M个将会被截断,比指定的M小的时候将会用零来补充.

varchar(M)类型是可变长字符串类型,M也是代表了存储的字符,它与char类型的不同在于MYSQL数据库处理这个指示器的方式,char(M)是把M视为值的大小,在字符串长度小于M时会用空格来补足,而vechar类型是把M 视为最大可以存储字符串的大小,并且只是用实际存储字符串的长度(再增加1个或者实际存储的字符大于127的时候用2个字节来表示存储字符串本身的长度)来存值,所以在存储的字符串长度小于M时是不会用空格来不足的,但大于M 时仍然会被截断.

因此说明了vechar类型会根据实际存储字符串的长度动态改变存储值的长度,在不能确定字段需要多少字符的时候也可以用vechar类型来节约磁盘空间,提高存储量.

这里再进行扩展一下,varchar(M)类型 M 代表了存储字符的大小,但varchar 是受字节和字符编码的限制的它的字节数范围是0~65535.因此它跟char(M)类型不同 M是有范围的.vechar(M)类型根据编码方式的不同M的取值范围是不同的.

当用GBK方式进行编码时每个字符最多占2个字节,虽然一个英文字符是占1个字节但是GBK在定义字段时是默认以每个字符最大为2个字节来计算存储字符的个数,因此以GBK编码时M最大为(65535-1-2)/2 = 32766 减去1是因为实际行存储是从第二个字节开始,减去2是因为vechar要用2个字节去表示实际存储字符长的长度. 当用UTF8编码时 UTF8 中每个字符是占1~3个字节,它跟GBK编码一样在计算存储字符个数时是按3个字节来计算的 M 最大为(65535-1-2)/3 = 21844.

1.2.2char与varchar的区别.

(1)char是定长的 vechar 是可变长的

在定义char类型的时候该字段的存储空间已经定了下来有固定的长度,也就是说它规定了多少字节就要存储多少个字节,超过该字段的长度会被截取,不够的会用零来补充.

而varchar 类型只要在规定的字节内有多少个字符就存储多少个字符,不用零来填补.但超过的长度仍然会被截取.

(2)存储容量上

char类型最多可以存储M个也就是255 个字符,char存储的时候是不受字节和编码限制的.

varhar类型中它是可变长字符串,它要用1个或者2个字节去记录它的实际字符串的长度.

vechar(M) M最大能存储的字符跟编码有关在GBK编码时M最大为32766 ,UTF8编码时M最大为 21844.在UTF8mb4编码也就是在MYSQL8.0版本默认的编码方式Mzuida为16383.

(3)使用场景上

虽然varchar类型比char类型有时更节省空间,但一个varchar列若是频繁被修改更新的话,并且修改的长度不一致甚至比之前存储的字符串长度还要长,不仅会频繁的更新记录实际存储字符串的长度还会出现"行迁移"的现象.而这造成多余的I/O，是数据库设计和调整中要尽力避免的，在这种情况下用CHAR代替VARCHAR2会更好一些。

因此在存储数据短,频繁更新,数据检索需求少用char.

存储数据长,不频繁更新,数据检索需求多用varchar.

1.2.3总结一下int(M) char(M) varchar(M);

首先int(M) 中M 指的是要这个字段会显示M个长度而不是存储的长度,配合zerofill参数来使用,存储的数字位数小于M时会用"0" 来填充,M的范围是0~255,M超过255时,会提示M最大为255。

char(M) char类型是不受字节和字符编码的限制的,不管字符编码是ascii编码还是GBK,还是UTF8,UTF8mb4,M的取值范围都是0~255个字符.(说到字符编码再这里在进行扩展一下,前面也说过char类型是定长字符串类型,一旦确定是char类型,那莫它的存储空间也确定了下来.

但再仔细想一想字符编码的问题,字符编码你也可以分为定长字符编码和不定长字符编码,定长字符编码有ASCII字符编码每个字符一个字节"GBK"字符集每个字符会占1~2个字节,"UTF8"字符集每个字符会占1~3个字节,"UTF8mb4"字符集每个字符会占1~4个字节.

ASCII字符编码它的每一个字符都是一个字节,char(M)中M个字符就是 M个字节.它的存储空间也是M个字节,现在看起来都没什么问题.但若是用GBK字符编码的话,它的字符集中字符可能是1~2个字节,char(M)类型存储的字符串可能会占M~2M个字节,由于不确定它到底实际占了多少个字节长度,会跟varcahr类型一样会额外用1个字节去存储它实际存储字符串的长度.

那莫再想一想如果用不定长的字符集 MYSQL为char(M)类型分配存储空间是按每个字符一个字节来分配还是按二个字节来分配,在INNODB存储引擎中规定,char(M)类型至少要分配M个字节长度即按每个字符一个字节,这样虽然节约了空间,)

varchar(M) 类型中M 虽然也代表了存储字符的个数,但是会受字节长度和字符编码的制,varchar类型最多存储65535个字节,存储的最大整体长度是65535-1-2 为65532个字节,前面也说过减去1是因为实际行存储从第二个字节开始,减去二是因为会用1~2个字节来保存实际存储的字符串长度.varchar会根据字符编码的不同,M的范围也会不同.GBK编码时 M最大为32766,UTF8编码时M最大是21844.

1.3 日期时间类型

1.3.1日期时间类型

MYSQL中有多种常用的数据类型来表示时间和日期如下:

日期和时间类型	字节	最小值	最大值	零值
year	1	1901	2155	0000
time	3	-838:59:59	838:59:59	00:00:00
date	4	1000-01-01	9999-12-31	0000-00-00
datetime	8	1000-01-01 00:00:00	9999-12-31 23:59:59	0000-00-00 00:00:00
timestamp	4	19700101080001	2038-1-19 11:14:07	0000000000000000000

从上面可以看出如果只用到年份存储的话用year类型来表示,它比date类型占用更少的内存空间,如果经常插入或查询本地的系统时间的话可以用timestamp时间戳类型来表示,timestamp类型的返回值用"YYYY-MM-DD HH:MM:SS"格式的字符串,显示的固定宽度是19位,若想用数值来表示返回值,应在timestamp列后添加"+0";

在上面看到有零值,因为每个日期和时间类型都有一个有效范围,如果存储的值没有按照对应的格式去存储,MYSQL会存入对应的零值.

date,time,和datetime 这三种类型是最常用的类型,在表中创建一个例子来展示一下;

创建一个data的表,有三个用date,time,datetime类型的列,分别插入系统时间.

显示为:

显而易见,datetime类型是date 类型和time类型的组合,但占用空间较大,可以根据不同的需求来选择不同的数据类型.这里再介绍一下datatime类型和timestamp类型.

timestamp类型和datetime类型创建的时候默认值是一样的都是null,都可以为null.其中timestamp 类型和datetime类型的默认值可以设置为current_timestamp.和都有 ON UPDATA属性. 在表中的任何timestamp类型和datetime类型的列都可以设置为该默认值和ON UPDATE属性.

1.3.2 default current_timestamp属性

它为自动初始化属性 .对于自动初始化来说,在插入新行时,如果忽略这两种类型的列,那么该列会自动设置为当前的时间戳.如

1.3.3. on update current_timestamp :

这个属性设置是自动更新,对于已有的行,该行中包含有这个属性的列的时候,修改该行中其他列的值时,timestamp类型和datetime类型的列会被自动更新成当前的时间戳.

在MYSQL 5.6.5 版本之前,mysql仅仅支持第一个出现timestamp的列指定这两种属性,后面的列不能指定和修改这两种属性.MYSQL5.6.5版本之后才开始支持表中所有的timestamp和datetime类型的列都能指定为这两种属性.

这里在介绍一下timestamp类型默认为null 属性:

timestamp类型的默认值是null,这会产生一种特殊效果.创建一个列类型为timestamp并设置current_timestamp如果在为该列插入一个null值,那么存储的是null,而不是时间戳.而当修改该列为not null 时如修改该列alter table 表名列明 modify timestamp类型 not null default current_timestamp修改该列为not null,即当你把列显示修改为not null MySQL会将原本该列的null 值设置为当前时间戳如:

这里再将一下 timestamp类型与datetime 类型的区别:

(1) 存储容量上: timestamp类型比datatime类型的占用空间小为4个字节

(2)范围上: timestamp类型最大值就到2038年的一月份左右,而datatime类型可以到9999年

(3)时区上.timestamp类型和时区有关.在插入日期时,会先转换成本地时区后存放,而从数据库里面取出的时候也同样要先转换成本地时区后显示.这样不同的时区用户去看同一日期看到的结果是不一样的.例如:用timestamp类型在东八区存入时间为8:00,再设置数据库的时区为东九区,把该timestamp类型的列显示出来为9:00.

因此timestamp类型能反映出实际的日期,而datetime类型只是把当时的日期存起来,取出来的时候还是那个日期没有考虑时区的情况.