MySQL字段(一)
#一、字段类型
MySQL中有四大数据类型,规定类型的字段只能插入相应的数据格式。
*整数类型
*小数类型
*字符串类型
*时间日期类型
##1、整数类型
整数类型:有效的整数数据
MySQL中为了数据空间的有效使用,设定了五种整数类型
迷你整型:tinyint,使用1个字节存储整数,最多存储256个整数(-128~127)
短整型:smallint,使用2个字节存储整数
中整型:mediumint,使用3个字节存储整数
标准整型:int,使用4个字节存储整数
大整型:bigint,使用8个字节存储
数值型存储在MySQL中分为有符号(有负数)和无符号(纯正数)
步骤:
1、确定数据的格式是存储整数
2、预估整数的范围,选择合适的整数类型
3、确定整数是否需要符号(负数区间)
eg:设计一个表记录个人信息:年龄、头发数量
年龄和头发数量没有负数,正常年龄不会超过150,可用迷你整型,头发数量几百万根,可用标准整型。
create table t_1(
age tinyint unsigned, # unsigned修饰整数,表示无符号(从0开始)
haircount int unsigned
)charset utf8;
显示宽度:int(L),整数在数据库中显示的符号(数字+符号)个数
显示宽度一般是类型能表示的最大值对应的数字个数(通过desc查看表字段显示)
显示宽度包含符号(如果允许为负数,-负号会增加一个宽度)
显示宽度可以主动控制:创建字段时加括号确定
显示宽度不会影响类型能表示的最大数值
可以通过zerofill让不够宽度的数值补充到对应宽度:在字段类型后使用zerofill
##2、小数类型
###浮点数:float/double,存储不是特别精确的数值数据
浮点数又称之为精度数据,分为两种:
*单精度:float,使用4个字节存储,精度范围为6-7位有效数字
*双精度:double,使用8个字节存储,精度范围为14-15位有效数字
浮点数超过精度范围会自动进行四舍五入
精度可以指定整数和小数部分
默认不指定,整数部分不超过最大值,小数部分保留2位
可以指定:float/double(总长度,小数部分长度)
可以使用科学计数法插入数据:AEB,A * 10 ^ B
步骤:
1、确定当前设计的字段的数据为不精确型数据(或者小数)
2、确定数据的大小或者精度的要求范围:6-7位有效数字使用float,14-15位有效数字使用double
3、确定精度的分布:整数部分和小数部分
eg:记录商品的价格(名字为字符串,价格一般带小数)
create table t_2(
goods_name varchar(20),
goods_price float
)charset utf8;
insert into t_2 values('Nokia3310',199.99);
insert into t_2 values('Nokia6100',1999.9999);
###定点型:decimal,能够保证精度的小数
*不固定存储空间存储
*每9个数字使用4个字节存储
*定点型可以指定整数部分长度和小数部分长度
默认不指定,10位有效整数,0位小数
可以指定:decimal(有效数位,小数部分数位)
有效数位不超过65个
*数据规范
整数部分超出报错
小数部分超出四舍五入
步骤:
1、确定小数是否需要保证精度
2、确定有效数位长度
eg:记录个人资产情况,资产与负债(精确的)
create table t_3(
money decimal(14,2),
bet decimal(10,2)
)charset utf8;
insert into t_3 values(1111111111.12,1111111.999);
insert into t_3 values(1111111111.12,99999999.999);
## 错误:进位导致正数部分超过指定范围
##3、字符串类型
###定长型:char(L),指定固定长度的存储空间存储字符串
*定长是指定存储长度
*定长的长度是字符而不是字节
L的最大值是255
实际存储空间:L字符数 * 字符集对应字节数
*定长里存储的数据不能超过指定长度,但是可以小于指定长度
*字符串数据使用单引号或者双引号包裹
步骤:
1、确定数据类型为字符串(或不能用整数存储的超长数字符号)
2、确定数据长度基本一致(定长占用固定空间)
3、确定具体长度
eg:记录个人信息,身份证号(18位)和手机号码(11位)
create table t_4(
id_number char(18),
phone_number char(11)
)charset utf8;
insert into t_4 values('440111999912120304','13512345678');
###变长型:varchar(L),根据实际存储的数据变化存储空间
*变长型的存储空间是由实际存储数据决定的
*变长型的L也是指字符而不是字节
*L指定的是最大存储的数据长度
*L最大值理论是65535
*变长需要额外产生1-2个字节,用来记录实际数据的长度
*数据长度小于256个,多1个字节
*数据长度大于256个,多2个字节
*实际存储空间:实际字符数 * 字符集对应字节数 + 记录长度
*变长数据不能超过定义的最大长度
步骤:
1、确定数据类型为字符串
2、确定数据是不规则的数据
3、确定最大长度
eg:记录个人信息:用户名(不确定长度),密码(不确定长度),姓名(不确定长度),身份证(固定长度)
create table t_5(
`username` varchar(50),
`password` varchar(15),
`name` varchar(10),
`id_number` char(18)
)charset utf8;
insert into t_5 values('username','password','name','444111999912121111');
###文本字符串:text/blob,专门用来存储较长的文本
*文本字符串通常在超过255个字符时使用
文本字符串包含两大类
text:普通字符
tinytext:迷你文本,不超过2 ^ 8 -1个字符
text:普通文本,不超过 2 ^ 16 - 1个字符
mediumtext:中型文本,不超过 2 ^ 24 - 1 个字符
longtext:长文本,不超过 2 ^ 32 - 1 个字符(4G)
blob:二进制字符(与text类似)
tiny blob
blob
medium blob
long blob
*文本字符串会自动根据文本长度选择适合的具体类型
*一般在文本超过255个字符时,都会使用text(blob现在极少使用)
步骤:
1、确定类型为文本类型
2、确定数据长度可能超过255个字符
3、使用text
eg:记录新闻信息:标题、作者和内容
create table t_6(
author varchar(10),
title varchar(50),
content text
)charset utf8;
insert into t_6 values('李安然','给老狗的一封信','给刘闻钦的一封信...');
###枚举:一种映射存储方式,以较小的空间存储较多的数据
*枚举是在定义时确定可能出现的可能
*枚举在定义后数据只能出现定义时其中的一种
*枚举类似一种单选框
*枚举使用1-2个字节存储,最多可以设计65535个选项
*枚举实际存储是使用数值,映射对应的元素数据,从1开始
*枚举语法:enum(元素1,元素2,…元素N)
步骤:
1、确定数据是固定的几种数据之一
2、使用枚举穷举相应的元素
3、数据存储只能选择穷举中的元素之一
eg:记录人群的类型:小朋友,少年,青年,中年,老年
create table t_7(
type enum('小朋友','少年','青年','中年','老年')
)charset utf8;
insert into t_7 values('少年');
insert into t_7 values('仙人'); # 不存在的数据不能插入
# 可以使用字段 + 0来判定数据具体的效果(字符串转数值为0)
select type,type + 0 from t_7;
insert into t_7 values(5);
数据储存:
graph LR
A(指令开始)-->B[插入数据]
B-->C[读取映射关系<br>元素==数值<br>数值==数值]
C-->D[数值存储到字段]
D-->E((结束))
###集合:set,一种映射存储方式,以较小的空间存储较多的数据
*集合是在定义时确定可能出现的元素进行穷举
*集合在定义后数据只能出现定义时其中的元素(可以是多个)
*集合类似一种多选框
*集合使用1-8个字节存储数据,最多可以设计64个元素
*集合实际存储是使用数值(二进制位),映射对应的元素数据,每个元素对应一个比特位
*集合语法:set(元素1,元素2,…元素N)
步骤
1、确定数据是固定的几种数据组合
2、使用集合穷举相应的元素
3、数据存储只能选择穷举中的元素组合(多个使用逗号分隔)
eg:记录个人的球类爱好,有篮球、足球、羽毛球、网球、乒乓球、排球、台球、冰球
create table t_8(
hobby set('足球','篮球','羽毛球','网球','乒乓球','排球','台球','冰球')
)charset utf8;
insert into t_8values('足球');
insert into t_8 values('冰球,台球,篮球');
# 可以通过字段 + 0的方式查看存储的具体数值
select hobby,hobby + 0 from t_;
# 可以通过插入数值来组合元素,但是需要确定对应的十进制转换成二进制有对应的元素对应
insert into t_8 values(8);
/*
8:00001000
1:00000001
冰球、台球、篮球
11000010 => 1 + 2 ^ 1 + 1 * 2 ^ 6 + 1 * 2 ^ 7 = 2 + 64 + 128 = 194
*/
数据存储
graph LR
A(指令开始)-->B[插入数据]
B-->C[读取映射关系<br>元素选中==位值为1<br>元素未选中==位值为0]
C-->D[转化成十进制存储]
D-->E((结束))
##4、时间日期类型
###年:year,MySQL中用来存储年份的类型
*MySQL中使用1个字节存储年份
year能够表示的范围是1901-2155年(256年)
year的特殊值是:0000
*year允许用户使用两种方式设计(效果一样)
year
year(4)
步骤
1、确定存储的数据是年份
2、确定年份的区间在1901-2155之间
3、使用year类型
eg:记录个人的出生年份
create table t_9(
y1 year,
y2 year(4)
)charset utf8;
insert into t_9 values(1998,2100);
#Year类型允许使用2位数来插入,系统会自动匹配对应的年份,以69和70为界限
insert into t_9 values(20,80);
#Year类型的特殊值是0000,可以使用00或者0000插入
insert into t_9 values(00,0000);
###时间戳:timestamp,基于格林威治时间的时间记录
*MySQL中时间戳表现形式不是秒数,而是年月日时分秒格式
YYYY-MM-DD HH:II::SS
YYYYMMDDHHIISS
*timestamp使用4个字节存储
*timestamp的特点是所对应的记录不论哪个字段被更新,该字段都会更新到当前时间
步骤
1、确定类型需要使用年月日时分秒格式
2、确定当前字段需要记录数据的最近更新时间
3、使用timestamp时间戳
eg:记录商品库存的最后更新时间
create table t_10(
goods_name varchar(10),
goods_inventory int unsigned,
change_time timestamp
)charset utf8;
insert into t_10 values('Nokia3110',100,'2020-10-20 00:00:00');
insert into t_10 values('Nokia7100',100,'20201020000000');
timestamp会在自己所在的记录任何位置被修改时自动更新时间
update t_10 set goods_inventory = 90;
但是在MySQL8以后,取消了timestamp的默认自动更新,如果需要使用,需要额外使用属性: on update current_timestamp
alter table t_19 add c_time timestamp on update current_timestamp;
update t_19 set goods_inventory = 80;
###日期:date,用来记录年月日信息
*使用3个字节存储数据
*存储日期的格式为:YYYY-MM-DD
*存储的范围是:1001-01-01~9999-12-31
步骤
1、确定存储的数据格式为日期类格式
2、确定数据格式为YYYY-MM-DD
3、使用date类型
eg:记录个人生日
create table t_11(
name varchar(10),
birth date
)charset utf8;
insert into t_11 values('Jim','2020-10-20');
insert into t_11 values('Tom','10011212');
###日期时间:datetime,用来综合存储日期和时间
*使用8个字节存储数据
*存储格式为:YYYY-MM-DD HHIISS
*存储区间为:1000-01-01 000000 到9999-12-31 235959
步骤
1、确定要存储的时间格式包含日期
2、确定存储格式为:YYYY-MM-DD HHIISS
3、使用datetime
eg:个人具体出生时间
create table t_12(
name varchar(10),
birth datetime
)charset utf8;
insert into t_12 values('Jim','2020-10-12 12:12:12');
insert into t_12 values('Tom','10011212182323');
###时间:time,用来记录时间或者时间段
*使用3个字节存储数据
*数据范围是 -838:59:59 - 838:59:59
*数据插入的格式分为两种
时间格式:[H]HHIISS([]表示可以没有)
时间段格式:D HHIISS(D表示天)
步骤
1、确定要存储的类型是时间格式
2、确定格式类型为time能表示的格式
3、使用time存储
eg:用户登录的具体时间
create table t_13(
login_time1 int unsigned,
login_time2 datetime,
login_date date,
login_time3 time
)charset utf8;
insert into t_13 values(12345678,'2020-10-12 12:12:12','2000-12-12','12:12:12');
insert into t_13 values(1234567,'2020-10-12 12:12:12','2000-12-12','3 12:12:12');
##5、总结
1、字段类型是用来规范数据的格式的
2、MySQL中有很多类型用来规范数据格式
整数类型(常用)
常用类型: tinyint、int
小数类型(常用)
常用类型:decimal、float
字符串类型(常用)
常用类型:char、varchar、text
时间日期类型(不常用:通常使用真正时间戳存储数据,然后PHP进行灵活解读)
3、实际开发的时候,一定要仔细了解需求,根据需求判定好具体选用那种数据类型
最原始的维护能够具有最大的通用性(选中类型)
最小的消耗能够解决全部的问题(巧妙利用存储空间)