05_MySQL数据库_数据类型

此专栏所有章节快速导航

01_MySQL数据库_CentOS7安装MySQL
02_MySQL数据库_数据库基础知识
03_MySQL数据库_库的操作
04_MySQL数据库_表的操作
05_MySQL数据库_数据类型
06_MySQL数据库_表的约束
07_MySQL数据库_增删查改
08_MySQL数据库_复合查询
09_MySQL数据库_索引
10_MySQL数据库_事务管理
11_MySQL数据库_用户管理
12_MySQL数据库_CentOS7下C-C++链接MySQL

一. 数据类型分类

二. 具体类型解析

2.1 数值类型

2.1.1 整型

tinyint

• tinyint占一个字节，tinyint unsigned为无符号的tinyint
• 有符号的取值范围为-2^7到（27）-1，无符号的取值范围为0到2^8-1
• 总得来说tinyint的取值范围跟C语言的char类型一样
• 数据库对类型有着很强的约束，如果插入不在取值范围内的数据会直接报错，不像C/C++那样发生截断，所有数据类型都是这样的

create table t1 (num tinyint);
create table t2 (num tinyint unsigned);

# 其他类型
# smallint的取值范围跟C语言的short类型一样
# int的取值范围跟C语言的int类型一样
# bigint的取值范围跟C语言的long long类型一样

2.1.2 浮点型

float

• float[(m, d)]： m指定显示长度，d指定小数位数，占用空间4个字节

• float(4,2)表示的范围是-99.99到99.99

• MySQL在保存值时会进行四舍五入

• 如果是99.994四舍五入等于99.99插入的时候不会报错

• 如果是99.995四舍五入等于100.00，超过了范围，插入时会直接报错

• float和double的取值范围和占用空间大小和C语言的float和double是一样的

• 不同的是sql的float和double可以有无符号类型

# 有符号
create table t1 (a float(4,2));  # 表示显示的长度是4个数字，其中小数站2个，范围是-99.99到99.99

# 无符号
create table t3 (a float(4,2) unsigned);  # 只能存储0到99.99之间的数

decimal

• decimal和float的使用是一样的
• 不同的是float和double的大小是固定的，float占4个字节，double占8个字节
• 而decimal会根据m和d的取值不同，占用的字节数也不同
• float表示的精度大约是7位，而decimal的精度就非常高了
• decimal整数最大位数m为65，支持小数最大位数d是30
• 如果d被省略，默认为0.如果m被省略，默认是10

create table t2 (a decimal(4,2));   # 表示显示的长度是4个数字，其中小数站2个

2.1.3 位字段类型

bit

# bit(M)：M表示大小为多少个bit位，范围从1到64。如果M被忽略，默认为1。
create table if not exists t1 (a bit(2));  # 表示a的大小为2bit，取值为0到3
create table if not exists t1 (a bit(8));  # 表示a的大小为8bit，取值为0到255，显示的结果是以字符类型显示的

2.2 字符串类型

char

• char(L): 固定长度字符串，L是可以存储的长度，单位为字符（注意单位为字符），最大长度值可以为255
• 一个英文字母或者符号是一个字符，同样一个中文或者中文符号也是一个字符
• 一个字符占几个字节取决于你的数据库的编码格式是什么
• UTF-8编码：一个英文字符等于一个字节，一个中文等于三个字节。中文标点占三个字节，英文标点占一个字节
• Unicode编码：一个英文等于两个字节，一个中文等于两个字节。中文标点占两个字节，英文标点占两个字节
• 这里的固定长度的意思是char类型的字段定义好就确定了大小了，类似C/C++的字符数组

create table t1 (name char(3));  # 可以存放3个字符，比如'abc'  '程序员'

varchar

• varchar(L): 可变长度字符串，L表示字符长度，单位为字符，最大长度为65535个字节
• 这里的可变长度意思是定义的时候的大小只是限制了最大长度，实际的长度是使用的时候根据插入的字符串决定的，类似C++的string对象
• 关于varchar(L),L到底是多大，这个L值，和表的编码密切相关：
• varchar长度可以指定为0到65535个字节之间的值，但是有1到3个字节用于记录数据大小，所以说有效字节数是65532。
• 当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf中，一个字符占用3个字节]
• 如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符占用2字节）

create table t2 (name varchar(3));

char和varchar的使用场景和对比

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

2.3 日期时间类型

• date： 日期 ‘yyyy-mm-dd’ ，占用三字节
• datetime： 时间日期格式 ‘YYYY-MM-DD HH:MM:SS’ ，默认情况下，datetime的值范围为1000-01-01 00:00:00至9999-12-31 23:59:59，占用八字节
• timestamp： 时间戳，从1970年开始的YYYY-MM-DD HH:MM:SS 格式和 datetime 完全一致，占用四字节。添加数据时，时间戳自动补上当前时间

create table dt1 (t1 date, t2 datetime, t3 timestamp);

如下图我们的t3是时间戳类型的，显示不能为空，默认会自动补上当前的时间

如下图，我们一开始只往t1，t2里面插入数据，t3会自动补上当前的时间。然后我们t1，t2，t3都插入数据，这是t3的数据就是我们插入的值。实际上我们对t1和t2任何两列的改变，对应行的t3都会把当前时间存进去的

2.4 选项类型

enum

enum只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值。而且出于效率考虑，这些值实际存储的是“数字”，从1开始最多65535个。当我们添加枚举值时，也可以添加对应的数字编号。

# enum('选项1','选项2','选项3',...);
create table person(sex enum('男', '女'));
# 插入数据的时候可以用'男'或'女' 也可以用1或2
# 显示的时候只会显示男或者女

set

set提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值。而且出于效率考虑，这些值实际存储的是“数字”，这些选项的每个选项值依次对应如下数字：1,2,4,8,16,32，… 最多64个选项

create table person
(
    sex enum('男', '女'),  # 只能选一个
    hobby set('游泳', '摄影', '打篮球', '踢足球')  # 可以选多个
);
# 这里的hobby有4个选项，会用4个比特位来一一映射他们
# 假如用数字的方式插入，插入的是3，对应的就是1100（左边是低位右边是高位），也就是对应的游泳和摄影两个选项

find_ in_ set函数

select * from votes where find_in_set('登山', hobby);  # 意思是查询hobby这一列中包含'登山'的所有行数据