Skip to main content
  1. 经验技巧分享/
  2. thinking in SQL | 废话SQL,思维与实践/

为什么数据类型如此重要

··595 字
sql 概念

在《在开始之前》,简要地提到了数据类型。编程语言里,数据类型是语言本身的重要组成部分,程序在运行过程,就是操作数据的过程,在 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地理等几大类,有的数据库还支持集合类型。

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

数字类型 #

类型名称 描述
int2/smallint 使用2个字节空间的有符号整数,值范围为 -2(7+8) ~ 2(7+8)-1
int/int4/integer 使用4个字节空间的有符号整数,值范围为 -2(7+24) ~ 2(7+24)-1
int8/bigint 使用4个字节空间的有符号整数,值范围为 -2(7+56) ~ 2(7+56)-1
numeric/decimal 任意精度十进制数字,自定义整数与小数位
float/real/double 浮点型/单精度/双精度

有整数型、通用型之分。

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

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

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

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

字符类型 #

类型名称 描述
character(n), char(n) 定长字符串,使用空格补齐
character varying(n), varchar(n) 可变长字符串
text, clob 超长文本

字符串实际上一个结构体,它是一个字节列表以及指向这个列表的指针所构成。因长度,有 可变成(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 类型

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

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

结果为:

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

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

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

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

postgres 的 几何类型:

类型名称 描述 展示形式
point Point on a plane (x,y)
line Infinite line {A,B,C}
lseg Finite line segment ((x1,y1),(x2,y2))
box Rectangular box ((x1,y1),(x2,y2))
path Closed path (similar to polygon) ((x1,y1),…)
path Open path [(x1,y1),…]
polygon Polygon (similar to closed path) ((x1,y1),…)
circle Circle <(x,y),r> (center point and radius)

比如在平面上,矩形 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 类型,用于表示年份。


各数据库中的数据类型 #

数据类型 postgres mysql sqlite oracle
整数型 int2 / int / int8 samllint / int / bigint integer integer / number
数字型 numeric / deciaml numeric / deciaml numeric number / deciaml
浮点型 real / double double
字符型 character / character varying char / varchar
长文本 text text clob
二进制 bit type, bytea blob blob blob
数组 array, type[]
set
枚举 enum enum
布尔
xml
json
几何类型

数据类型的转换 #

前文提到,程序过程是操作数据的过程。计算机在底层计算是二进制的位操作,程序中的计算是针对具体数据类型的操作。编写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字符串