【MySQL系列】数据结构详解(全网最全)

数据库建模，创建库表是项目开始进行编码的最后一步，库表设计的是否合理高效不仅取决于对项目业务流程的掌握程度，更要懂得数据库字段合理使用，才能设计出性能卓越易于扩展的数据模型。本文基于MySQL8，分享常用数据结构使用。MySQL数据结构大致分为数字类型、字符串类型、日期时间类型、二进制类型、空间数据类型。1 数字类型数字类型 UNSIGNED 无符号（没有负值，最大值会加倍） ZEROFILL

陌鱼

5952人浏览 · 2022-05-10 21:14:17

陌鱼 · 2022-05-10 21:14:17 发布

数据库建模，创建库表是项目开始进行编码的最后一步，库表设计的是否合理高效不仅取决于对项目业务流程的掌握程度，更要懂得数据库字段合理使用，才能设计出性能卓越易于扩展的数据模型。本文基于MySQL8，分享常用数据结构使用。

MySQL数据结构大致分为数字类型、字符串类型、日期时间类型、二进制类型、空间数据类型。

1 数字类型

数字类型 UNSIGNED 无符号（没有负值，最大值会加倍） ZEROFILL 如果位数不够前面补0

类型	大小(字节)	有符号范围	无符号范围
`tinyint`	1	-128到127	0到255
`smallint`	2	-32768到32767	0到65535
`mediumint`	3	-8388608到8388607	0到16777215
`int`	4	-2147483648到2147483647	0到4294967295
`bigint`	8	-9223372036854775808到9223372036854775807	0到18446744073709551615
`float`	4	-3.402823466E+38到-1.175494351E-38，0 和1.175494351E-38到3.402823466E+38	0 和1.175494351E-38到3.402823466E+38
`double`	8	-1.7976931348623157E+308到-2.2250738585072014E-308、 0和2.2250738585072014E-308到1.7976931348623157E+308	0和2.2250738585072014E-308到1.7976931348623157E+308
`decimal[M,D]`		根据M和D的值	根据M和D的值

使用建议：

1、根据业务数据大小决定合适的字段类型
2、如果字段非负数建议加上UNSIGNED，可以使其范围增加一倍
3、金钱等需求精度要求高的数据建议使用decimal，或者将其转化成更小的单位用其他整形字段保存，比如将金钱转成分，用int或者bigint保存

2 字符串类型

类型	范围	描述
`char`	1 ～ 255个字符	一个定长字符串
`varchar`	1 ～ 255个字符	一个变长字符串
`tinyText`	255(2^8-1)个字符	小型文本
`text`	65535(2^16-1)个字符	普通文本
`mediumText`	16777215(2^24-1)个字符	中型文本
`longText`	4294967295(2^32-1)个字符	大型文本
`enum`		枚举类型，格式属性名 `ENUM`(‘值1’,‘值2’,‘值3’…‘值n’)，`ENUM`有NOT NULL属性，其默认值为取值列表的第一个元素
`set`		属性名 SET(‘值1’,‘值2’,‘值3’…,‘值n’)类似多选的`ENUM`
`json`		可以存储`json`格式数据，通过函数可以进行字符串操作

使用建议：

1、char长度固定， 即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定。
2、varchar可变长度，可以设置最大长度；适合用在长度可变的属性。
3、text不设置长度，可以存储大文本数据，如文字图片文档等，当然只是相对来说。、
4、json是MySQL新增的数据结构，可以像非关系数据库一样操作json格式数据，如果功能非必要进入非关系型数据库，可以使用json代替

3 日期时间类型

类型	范围	描述
`date`	1000-01-01到9999-12-31	`MySQL`以`YYYY-MM-DD`格式来显示DATE值，但是允许你使用字符串或数字把值赋给DATE列
`datetime`	1000-01-01 00:00:00到9999-12-31 23:59:59	MySQL以’YYYY-MM-DD HH:MM:SS’格式来显示DATETIME值，但是允许你使用字符串或数字把值赋给DATETIME的列
`timestamp`	1970-01-01 00:00:01到2038-01-19 03:14:07	MySQL以YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD或YYMMDD 格式来显示TIMESTAMP值，取决于是否M是14(或省略)、12、8或6，但是允许你使用字符串或数字把值赋给TIMESTAMP列。一个 TIMESTAMP列对于记录一个INSERT或UPDATE操作的日期和时间是有用的，因为如果你不自己给它赋值，它自动地被设置为最近操作的日期和时间
`time`	-838:59:59到838:59:59	MySQL以’HH:MM:SS’格式来显示TIME值，但是允许你使用字符串或数字把值赋给TIME列
year	1901到2155	一个2或4位数字格式的年(缺省是4位)。允许的值是1901到2155，和0000(4位年格式)，如果你使用2位，1970-2069( 70-69)。MySQL以YYYY格式来显示YEAR值，但是允许你把使用字符串或数字值赋给YEAR列

使用建议：

一般使用存储时间使用datetime和timestamp居多，timestamp所占空间较小，推荐使用datetime，timestamp由于所占字节数为32位，最多能表示到2038年

4 二进制类型

类型	范围	描述
`binary`	M	字节数为M，允许长度为0~M的定长二进制字符串
varbinary	M	允许长度为0~M的变长二进制字符串，字节数为值的长度加1
bit	64	M位二进制数据，M最大值为64
tinyblob		可变长二进制数据，最多255个字节
blob		可变长二进制数据，最多2的16次方-1个字节
mediumblob		可变长二进制数据，最多2的24次方-1个字节
longblob		可变长二进制数据，最多2的32次方-1个字节

使用建议：

一般这种类型数据使用比较少，如果不适用分布式文件系统，可以将使用该类型数据结构存储文档

5 空间数据类型

MySQL为空间数据存储及处理提供了专用的类型geometry(支持所有的空间结构)，还有有细分类型Point, LineString, Polygon,MultiPoint,MultiLineString,MultiPolygon等等，我们了解了空间函数，在涉及到经纬度存储，路线存储方面的业务就能够使用此类型进行存储，使用相关空间函数进行分析业务实现。

类型	描述
`geometry`	支持所有的空间结构
`point`	简单点
`linestring`	简单线
polygon	简单面
`multipoint`	多点
`multilinestring`	多线
`multipolygon`	多面