【MySQL】数据库数据类型

1. 整体概要

看起来分为很多类型，例如数值类型,有一些看一眼就懂啦，所以不会全部介绍

2. 数值类型

(有符号) tinyint 创建表

进入 test_db数据库中，并创建表t1 ，其内部包含 tinyint类型的num

---

输入 desc t1; 查看t1表结构

tinyint类型为一个字节，有符号，所以取值范围为 -128 到 127

---

向t1 表中分别插入 -128 、128 、1

---

输入 select * from t1; 查询 t1 表的结构

---

由于 -129 和128 超过了 tinyint类型的取值范围，所以插入失败

---

(无符号) tinyint 创建表

再次创建表，内部包含一个无符号的 tinyint类型的num
所以取值范围为 0 到255

---

输入 desc t2; 查询t2表的结构

---

由于取值范围是 0 到255
所以向t2表插入 0 100 255 ，可以插入成功
而向t2表插入 -1，则会插入失败

bit类型

bit[M] ：位字段类型，M表示每个值的位数，范围从1到64，如果M被忽略，默认为1

先进入 test_db 数据库中，创建一张 t3表
内部包含 int类型的id 、 1字节的online
id 表示 用户某一个身份id
online 表示 当前用户是否登录 (1表示登录 0表示没有登录)

---

插入 （123 ,0 ) 表示123号用户不在线
插入 (124 ,1 ) 表示 124号用户在线
可当插入 (123,5)时 ，由于 online 的类型为1个比特位，所以只能插入0或者1，所以会报错

---

输入 select * from t3; 即查看t3表的结构
发现online 对应的位置 什么都看不到
是因为通常以ASCII值形式显示的，而ASCII值在当前是不可显示的

---

让 online 以十进制方案显示出来
这样就可以看到 0 或者 1

---

创建表时，bit范围是1到64,而当前设置是65，所以创建表不成功 会报错

float 类型

float [ (m,d) ] [unsigned] : m指定显示长度(数字位数的总个数) d指定小数位数 占用空间4个字节

---

创建一张表t5，包含 int类型的id 以及 float类型的salary，并且salary共有4位，其中2位是小数
id 表示 这个人的身份
salary 表示 这个人的工资
float [ (4,2) ] 的取值范围是 -99.99 和 99.99

---

向t5表插入 （1,99.99) 和(1,-99.99) ，是可以插入成功的
(float的取值范围是有符号区分的)

---

若slary为23.46，则插入成功，并且显示为23.46

---

若slary为23.467，则插入成功，并且显示为23.47 ，因为四舍五入，将其进位

---

若slary为23.464，则插入成功，并且显示为23.46，因为四舍五入，将其舍去

所以进行浮点数存储时，若要求是2位精度，而传入更多位的精度，mysql就会采用 四舍五入的方式 将数据进行存储

---

(无符号)float

mysql支持定义符号的浮点数

float [ (4,2) ] 的取值范围为 0 到 99.99

创建一张表t6,内部包含 一个bigint类型的id 以及 无符号的浮点数类型的salary，并且salary共有4位，其中2位是小数

---

若插入 一个99.999，正常来说小数末尾是9 是要四舍五入的，但是进位 就会超过99.99的取值范围 ，所以会报错

---

decimal

decimal (m,d) [unsigned] : 定点数m指定长度 ，d表示小数点的位数

decimal(5,2) 的取值范围是 -999.99 - 999.99
decimal(5,2)unsigned的取值范围是 0 - 999.99
decimal和float很像，但是有区别：
float 和 decimal 表示的精度不一样

---

创建一张表 t7 ，内部包含 float类型的f1，f1总长度为10 ，其中小数部分为8
以及decimal 类型的f2，f2总长度为4，其中小数部分为2

---

当插入 99.99 在decimal的取值范围内时，则插入成功
当插入99.999不在decimal的取值范围内时，则插入失败

---

将属于t7表的 decimal类型的f2 的精度 改为 总长度为10 ，其中小数部分为8

---

在t7表中的成员 f1和f2中分别插入 23.12345612

输入 select *from t7; 查看t7表的信息 发现 f1是精度方面的问题

float在精度过大时，会进行优化策略
但decimal不会这样，decimal可以完全保证数据的精度

3. 二进制类型

char类型

char(L) ： 固定长度字符串，L是可以存储的长度，单位为字符，最大长度值为255

---

在test_db数据库中，创建一张表t8 ,内部包含 int类型的id 以及 char类型的name

---

输入 desc t8; 查看t8表中更详细信息
t8表中的name 最多储存2个字符

---

向t8表中插入 一个字符’a’ 或者插入两个字符’ab’ 时，就可以插入成功
向t8表中插入三个字符’abc’时，就会报错

---

MySQL中的字符代表一种符号，一个汉字代表一个字符

当插入1个字符 中 和2个字符 中国 时，是可以插入成功的
但当插入 3个字符 中国人时 ，因为最多储存2个字符，所以会报错

---

varchar类型

varchar(L) ：可变长度字符串，L表示字符串长度，最大长度为65535个字节

---

创建一张表t9，内部包含int类型的id 和 varchar类型的name，最多储存6个字符
并默认字符集为uft8

---

每一个汉字都可以看做是一个字符
若输入 1个字符 中， 3个字符中国人 都可以插入成功
若输入 7个字符时，就会报错

---

想要修改 t9表中的name成员的varchar类型 最多储存65536个字符
但是报错显示 最多 为21845 个字符 ，可明明varchar 最大长度为65535个字节

MysQL在存储varchar类型时，是按照utf8编码的
在uft8编码中，一个英文字符等于1个字节，一个中文等于3个字节
假设以中文计算，21845个字符 乘以3 就为 65535 个字节

---

char为固定长度字符串，类似于c++中的数组
如：char(6) 就表示数组字符个数为6个字符，可能只用了1个字符空间，但是依旧会给6个字符空间

varchar为变长字符串
varchar(len) len定义多大就表示字符空间的上限
如：varchar(6) 可能只用了1个字符空间，就只会分配保存一个字符空间

---

varchar的字节长度在0 -65535之间 ，需要有1-3个字节用于记录数据的大小
(传入数据的大小不同，有可能数据小 1个字节就能记录，有可能数据太大，就需要3个字节才能记录)
假设有3个字节记录数据，所以有效字节数为65532

当表的编码为utf8时，varchar(len) 的参数 len最大值为 65532/3=218844
(一个字符占用3个字节为例)
所以最大字符个数为 21844

---

创建一张表t10，内部包含 varchar类型的name ，其最多储存21845个字符
因为超过了21844范围，所以会报错

---

当表中 name的varchar类型的取值为21844时，就可以创建成功

---

4. 日期时间

日期时间类型

常用的日期如下：
date：日期 ‘yyy-mm-dd’ （年月日)，占用三个字节
datetime： 时间日期格式 ‘yyyy-mm-dd HH: ii:ss’ (年 日 时 分 秒) 表示范围从1000到9999，占用八个字节
timestamp：时间戳 ，从1970年开始的 yyy-mm-dd HH:ii:ss (年 月 日 时 分 秒) 格式和 datetime 完全一致，占用四个字节

---

创建一张表 t10 ，内部包含 date类型的t1 ，datetime类型的t2，timestamp类型的 t3

---

输入 desc t10; 查看t10表的更多详细信息
t3的类型为时间戳，默认值为 CURRENT_TIMESTAMP
扩展说明：创建表结构时，向表中插入数据，timestamp会自动更新
而date与datetime 需要外部插入

---

向t10表中的成员 date类型的t1 和 datetime类型的t2 分别插入 2001-10-02 和2002-10-03 08:00:00

输入 select * from t10; 查看t10表的更详细的信息
发现t3自动更新为当前时间

---

当输入 update t10 set t1=‘2003-10-04’; 更新t10表中t1的时间
发现t3所表示的时间戳 也会跟着变化

5. string 类型

enum类型和set类型

enum：枚举
enum(‘选项1’,‘选项2’,‘选项3’);
提供若干个选项的值，最终一个单元格中，实际只存储其中一个值
如：问卷调查 性别 只能选男或者女

---

set ：集合
set(‘选项1’,‘选项2’,‘选项3’);
提供若干个选项的值，最终一个单元格中，可存储任意多个值
如：问卷调查 爱好 可以选打羽毛球 乒乓球 篮球等

---

创建一张表 votes，其中包含 最多存储30字节的名字 、 性别 、 爱好
同时 性别 只能从男或者女中 选择一个
而爱好 可以从 代码 篮球 游泳中 选择一个或者多个

---

向votes表中插入 姓名为 张三 性别为 男 爱 好为代码

---

当性别中 插入 0 和3 是不可以的，可插入 1 和 2 是可以的

---

输入 select * from votes 时，发现 1对应性别男 2对应性别女

所以使用枚举类型，可以使用对应的枚举常量
也可以使用枚举常量对应的下标 (从1开始 分别代表第一个枚举值 依次往后推)

---

向votes表中插入 姓名 为赵六 、性别为男、 爱好为 代码、篮球、游泳
(不允许插入不存在的爱好，同时也可以选择插入一个爱好或者多个爱好)

---

enum类型 与 set类型 允许为空

只插入 名字，性别与爱好会显示为NULL
NULL表示什么都没有

---

’ ’ 表示有东西，但是 为空串

若set类型 对应的爱好 插入 0 ，则显示为空串
若set类型 对应的爱好 插入 1 ，则显示为第一个设置的值 ，依次类推

---

若set类型 对应的爱好 插入 2，则显示为第二个设置的值
若set类型 对应的爱好 插入 3 ，则显示为第一个设置的值 和第二个设置的值

---

因为共有3个爱好，所以设置3个比特位 000
从右到左，依次为从低到高
若表示代码，则为 001
若表示篮球，则为 010
若表示代码和篮球，则为011
若表示游泳，则为 100

---

enum类型和set类型的查找

在枚举中的查找

输入 select * from votes where gender=‘男’;
挑选出votes表中所有男生的信息

---

输入 select * from votes where gender=‘女’;
挑选出votes表中所有女生的信息

---

在set中的查找

输入 select * from votes where hobby=‘游泳’;
挑选出votes表中爱好只有游泳的人
有的人爱好是广泛的，其中就包含游泳，但是筛选并不显示

---

所以需要借助 mysql的筛选函数 —— find_in_set 函数

find_in_set(sub,str,str_list)：如果sub在str_list中，则返回下标，如果不在，返回0；
str_list 用逗号分隔的字符串

---

输入 select * frin votes where find_in_set(‘游泳’,hobby);
在votes表 的 hobby 查找所有爱好有游泳 的人

---

and相当于 C语言的逻辑与
在votes表 的 hobby 查找所有爱好有游泳 以及 爱好有篮球的人