【MySQL】MySQL的数据类型

数据类型

数据类型总体介绍

所谓的数据类型:对数据进行统一的分类,从系统的角度出发是为了使用统一的方式进行管理,更好的利用有限的空间,其次还可以约束SQL语句的正确性

• SQL中将数据类型分了三大类：数值类型,字符串类型,时间日期类型

---

分类	数据类型	解释
数值类型	BIT (M)	位类型.M指定位数,默认为1,范围1-64
	BOOL	使用1或0表示真或假
整型	TINYINT [UNSIGNED]	相当于C语言中的char类型
	SMALLINT [UNDIGNED]	相当于C语言中的short类型
	INT [UNSIGNED]	相当于C语言中的int类型
	BIGINT [UNSIGNED]	相当于C语言中的long long类型
小数类型	FLOAT [ (M, D) ] [UNSIGNED]	小数类型,M指定长度,D指定小数位数
	DOUBLE [ (M, D) ] [UNSIGNED]	对应C的double类型,M长度,D小数位数
	DECIMAL (M, D) [UNSIGNED]	M长度,D小数位数
文本、二进制类型	CHAR (SIZE)	定长字符串,最大长度255
	VARCHAR (SIZE)	变长字符串,最大长度65536字节
	BLOB	二进制数据
	TEXT	大文本,不支持全文索引,不支持默认值
时间日期	DATE / DATETIME / TIMESTAMP	日期类型(yyyy-mm-dd) / (yyyy-mm-dd hh:mm:ss) timestamp时间戳
String 类型	ENUM	ENUM是一个字符串对象,其值来自表创建时在列规定中显示枚举的一列值
	SET	SET是一个字符串对象,可以有零或多个值,其值来自表创建时规定的允许的一列值。指定包括多个set成员的set列值时各成员之间用逗号间隔开。这样set成员值本身不能包含逗号

---

数值类型

C语言给变量赋值超过其本身承受的大小,会自行截断.但SQL中当我们插入了类型大小范围之外的越界数据,SQL会直接拒绝.数据类型的本质就是一种SQL对我们的约束.

tinyint类型

1)tinyint:中文意思是非常小的整数,这个整数占一个字节,表示的范围为**-128~127**,如果插入超过该范围的数,在MySQL下会报错.

2)在设计数据库表字段的时候,设计到整形数据,尽量不要使用tinyint,而是用表示范围更大的类型.

3)在MySQL中,整型可以指定有无符号两种,默认是有符号的.可在类型之后指明unsigned以示无符号

• 对于整形数据,尽量少用unsigned,因为一个小的数字减去一个大的数字,有可能会导致查询结果出错,因此无符号表示不了负数, 对于有符号类型存不下不如提升至更大容量的类型.

例子:无符号案例

因为当前表的id的数据类型为无符号的,表示的范围是:0~255,所以插入不在这个范围的数据会报错

---

问:unsigned char c = -1,这个能否编译通过?

C语言不会报错,让我们写入char类型里,但是必须转化为二进制,因为内存空间只认识二进制,类型决定了如何识别一个二进制序列

---

bit类型

bit(M): 位字段类型.M表示每个值的位数,范围从1到64.如果M被忽略,默认为1.

• 所以如果出现类型为:bit(65)是会报错的

• bit字段在显示时,MySQL5.7默认以ASCII字符显示,MySQL8.0默认以十六进制显示.

发现很怪异的现象,a的数据10没有出现,因为此时bit字段在显示时是按照ASCII码对应的值显示

---

bit(M) 表示的十进制数是无符号整数, 例如:bit(3),则可以插入数据的范围是:0~2^3-1 如果插入超过该范围的数据,就会报错！可以使用bin() 或者 hex() 函数对结果进行显示

如果插入的是小数, 它会对小数进行四舍五入,然后再转换成二进制插入到表格

---

如果我们有这样的值,只存放0或1,这时可以它的类型为bit(1),这样可以节省空间

---

小数类型

float[(M, D)] [unsigned] # M指显示长度,D指小数位数,总大小4字节

比如1 float(4,2) 表示的范围是：-99.99~99.99, float(6,4)表示的范围是:-99.9999~99.9999

注意: float(4,2) usigned 表示的范围是：0~99.99,并不会将正数范围扩大2倍.

• 浮点数也有无符号类型,但是很少使用无符号类型.
• MySQL在保存时会进行四舍五入

---

decimal(m, d) [unsigned] # M指显示长度,D指小数位数

decimal和float的区别

decimal,精度更高：float表示的精度大约是7位.decimal整数最大位数m为65.支持小数最大位数d是30.如果d被省略,默认为0.如果m被省略,默认是10

---

验证四舍五入:

验证decimal和float的精度

如果我们希望某个数据表示高精度,就选择decimal

---

字符类型

char类型

char(L) # 定长字符串 L是可以存储的字符长度,最大长度为255个字符

char(L):固定长度的字符串,L不是占用字节的大小,而是字符串的长度, 字符占用字节的数量和数据库的编码格式息息相关 在不同的编码格式下字符和字节不一样

• LATIN1:1个字符占用1个字节, utf8：1个字符占用3个字节 Unicode编码:1个字符占用1个字节
• 比如在utf8格式下,char(10)占用的字节数为 3*10=30字节.

---

char(2) 表示可以存放两个字符,可以是字母或汉字,但是个数不能超过2个

---

varchar类型

varchar(L) # 变长字符串,L表示字符长度,最大为65536字节

varchar(L):可变长度的字符串,L表示字符长度,最大长度是65535个字节 到底可以存储多少个字符,和数据库的编码息息相关

例子:

varchar(5):表示这里可以存放5个字符,可以是字母或汉字,但是个数不能超过5个

---

• varchar有1 - 3 个字节用于记录数据大小,所以说有效字节数是65532

• 当我们的表的编码是utf8时,varchar(n)的参数n最大值是65532/3=21844

  • 原因:utf编码中,一个字符占用3个字节,如果编码是gbk,varchar(n)的参数n最大是65532/2=32766（因为gbk中,一个字符占用2字节）

验证utf8时,参数n最大值是65532/3=21844

---

固定长度字符串和可变长度字符串区别：

1)对于char而言,定义之后就开辟好了空间,对varchar而言,在定义的范围之内,存储多长的字符串,就开辟多大的空间.但是varchar不可以超过设定的长度L

• char 不管存储的数据如何,实际占用始终都是L个字符的大小,varchar 的实际占用的字节数和数据个数相关.定长和变长体现在实际存储大小.
• 如果数据长度一样就用定长char,如果变化就用变长varchar；
• 定长浪费空间但效率高,变长节省空间但效率低.

可以理解成char类型就是C语言中的字符数据char*,varchar就是C++中的string容器.

2)varchar类型需要用到1-3个字节来保存字符串的长度

3)varchar和char的(L)值的单位一样,都是字符长度.

varchar类型的数据读取时先找到记录内容大小的数据,然后再读取规定的内容

---

---

如何选择定长或者变长字符串:

• 如果数据确定长度都一样,就使用定长的字符串(char)
  • 比如:身份证,手机号
• 如果数据长度有变化,就使用变长字符串(varchar)
  • 比如：名字,地址,但是你要保证最长的能存的进去
• 定长的磁盘空间比较浪费,但是效率高
• 变长的磁盘空间比较节省,但是效率低
• 定长的意义就是:直接开辟好对应的空间
• 变长的意义是:在不超过自定义范围的情况下,用多少开辟多少

---

日期和时间类型

常用的日期时间类型有三个：

类型	含义	格式	解释
date	日期 (只表示年月日)	yyyy-mm-dd	占用三字节
datetime	时间日期类型	yyyy-mm-dd HH:ii:ss	表示范围从1000到9999，占用八字节
timestamp	时间戳类型	yyyy-mm-dd HH:ii:ss	只能表示从1970年开始的时间,占用四字节

• date和datetime的区别就是格式上的区别
• timestamp自动更新当前操作的时间,不需要手动设置.

注意:date和datetime的区别就是格式上的区别,当我们修改数据的时候,timestamp自动更新当前操作的时间,不需要手动设置.

更新时间:updata 表名字 set 列名=修改后的时间

---

enum set

# 枚举,单选类型
enum ('选项1', '选项2', '选项3', ...);

enum： 该设定只是提供了若干个选项的值,最终一个单元格中,实际只存储了其中一个值;而且出于效率考虑,**这些值实际存储的是"数字”,**使用enum枚举类型,实际上在存储的时候使用的是从1开始的数字字符, 当我们添加枚举值时,也可以添加对应的数字编号.

• enum枚举了很多值,但是只能在其中选择一个,如果插入的值不再枚举列表之中,就会报错.插入是必须插入规定的选项值,不允许其他值.
• 规范使用,不推荐使用数字做插入筛选等操作

在向enum中插入的时候,我们可以采用数字的方案,1,2,3,4…可以看做数组的起始下标,数组的内容,就依次对应到enum中的一个一个枚举出的元素

---

# 集合,多选类型
set  ('选项1', '选项2', '选项3', ...);

• enum枚举只能单选,但set集合类型可以多选.同样,set类型只能插入规定好的值

set类型存储时采用的是比特位结构.从低到高每个比特位表示一个选项,比特位为1表示具有该选项,反之则没有

例如:选项有:A B C D 如果set对应的字段的值为5（0101）,则表示A和C被选上了, 如果set对应的字段的值为1（0001）,则表示A被选上了 (其中A选项是低位,D选项是高位）

比特位的位置代表特定的爱好,比特位的内容(0 or 1),’ 是否 ’ 具有该爱好

---

例子:调查表votes,要调查人的喜好,比如:敲代码,游泳,羽毛球,健身中去选择(可以多选),性别为男/女/未知, 单选

对于set中的选项:可以用二进制表示, 第一项为低位, 所以1011:表示敲代码,羽毛球,健身都被选上了, 如果为0001,表示敲代码被选上了

对于enum中的选项:使用的是从1开始的数字字符, 当我们添加枚举值时,可以使用数字,第一项是1

---

如果我们想查找所有喜欢敲代码的人：

我们可以发现,如果直接使用=去查找,是找不到的！因为查询时=是严格匹配,严格筛选出只具有该选项的记录, 也就是只能找到兴趣只有敲代码的人

---

若想要筛选出包含该选项的记录可以使用,则可以使用：find_in_set函数

• find_in_set(sub,str_list) : 如果 sub 在 str_list 中,则返回下标,如果不在,返回0； str_list 用逗号分
  隔的字符串
• 注意这里的下标从1开始

使用例子:

---

所以上述查找所有喜欢敲代码的人：

当然,也可以用and连接查找的内容: 例如:查找兴趣含有健身和敲代码的人:

select * from votes where find_in_set('健身,hobby') and find_in_set('敲代码,hobby');

当然也可以使用模糊查找like: %表示匹配任意 ,%在后面就是后面匹配任意字符,如果%在前面,就是%前面的内容匹配任意字符

---

数据类型本质也就是一种约束,所谓约束可以理解为不满足条件就不允许操作.约束给予了表中的数据极大的确定性.