MYSQL学习笔记05:列类型:数值型,时间日期型,字符串型,enum和set

列类型/字段类型:

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

资料参考来自:https://www.runoob.com/mysql/mysql-data-types.html

整型

让我们做个实例,如下:

1. 创建表

2. 插入合理数据

3. 插入错误数据(超出对应的数据范围)

插入的第一个255超出范围,对应的字段名/类型是:int_1/tinyint.

4. 错误原因:并不是tinyint没有这么大的空间,而是msyql默认为整型增加负数,即tinyint有符号取值范围(-128，127).

我们插入1个-128,可以成功插入.

实际应用中,应该根据对应的数据的范围来选定对应的整型类型.

无符号设置

无符号:表示数据只有正数或0,没有负数.

基本语法:在类型之后加unsigned关键字.

增加1个名为int_6的tinyint类型的字段,添加至首位,如上.

如上,现在可以成功给首位(无符号tinyint类型)添加255.

我们试着尝试给首位增加一个负数,结果出错提示超出范围无效,如下:

因为现在首位是无符号(非负数)类型的tinyint.

显示长度

显示长度:指数据(整型)在数据显示的时候,到底可以显示多长位数.

tinyint(3) unsigned:表示最长可以显示3位.0-255,最多有3位数

tinyint(4):表示最长可以显示4位.-128-127,最多有4位数(负号占1位)

显示长度只是代表了数据是否可以达到指定的长度,但是不会自动满足到指定长度.如果想要数据显示的时候保持最高位(显示长度),还需要给字段加入zerofill属性.

zerofill:从最侧开始填充0(不会改变数值大小),所以负数不能使用zerofill.一旦使用zerofill就相当于自动确定该字段为unsigned.

数据显示的时候,zerofill会在左侧填充0到指定位.不足位数则填充0,如果本身已经够了或者超出,则不再填充.

现在我们指定增加1个显示长度为2位的tinyint类型的int_8到首位,如下:

给首位插入数据100;再插入数据1.

我们会发现100超过显示该字段的显示长度(2位),则正常显示100;

而1则显示为01,即按指定长度2位显示.如下:

显示长度可以自己设置:它不能改变数值大小.不足显示长度的位数则补足位数,达到或超过显示长度则按原值显示.

小数型:

分为浮点型float,double和定点型decemdecimal

实例:

1. 创建一个数据表来保存浮点数据

2. 存入数据:合法数据

float精度大概在7位左右,超过7位就不准了.注意;如果数据精度丢失,那么浮点型是按照四舍五入方式进行.计算.

3.插入数据:超出范围的.

插入失败.超出指定位数.f2字段为float(10,2)类型,即总位数10位,小数部分2位.

4.插入数据 数据长度刚好满足条件,但是会超出精度.

说明:用户插入数据不能超过限定位数,但是如果是系统进位导致的则可以.即人为不行,系统则可以.

之所以99999999.99会进位是因为float精度在7位,超过7位会丢失精度,丢失则浮点数按四舍五入计算,因此产生了进位.

5 浮点数可以采用科学记数法

浮点数的应用:通常是用来保存一些数量特别大,大到可以不用那么精确的数据.

定点数:

定点数:能够保证数据精确的小数(小数部分可能不精确,超出长度会四舍五入),整数部分一定精确.

decimal

decimal定点数:系统自动根据存储的数据来分配存储空间,每大概9个数就会分配四个字节来进行存储,同时小数部分和整数部分是分开的.

decima(M,D):M表示总长度,最大不能超过65位;D代表小数部分长度,最长不能超过30位.

实例:

1. 创建表,与浮点数对比

2. 插入2个正常数据对比1下:

3. 插入最大数据对比1下

4. 尝试定点数进行四舍五入:如下图出错

定点数的应用:如果涉及到钱的时候有可能使用定点数.

时间日期类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型	大小 (字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

实例:

1. 创建1个时间日期类型的数据表

2. 插入数据:正常数据

3. year的特殊性:可以采用两位数的数据插入,也可以采用4位数的年份插入.

4. year采用两位数插入的时候,有一个临界点划分,69和70:

69以下为20+数字;如果70以上,那么系统时间为19+数字.

5. timestamp:当对应的数据被修改的时候,会自动更新(这个被修改的数据不是自己)

6. time类型特殊性: 本质用来表示时间区间,能表示的范围比较大

7. 在进行时间类型录入的时候(time)还可以使用一个简单的日期代替时间,在时间之前加一个空格,然后指定一个数字(可以是负数),系统会自动将该数字转换成天数*24小时,再加上后面的时间.

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

Text 类型：参考

 

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

char

语法: char(L), L代表字符数.L为0-255个字符

定长字符串:指定长度L之后,系统一定会分配指定的存储空间.注意:数据长度不能超过L个。

varchar

语法: varchar(L),L代表字符数,L理论上为0-65535个字符

变长字符串:varchar指定长度L之后,varchar长度可小于等于L个字符，系统根据数据实际长度分配存储空间.但数据长度不能超过L个。

因为varchar要记录数据长度,所以每个varchar在尾部都有1-2个字节记录字符串长度.

 如果数据小于127个字符,需要1个字节;如果大于127个字符,需要2个字节.

char和varchar对比(假设utf8,1个字符都占用3个字节)

text

文本有两大类:text:普通文本和blob:二进制文本

text:提供4种text.

tinytext:占用1个字节 ,可存储数据:(2^8+1)个字符

text:占2个字节,可存储数据:(2^16+2)个字符

MEDIUMTEXT:占3个字节,可存储数据:(2^24+3)个字符

LONGTEXT:占4个字节,可存储数据:(2^32+4)个字符

注意:

1.在选择存储文本的时候,不用刻意去选择text类型,系统会根据存储的数据长度来选择合适的文本类型.

2.在选择字符存储的时候,如果数据超过255个字符,那么一定要选择text存储(不建议用varchar)

enum[单选]

枚举类型:定义可能的一个或多个值选项范围.之后定义好enum属性的字段数据使用时只能选择预定义时其中之一的值..

例如性别的值为:保密,男,女.它只可能是这三种情况之一.就可以使用枚举.

基本语法:enum(值1,值2,值3...)

系统提供了1-2个字节来存储枚举数据:

值在255以内,使用1个字节;

超过255并小于65535,使用2个字节.

即最大可有65535个选项值范围.

实例:

1. 创建表,创建1个gender(性别)字段,类型为enum型,值为{男/女/保密}三者之一.

2. 插入合法数据:必须是符合enum定义的枚举值.此处插入几个男/女.

3. 测试-插入错误数据:如果插入enum中不存在的值将产生错误.enum具有规范数据的功能.

此处我们插入1个不在enum枚举范围内的数据'male','male'不是enum取值范围{男/女/保密}三者之一.插入失败!

4. 枚举enum存储的原理:实际上字段存储的不是字符串本身,而是对应的下标.

系统设定枚举类型的时候,会给枚举中每个元素定义一个下标,,下标从1开始.

利用字段+0的小技巧,我们看到enum值确实是数值下标构成的.因为它们+0后为原数值

5. 既然enum字段存储的结果是数值:那么在进行数据插入的时候,就可以使用对应的数值来进行.

枚举的意义:

1. 规范数据本身,限定只能插入规定的数据

2. 节省存储空间

set集合[多选]

集合:有些类似enum,可预定义多个(最多64个)值.不同的是enum类型的字段值只能单选,而set类型的字段值可存有多个数据,最多能存有64项.

,本质是将指定的项按照对应的二进制位来进行控制:1表示选中;0表示未选中.

基本语法:set(值1,值2,值3...)

系统为set提供了多个字节进行保存.但是系统会自动计算来选择具体的存储单元.

1个字节:set只能有8个选项

2个字节:set只能有16个选项

3个字节:set只能有24个选项

8个字节:set只能有64个选项

set和enum一样,最终存储到数据字段中的依然是数字而不是真实的字符串

演示实例:

1. 创建表:

2. 插入数据:set属性的字段可以插入多个数据,就是在数据插入的字符串中,使用对应的逗号隔开.

3. 数据选项所在的数据与数据插入的顺序无关:最终都会变成选项对应的顺序.

4.分析数据存储的方式:

4.1 系统将对应的数据选项按照设计时的顺序进行编排:从第一个开始占位,每一个都对应1个二进制位.,如下图

4.2 数据存储的时候,如果被选中,按照设计时的顺序,对应的位的值为1,否则为0,请看下图演示.

4.3 系统在数据存储的时候会将得到的二进制数颠倒过来,然后转化为十进制存储.

5. 以数值的方式查看集合数据,验证无误.

6. 既然是数值,那么可以插入数值来替代实际的插入数据:我们插入255,代表选中所有选项.(仅为举例.不建议用这种方式)

注意:数字插入的前提是对应的二进制位上有对应的数据.!一般我们不用插入数字的方式!

set集合的意义:

1. 规范数据;

2. 节省存储空间. 

enum:单选

set:复选

mysql记录长度

在mysql中,有一项规定:mysql的一行记录长度(record == 行 row)总长度不能超过65535个字节[★需要牢记★].

varchar能够存储的理论值为65535个字符:字符在不同的字符集下可能占用多个字节.(utf8下1个中文字符占3个字节)

示例演示

1. 创建表,证明varchar在mysql中能够达到的理论值(utf8和gbk)

varchar除了存储的数据本身要占用空间,还需要额外的空间来记录保存长度

2. 我们来推理/计算utf8或GBK下实际的存储长度

varchar utf8最多只能存储21844个字符

varchar GBK最多只能存储32766个字符

如上,utf8下varchar存21845× 存21844√;  gbk下varchar存32767× 存32766√