MySQL 数据类型

文章目录

• 数据类型
  • 数据类型分类
  • 数据类型
    • tinyint类型（整型）
    • 总结
    • bit类型（字节）
  • 浮点类型
    • float类型
    • decimal类型
  • 字符串类型
    • char类型
    • varchar（变长字符串）
  • char 和 varchar的对比
  • 日期类型
  • enum和set类型（枚举和集合）
  • enum和set的查找

数据类型

数据类型分类

红色是比较重要的

数据类型

1. 整型可以指定是有符号的和无符号的，默认是有符号的
2. 无符号的后面要加上unsigned

tinyint类型（整型）

• 有符号的案例
  -128 ~ 127

insertintovalue(-128);insertintovalue(127);insertintovalue(0);insertintovalue(1);insertintovalue(-1);

超过tinyint的范围的则无法插入
注意：创建表的时候要在某个库里面创建

• 无符号的案例
  0 ~ 255

insertintovalue(0);insertintovalue(255);

总结

1. MySQL规定了数据类型不会发生截断，保证了数据的完整性和确定性，也就是在规定的范围内才可以插入数据
2. 数据类型本身也是一种约束
3. 什么是约束呢？让程序员正确地插入，约束使用者，保证插入的合法性和可预期性
4. mysql表中建立属性列的规则：
列名称 类型在后
num tinyint unsigned

注意：尽量不使用unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不下，与其如此，还不如设计时，将int类型提升为bigint类型。

bit类型（字节）

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

• 案例

创建表 createtable t3( id int, 用户名 online bit 在线状态 );insertinto t3 (id,online)values(123,1);insertinto t3 (id,online)values(124,0);select*from t3;

实际上查表的时候是以ascii的形式存的，当前的ascii是不可显示

使用16进制的方式显示

• 验证它是否是以ascii的形式存储的

// 将online的存储位数增大altertable t3 modify online bit(10);insertinto t3 (id,online)values(123,97);insertinto t3 (id,online)values(123,'a');select*from t3;

浮点类型

float类型

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

• 案例
  小数：float(4,2)表示的范围是-99.99 ~ 99.99，MySQL在保存值时会进行四舍五入。

createtable t5( id int, salary float(4,2));insertinto t5 (id,salary)values(123,99.99);insertinto t5 (id,salary)values(123,-99.99);insertinto t5 (id,salary)values(123,10.0);insertinto t5 (id,salary)values(123,1.00);

float也不能超过范围

精度比规定的精度要高，不会拦截，会进行四舍五入

在合法的范围内才可以进行四舍五入

• 无符号的float

createtable t6( id bigint, salary float(4,2)unsigned);insertinto t5 (id,salary)values(1,0.00);insertinto t5 (id,salary)values(1,99.99);

无符号的float是直接把负数的部分砍掉不要，只保留正数的部分[0，99.99]

• 默认的float会存在精度的损失（整数和小数部分）

altertable t6 modify salary float;

存的是32423423422.21
但保存的是32423400000

decimal类型

decimal类型可以解决float的精度损失的问题

• 案例，float精度会损失，decimal会完整保留小数位数，不会损失

createtable t6( salary float(10,8), salary1 decimal(10,8));insertinto t6 (salary,salary1)values(10.23232232,10.23423423);

1. float表示的精度大约是7位。
2. decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0.如果m被省略，默认是10。
3. 默认的精度不同的MySQL版本不一样，最好自己设置
4. 如果希望小数的精度高，推荐使用decimal。

字符串类型

char类型

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

createtable t8( id int, name char(2));insertinto t8 (id,name)values(1,'a');insertinto t8 (id,name)values(1,'ab');insertinto t8 (id,name)values(1,'中国');

2. MySQL的一个字符表示只要占一个位置就是一个字符，不像编译器，一个汉字占3或者4个字节

varchar（变长字符串）

1. varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字节，最大可以存21845个字符

createtable t9( id int, name varchar(6));insertinto t9 (id,name)values(1,'中国人，加');

2. varchar(6)，给了你6个字符的空间，如果你只使用1个空间，那么就只给你分配一个空间，二char是给你分配6个空间的

3. 关于varchar(len),len到底是多大，这个len值，和表的编码密切相关：
   <> varchar长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字节数是65532。
   <> 当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf中，一个字符占用3个字节]，如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符占用2字节）。
   

mysql>createtable tt11(name varchar(21845))charset=utf8;--验证了utf8确实是不 能超过21844

还有就是一行可能会被其他变量分去空间，比如一个字符占3个字节，所以说一行的最大空间是65532字节

char 和 varchar的对比

1. 变长效率低的原因：varchar需要维护有效字符的长度，需要额外的空间保存字符的长度

如何选择定长或变长字符串？

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

日期类型

• date :日期 ‘yyyy-mm-dd’ ，占用三字节（年月日）
• datetime 时间日期格式 ‘yyyy-mm-dd HH:ii:ss’ 表示范围从 1000 到 9999 ，占用八字节（年月日时分秒，不会随着时间变化而变化的）
• timestamp ：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用四字节（年月日时分秒，会随着时间变化而变化的）
• 案例

createtableifnotexists t11( t1 date, t2 datetime, t3 timestamp);insertinto t11 (t1,t2)values('2006-10-01','2006-10-01 08:00:00');// t3是自动更新的update t11 set t1='1999-01-01';

t3时间戳是自动更新的

时间戳的应用场景，比如你发表评论的时间是自动更新的

固定时间datetime的应用场景：比如你的出生年月时分秒，你公司的入职时间

enum和set类型（枚举和集合）

1. enum：枚举，“单选”类型；（多选一）
   enum(‘选项1’,‘选项2’,‘选项3’,…);
2. set：集合，“多选”类型；（多选多和多选一都可以）set(‘选项值1’,‘选项值2’,‘选项值3’, …);

• 案例

createtableifnotexists votes( usename varchar(30), gender enum('男','女'), hobby set('代码','羽毛球','乒乓球'));desc votes \G; 枚举类型 insertinto votes (usename,gender)values('张三','男');insertinto votes (usename,gender)values('张三','女');insertinto votes (usename,gender)values('张三',1);insertinto votes (usename,gender)values('张三',2);// 枚举的下标是从1开始的,1表示男,2表示女 集合类型:可以插入一个,也可以插入多个值 insertinto votes (usename,gender,hobby)values('张三',1,'代码');insertinto votes (usename,gender,hobby)values('张三',1,'羽毛球,足球,游泳');insertinto votes values('张三',1,'羽毛球,足球,游泳');

3. 枚举和集合类型允许插入的时候为空

4. NULL vs ‘ ’ 的区别：NULL是什么都没有，而’ '是有只不过是空串 （前者就是你没有银行卡，后者就是你有银行卡但是里面没钱）

5. 多选是位图，00000表示0是代码，00010表示2是羽毛球，00111表示7是代码，羽毛球和乒乓球，1代表有，0代表没有

00011 -> 代码和羽毛球

6. 枚举是下标，下表从1开始，集合是位图，7表示00111，从左往右选三项运动

enum和set的查找

1. enum

select*from votes where gender='男';select*from votes where gender='1';

2. set
   集合查询使用find_ in_ set函数：
   find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回0；
   str_list 用逗号分隔的字符串。

// 查找爱好只有羽毛球的,只选了羽毛球的select*from votes where hobby='羽毛球';// 查找爱好有羽毛球的,可能选了多种select*from votes where find_in_set('羽毛球',hobby);// 如果函数返回非0为真,0为假

2. 找到返回下标

3. 只查对应的一个元素的集合里，多个元素的查不到的

4. 查询爱好里面有羽毛球的，只要有就行
查询爱好里面有羽毛球的和代码的，不是只有羽毛球和代码的，可以有其他的

5. 绝对查询，要全部符合条件的才能够查到