为什么数据类型如此重要

在《在开始之前》，简要地提到了数据类型。编程语言里，数据类型是语言本身的重要组成部分，程序在运行过程，就是操作数据的过程，在 SQL 中也一样。如果这样说，程序处理的对象，是某个具体实例化的、带有数据类型并受之约束的变量，会把你绕晕，那让我们从另外的角度看来待数据类型。

你可能不是专业的编程人员，对一些概念不太理解。不要紧，这些会在使用数据库和编写 SQL 脚本的过程中慢慢学习到。若有些段落不太理解的，请先跳过，但不建议完全忽略本文的其它内容。

什么数据类型 #

数据类型是基础数据结构，由编程语言所提供的内置实现的最小数据单元。数据类型定义了程序中可以操作的最小数据单位。复合数据类型或者用户自定义数据结构，都是由基础数据类型组成的。

在编写 SQL 时，查询结果的每一列数据，都带有数据类型。它有可能是表字段的数据类型，或者经过计算转换，得到新的数据类型。

与在编写 pl/sql 存储过程或者函数时，遇到变量定义，需要指定它的具体数据类型一样，在对建模、表设计时，我们需要将逻辑模型中的字段进行物理化设计，此时需要结合数据库支持的数据类型，来定义每个字段的具体数据类型。这个数据类型将决定数据库如何存储数据，并且在 SQL 中如何体现。

-- postgres
CREATE TABLE employees (
    emp_no      INT             NOT NULL,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,
    -- gender      ENUM ('M','F')  NOT NULL,
    gender      char(1)         not null CHECK(gender in ('M','F')),
    hire_date   DATE            NOT NULL,
    PRIMARY KEY (emp_no)
);

上例是 employees 示例库中 的雇员表，使用 int（整数） 作为员工号的类型，性别使用带有条件约束的 char（定长字符串） 作为类型。

平常接触最多的要数字符（串）、数字等类型（因为它们覆盖了绝大部分字段），往往不会去深究数据库所提供的数据类型说明文档。我们了解数据类型，可以不去了解数据库如何存储不同类型的数据，但不能不了解数据类型的种类。

例个粟子。我们使用 “(经度, 纬度)” 来表示地理位置，如 “-99.5075448 27.5064117”。如果使用传统的方法，可能会使用两个 numeric 类型的字段，分别表示经纬度。再改进一下，可能会把这个经纬度的字面值转为字符串后保存在一个 varchar 可变长字符串字段中。请注意，字符串在转换为数字之前，是不能进行数值计算的。倘若有一个数据类型，假设它叫 GEO，可以让它保存这个经纬度的值，又提供一些经纬度类型专用的数据操作函数，会不会更香呢？

当然，最终使用什么数据类型，是受目标需求与使用的数据库而定的。不是使用越多个性化、越强大越显摆的类型，就一定是对的。如果只是为了显示，没有什么比字符、字符串更省事了。

再来一个粟子。在编程中，“ (key, (key, value…)) ” 这种带有嵌套结构的数据非常常见。如果数据是存储在数据库中，你会非常希望，读到了这个字段，自动就可得到这个嵌套结构，或者从数据库中对这个嵌套数据进行筛选，使用 “key.key…” 提取其中的值。而没有专用数据类型之前，还只能把整个值当成是一个长长的字符串，塞到一个字段，等取出来之后，再使用专门的格式解析器来分析这段特别格式文本，何止用麻烦可以形容。还好，当前主流数据库，在这方面下了功夫，比如 xml、json、地理数据这类结构复杂的类型，都有专门的支持。

这确实涉及到了数据结构的范畴。不过不用担心，本文不是指导你来设计新的数据结构类型，而是解释数据库的数据类型、并如何运用它来帮助完成工作。

基础数据类型 #

一般地，常见的数据类型有数字类型、字符类型，以及日期时间类型、XML、JSON、GEO地理等几大类，有的数据库还支持集合类型。

本节所提数据类型，并非所有的数据库均有支持。

数字类型 #

有整数型、通用型之分。

因整数的存储占用空间，有 2字节、4字节、8字节，分别对应 int2、int4、int8，一般的 integer 对应 int4。

通用型数字类型，有的数据库叫 numeric，也有叫 decimal，或者两者都支持。它们通常定义整数与小数据部分，如 numeric(p,s)，p为整数位长，s 为小数位数。

使用数字类型时，要记得 整数 与 非整数数字混合计算时的小数位问题；单精度双精度等浮点数计算时的精度问题。设计表字段时，要注意类型的精度与取值是否满足实际的需要。

若需要有精确的存储和计算的（如表示货币金额）的，请使用 numeric/decimal 类型。但为什么呢 🤔（搜索了解浮点数计算问题）

字符类型 #

字符串实际上一个结构体，它是一个字节列表以及指向这个列表的指针所构成。因长度，有 可变成（varchar） 与 *不可变长（char）*之分。这个区分，并不是需求场景所致，而是底层实现的差别。定长的字符串，系统会为所有值预分配空间，当有值且字符值不足长时，使用空格在右边补齐。

而 text 或 clob 是超长文本类型，用于存储超过 varchar 最大长度限制的文本。

⚠️ 开卷：

1. 查阅数据库文档后思考，定长字符串、可变长字符串类型分别在什么场景下使用？什么时候使用 text 或者 clob？
2. 大部分人初学者都犯错误的问题 – 空字符串与空值

日期时间类型 #

date, 日期
time，时间
datetime / timestamp，日期+时间、时间戳
interval，日期、时间间隔

日期时间类型可细分为日期、时间、日期时间（或时间戳）、间隔。日期类是非常有趣的数据类型，它与数字、字符构成了重要的三种类型。以日期为例，数据库通常使用三个部位来分别表示年、月、日，配合对应的日期函数，可以玩出非常多的花样，月初、月末。部分数据库还提供间隔类型（interval），表示两个日期之间的差，也可用来计算如 “几天前”、“几天后” 等这些常见的日期计算。

使用日期类型时，需要注意时区对时间值的影响，数据库有具体的对应方法。

⚠️ 开卷：

1. 在 postgres 上查出今天的日期、当前的时间戳，并使用函数计算月初、月末、年初、年末，提取当前月份。然后再到 mysql 数据库上实现同时的计算。并观察不同数据库上的差异。

集合类型 #

表示集合的有数组 与集类型。

数组类型是指具有相同数据类型元素组成的列表，集是有相同数据类型元素组成的集合。数组与集的区别在于，数据中的元素有排列顺序，元素值可以相同，而集内元素是无序且唯一的。

集类型可以用于表示码值字段的可选值选项，数组类型用于表示一个对象或者事物的多种情况。当数组元素为数组时，该数组就变成矩阵。

二进制类型 #

通常我们使用数据库来保存的数据，有文本、数字，使用文字串、长文本、数字类型足够。但有时需要存储一些非 ascii 的数据，如图片，这里就需要使用到二进制类型。数据库的最小存储单位是字节，8位的二进制数可使用2位的十六进制数表示。在数据传输时，需要使用数值的文本化表示（序列化），那么往往会使用十六进制来替代二进制（两者值相同，但字面文本的长度是1:4）

-- postgres
select b'1100'
     , x'C'
     , 'C'::bytea
     , E'\x43'
     , ascii('C')
     , to_hex(ascii('C'))

当见到十六进制的数据时，把它称为 binary data 是正确的，它是二进制数据十六进制形式。

⚠️ 开卷：

1. 你的日常使用的数据库是否有上面例中这些 bit 类型、escape string( E’\x…’)、十六进制函数 hex？
2. 什么是 ascii 码，它与二进制、十进制之间的关系？
3. 与 SQL 无关的数学题，编写一个用于将二进制数字转为十进制的转换函数。
4. 编写一个用于将十进制数字转二进制的转换函数。

其他复杂类型 #

用于存储 xml 数据。它较使用 text 长文本类型直接存储 xml 数据的字面值形式的优势在于可以约束校验来检查数据的格式，还有一些类型安全的专用函数。

-- postgres
select xmlcomment('hello youwu.today');

结果为:

        xmlcomment
--------------------------
 <!--hello youwu.today-->
(1 row)

与 xml 类型类似，数据库也提供了专用的操作函数。

--- postgres
SELECT '{"domain": "youwu.today", "options": {"type": "site", "active": true}}' ::json;

几何类型包含 点 point、直线 line、线段 line segment、矩形 box、圆 circle、路径 path、多边形 polygon、曲线 curve、面 surface。这些类型也是计算图形学中重要的基本数据结构。

postgres 的 几何类型:

比如在平面上，矩形 box '(1, 1), (0,0)' 在 x 轴上右向2个单位，即与点 point '(2, 0)' 相加，select box '(1,1),(0,0)' + point '(2,0)'，得到 box '(3,1), (2,0)'

⚠️ 开卷：

1. 线 path + path 会得到什么
2. 矩形 box 除以 点 point 又是什么鬼 😵

再来看看几何类型的配套函数，比如 求面积 area(几何类型)、中心点 center()、矩形高度 height(矩形)等等。有兴趣的同事可以找些资料看看，顺便复习下初中的几何知识。

以上特殊的复杂类型，是有特定的应用场景。因为场景应用比较突出，数据库产品提供了原生支持，使用它们时，往往需要配合数据库所提供的专用函数来使用，因此，学习这些数据类型时，应配合阅读这些类型对应的操作函数。

⚠️ 开卷：

1. 从 xml 类型操作函数说明中，找到可以实现字符串聚合（拼接）的函数（如xmlagg)，并编写出完整的SQL。
2. 从 json 类型操作函数中，找出类似实现上述功能的函数，并编写出完整的SQL。

自定义类型 #

自定义类型，即自定义数据结构，并且为该类型定义专用操作函数，类似于在 java 中定义类型的属性与方案。 它分为定义数据结构、定义算法操作函数两个部分。如果没有类型专属的函数，那么查询后得到的结构化结果是非常难以使用的。

当所用数据库内置的数据类型无法满足使用时，可以考虑自已来定义类型，特别是这个类型有非常多的专用操作算法函数的时候。

⚠️ 开卷：

使用 postgres 的自定义类型，设计一个 year 类型，用于表示年份。

各数据库中的数据类型 #

数据类型的转换 #

前文提到，程序过程是操作数据的过程。计算机在底层计算是二进制的位操作，程序中的计算是针对具体数据类型的操作。编写SQL时也一样，对于初学者来说，这可能会增加心智负担，但这确实是一个无法回避而需要迈过去的坎。至少理解数据类型与转换，有助的排除SQL错误。

看看在 postgres 中的这个例子:

select t.col::int
from   ( select '' col) t

得到的是这样的错误提示：

ERROR:  invalid input syntax for type integer: ""

假如，有一个张表，其中有一个字段是字符类型，该字段实际上是存储数字型的值，当无值时填了空字符串（’’)，当SQL很长的时候，这个错误是非常难定位的，postgres 只提示“转换 integer 时出现无效的输入”。很难能联系到问题所在。

上面的例子中，至少有如下两个问题：

1. 字符类型的字段，空值与字字符串的差别
2. 字符类型转整数类型

数据类型的转换有显式转换、隐式转换两种。显式转换，即使用数据库的数据类型转换函数强制转换数据类型；隐式转换，在SQL为了计算，执行引擎会将数据按照实际的计算进行数据类型转换，或者在函数调用时，会将输入值转为函数参数的数据类型。

当转换时出现无效值（即无法转换），数据库则会报错。

• 显式转换

-- postgres
-- 将表示有效日期的 yyyy-mm-dd 字符串转为 date 类型
select cast('2022-01-16' as date), date '2022-01-16'

• 隐藏转换

select '1' + 2, ascii('1')

ascii('1') 计算出字段“1” 的 ascii 码是 49，并不是 1。当运算 '1' + 2 时，数据库会尝试将第一个操作数（字符串“1”）按字面量转换为数字，然后与第二个操作数（数字 2）进行相加操作。

⚠️ 开卷：

1. 你日常使用的数据库，是如何处理 select 'a' + 1 这个计算的？会有什么结果？

小结 #

通过本文，简单了解了数据库中的基础数据类型。若稍微有编程经验的，可能会出现，这些数据类型与编程语言中的基础数据类型非常接近。

与编写一般程序一样，编写 SQL 与编写存储过程、函数时使用数据类型，搭配对应的函数，非常强大，一通便通。通常会使用或需要注意：

1. 数字型:
   • 精度: 单精度、双精度问题
   • 类型转换: 数字字符串与数字之间的隐性类型转换
   • 常用函数: 四舍五入、零存整取
2. 字符类型:
   • 字符串拼接
   • 字符串模板
   • 与各种数据类型间的格式转换
3. 日期类型:
   • 特定日期: 月初、月末、年初、季末
   • 日期计算: … x 以后、… x 以前、间隔
   • 当前时间、当前日期、当前时间戳
   • unix 时间戳与日期之间有转换
4. 二进制:
   • 使用场景
   • 字节数组与字符串间的关系
   • 十六进制字符串、UNICONDE字符串