内容

数值类型
字符串类型
日期和时间类型
enum和set

设计数据类型的重要性

业务层是工作在内存上的，速度很快。

而最先达到性能瓶颈的一般都是数据库，因为数据库涉及到磁盘IO。磁盘IO是很慢的。

所以我们定义数据表时，每一个字段的数据类型都要好好去斟酌。

MySQL server虽然工作在内存上，但是它要读取数据、索引时，就要进行磁盘IO。磁盘IO次数越少，则性能越高，所以我们要尽力减少磁盘IO次数。

MySQL数据类型定义了数据的大小范围，因此使用时选择合适的类型，不仅会降低表占用的磁盘空间，还能间接减少磁盘IO的次数，提高了表的访问效率；不仅如此，索引的效率也和数据类型息息相关。

数值类型

整数类型	字节	最小值	最大值
`TINYINT`	1	有符号`-128`；无符号0	有符号127；无符号255
`SMALLINT`	2	有符号`-32768`；无符号0	有符号32767；无符号65535
`MEDIUMINT`	3	有符号`-8388608`；无符号0	有符号8388607；无符号1677215
`INT`、`INTEGER`	4	有符号`-2147483648`；无符号0	有符号2147483647；无符号4294967295
`BIGINT`	8	有符号`-9223372036854775808`；无符号0	有符号9223372036854775807；无符号18446744073709551615

浮点数类型	字节	最小值	最大值
`FLOAT`	4	$\pm 1.175494351E-38$	$\pm 3.402823466E+38$
`DOUBLE`	8	$\pm 2.2250738585072014E-308$	$\pm 1.7976931348623517E+308$

其中浮点类型推荐使用decimal类型（保存为字符串格式）。数据精度范围很大，约28位，而且计算时，越界、溢出会报错，而普通整数、浮点只是截断。

注意
- age INT(9)这不是意味着定义了一个长度为9字节的整型。数值类型占用内存的大小是固定的，和具体的类型是强相关的，括号内的数目只是代表它显示的宽度。

示例

1	create table user(age TINYINT unsigned NOT NULL default 0)

字符串类型

字符串类型	字节	描述及存储需求
`CHAR(M)`	M	M为`0~255`之间的整数。占用空间M字节。
`VARCHAR(M)`		M为`0~65535`之间的整数，值的长度+1个字节
`TINYBLOB`		允许长度`0~255`字节，值的长度+1字节
`BLOB`		允许长度`0~65535`字节，值的长度+2字节
`MEDIUMBLOB`		允许长度`0~167772150`字节，值的长度+3字节
`LONGBLOB`		允许长度`0~4294967295`字节，值的长度+4字节
`TINYTEXT`		允许长度`0~255`字节，值的长度+2字节
`TEXT`		允许长度`0~65535`字节，值的长度+2字节
`MEDIUMTEXT`		允许长度`0~167772150`字节，值的长度+3字节
`LONGTEXT`		允许长度`0~4294967295`字节，值的长度+4字节
`VARBINARY(M)`		允许长度`0~M`个字节的变长字节字符串，值的长度+1字节
`BINARY(M)`	M	允许长度`0~M`个字节的定长字节字符串

CHAR(12)和VARCHAR(12)的区别在于，如果实际的字符串长度不够12，VARCHAR大小则为实际的长度，而CHAR的大小则依旧是12字节；共同点：如果数据超过12则都会产生截断。
BLOB一般用于存储二进制格式的图片、音频。
TEXT的应用：留言板，商品的备注说明，聊天信息的存储。一些大文本。

初期设计时，可能低估了将来的数据量，可能会超过设计的范围。

数据库中字符串以单引号包裹。

SQL注入式攻击——字符串拼接导致的问题。

日期和时间类型

日期和时间类型	字节	最小值	最大值
`DATE`	4	`1000-01-01`	`9999-12-31`
`DATETIME`	8	`1000-01-01 00:00:00`	`9999-12-31 23:59:59`
`TIMESTAMP`	4	`19700101080001`	2038年的某个时刻
`TIME`	3	`-838:59:59`	`838:59:59`
`YEAR`	1	`1901`	`2155`

日期类型是做项目过程中经常使用的类型信息，尤其是TIMESTAMP和DATETIME两个类型；
注意TIMESTAMP会自动更新时间，非常适合需要记录最新更新时间的场景，对应的函数是unix_timestamp(now())；而DATETIME需要手动更新。
这里虽然介绍了MySQL的时间类型，但是现代的后端开发，MySQL基本已经很少使用自身的存储过程、存储函数、触发器、外键设置了，尽量把复杂的业务在业务层处理。涉及的时间操作也是同理。应该让MySQL聚焦在核心的增删改查操作上。

时间戳示例

1 2	select now(); # 2022-04-28 19:30:11 select unix_timestamp(now()); # 1617683855

unix_timestamp(now());用于生成UNIX的时间戳，数值含义是自1970年起的秒数。用整型存储。

枚举和集合（enum和set）

这两种类型都是限制该字段只能取预定义的固定的某些值。比如性别——只能男或女。
枚举字段只能取一个唯一的值。
集合字段可以取任意个值。

示例

1	sex enum('M','W') default 'M'

内容

给你一个场景，让你设计表。
关系型数据库的范式设计。
一对一/一对多实体关系的表设计原则
多对多实体关系的表设计原则
总结 - 表设计三要素

表间的关系

现实中，表与表之间的关系无非就三种：一对一、一对多、多对多。

如何设计表之间的关联关系？

一对一

举个例子：用户User和身份信息Info的关系，一个用户对应一个身份信息。两张表是一对一的关系。

用户User

uid(主键、自增)	name	age	sex
1000	zhang	20	M
1020	liu	21	W
2010	wang	22	M

身份信息Info

cardid	addrinfo
112233	china
334455	japan
556677	canada

目前来看，身份信息表和用户表完全对应不上。

需要在身份信息表加一个关联父表主键的字段uid，才能和用户表对应上。

uid	cardid	addrinfo
1000	112233	china
1020	334455	japan
2010	556677	canada

现在，两个表之间的关联就是靠Info表的uid建立起来的，即Info表的uid字段关联了父表的主键，Info表的uid在逻辑上实际上就是外键，Info叫做子表，User叫做父表。子表的外键和父表的主键的数据类型必须一致，字段名无所谓。

mysql的性能瓶颈主要集中在磁盘IO，出于对性能的优化考虑，我们现在不会在sql层明确指出这是一个外键，不会把外键的约束工作分配给mysql。而是把外键的处理我们放到应用层代码。以让sql处理更核心的业务。

使表之间能够一对一关联起来的做法：在子表中增加一列，与父表的主键关联。

1	select * from info where uid=2010; #如此就相当于间接查到“王”的身份信息

一对多/多对多

举个例子：电商平台。有三个实体：用户User、商品Product、订单Order。

分析

用户 - 商品：在用户购买商品之前，它们之间没有直接的关联关系
用户 - 订单：一对多的关系
商品 - 订单：多对多的关系

表设计

用户User表

uid	name	age	sex
1000	zhang	20	M
1020	liu	21	W
2010	wang	22	M

商品Product表

pid	pname	price	amount
1	phone	600.0	100
2	laptop	2000.0	50
3	battery	10.0	200

订单Order表

orderid	pid	number	money	totalprice	addrinfo
O1000	1	1	600.0	4640.0	西安工业大学
O1000	2	2	4000.0	4640.0	西安工业大学
O1000	3	4	40.0	4640.0	西安工业大学
O2000	2	1	2000.0	2000.0	陕西科技大学

用户和订单 - 一对多

订单中没有用户的信息，对应不上是哪个用户的订单。

则需要给订单加一个用户id字段，需与主表的主键关联，类型必须一致。即可与用户对应。

orderid	uid	pid	number	money	totalprice	addrinfo
O1000	1000	1	1	600.0	4640.0	西安工业大学
O1000	1000	2	2	4000.0	4640.0	西安工业大学
O1000	1000	3	4	40.0	4640.0	西安工业大学
O2000	2010	2	1	2000.0	2000.0	陕西科技大学

商品和订单 - 多对多

目前的订单表的设计是有很多问题的，信息冗余过多。如果某用户在同一订单中购买了多种商品，则orderid、uid、totalprice和addrinfo都是一致的，为每一种商品都单列一条记录，冗余过多。

冗余带来的问题有很多，其中最大的问题是，如果用户对订单进行增删改查，则有可能影响整个有关此订单的所有记录，修改代价高。

如果订单(Order)表像下面这样，就很简洁了。这就不存在冗余了。如果需要更改订单，不用删除多行记录，只需修改一次。

orderid	uid	totalprice	addrinfo
O1000	1000	4640.0	西安工业大学
O2000	2010	2000.0	陕西科技大学

但是还要知道订单的内容，需要一个中间表 - 订单内容OrderList。

orderid	pid	number	money
O1000	1	1	600.0
O1000	2	2	4000.0
O1000	3	4	40.0
O2000	2	1	2000.0

由此设计，商品表和订单表则不会出现数据冗余。

总结

在进行表的设计时，考虑三个问题

表字段数据类型的斟酌
完整性约束条件的规范
实体关系对应的表设计原则

实体之间的关系，无非是一对一、一对多、多对多。

一对一、一对多中，子表和父表的关联可由子表增加一列字段来关联父表的主键。

而多对多关系中，除了增加一列字段来关联父表，还需要增加一个中间表来消除冗余。

MySQL_数据类型

内容