MySQL字段类型和列属性详解

字段类型

字段类型又称之为列类型和数据类型.

Mysql中SQL并不是一种完全的强类型语言: 但是在某些时候(表中维护字段数据的时候)可以理解为是强类型语言: 不是同类型的数据不能存入.

Mysql字段类型分为三大类:

• 数值型
• 字符串型
• 时间日期型

一、数值型

只能存放数值数据，在计算机中,会严格区分整数和小数: 数值型分为两种: 整型和小数型

1. 整型

用来存储整数(指定范围内的整数)

SQL中根据整数的具体应用分为了5类整数

• tinyint: 迷你整型,使用1个字节存储, 最多能存储256个数据
• smallint: 小整型,使用2个字节存储, 最多存储65536个数据
• mediumint: 中整型,使用3个字节存储
• int: 标准整型,使用4个字节进行存储
• bigint: 大整型, 使用8个字节进行存储

为什么要有如此多的整型呢?

根本原因: 关系型数据使用二维表存储数据, 如果某个字段没有值, 系统依然需要分配磁盘空间(关系型数据库比较浪费空间)
因为自然界的真实数据中,有很多是在某个指定的区间内部的: 如人的年龄一个字节够存; 中国的人数使用标准整型即可; 全球人数使用大整型.

插入数据: 数据不能达到预期的效果: tinyint: 0-255

在SQL中: 所有的数值类型都是有符号类型: 有正负

有的数据不需要负数: 无符号: 在数据类型之后使用unsigned

数据插入: 只能放正数,不能放负数(0-255)

每一种正数的数据类型之后都有一个(), 里面有一个数字: 数据长度: 默认的如果数据没有达到指定的长度的时候,系统会想办法变成对应的长度的数据, 但是又不改变数据的大小: 加前导0: zerofill: 显示长度不限定数据本身的大小(如果数据超过显示长度不管)

zerofill只能针对无符号整型(正数): 不能针对负数

如何选择整型数据类型?

根据需求: 具体的业务对应的数据的边界值是多少.

2. 小数型

小数型: 带小数点, 小数型包含两种: 浮点型和定点型

浮点型

浮点型数据又称之为精度数据: 只能保证一定的数据精度(有效数字位数), 精度之外会丢失, 但是能够表示很大的数据.

浮点型分为两种: 单精度和双精度

• 单精度: float, 使用4个字节进行存储,有效数字大概为7位左右
• 双精度: double,使用8个字节存储,有效数字大概为15位左右

创建浮点数表: 精度是可以指定
float: 表示浮点数, 没有小数的浮点数
float(M,D): 表示总长度为M(位数), D表示小数部分的长度, 整数部分的长度(M-D)

插入数据: 只要在范围之内都可以

如果浮点数限定长度: 整数部分绝对不能超出长度,但是小数部分可以: 小数会自动进行四舍五入

注意: 如果是系统自动进位导致整数部分超出长度: 系统允许

浮点数支持使用科学计数法插入数据

浮点型也是默认的有符号类型.

定点型

定点型: 小数点是固定, 可以保证精度不丢失(整数部分)
Decimal(M,D): M表示总长度,D表示小数长度: 整数部分永远不会丢失精度, 但是小数部分有可能

创建表

插入数据

定点数的小数部分, 允许超出长度: 自动进行四舍五入

整数部分不允许超出长度: 如果是系统进位导致整数部分超出长度: 那么也不允许

定点数的使用: 需要精度,但是又不确定长度的数据: 如银行存款, 价格(跟钱有关)

二、时间日期型

存储时间日期信息: mysql提供了很多存储时间日期的数据类型,而且很强大.

• datetime: 时间日期
• timestamp: 时间戳, 表示从1970年格林威治时间开始,使用的格式不是真实时间戳,而是与datetime格式一致
• date: 天,就是datetime的date部分
• time: 既可以表示时间, 又可以表示时间段, 过去的某段时间或者将来的某段时间
• year: 年,分为两种格式: year(4)标准4位年, 1901-2155, year(2)表示从1970-2069

插入数据

time表示是时间段: 所以可以使用时间段数据插入

year字段会自动根据数据进行处理(2位年处理)

从PHP角度出发: 时间日期的格式具有非常大的灵活性(date函数可以任意转换), 但是mysql中的时间日期几乎都是固定: 如果从PHP角度出发, 很少使用mysql提供的时间日期类型, 几乎都是使用时间戳(整型)

三、字符串类型

保存字符串数据

Mysql中提供了6中字符串数据类型: char,varchar,text,blob,enum,set

• char: 定长字符
• varchar: 变长字符
• text: 文本字符
• blob: 二进制文本字符
• enum: 枚举字符
• set: 集合字符

1. 定长字符串char

char(M): 会给数据分配固定的存储空间, M代表字符数, M不能超过255

2. 变长字符串varchar

varchar(L): 给数据分配的存储空间不固定, 根据数据本身的长度来定: L表示字符数, L的理论值可以达到65536个. 但是一般认为超过255就不再使用varchar(使用text代替).
变长的varchar一定会产生一个或者两个多余的字节来保存数据原始长度

Char与varchar的存储对比(字符集为utf8: 一个字符 = 3个字节)

存储数据	char(4)	varchar(4)	char所占用空间(字节)	varchar所占用空间(字节)
Abc	Abc	Abc	4字符 = 3 * 4 = 12字节	3字符 = 3 * 3 = 9字节(1) = 10
Abcd	Abcd	Abcd	4字符 = 3 * 4 = 12字节	4字符 = 4 * 3 = 12字节(1) = 13

定长(char)与变长(varchar)区别

1、char所占用的空间一定是固定,一开始在分配的时候就固定, 不需要计算
2、varchar所占用的空间不固定, 是在数据插入的时候才会固定: 需要计算
3、Char的效率要高
数据存储时不需要计算, varchar要计算
数据读取时不需要考虑长度问题(读取全部), varchar也要计算
4、varchar的空间使用效率更高: 不会浪费空间

如何选择char和varchar?

1、如果数据长度是固定,那么一定选char: 效率问题, 如:身份证(18位), 电话号码(11位), 银行卡号等
2、如果数据长度变化比较大, 使用varchar: 节省空间, 如:姓名, 家庭地址，如果数据超过255个字符: 都不会使用char或者varchar: 使用text

3 文本类型text

文本类型分为两种: 字符型和二进制型

字符型字符串: text
二进制型字符串: blob

文本类型都是用来存储较大的数据: 用户都只需指定一个类型即可: 系统会自动根据数据的长度,选择合适的文本类型.

实际使用中: 通常不会使用blob类型: 二进制数据通常不会存储到数据库(如图片)

4. 枚举类型enum

枚举: 事先将可能出现的数据定义好: 以后存放的数据必须是指定的数据里面某一个数据.

枚举: enum(数据1,数据2,数据3...);

插入数据: 每个记录对应的枚举字段只能是其中定义好的某个值

枚举作用1: 数据规范, 规定之外的数据是不能存放到枚举字段中.
枚举作用2: 节省空间: 枚举本身存储并不是真正的字符串,而是数字

Mysql中允许自动转换: 碰到相关符号的时候,数据会自动转换
只要数据是有返回值的表达式, 那么都可以使用select
‘a’ + 1 = 0 + 1 = 1;

证明: 枚举字段中存储的实际内容不是字符串,是数字: 将字段查出来之后 + 0操作(系统会自动转换)

数字占用的空间比字符要少

枚举的原理: 在定义的时候,会将枚举对应的字符串进行数字编号: 从左向右,依次从1开始(类似php是一个索引数组). 然后在日志表中维护一个数字与字符串对应关系的表(类似数组)。

• 当用户从数据库查询数据的时候： 系统先将值取出（数字），然后进入到日志中进行转换，变成对应的字符串
• 当用户向数据表插入数据的时候：系统会经过日志文件，找到字符串对应的数字索引，然后再插入到数据表对应的字段。

因为枚举字段中存储的数值，而不是字符串： 所以可以直接向表中插入数字数据

5. 集合类型set

集合类型是一种多选： 最原始最优解决的是复选框的问题。

集合：set
set(元素1,元素2元素3...);

数据存放: 集合是多选数据,可以一个字段选择多个元素: 元素之间使用逗号”,”分隔
数据元素的顺序没有关系

集合作用1: 规范数据, 只有事先定义的元素才能存放进去.
集合作用2: 节省空间: 内部存储的又是数字而不是对应的字符串

实时: 数据存储是按照二进制位存储,而不是十进制数值: 原理图

集合的范围: 最多有64个元素

从PHP的管理角度出发: 集合或者枚举都会存在数据操作的不确定性(数值操作), 不利于PHP对数据进行管理: PHP做网站的过程中, 很少使用这两种类型.

列属性

列属性: 在数据类型之外用来限制数据的一些额外的内容.

Mysql中的列属性有: comment(注释),NULL/NOT NULL(数据是否允许为空),Default(默认值), Primary key(主键), Unique key(唯一键), Auto_increment(自增长)

所有的属性描述都是在字段之后,可以有多个属性.

一、描述

描述: comment,是一种用来描述定义规则的, 给人看的

建议: 以后创建表的时候, 只要是非大众(id)就使用comment进行描述.

二、空

空: NULL/NOT NULL, 主要是用来限制数据(字段)是否允许为空.

几乎所有的字段定义之初, 默认都是允许为空的: 数据是业务产生, 如果数据为空通常没有任何意义. 在以后的设计表中尽量的让数据字段不允许为空.

NULL与NOT NULL能够限制数据

建议: 最开始尽量使用不能为空NOT NULL

三、默认值

默认值: default, 当某个字段没有进行字段数据插入的时候, 系统自动的选择一个一开始定义好的数据作为初始值(大部分的默认值都是NULL)

如何让默认值生效呢?

方案1: 在插入数据的时候排除当前有默认值的字段

方案2:在想使用默认值的字段值位置,使用default关键字

四、主键

主键: primary key, 主要的键(索引), 用来唯一的标志一条记录 ,不允许出现重复.

主键是一类非常特殊的索引: mysql提供了最高规格的”待遇”, 一张表只能有一个主键

1. 增加主键

主键增加有三种方式

方案1: 直接在字段之后,使用primary key,让字段变成主键

方案2: 可以在所有字段之后,指定主键

primary key(字段列表);

 

方案3: 在表创建好之后,再增加主键

alter table 表名 add primary key(主键列表);

2. 主键作用

主键: 保证字段具有唯一性,不能为空

主键具有唯一性: 唯一约束

主键不能为空

复合主键: 必须多个字段都相同才重复

3. 删除主键

主键不能被修改,只能先删除,在增加.

alter table 表名 drop primary key;

如果是在表后期增加主键: 注意前提是保证里面对应主键的字段的数据必须没有重复的.

主键分为两种称呼: 业务主键和逻辑主键

• 业务主键: 主键字段对应的数据是有业务含义的(学号)
• 逻辑主键: 主键字段没有业务意义(id): 通常使用的是逻辑主键, 逻辑主键使用的字段类型通常是整型(int: 方便自增长)

五、自增长

自增长: auto_increment, 指某个字段的数据在没有或者给NULL的时候, 会自动的从以后的内容的最大值自动+1变成新值: 自增长字段对应的数据类型必须是整型.

自增长字段: 字段本身必须是一个索引(保证效率): 通常自增长搭配逻辑主键

插入数据: 自增长数据如果用户给定数据, 系统自增长不会起作用

自增长是从最大值+1: 自增长其实在数据插入之前就已经生成好: show create table 表名;

如果使用自动增长 : 下一个的值是11

自增长会上升到表选项: 一张表只能有一个自增长字段: 可以通过修改表选项来实现自增长值的修改: 只能比当前已有数据大不能小(小不会生效)

删除自增长: 自增长是字段的属性: modify,在修改字段的时候,不再保留auto_increment属性

自增长的初始值为什么是1? 自增长的步长为什么是1? 系统内部有变量在进行控制
auto_increment_offset = 1; -- 初始值
auto_increment_increment = 1; -- 每次增加1
show variables like ‘auto_increment%’;

可以修改自增长的控制: 修改都是会话级别(当前客户端当次连接有效)
set auto_increment_increment = 5;

验证自增长

六、唯一键

唯一键: unique key 与主键相似: 用来保证数据的唯一性.
唯一键: 允许为空(不统计为空的数据), 唯一键可以在一张表中有多个

1. 增加唯一键

与主键的增加几乎完全一致: 三种方式

方案1: 在字段之后直接增加唯一键: unique[key];

方案2: 在所有字段之后增加唯一键

nique key(字段列表); -- 可以有复合唯一键

方案3: 在表创建结束之后增加唯一键

alter table 表名 add unique key(字段列表);

2. 唯一键作用

查看唯一键效果: 数据必须唯一

唯一键允许字段为空: 只要是为空的字段,唯一键都不进行比较.

3. 删除唯一键

唯一键不可以被修改,只能被删除,后增加.
alter table 表名 drop unique key; -- 语法错误: 不存在

唯一键在系统中没有逐渐的地位高: 没有特殊”待遇”, 唯一键被当成一个普通索引(index)
alter table 表名 drop index 索引名字;