Mysql数据库设计，基础知识总结

在日常开发中，大部分时间都在用mysql数据库，零零散散，积累了一些知识点，所以今天决定梳理总结成一篇文章。

目录

1、数据库设计三大范式

2、mysql数据库的数据类型

3、mysql数据库主键、外键、索引的区别与联系

---

1、数据库设计三大范式

1、第一范式1NF（原子性、字段不可分）

定义：每个列都是不可再分割的数据项。

 

2、第二范式2NF（ 主键列与非主键列遵循完全函数依赖关系,确保表中的每列都和主键相关）

定义： 要求实体属性要完全依赖主键，不能依赖部分主键。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

这样设计，在很大程度上减小了数据库的冗余。

 

3、第三范式3NF( 非主键列之间没有传递函数依赖关系)

定义：一个表中不能包含其它表中已包含的非主键关键字信息。

不严谨的说就是这个表只包含其他表的ID。

 

一般来说，我们都会遵循第一和第二范式，但是为了性能，为了避免过多的join，有时候会违反第三范式，冗余一些字段的信息，可以理解。

2、mysql数据库的数据类型

MySQL支持多种类型，大致可以分为三类：

• 数值

• 日期/时间

• 字符串(字符)类型。

1、数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

注意：数据类的括号中的字符表示显示宽度，与该整数需要的存储空间的大小都没有关系。

int(2) 与int(11)后的括号中的字符表示显示宽度，整数列的显示宽度与 MySQL 需要用多少个字符来显示该列数值，与该整数需要的存储空间的大小都没有关系，int类型的字段能存储的数据上限依旧是2147483647(有符号型)和4294967295(无符号型)。

而chart(10)和varchar(10)的10表示存储数据的大小，即表示存储多少个字符。

 

2、日期和时间类型

类型	大小（字节）	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

datetime精确到3位数毫秒，需给出长度（3）

注意：mybatis中if判断timestamp类型时间戳 !=''会报错，因为是时间类型对比字符串类型。

from_unixtime(time_stamp) -> 将时间戳转换为日期

unix_timestamp(date) -> 将指定的日期或者日期字符串转换为时间戳

 

3、字符串类型

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

mysql数据库不提供boolean类型的数据存储，但是可以用tinyint代替，改该字段对应的javabean的那个变量定义为boolean类型即可，当存入true时，自动转换为1，false为0，取的时候也一样。

 

3、mysql数据库主键、外键、索引的区别与联系

1、主键(primary key) 能够唯一标识表中某一行的属性或属性组。一个表只能有一个主键，但可以有多个候选索引。主键常常与外键构成参照完整性约束，防止出现数据不一致。主键可以保证记录的唯一和主键域非空,数据库管理系统对于主键自动生成唯一索引，所以主键也是一个特殊的索引。

2、外键（foreign key） 是用于建立和加强两个表数据之间的链接的一列或多列。外键约束主要用来维护两个表之间数据的一致性。简言之，表的外键就是另一表的主键，外键将两表联系起来。一般情况下，要删除一张表中的主键必须首先要确保其它表中的没有相同外键（即该表中的主键没有一个外键和它相关联）。

3、索引(index) 是用来快速地寻找那些具有特定值的记录。主要是为了检索的方便，是为了加快访问速度， 按一定的规则创建的，一般起到排序作用。所谓唯一性索引，这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。

下面是索引的分类：

• 常规索引，普通索引（index或key）,它可以常规的提高查询效率。

• 主键索引（Primary Key）,简称主键。 表中唯一，提高查询效率，并提供唯一性约束。 主键的数据类型最好是数值。

• 唯一索引（Unique Key）, 可以提高查询效率，并提供唯一性约束。一张表中可以有多个唯一索引。

• 全文索引（Full Text）, 可以提高全文搜索的查询效率，一般使用Sphinx替代。但Sphinx不支持中文检索，Coreseek是支持中文的全文检索引擎，也称作具有中文分词功能的Sphinx。实际项目中，我们用到的是Coreseek。

• 外键索引（Foreign Key）,简称外键，它可以提高查询效率， 外键会自动和对应的其他表的主键关联。 外键的主要作用是保证记录的一致性和完整性。注意：只有InnoDB存储引擎的表才支持外键。外键字段如果没有指定索引名称，会自动生成。如果要删除父表（如分类表）中的记录，必须先删除子表（带外键的表，如文章表）中的相应记录，否则会出错。 创建表的时候，可以给字段设置外键，如 foreign key(cate_id) references cms_cate(id)，由于外键的效率并不是很好，因此并不推荐使用外键，但我们要使用外键的思想来保证数据的一致性和完整性。

总结：

• 主键一定是唯一性索引，唯一性索引并不一定就是主键。

• 一个表中可以有多个唯一性索引，但只能有一个主键。

• 主键列不允许空值，而唯一性索引列允许空值。

• 主键可以被其他字段作外键引用，而索引不能作为外键引用。

• 主键与外键，都默认拥有索引。