列类型/字段类型:
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
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√