数据库基础（四）—— 表的字段类型（Mysql数据类型）
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |
Mysql的数据类型是一种约束为了确保数据插入和存储的一致性一旦我们插入的数据不合法比如插入与字段类型不符的数据Mysql会直接终止。这一点上和C语言就有区别C语言存在隐式类型转换即便类型不一致编译器也不会报错。
除此之外虽然Mysql中的数据类型和C语言具有相似性但是表达的意义不一定相同比如下面的 varchar存储时是以字符为单位而并非字节。
目录
1、Mysql数据类型的分类
Mysql和C语言一样数据类型可以分为整型、浮点型、字符型也可以带符号有符号、无符号。Mysql 也有自己独有的日期类型。
为了便于理解Mysql 和 C语言的某些类型比较相似。
| Mysql类型 | C语言类型 | 取值范围 |
| tinyint | char | 带符号-128~127 |
| tinyint unsigned其他同理 | unsigned char | 无符号0~255 |
| smallint | short | 详见上表 |
| int | int | 详见上表 |
| bigint | long | 详见上表 |
注意Mysql的 char —— C语言的数组、Mysql的varchar —— C语言的字符串。这两个例子比较特殊后面会具体介绍。
1、数值类型整数、小数
在MySQL中整型可以指定是有符号的和无符号的默认是有符号的。可以通过UNSIGNED来说明某个字段是无符号的注意使用无符号的取值范围变化。
(1) tinyint / int / bigint
使用这些类型的时候只需注意不要越界。
比如 tinyint的取值范围是 -128 ~ 127如果插入 128Mysql会直接终止。
mysql> insert into t1 values(128); -- 越界插入报错
ERROR 1264 (22003): Out of range value for column 'num' at row 1
(2) bit 类型
bit 是一个位字段类型M的取值范围是 1~ 64没有明确M时默认分配1 bit
-- 语法格式: M表示位数M 可以省略如果不写 M默认分配 1 bit
bit(M)
-- 案例:
create table t2 (
id int,
sex bit, -- 默认分配 1 bit
number bit(8) -- 分配 8 bit
);需要注意的是bit字段在显示的时候是按照ASCII码对应的值显示。比如插入 41此时就会显示字母 ' A '但并非所有的插入的值转换成ASCII 码显示有些字符Mysql 不支持显示。
(3) float / double
float 和 double都可以用于存储小数float 支持的最大精度是7位。有符号和无符号的取值范围不同以float(4, 2) 为例
- float(4, 2) 是有符号取值范围 -99.99 ~ 99.99
- float(4, 2) unsigned 是无符号取值范围 0 ~ 99.99
-- 语法格式: M 表示有效位数(整数+小数位数)D 表示小数位数
float(M, D)
-- 案例:
create table t5 (
id int,
salary float(4,2), -- 有效位数是4小数位数是2取值范围 -99.99 ~ 99.99
);Mysql会检查插入的小数位数和总体长度
- 小数部分如果实际小数部分的长度超出预设的值 ( D )会四舍五入
- 整数部分如果四舍五入以后总体长度超出预设的值 ( M ) 或者小数部分长度不满足预设的值 ( D )此时如果即便补全也无法满足条件Mysql会直接终止
- float(4, 2)插入 1.99 可以补全为 01.99
- float(4, 2)插入111.99直接报错
总而言之四舍五入以后如果总体长度 ≠ M 且 小数位数 ≠ DMysql都会直接终止。
(4) decimal
float 和 decimal 的使用方式完全一样同样也需要注意四舍五入以后总体长度 = M 且 小数位数 = D他们之间的区别在于 decimal 和 float 支持的精度不同float的支持的最大精度是7位decimal的支持的最大精度是30。一般更推荐使用decimal。
-- 语法格式: M 表示有效位数(整数+小数位数)D 表示小数位数
decimal(M, D)
-- 案例:
create table t5 (
id int,
salary decimal(4,2), -- 有效位数是4小数位数是2取值范围 -99.99 ~ 99.99
);2、字符串类型
(1) char
char(L) 单位是字符最多是255。限制的是字符个数
char(L) 类似于数组实际占用的空间就是 L 个字符的空间。
-- 语法格式: L是可以存储的长度单位是字符最大长度值可以为255
char(L)
-- 案例:
create table t5 (
id int,
name char(10), -- 分配10个字符的长度
);注意char(L) 的单位是字符不是字节。字符可以是汉字或者字母一个汉字算一个字符一个字母也算一个字符。
(2) varchar
varchar(L) 单位是字符最大字节数是65535其实是65532uft8因为需要有1 - 3 个字节用于记录“实际存储的数据个数”。
varchar(L) 类似于字符串容器相当于给你预留了 L 个字符 ( L* 3 或者 L* 2 个字节 ) 的空间实际占用多大空间根据你存入的数据决定。
注意一个字符占多少字节会随当前系统的字符集变化。
- 如果字符集为 utf8一个字符 = 3个字节此时varchar最多可以保存的字符个数为 65532/3 = 21844
- 如果字符集为 gbk一个字符 = 2个字节此时varchar最多可以保存的字符个数为 65532/2 = 32766
-- 语法格式: L是可以存储的长度单位是字符最大字节数是65535
varchar(L)
-- 案例:
create table t5 (
id int,
name char(10), -- 分配10个字符的长度
);
(3) char 和 varchar 的区别
限制条件的角度char限制的是字符数varchar限制的是字节数
空间占用的角度
- char(L)实际占用的空间大小就是 L 个字符的大小
- varchar(L)L个字符仅表示最多可以使用多大空间实际占用的空间根据存入的内容大小决定
举个例子我们创建的表中包含两个字段类型分别是char(4) 和 varchar(4) 两个字段都存入字母'A' 请问这两个字段分别占用了多大空间假设字符集是utf8
create table t5 (
name1 char(4), -- 存入字母'A'占用了多大空间
name2 varchar(4), -- 存入字母'A'占用了多大空间
);首先是字段name1 char(4)实际占用的空间就是分配的字符个数。分配了4个字符占用的字节数为 4 * 3 = 12 个字节
然后是字段 name2 varchar(4)实际占用的空间取决于存入的内容大小。存入 1 个字符占用的字节数为 1 * 3 + 1 = 4 个字节最后加1是因为需要1个字节记录实际存储的数据个数
3、日期类型
常用的日期类型有以下三个
- date日期格式 'yyyy-mm-dd' 占用3个字节仅存储日期
- datetime时间日期格式 ' yyyy-mm-dd HH:ii:ss '占用8个字节存储日期 + 时间
- timestamp时间戳从1970年开始到当前日期时间经过的秒数 转换成 yyyy-mm-dd HH:ii:ss 格式以后和datetime类型完全一致占用 4 个字节存储日期 + 时间
其中有几点需要注意
第一timestamp 类型有默认值默认就是当前时间。所以即便不插入内容也会自动补充建议能手动插入的务必手动插入不要把“生杀大权握在别人手里”。
第二数据库会自动修正与格式不符的分隔符或者补全不完整的日期。比如图中的10补全为2010分隔符由' / ' 修正为 ' - '
4、枚举类型
(1) enum
和C语言中的一致在定义字段的时候就要指定枚举的内容。插入的值必须是枚举列表中的一个值。
-- 语法格式: 最多可以有65535个选项
enum('选项1','选项2','选项3',...);
-- 案例:
create table t5 (
sex enum('男','女','未知')
);
插入数据时插入的内容必须是枚举范围内的内容或者下标如果写入了枚举范围之外的内容数据库就会报错。比如 sex enum('男','女','未知') 字段可以写入的值只能是 '男' 、'女'、'未知' 中的一个 或者0 、1、2中的一个
(2) set
set 在这里的意思仅表示集合与C++中的set无关。set 和 enum的使用方法一样必须在定义字段的时候指定枚举的内容。与 enum 不同的是set 可以选择枚举列表中的多个值来插入到数据库中多个值需要用逗号分隔符隔开。
-- 语法格式: 最多可以有64个选项
set('选项1','选项2','选项3',...);
-- 案例:
create table t5 (
hobby set('游泳','踢足球','打羽毛球','打篮球')
);
-- 选择多个枚举值插入注意分隔符后面不能出现空格
insert into t5 (hobby) values ('游泳,打羽毛球');
-- 也可以插入数字等价于上面的写法
insert into t5 (hobby) values (5);注意 set 也可以插入数字但是表示的意义和enum不一样是以位图的形式来识别的。比如插入 5转换成二进制就是 0101 第二个 1 对应 ' 打羽毛球 '第四个 1 对应 ' 游泳 '
(3) set 集合的查询find_in_set 函数
假设要查询字段 hobby 包含 '游泳' 的记录
-- find_in_set函数:
-- 第一个参数: 要查找的内容
-- 第二个参数: 要查找的字段
select * from t5 where find_in_set('游泳',hobby);