mysql的几种较少使用的数据类型探究

〇、环境

如下图：

msyql 5.7.30

一、问题的产生

使用客户端给mysql表的列指定数据类型时，有如下界面。
显然支持的数据类型是非常多的。
今天突然想探究一下那些很冷门的数据类型有什么用，怎么用，适合怎么用。

二、必备常识

mysql是一个社区产品，一开始就比较宽容。
这点就有点像浏览器的html识别标准：“不管你进来的样子是什么，严不严谨我都认，但我输出的就比较标准了”
为了能够兼容尽可能多的数据库类型（就是看起来跟其他数据库更像，比如它的int你这里也叫int）,就有了同义词这个概念。
比如integer和 int，decimal 和 numeric等。
比如我在实际操作时，如果要将一个字段置为numeric类型，按了保存按钮之后就会变成 decimal类型。
可见至少在这个版本的mysql里面，这两个类型是完全等价的。

三、具体的几个“冷门”类型

3.1 year

这个也是今天才发现，感觉可能是最没用的类型了，有种弄巧成拙的感觉(有可能是水平不够，个人看法)。
长度是不能选的，始终是这样的：

而且逻辑非常地沙雕。
比如

00 ~ 69 将转换为2000~2069之间
70 ~ 99 将转换为1970~1999之间

赋值的时候可以输入2位数或4位数。
比如
输入50，会保存为2050；输入80，会保存为1980。
输入1980，会保存为1980; 输入2040，会保存为2040

所以我感觉很弄巧成拙，莫名其妙。

3.2 numeric 和 decimal

这两个是同义词，不论在建表时候你选择哪个，保存的时候都会变成decimal，由此可知二者是同义词。没有任何区别。

值得一提的是，decimal类型的数据，数据库的小数位数就有意义了，
如下图

在给字段赋值的时候，给这三个字段分别填入0.123456这个六位小数，实际保存的内容会根据小数点位数的不同而不同。
如下图：

这就很明显了。
顺便一提，显然，这根据小数点位数截断的是默认采取了四舍五入策略的。
但应该能够猜到，这个取舍的时候采取什么策略，如直接舍掉，还是直接进位，或者财务计算法截断等，是可以配置的。暂且不提，意义不大。

3.3 bit

解读
顾名思义，bit，比特类型。
不再像其他类型一样需要将内容编码后存储如保存为abc这样的字符串，而是直接存储0和1。
我顺手试了下，毫不意外地，只能输入0 或 1，其他数值无法输入。
那么，很显然，如果业务中某字段的类型就是bool类型，只可能为是或否这样的，用bit来对应确实是非常合适的。

限制
另外，可以猜到，这个类型总是有个上限约束的。
比如：

可知，bit类型的长度最多为64位。
其中，长度为1,2，20的，可保存的内容形如：

3.4 json

顾名思义，这个字段里存的是json字符串。
试了一下，确实如此。比如直接输入aaa就不能保存。
那这个字段其实挺好理解的，就是保存json字符串而已。

3.4.1 素质拓展 — 新问题 — json字符串的标准是什么？(高端)

但有点意外的是输入123这样的也能保存，那么为什么呢，难道123这样的字符串也是json字符串?

于是我就去探究json字符串的标准是什么

初步考证

最终找到了一个比较可靠的依据，不展开了，直接上结论：

json中文网

然后下载到pdf文件仔细看，好在文件并不长，好像也不太难懂。
看到了这段内容：

原文：

1. Introduction
 JavaScript Object Notation (JSON) is a text format for the
 serialization of structured data. It is derived from the object
 literals of JavaScript, as defined in the ECMAScript Programming
 Language Standard, Third Edition [ECMA].
 JSON can represent four primitive types (strings, numbers, booleans,
 and null) and two structured types (objects and arrays).
 A string is a sequence of zero or more Unicode characters [UNICODE].
 An object is an unordered collection of zero or more name/value
 pairs, where a name is a string and a value is a string, number,
 boolean, null, object, or array.
 An array is an ordered sequence of zero or more values.
 The terms "object" and "array" come from the conventions of
 JavaScript.
 JSON’s design goals were for it to be minimal, portable, textual, and
 a subset of JavaScript.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

从中看来，说的意思好像是 ：

一个普通字符串是json字符串
一个普通数值是json字符串
一个bool值，一个null值，都是
以及常规我们理解的那个“json字符串”

以上是一个考证的过程。

佐证

另外，为了进一步佐证一下这个说法，我使用JS的语法来检验一下某字符串是不是json字符串。
如下图

这就是很显然的了，一切尽在不言中。

3.4.2 回到主题

所以，mysql的json字段里，目前能存储类似这样的内容：

此处算是一个小小的冷门知识，提升一个B格。

3.5 enum

这个类型也较为奇特。
首先，它的长度和小数点位数是无意义的，也是不允许设置的。

这个很容易理解。

另外就是新增数据的时候交互方面有点特别，必须把具体的枚举值都放进去。
如下图：

然后取的值就只能在几个枚举中选择。
如下图：

查询：

我记得，好像查出来的数据，用字符串，或者对应的int数值都可以接收到。
类似这样（C# dapper查询方式）：

List<int> enums = conn.queery<int>("SELECT enum_field FROM `type_table`  ").ToList();
1

但这个可能取决于具体的数据库中间件。

另外，盲猜mysql存储所谓的enum类型实际上是int类型。

3.6 blob 和 binary、varbinary

这几个个其实非常像。
顾名思义，都是以二进制方式存储数据。

另外，binary是固定长度，varbinary是可变长度。
在这两个之间，

当列数据项大小一致时应使用 binary
当列数据项大小不一致时应使用 varbinary

对于中小型项目来说，程序员们可能经常会有这个想法：能够把文件(如图片、文档等)像存储字符串一样存在数据库某表的列里面。
取的时候取出来就行了。
而不是把文件存在服务器目录下作为普通文件放在那里。

这里来比较一下两种方式的优缺点

实际上，对于我个人来说，我曾经用C#等语言，把图片等普通文件，转换成字节数组(byte[])，然后存入到mysql数据库中的blob或binary字段中。
取出也是一样，然后再转换成文件流，使用和展示文件。
可行性是毫无疑问的，而且在当时完全感受不到binary和blob的区别。

就我目前的感受来看，blob就是可以非常非常大，最大能到4GB。
其他没啥区别。

这里可以参考一下我之前写过的一篇博文：

C#二进制方式(binary、varbinary、blob、longblog等)读写mysql

类似地，text 与 char varchar 的关系同理。

---

四、其他几个冷门类型(地理空间相关)

上面的几个，还是算是比较好理解的。
现在还剩下这几个更加冷门的，下面逐一试验和说明。

值得一提的是，这些类型，都不支持长度和小数点的设置，就是上图中所示的样子

4.1 一些GIS相关的闲话

GIS一般指地理信息系统。 地理信息系统（Geographic Information System或 Geo－Information system，GIS）
1

以下的内容，如果能够有一些GIS的基础知识会好一些。

如上图，我们日常使用的地图，是怎样做出来的？

实际上都是瓦片图(Tiles)，即各种分辨率的，一层一层的图片，随着你鼠标滚轮或手指的缩放，瓦片图一层一层地展示，所以也才有了上图中的“加载瓦片图”的过程。这些都是每个人很熟悉的了。
比如前些年很知名的javascript库OpenLayers.js，就是做这个的。

然后用户在地图上还可以做很多事：

1. 点到点之间，拉一条线，它帮你算出两点之间的距离（如百度地图小工具里的测距）
2. 框选一个区域，它有现成的函数(需传入框选时候的一系列的点)可以帮你把这个区域的面积算出来
3. 甚至在选取很多个点，库提供的函数可以算出这个空间的体积
4. 在某些点打tag(如标记一个小红旗等)之类的

所以这里也才有了点(point)、线(line)、面、体等的概念。
也便于理解其他的地理空间信息数据类型。
1
2

对于地球，该如何定位呢？
实际上也是有各种坐标系的，
如下图

就像数学里的xy坐标系，U3D里面的二维、三维坐标系，cavans里的坐标系等等坐标系一样，先定义一个坐标系，再用类似(1,2)这样的值来表示坐标系中的一个位置。

就是给定义一个坐标系，然后以此为标准，地球的各个点都可以表示了。

简单点讲，经纬度(如北纬23.5°，东经75°这样的)位置信息凭什么有意义，那就是在某个坐标系下才有意义。
那么，mysql里面的point，实际上就可以理解为某个点的坐标系下的一种表示（比如记录经纬度信息）。

4.2 point

经试验，想要像普通字符串那样直接往这些字段赋值是不允许的。
只能输入类似这样的：POINT(1 3)

INSERT into type_table (point) VALUES(GeomFromText('Point(21 23)'))

1
2

插入如图：

查询如图：

我猜用后端编程语言接收查询内容的时候需要一个动态类型，或者一个什么class。
没有尝试，意义不大。

4.3 multi_point

同理：
insert语句：

INSERT into type_table(multi_point) VALUES (GeomFromText('MULTIPOINT((1 1),(2 2))'))
1

查询略。

4.4 linestring、multi_linestring

形如：
LINESTRING(121.342423 31.542423,121.345664 31.246790,121.453178 31.456862)

insert语句:

INSERT into type_table(linestring) VALUES (GeomFromText('LINESTRING(121.342423 31.542423,121.345664 31.246790,121.453178 31.456862)'))
1

查询略：

4.5 polygon、multi_polygon

polygon: 多边形

geomfromtext('POLYGON((121.474243 31.234504, 121.471775 31.233348, 121.470724 31.23155, 121.471603 31.230229, 121.472655 31.230357, 121.475777 31.232045, 121.474243 31.234504))')

insert 语句：

INSERT into type_table(polygon) VALUES (geomfromtext('POLYGON((121.474243 31.234504, 121.471775 31.233348, 121.470724 31.23155, 121.471603 31.230229, 121.472655 31.230357, 121.475777 31.232045, 121.474243 31.234504))'))
1

查询如图：

4.6 geometry、geometry_collection

看名字其实就可以猜出来，geometry 是 point，line，polygon等类型的“基类”，可以更自由地保存不同种类的信息到一起。
用法肯定也是同理。

那么问题来了，知道这些类型，有个鸟用？

这就印出了下面的内容，即mysql自带的空间函数。
如果没有这些函数的话，自己写也不大现实，而且复杂度难以想象。
没有这些函数的话，那些地理空间的点线面之类的也就几乎没有意义了，因为无法使用。

五、mysql自带地理空间函数

就如同前面提到的OpenLayers.js 一样，提供了瓦片图的加载之外，还提供了各种功能函数，如点与点之间的距离，面的面积等，这才让空间数据有了意义。
mysql的同理，有这么多，需要自己体会。
但也不必一个个去较真地学习，把它们当成一个字典，用的时候去查最合适。
随便搜了下，如

MySQL 8 中文手册 -> MySQL 函数和运算符 -> 空间分析功能–空间函数参考

更多空间解析函数见官方文档：
MySQL5.7版本：
https://dev.mysql.com/doc/refman/5.7/en/spatial-function-reference.html

MySQL8.0版本：
https://dev.mysql.com/doc/refman/8.0/en/spatial-function-reference.html

六、延伸 – mongoDB与空间函数

有了上面提到的这些知识储备，就可以使用mysql完成一些之前很难想象的业务功能了。
比如做个上门捏脚的服务，那么就需要下单，接单。
接单的时候就可以判断下单人的位置，以及与自己的空间距离等，
判断是否在服务区域内等等。
专业点来说，就是可以实现一些基于 LBS 的业务了。

但是，虽说mysql有了这些能力，但别的中间件可能也有这个能力啊。
比如你要做个什么打车app之类的，需要一些地理空间信息业务处理的，把这样的空间信息存储在mysql中也不一定是最好的方案。
据我说知，mongoDB也提供类似的解决方案。
但二者究竟孰是更优的选择，我尚未做过具体对比，只是提一下。

七、后记

说到这里，mysql中应该暂时没有陌生的数据类型了。
但是有个有趣的问题可以延伸一下：
看mysql表的索引类型:

如上图，mysql的索引类型里面有一个SPATIAL类型，看名字，盲猜就跟上面提到的空间数据有关系。
但具体怎么用，我尚未专门研究过。
这个以后再说。