SQL 字段类型（4种）、属性（6种）总结

您所在的位置：网站首页 › 各种栏目名称 › SQL 字段类型（4种）、属性（6种）总结

SQL 字段类型（4种）、属性（6种）总结

2024-06-20 05:02| 来源: 网络整理| 查看: 265

文章目录字段类型整数类型小数类型(2种)字符串类型(5种)时间日期类型(5种) 属性 NULL属性Default属性主键自增长属性唯一键comment属性数据库记录长度

字段类型

字段类型：MySQL中用来规定实际存储的数据格式

字段类型在定义表结构时设定设定好字段类型后，插入数据时必须与字段类型对应，否则数据错误字段类型的作用就是强制规范录入的数据格式规范数据的格式保证数据的有效性 MySQL有四大数据类型整数类型：只能存储整数小数类型：可以存储有效数值字符串类型：存储字符串数据时间日期类型：存储时间日期格式数据整数类型

整数类型：有效的整数数据

MySQL中为了数据空间的有效使用，设定了五种整数类型迷你整型：tinyint，使用1个字节存储整数，最多存储256个整数（-128~127）短整型：smallint，使用2个字节存储整数中整型：mediumint，使用3个字节存储整数标准整型：int，使用4个字节存储整数大整型：bigint，使用8个字节存储数值型存储在MySQL中分为有符号（有负数）和无符号（纯正数）需要unsigned 修饰整型

示例：设计一个表记录个人信息：年龄、头发数量

# 年龄：没有负数，正常年龄也不超过200岁，迷你整型无符号即可 # 头发数量：没有负数，大概在几百万根，所以标准整型无符号即可 create table t_7( age tinyint unsigned, # unsigned修饰整数，表示无符号（从0开始） haircount int unsigned )charset utf8; 123456

显示宽度：int(L)，整数在数据库中显示的符号（数字+符号）个数

显示宽度一般是类型能表示的最大值对应的数字个数（通过desc查看表字段显示）显示宽度包含符号（如果允许为负数，-负号会增加一个宽度）显示宽度可以主动控制：创建字段时加括号确定显示宽度不会影响类型能表示的最大数值可以通过zerofill让不够宽度的数值补充到对应宽度：在字段类型后使用zerofill 小数类型(2种)

1、浮点型：float / double，存储不是特别精确的数值数据

浮点数又称之为精度数据，分为两种单精度：float，使用4个字节存储，精度范围为6-7位有效数字双精度：double，使用8个字节存储，精度范围为14-15位有效数字浮点数超过精度范围会自动进行四舍五入精度可以指定整数和小数部分默认不指定，整数部分不超过最大值，小数部分保留2位可以指定：float/double(总长度,小数部分长度) 可以使用科学计数法插入数据：AEB，A * 10 ^ B因为浮点数会自动四舍五入，所以不要使用浮点数来存储对精度要求较高的数值

示例：记录商品的价格

# 商品名字字符串 # 商品价格一般都允许带小数 create table t_11( goods_name varchar(20), goods_price float )charset utf8; insert into t_11 values('Nokia3310',199.99); insert into t_11 values('Nokia6100',1999.9999); 12345678

2、定点型：decimal，能够保证精度的小数

定点数的存储模式不是固定长度，所以数据越大占用的存储空间越长每9个数字使用4个字节存储定点型可以指定整数部分长度和小数部分长度默认不指定，10位有效整数，0位小数可以指定：decimal(有效数位,小数部分数位)有效数位不超过65个数据规范整数部分超出报错小数部分超出四舍五入

示例：记录个人资产情况：资产和负债

# 资产和负债应该都是精确的，小数部分可以到分 create table t_12( money decimal(14,2), bet decimal(10,2) )charset utf8;

insert into t_12 values(1111111111.12,1111111.999); insert into t_12 values(1111111111.12,99999999.999); # 错误：进位导致正数部分超过指定范围

12345678 字符串类型(5种)

1、定长型：char(L)，指定固定长度的存储空间存储字符串

定长是指定存储长度定长的长度是字符而不是字节 L的最大值是255实际存储空间：L字符数 * 字符集对应字节数定长里存储的数据不能超过指定长度，但是可以小于指定长度字符串数据使用单引号或者双引号包裹定长的访问效率较高，但是空间利用率较低

示例：记录个人信息：身份证信息和手机号码

# 身份证为固定长度18位（数字） # 手机号码是11位固定长度（数字） create table t_13( id_number char(18), phone_number char(11) )charset utf8; insert into t_13 values('440111999912120304','13512345678'); 1234567

2、变长型：varchar(L)，根据实际存储的数据变化存储空间

变长型的存储空间是由实际存储数据决定的变长型的L也是指字符而不是字节 L指定的是最大存储的数据长度L最大值理论是65535变长需要额外产生1-2个字节，用来记录实际数据的长度数据长度小于256个，多1个字节数据长度大于256个，多2个字节实际存储空间：实际字符数 * 字符集对应字节数 + 记录长度变长数据不能超过定义的最大长度变长字符串在读取时需要进行长度计算，所以效率没有定长字符串高变长字符串能够更好的利用存储空间

示例：记录个人信息：用户名、密码、姓名、身份证

# 用户名不确定长度，最长不超过50个字符 # 密码不确定长度，最长超过15个字符 # 姓名不确定长度，最长不超过10个字符 # 身份证固定长度，18个字符 create table t_14( username varchar(50), password varchar(15), name varchar(10), id_number char(18) )charset utf8;

insert into t_14 values(‘username’,‘password’,‘name’,‘444111999912121111’);

123456789101112

3、文本字符串：text/blob，专门用来存储较长的文本

文本字符串通常在超过255个字符时使用文本字符串包含两大类 text：普通字符 tinytext：迷你文本，不超过2 ^ 8 -1个字符text：普通文本，不超过 2 ^ 16 - 1个字符mediumtext：中型文本，不超过 2 ^ 24 - 1 个字符longtext：长文本，不超过 2 ^ 32 - 1 个字符（4G） blob：二进制字符（与text类似） tinyblobblobmediumbloblongblob 文本字符串会自动根据文本长度选择适合的具体类型一般在文本超过255个字符时，都会使用text（blob现在极少使用）

示例：记录新闻信息：标题、作者和内容

# 标题一般不会超过50个字符，varchar # 作者一般不会超过10个字符：varchar # 内容通常都很长，使用text create table t_15( author varchar(10), title varchar(50), content text )charset utf8;

insert into t_15 values(‘佚名’,‘给联合国的一封信’,‘给联合国的一封信…’);

12345678910

4、枚举型：enum, 一种映射存储方式，以较小的空间存储较多的数据

枚举是在定义时确定可能出现的可能，而后数据只能出现定义时其中的一种的数据类型枚举类似一种单选框枚举使用1-2个字节存储，最多可以设计65535个选项枚举实际存储是使用数值，映射对应的元素数据，从1开始枚举语法：enum(元素1,元素2,...元素N)使用枚举的作用：规范数据模型优化存储空间

示例：记录人群类型：小朋友、少年、青年、中年、老年，每个人实际只属于一种类别

# 要保证未来数据只能出现在某种可能中，所以要先列出来，可以使用enum create table t_16( type enum('小朋友','少年','青年','中年','老年') )charset utf8;

insert into t_16 values(‘少年’); insert into t_16 values(‘仙人’); # 不存在的数据不能插入

1234567

枚举定义原理：

枚举数据映射值数据11数据22……数据NN（小于65535）

数据存储（读取反过来）：

:root { --mermaid-font-family: “trebuchet ms”, verdana, arial;}

指令开始插入数据读取映射关系元素==数值数值==数值数值存储到字段结束

5、集合型：set，一种映射存储方式，以较小的空间存储较多的数据

集合是在定义时确定可能出现的元素进行穷举，而后数据只能出现定义时其中的元素（可以是多个）集合类似一种多选框集合使用1-8个字节存储数据，最多可以设计64个元素集合实际存储是使用数值（二进制位），映射对应的元素数据，每个元素对应一个比特位数据存在：对应位为 1数据不存在：对应位为 0 集合语法：set(元素1,元素2,...元素N)使用集合的作用：规范数据模型优化存储空间

示例：记录个人的球类爱好，有篮球、足球、羽毛球、网球、乒乓球、排球、台球、冰球

# 爱好可以是多种，并非固定的，但是只能从规定的类型中选择 create table t_17( hobby set('足球','篮球','羽毛球','网球','乒乓球','排球','台球','冰球') )charset utf8;

insert into t_17 values(‘足球’); insert into t_17 values(‘冰球,台球,篮球’);

1234567

集合定义原理：

集合数据映射位数据100000001数据200000010……数据810000000

数据存储（读取反过来）：

:root { --mermaid-font-family: “trebuchet ms”, verdana, arial;}

指令开始插入数据读取映射关系元素选中==位值为1 元素未选中==位值为0 转化成十进制存储结束时间日期类型(5种)

1、年：year，MySQL中用来存储年份的类型

MySQL中使用1个字节存储年份year能够表示的范围是1901-2155年（256年） year的特殊值是：0000 year允许用户使用两种方式设计（效果一样） yearyear(4) 因为year字段表示的范围有限，所以通常会使用字符串来存储（牺牲空间换安全）

示例：记录个人的出生年份

create table t_18( y1 year, y2 year(4) )charset utf8; insert into t_18 values(1901,2155); 12345

2、时间戳：timestamp，基于格林威治时间的时间记录

MySQL中时间戳表现形式不是秒数，而是年月日时分秒格式 YYYY-MM-DD HH:II::SSYYYYMMDDHHIISS timestamp使用4个字节存储表示范围是 1971年1月1日0时0分0秒-2155年12月31日23是59分59秒timestamp可以使用 0000-00-00 00:00:00 所对应的记录不论哪个字段被更新，该字段都会更新到当前时间但在MySQL8中需要主动使用on update current_timestamp才会自动更新

示例：记录商品库存的最后更新时间

create table t_19( goods_name varchar(10), goods_inventory int unsigned, change_time timestamp )charset utf8;

insert into t_19 values(‘Nokia3110’,100,‘1971-01-01 00:00:00’); insert into t_19 values(‘Nokia7100’,100,‘19710101000000’);

12345678

注意：在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;

123

3、日期：date，用来记录年月日信息

使用3个字节存储数据存储日期的格式为：YYYY-MM-DD存储的范围跨度很大，存储区间是1000 - 9999年：1001-01-01~9999-12-31

示例：记录个人生日

create table t_20( name varchar(10), birth date )charset utf8;

insert into t_20 values(‘Jim’,‘2000-12-12’); insert into t_20 values(‘Tom’,‘10011212’);

1234567

4、日期时间：datetime，用来综合存储日期和时间

使用8个字节存储数据存储格式为：YYYY-MM-DD HH:II:SS存储区间为：1000-01-01 00:00:00 到9999-12-31 23:59:59

示例：记录个人具体的出生时间

create table t_21( name varchar(10), birth datetime )charset utf8;

insert into t_21 values(‘Jim’,‘2000-12-12 12:12:12’); insert into t_21 values(‘Tom’,‘10011212182323’);

1234567

5、时间：time，用来记录时间或者时间段

使用3个字节存储数据数据范围是 -838:59:59 - 838:59:59数据插入的格式分为两种时间格式：[H]HH:II:SS（[ ]表示可以没有）时间段格式：D HH:II:SS（D表示天）time类型通常被用来做时间段计算：如多少天后的什么时间点（可以理解为过期检查）

示例：记录用户登录的具体时间

# 具体登录时间可以使用时间戳（包含年月日时分秒信息） # 也可以时间datetime格式，或者date+time双字段格式（具体后面学习范式时会知道该怎么用） create table t_22( login_time1 int unsigned, login_time2 datetime, login_date date, login_time3 time )charset utf8;

insert into t_22 values(12345678,‘2000-12-12 12:12:12’,‘2000-12-12’,‘12:12:12’); insert into t_22 values(1234567,‘2000-12-12 12:12:12’,‘2000-12-12’,‘3 12:12:12’);

1234567891011 属性

属性：建立在字段类型之后，对字段除类型之外的其他约束

属性是在定义表字段的时候针对每个字段进行属性设定，是MySQL用来增加字段规范和约束的设定好的属性可以通过查看表字段desc进行查看数据在进行增删改（写）操作时需要在满足字段类型和属性的要求用好属性能够提升数据的有效性，方便未来进行数据操作和数据分析（数据真实性和有效性）

查看表属性：desc 表名;

desc t_1; # Field：字段名字 # Type：数据类型 # Null：是否为空（属性） # Key：索引类型（属性） # Default：默认值（属性） # Extra：额外属性 1234567 NULL属性

NULL：数据是否允许为空

Null/Not Null属性是用来限定数据是否为Null值的

默认情况下数据是允许为Null的，不为空设计：Not Null

一般有效的数据都必须设定为Not Null来保证数据的有效性，数据为空一般不具备运算和分析价值

示例：用户信息表：用户名、密码、姓名、年龄、注册时间

create table t_23( username varchar(50) not null, password char(32) not null, name varchar(20), age tinyint unsigned, reg_time int unsigned not null )charset utf8; 1234567 Default属性

默认值：default，在设计表字段的时候给定默认数据，在后续字段操作（数据新增）的时候系统没有检测到字段有数据的时候自动使用的值

默认值在字段设置的时候使用（默认值需要满足数据类型规范）默认值通常设计的是字段容易出现的数据一般字段的默认值默认是Null 默认值触发在系统进行数据插入时自动检测触发主动使用default关键字触发默认值

示例：用户开户：银行卡账号、身份证号码、姓名、账户余额

create table t_24( account varchar(19) not null, id_card char(18) not null, name varchar(20) not null, money decimal(16,2) default 0.00 not null )charset utf8; 123456 主键

主键：primary key，用来保证整张表中对应的字段永远不会出现重复数据（唯一性）

主键在一张表中只能有一个主键的另外一个特性是能够提升主键字段作为查询条件的效率（索引）主键不能为空：Not Null（默认）逻辑主键：数据没有具体业务意义，纯粹是一种数值数据逻辑主键通常是整数：int逻辑主键目的是方便检索和数据安全（不暴露数据真实信息）复合主键：多个字段共同组成不能重复的数据 primary key(字段1,字段2,…字段N)联合主键使用不多，一般也不会超过2个字段

示例：银行账户信息：账户、姓名、余额

# 银行账户具有唯一性，不能重复，也不允许为空 create table t_25( account varchar(17) primary key, name varchar(20) not null, money decimal(16,2) not null default 0.00 )charset utf8;

# 复合主键 create table t_26( account varchar(17), name varchar(20), money decimal(16,2) not null default 0.00, primary key(account,name) )charset utf8;

# 一般使用逻辑主键 create table t_27( id int unsigned primary key, account varchar(17) not null, name varchar(20) not null, money decimal(16,2) not null default 0.00 )charset utf8;

12345678910111213141516171819202122

主键管理：在创建表并且已经有数据后的维护

删除主键追加主键修改主键（先删除后新增）

1、删除主键：主键只有一个，所以删除语法也比较特殊

alter table t_26 drop primary key; 1

2、后期新增主键：如果是针对业务主键需要保证字段数据没有Null数据且没有数据重复（一般主键都会在表创建时维护好）

alter table t_26 add primary key(account,name); 1 自增长属性

自增长：auto_increment，被修饰的字段在新增时，自动增长数据

自增长只能是整数类型，而且对应的字段必须是一个索引（通常逻辑主键）一张表只能有一个自动增长自增长一般是配合逻辑主键实现自动增长整型字段存在索引（主键）自增长数据可以理解为一种默认值，如果主动给值，那么自动增长不会触发自增长由两个变量控制初始值：auto_increment_offset，默认是1步长：auto_increment_increment，默认值也是1查看自增长控制：show variables like 'auto_increment%';

示例：记录学生信息：学号和姓名

# 学生信息：学号自动增长 create table t_28( id int primary key auto_increment, stu_no int(8) zerofill not null, stu_name varchar(20) not null )charset utf8; 123456

自增长管理：在某些特殊使用下，需要自增长按照需求实现

修改表中自增长的值：让下次自增长按照指定值开始修改自增长控制：调整自增长的变化

1、修改表中自增长的值：跳过一些值，直接从下次开始按照新的目标值出现

alter table t_28 auto_increment = 50; 1

注意：奇数会保留原值，偶数会自动加1（可能出现的情况）

2、修改自增长控制：步长和起始值（修改针对的是整个数据库，而非单张表）

set auto_increment_increment = 2; # 当前用户当前连接有效（局部） set @auto_increment_increment = 2; # 所有用户一直有效（全局） 12 唯一键

唯一键：unique key，用来维护数据的唯一性

一个表中可以有多个唯一键唯一键与主键的区别在于唯一键允许数据为Null（而且Null的数量不限）唯一键与主键一样，可以提升字段数据当做条件查询的效率（索引）复合唯一键：多个字段共同组成 unique key(字段1,字段2,…字段N)一般不会出现，最多2个字段组成

示例：学生成绩表：一个学生只能有一个学科成绩，但是可以有多个学科

# 学号和学科编号共同组成唯一 create table t_30( id int primary key auto_increment, stu_name varchar(20) not null, course varchar(20) not null, score decimal(5,2), unique key(stu_name,course) )charset utf8;

insert into t_30 values(null,‘Jim’,‘Math’,50); insert into t_30 values(null,‘Jim’,‘English’,80);

1234567891011

唯一键管理：在表创建后对唯一键的管理

删除唯一键相对麻烦，一张表中不止一个唯一键

新增唯一键要保证字段里的数据具有唯一性

删除唯一键：alter table 表名 drop index 唯一键名字；

新增唯一键： alter table 表名 add unique key(字段列表)；

1、删除表中已有的唯一键

alter table t_30 drop index stu_name; 1

2、追加唯一键

alter table t_30 add unique key stu_course (stu_name,course); 1 comment属性

描述：comment，是用文字描述字段的作用的

comment代表的内容是对字段的描述方便以后自己了解字段的作用方便团队了解字段的作用描述如果涉及到字符集（中文）一定要在创建表之前设置好客户端字符集（否则会出现描述乱码）

示例：学生成绩表

# 学生成绩表中通常是存储学生学号 # 学科通常也是学科代码 create table t_31( id int primary key auto_increment, stu_no varchar(10) not null comment '学号', course_no varchar(10) not null comment '课程号', score decimal(5,2) comment '考试成绩', unique key stu_course (stu_no,course_no) comment '学号和课程号组成唯一键' )charset utf8; 123456789 数据库记录长度

数据库记录长度：MySQL中规定一条记录所占用的存储长度最长不超过65535个字节

记录长度为表中所有字段预计占用的长度之和所有字段只有允许Null存在，系统就会预留一个字节存储Null（多个Null也只要一个就好）因为MySQL记录长度的存在，varchar永远达不到理论长度 GBK存储：65535（字符） * 2 + 2 = 131072（字节）UTF8存储：65535（字符） * 3 + 2 = 196607（字节）一般数据长度超过255个字符都会使用 text/blob 进行存储（数据存储不占用记录长度）

1、GBK表能存储的最大varchar字符串长度

create table t_32( content varchar(65535) )charset gbk; # 错误

create table t_32( content varchar(32767) )charset gbk; # 错误

create table t_32( content varchar(32766) )charset gbk;

1234567891011

2、UTF8表能存储的最大varchar字符串长度

create table t_33( content varchar(65535) )charset utf8; # 错误

create table t_33( content varchar(21844) )charset utf8;

1234567

3、Null也要占用一个字节

create table t_34( id tinyint, content varchar(21844) )charset utf8; # 错误

create table t_34( id tinyint not null, content varchar(21844) not null )charset utf8;

123456789

【本文地址】

SQL 字段类型（4种）、属性（6种）总结

SQL 字段类型（4种）、属性（6种）总结

今日新闻

推荐新闻