目录
一、数据类型一览
二、整型
(一)INT
(二)BIT
三、浮点数
(一)FLOAT
(二)DECIMAL
(三)如何选择
四、字符
(一)CHAR
(二)VARCHAR
(三)如何使用
五、时间和日期
(一)DATE
(二)DATETIME
(三)TIMESTAMP
(四)简易留言板
六、枚举和集合
(一)ENUM
(二)SET
(三)集合 中元素的查询
一、数据类型一览
作为专门用来存储数据的 数据库,MySQL 提供了丰富的数据类型,覆盖绝大多数使用场景
分类 | 数据类型 | 说明 |
---|---|---|
数值类型 | BIT(M) | 位类型,M指定位数,默认为 1,范围 [1, 64] |
TINYINT [UNSIGNED] | 有符号范围 [-128, 127],无符号范围 [0, 255],默认为有符号 | |
BOOL | 0 表示假,1 表示真,在许多数据库系统中,BOOL 与 TINYINT(1) 是同义的 | |
SMALLINT [UNSIGNED] | 有符号范围 [-2^15, 2^15-1],无符号范围 [0, 2^16-1] | |
INT [UNSIGNED] | 有符号范围 [-2^31, 2^31-1],无符号范围 [0, 2^32-1] | |
BIGINT [UNSIGNED] | 有符号范围 [-2^63, 2^63-1],无符号范围 [0, 2^64-1] | |
FLOAT [(M, D)] [UNSIGNED] | M 指定显示长度,D 指定小数位数,大小依赖具体平台,通常占用 4 字节,但在某些情况下可能达到 8 字节 | |
DOUBLE [(M, D)] [UNSIGNED] | 比 FLOAT 精度更高,大小通常为 8 字节,在某些情况下可能更大 | |
DECIMAL(M, D) [UNSIGNED] | 比 DOUBLE 精度更高,能完全保证数据的精度,适用于需要高精度的金融计算 | |
字符、二进制类型 | CHAR(SIZE) | 固定长度字符串,最大支持 255 字符,用空格填充字符串到指定的长度 |
VARCHAR(SIZE) | 可变长度字符串,最大支持 65535 字符,它不会用空格填充 | |
BLOB | 二进制数据 | |
TEXT | 大文本,不支持全文索引、默认值,需要注意有一些变体 | |
时间日期 | DATE | 格式为 yyyy-mm-dd |
DATETIME | 格式为 yyyy-mm-dd hh:mm:ss | |
TIMESTAMP | 时间戳,通常表示一个时间点,包括日期和时间 | |
其他类型 | ENUM | 字符串对象,表示枚举类型,只能选取其中一个值,由于在某些数据库系统中的限制,ENUM 的使用有时会受到争议 |
SET | 字符串对象,表示集合类型,可以选取零个或多个值 |
二、整型
(一)INT
首先看看最常用的 int
类型,同 C/C++
中的一样,MySQL 中的 int
占 4
字节,支持创建无符号类型
INT [UNSIGNED]
mysql> create table testINT
(
整型 int,
无符号整型 int unsigned
);
mysql> desc testINT;
注意:
MySQL
中创建无符号类型,是在类型之后加上unsigned
- 括号中的数字通常用于指定显示宽度,并不表示存储范围
向表中插入一组合法的数据
mysql> insert into testInt values (-1234, 1234), (1000000000, 3000000000);
整型的范围是 [-2147483648
, 2147483647
],无符号整型的范围是 [0
, 4294967295
],范围内的数据都可以正常插入,如果插入越界数据,则会插入失败:
mysql> insert into testInt (整型) values (2147483648);
mysql> insert into testInt (整型) values (-2147483649);
mysql> insert into testInt (无符号整型) values (4294967296);
mysql> insert into testInt (无符号整型) values (-1);
由此可见:MySQL 中对于数据范围具有严格约束,会直接拦截非法数据的插入。
也就是说:凡是成功插入 MySQL 中的数据,都是合法的。
约束使用者,
MySQL
能保证数据插入的合法性(数据库中的数据是可预期、完整的)
插入数据时,需要注意符合数据类型范围要求。
除了最常用的 int 外,MySQL 还提供了其他几种衍生类型:
- TINYINT 极短整型,占 1 字节,范围 [-128, 127]
- SMALLINT 短整型,占 2 字节,范围 [-32768, 32767]
- MEDIUMINT 中整型,占 3 字节,范围 [-8388608, 8388607]
- BIGINT 大整型,占 8 字节,范围 [-9223372036854775808, 9223372036854775807]
不同大小的整型适用于不同场景中,MySQL 提供了选择,把选择的权力交给了用户,由用户根据数据库的预期大小,自由选择整型。
注意: 这些衍生类型如同 INT 一样,可以声明为无符号类型,定义时在类型之后加上 unsigned
即可,创建无符号类型后,无法再存储负数,存储整正数的能力翻倍。
尽量不使用
unsigned
,对于int
类型可能存放不下的数据,int unsigned
同样可能存放不下,与其如此,还不如设计时,将int
类型提升为bigint
类型。
(二)BIT
MySQL 中提供了一个很有意思的类型:bit(m)
,表示 位字段类型,M
的范围 1~64
,这里的位指 比特位,64
比特位即 8
字节,接下来分别创建几个 bit
类型:
mysql> create table testBIT
(
比特位_1 bit(1),
比特位_2 bit(2),
比特位_8 bit(8)
);
mysql> desc testBit;
再分别向其中插入一些数据,查询时却看到了一些奇怪字符
mysql> insert into testBit values (1, 3, 100), (0, 2, 255);
mysql> select * from testBit;
这是因为 查表时,位字段类型默认按 ASCII
的形式显示,查表可以发现,100
正好对应字符 d
如果我们想让其正常显示,查询时可以使用 hex()
这个内置函数将查询结果转为十六进制显示:
mysql> select hex(比特位_1), hex(比特位_2), hex(比特位_8) from testBit;
注:64
表示 6 * 16 + 4 = 100
,FF
则表示 15 * 16 + 15 = 255
值得注意的是,对于 BIT
类型,MySQL
也做出了相应的 约束,对于超出范围的数据,拒绝插入
mysql> insert into testBit (位字段类型_1) values (3);
这里的 3
明明只是一个整数,为什么会失败?
注意: 位字段类型限制的是比特位,而非位数,3
的二进制表示为 0011
,已经使用了两个比特位,自然也就超出范围了,所以 bit(1)
只能插入 0
或 1。
三、浮点数
(一)FLOAT
MySQL 支持我们熟悉的浮点数类型 float
、double
,所占大小分别为 4
字节和 8
字节
FLOAT [(M, D)] [UNSIGNED]
DOUBLE [(M, D)] [UNSIGNED]
mysql> create table testFloat
(
浮点数 float(4, 2)
);
mysql> desc testFloat;
其中,float(4, 2)
中的 (4, 2)
表示当前浮点数支持显示 4
位,且小数精度为 2
位,数据范围为 [-99.99
, 99.99
]
float
和double
在定义时允许不指明显示位数和小数精度
插入一些正常数据,可以看到插入成功
mysql> insert into testFloat values (50.05), (99.99), (-99.99);
插入一些超出范围的数据,直接被拦截了
mysql> insert into testFloat values (500.05);
mysql> insert into testFloat values (-500.05);
mysql> insert into testFloat values (-100.00);
mysql> insert into testFloat values (-100.0);
为什么插入 -100.0
也被拦截了?
这是因为 当小数部分不足时,MySQL
自动补齐,补齐后,实际插入的值为 -100.00
,有五位数,超出范围,自然就被拦截了。
MySQL
也并非是铁面无私,当我们插入的数据小数部分超过指定精度时,MySQL
会遵循四舍五入的规则,将多余的小数部分省略,五入 向前进一位后,如果超出了范围,也会被拦截:
mysql> insert into testFloat values (50.005), (50.000001), (99.994);
mysql> select * from testFloat;
// 下面这种情况会被拦截,因为四舍五入后,数据会变成 100.00
// mysql> insert into testFloat values (99. 995);
总之就是 MySQL
必须确保数据库中数据的合法性。
如果将 float
类型定义为无符号类型,会导致它的 负数 部分直接丢弃
mysql> create table testFloatU
(
无符号浮点数 float(4, 2) unsigned
);
mysql> desc testFloatU;
mysql> insert into testFloatU values (99.991), (50.00), (0);
// 插入失败
mysql> insert into testFloatU values (-99.991), (-50.00), (-1);
至于为什么这么奇怪,这是因为浮点数的存储规则不同于其他类型,采用 IEEE 754
标准,不是简单的直接使用二进制位,而是将二进制位划分为几个区间,各司其职,如果将其设为无符号类型,等价于将符号表示的二进制位直接禁用。
float
存在精度损失,当插入的小数部分过长时,会导致精度丢失
比如这里先创建一张含有 float(10, 8)
类型的表
mysql> create table testFloatL
(
浮点数 float(10, 8)
);
mysql> desc testFloatL;
在表中插入一个较长的数据,并查询结果
mysql> insert into testFloatL values (1.23456789);
mysql> select * from testFloatL;
可以看到第八位小数精度丢失了,证明 float
在使用过程中存在 精度损失 问题。
float
至多保证7
位小数精度准确。
至于 double
,除了比 float
大一些之外,与 float
的使用方法一模一样,同样存在 精度损失问题,如果想要追求超高精度,可以改用 decimal
类型。
(二)DECIMAL
decimal
是 MySQL 针对高精度小数推出的浮点数类型,decimal
使用方法和 float / double
一样,在支持浮点数存储的同时,decimal
还可以保证小数部分的精度:
DECIMAL(M, D) [UNSIGNED]
创建一张表,其中分别包含两个高精度浮点数类型
mysql> create table testDecimal
(
f float(10, 8),
d decimal(10, 8)
);
mysql> desc testDecimal;
分别插入普通数据和高精度数据,查看两者的差异
mysql> insert into testDecimal values (99.99, 99.99), (1.23456789, 1.23456789);
mysql> select * from testDecimal;
可以看到,float
不仅出现了精度丢失,还出现了只插入两位小数的情况下,后面出现了一堆小数;而 decimal
则是指哪打哪,插入的数据非常准确,精度保持的非常稳定。
(三)如何选择
float 比 decimal 占用空间小,decimal 比 float 精度高。
- 如果对精度要求不是很高(精度少于等于七位),可以选择 float;
- 如果从事金融相关工作,对精度的要求极其苛刻,选择 decimal。
float 能保证的精度最多是 7 位。
decimal 能保证的精度最多是 30 位,不同版本的 MySQL 略有差异。
四、字符
(一)CHAR
char 在 C/C++
中称为字符类型,大小为 1
字节,在 MySQL
中则是叫做 固定长度字符串,也就是说 char
可以存储字符串(前提是长度足够)。
CHAR(L)
创建一张表,其中包含了长度为 2
的 char
mysql> create table testChar
(
固定字符串 char(2)
);
mysql> desc testChar;
向表中插入字符串
注:在 MySQL
中使用 ' ' 或者 " " 括起来的为字符串:
mysql> insert into testChar values ('a'), ('bb'), ('你'), ('你好');
mysql> select * from testChar;
mysql> insert into testChar values ('你好么');
mysql> insert into testChar values ('hhh');
在
utf8
编码中,一个英文字符占1
字节,一个中文字符占3
字节。
成功插入 2 个英文字符可以理解,但为什么也可以成功插入 2 个中文字符?
这是因为 MySQL 中的 char 类型大小并非 1 字节,而是 1 字符,无论是什么语言,都可以按个数插入,而非实际占用空间,这与 C/C++ 中的 char 有根本上的区别。
这也就是解释了为什么 char 被称为 固定长度字符串,不过这个字符串也是有 约束 的,当实际插入的字符串长度超过 char 的固定长度时,插入操作会被拦截,并且 char 支持的最大长度为 255,大于 255 的字符串注定不能使用 char 类型存储。
mysql> alter table testChar modify 固定字符串 char(255);
mysql> desc testChar;
// 修改失败
mysql> alter table testChar modify 固定字符串 char(256);
(二)VARCHAR
varchar 表示 变长字符串,不同于 char
至多支持插入长度为 255
的字符串,varchar
至多支持插入总大小为 65535
字节的字符串:
VARCHAR(L)
varchar 为什么被称为 变长字符串 ?
这是因为给 varchar 分配空间后(假设分配了 10 字符),如果实际插入的字符串长度为 5,则只会使用 varchar 中的 5 字符空间,如果实际插入的长度为 8,就会使用 8 字符空间,在长度范围之内,varchar 会根据实际插入的字符串长度,动态分配空间,达到 变长 的效果。
mysql> create table testVarchar
(
变长字符串 varchar(6)
);
mysqL> desc testVarchar;
mysql> insert into testVarchar values ('你'), ('你好'), ('你好么'), ('你好吃了么');
mysql> select * from testVarchar;
尝试将 变成字符串
的大小改为 65535
,结果提示最大支持修改为 21845
mysql> alter table testVarchar modify 变长字符串 varchar(65535);
为什么这里的
varchar
最大只支持21845
?因为此时存入的是中文字符,前面提到过,一个中文字符在
utf8
编码中占3
字节,21845 * 3 = 65535
,而这正是varchar
支持字符串的最大字节数。
需要注意的是 varchar
的最大长度与编码密切相关,在英文字符状态下,varchar
至多支持插入长度为 65535
的字符串,但是其中需要使用 1~3
字节的空间用来记录数据大小,所以实际至多支持插入长度为 65532
的英文字符串。
对于中文字符串来说,除去 3
字节的记录空间,可用空间剩余 65532
字节,每个中文字符占 3
字节,实际至多插入长度 65532 / 3 = 21844
的中文字符串。
将之前的 testVarchar
大小修改为 21844
mysql> desc testVarchar;
mysql> alter table testVarchar 变长字符串 varchar(21488);
mysql> desc testVarchar;
注意: 我这里能修改成功是因为当前的 testVarchar
表中只有一个字符,数据表中的字段也是需要占用空间的,当字段过多时,是无法修改 varchar
为最大容量的。
如果实际使用场景中多次涉及 varchar
的边界,可以改用 text
,这是一个专门用来存储文本数据的类型,比如当前编写的博客,就是通过该类型存储的。
(三)如何使用
char
与 varchar
的区别如下
实际存储 | char(4) | varchar(4) | char 占用字符 | varchar 占用字符 |
---|---|---|---|---|
abcd | abcd | abcd | 4*3=12 | 4*3+1=13 |
好 | 好 | 好 | 4*3=12 | 1*3+1=4 |
Abcde | X | X | 数据超过长度 | 数据超过长度 |
varchar
会额外使用 1~3
字节的空间,用于记录数据大小。
如何选择定长或变成字符?
- 如果数据确定长度都一样,就使用定长 char,比如身份证、手机号。
- 如果数据长度有变化,就使用变长 varchar,比如名字、地址。
- 定长的磁盘空间比较浪费,但是效率高。
- 变长的磁盘空间比较节省,但是效率低。
- 定长的意思是:直接开辟对应好的空间。
- 变长的意思是:在不超过自定义范围的情况下,用多少开辟多少。
五、时间和日期
(一)DATE
在 MySQL 中,日期表示 年月日,通过 date
类型表示,占用 3
字节空间
// yyyy-mm-dd
DATE
mysql> create table testDate( 日期 date );
mysql> desc testDate;
可以向其中插入日期信息
注意: 插入的日期信息需要使用 ' ' 或者 " " 引起来。
mysql> insert into testDate values ('2024-5-20'), ('2024-5-21'), ('2024-1-1');
mysql> select * from testDate;
(二)DATETIME
如果想插入更为详细的日期信息,可以改用 datetime
,格式为 年月日 时分秒,称为 日期时间类型,占用 8
字节空间。
范围从 1000
到 9999
// yyyy-mm-dd hh:mm:ss
DATETIME
mysql> alter table testDate add (日期时间 datetime);
mysql> desc testDate;
同样的,插入数据时需要使用 ' ' 或者 " " 将日期时间括起来:
mysql> insert into testDate values
('2024-5-20', '2024-5-20 0:0:0'), ('2024-5-21', '2024-5-21 12:0:0');
mysql> select * from testDate;
诸如评论时间、博客发布时间等都是通过 datetime
类型存储的:
除了 日期 和 日期时间,当然还有单纯 时间 类型,只能用来存储具体时间
// hh:mm:ss
TIME
(三)TIMESTAMP
MySQL中提供了 时间戳 类型 timestamp
,占用 8
字节,时间戳 最大的特点是随着对表的更新操作,更新时间,并且 时间戳 在查询时,显示的格式与 datetime
一样
时间戳是指从
1970-1-1 0:0:0
开始计时的秒数
// yyyy-mm-dd hh:mm:ss
TIMESTAMP
注意:时间戳是一个非空字段,并且不允许用户手动插入时间。
使用 update
指令更新字段信息,可以触发 时间戳 的更新。
mysql> select * from testDate;
mysql> update testDate set 日期='2000-1-1' where 日期='2024-5-20';
mysql> select * from testDate;
(四)简易留言板
利用这几个日期类型,编写一个简易版的 留言墙
mysql> create table commentWall
(
name varchar(32) comment '用户名',
regisTime date comment '注册时间',
msg varchar(256) comment '留言信息',
modifyTime timestamp comment '发布/修改时间'
);
mysql> desc commentWall;
向其中插入一些留言信息,并在片刻之后修改其中的留言信息。
注意: 因为时间戳不允许用户手动插入,所以在插入数据时,不能再使用全列插入的方式,需要指明往哪些列中插入数据。
mysql> insert into commentWall (name, regisTime, msg) values
('小明', '2021-1-1', '新年快乐!'),
('小红', '2022-5-1', '五一快乐!'),
('小黑', '2023-10-1', '国庆快乐!');
mysql> select name as 用户名, regisTime as 注册时间, msg as 留言内容, modifyTime as 修改时间 from commentWall;
mysql> update commentWall set msg='五一出来玩么' where name='小红';
mysql> select name as 用户名, regisTime as 注册时间, msg as 留言内容, modifyTime as 修改时间 from commentWall;
六、枚举和集合
(一)ENUM
enum称为 枚举 类型,用于提供一批元素,可以选择其中一个
ENUM
mysql> create table votes
(
name varchar(16),
gender enum('男', '女')
);
mysql> desc votes;
向其中插入值时,只能选择枚举中已经存在的元素,并且只能选择一个
mysql> insert into votes values ('张三', '男'), ('小红', '女');
mysql> select * from votes;
// 非法操作
mysql> insert into votes values ('李四', '男, 女');
mysq> insert into votes values ('王五', '第三人称');
除了直接选择枚举中的元素值外,还可以通过下标的方式进行选择,当然超过下标索引范围的选择也是非法的:
注:这里的下标从 1
开始
mysql> insert into votes values ('李四', 1), ('小婷', 2);
mysql> select * from votes;
// 非法操作
mysql> insert into votes values ('赵六', 0);
mysql> insert into votes values ('田七', 3);
(二)SET
如果想多选,需要使用 集合 类型
SET
mysql> alter table votes add (hobby set('唱', '跳', 'rap', '篮球', 'music'));
mysql> desc votes;
可以看到,枚举 和 集合 类型默认都是可以为空的,现在继续向表中插入数据。
注意: 在多选集合中的元素时,以 ,
分隔,并且中间不能有空格。
mysql> insert into votes values ('蔡徐坤', '男', '唱,跳,rap,篮球,music'), ('肖红', '女', '唱');
mysql> select * from votes;
集合 类型支持通过数字多选元素
mysql> insert into votes values ('陈立农', 1, 3), ('范丞丞', 1, 4);
mysql> select * from votes;
结果显得有些诡异,这是因为 集合中的数字并非下标,而是一个数值,其中的比特位对应着集合中的选项:
- 当值为 1 时,二进制表示为 0001,即 集合 中的第一个元素 唱
- 当值为 3 时,二进制表示为 0011,即 集合 中的第二个元素 唱,跳
- 当值为 4 时,二进制表示为 0100,即 集合 中的第三个元素 rap
所以如果想通过数字全选当前 集合 中的所有元素(11111
),转化成十进制,表示为 31
,实际插入时结果符合预期:
mysql> insert into votes values ('唯一真神', 1, 31);
mysql> select * from votes;
如果把数值设为 0
,会发现最终选择的元素为 空。
注意: NULL
与 空
不等价。
NULL
表示什么都没有,空
则表示某个类型存在,但它现在为空。
mysql> insert into votes values ('故人', 1, 0);
mysql> select * from votes;
(三)集合 中元素的查询
如果想查询包含其中一个元素的行信息,需要借助函数 find_in_set(val, set)
这个函数可以在 set
中查找是否存在 val
,如果存在就返回其下标,不存在则返回 0
,在 MySQL
中可以直接通过 select
指令执行函数:
mysql> select find_in_set('a', 'a,b,c,d,e');
mysql> select find_in_set('c', 'a,b,c,d,e');
mysql> select find_in_set('e', 'a,b,c,d,e');
mysql> select find_in_set('ee', 'a,b,c,d,e');
将 find_in_set
函数加入 select
查询子句中,即可筛选出包含 集合 中任意元素的行信息:
mysql> select * from votes where find_in_set('唱', hobby);
如果想同时筛选出包含多个元素的行信息,可以使用 and
级联两个条件:
mysql> select * from votes where find_in_set('唱', hobby) and find_in_set('跳', hobby);
关于 MySQL
中数据类型的讲解到此结束,总之:数据类型本质上就是一种约束,确保插入数据库中数据的合法性。