Day 6｜SQL、数据类型与函数体系：从兼容到正确建模

前五天，我们完成了 Doris 的定位、架构、部署形态与第一个实验集群。今天开始进入数据工程阶段。

会写 SELECT 只是起点。一套长期稳定的数据平台，还需要回答更具体的问题：金额为什么选 DECIMAL，全球事件时间为什么要区分 DATETIME 与 TIMESTAMPTZ，标签应该放进 ARRAY 还是拆成明细表，动态 JSON 什么时候适合 VARIANT，精确去重与近似去重如何取舍，窗口函数和 LATERAL VIEW 又分别解决什么问题。

本日学习目标

完成今天的学习后，你应当能够：

分清 MySQL 协议兼容、SQL 语法兼容和执行语义兼容；
按照业务语义、精度、范围、查询方式和演进方式选择字段类型；
正确使用整数、浮点数、DECIMAL、字符串、日期与时间类型；
理解 ARRAY、MAP、STRUCT、JSON、VARIANT 的建模边界；
在精确集合计算与近似去重之间选择 BITMAP 或 HLL；
建立标量、聚合、窗口、表函数和高阶函数的整体地图；
使用窗口函数完成排名、累计、环比等分析；
使用 LATERAL VIEW 将集合列展开为多行；
识别隐式类型转换、时间语义、NULL 和 JSON Null 带来的正确性风险；
判断一段业务逻辑应使用内置函数、Java UDF，还是处于实验阶段的 Python UDF。

一、理解 Doris SQL，先分清三种“兼容”

Apache Doris FE 默认通过 9030 端口提供 MySQL 网络协议服务。MySQL Client、JDBC、ODBC、DataGrip 以及大量 BI 工具都可以直接连接。这个能力显著降低了接入门槛，也很容易让团队产生一个误解：既然客户端能够连接，原有 MySQL SQL 就可以原样迁移。

真实迁移需要分成三层验证。

1.1 第一层：网络协议兼容

协议兼容解决的是“如何建立连接并交换结果”。客户端完成握手、认证、提交 SQL，FE 返回字段元数据和结果集。团队可以继续使用成熟的 MySQL 驱动、连接池、BI 工具和开发工具。

这一层带来的价值很直接：

应用无需安装 Doris 专用驱动；
数据分析师可以继续使用熟悉的 SQL 客户端；
BI 平台通常只需新增一个 MySQL 类型的数据源；
JDBC 连接池、监控代理和数据库网关能够继续复用。

协议层不会替业务确认 SQL 语义。客户端显示“连接成功”，只说明网络、账户和协议握手正常。

1.2 第二层：语法与函数兼容

Doris 支持标准 SQL，并覆盖大量 MySQL 常用语法与函数。常见的 SELECT、JOIN、GROUP BY、HAVING、子查询、CTE、窗口函数、INSERT、UPDATE、DELETE 都有对应能力。

迁移时仍需逐项检查：

MySQL 私有语法是否有 Doris 等价写法；
函数名称、参数顺序和返回类型是否一致；
保留字是否与字段名冲突；
LIMIT、日期格式化、字符串处理是否存在细微差异；
存储过程、触发器、外键等 OLTP 能力是否被原系统依赖；
原 SQL 是否隐含依赖 MySQL 的类型转换规则。

建议把业务 SQL 分成固定报表、即席查询、同步任务、数据校验和管理语句五类，批量扫描后再选择改写策略。不要只抽取十几条“跑得通”的 SQL 作为兼容结论。

1.3 第三层：执行语义兼容

执行语义关注结果正确性和性能行为。以下 SQL 在不同数据库中可能都能执行，结果或资源开销却可能不同：

SELECT true + true;

SELECT CAST(1.3 AS FLOAT) - CAST(0.7 AS FLOAT) = CAST(0.6 AS FLOAT);

SELECT CAST('null' AS JSON) IS NULL;

SELECT amount / quantity FROM order_item;

Doris 的 BOOLEAN 是独立类型，内部以 0/1 表示；浮点数遵循近似数规则；JSON Null 与 SQL NULL 含义不同；DECIMAL 运算会进行精度推导。再加上分布式执行中的并行聚合顺序、数据模型、分区分桶和统计信息，最终表现会与单机 OLTP 数据库产生差异。

一套可靠的迁移流程应包含：

连接验证；
语法和函数扫描；
小样本结果对账；
全量边界值对账；
EXPLAIN 计划检查；
冷热缓存性能测试；
并发与尾延迟测试；
差异清单和回滚门禁。

1.4 SQL 的书写顺序与逻辑处理顺序

SQL 文本通常按照以下顺序书写：

SELECT ...
FROM ...
JOIN ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ...;

理解查询时，可以使用另一条逻辑顺序：

FROM 与 JOIN 确定数据来源；
WHERE 过滤明细行；
GROUP BY 建立分组；
聚合函数计算每组结果；
HAVING 过滤聚合结果；
窗口函数基于当前结果集计算；
SELECT 形成输出表达式；
ORDER BY 排序；
LIMIT 截断返回行。

这条顺序能够解释很多常见报错。WHERE 无法引用同层聚合结果，因为聚合尚未发生；窗口函数别名通常要放入子查询后再过滤；HAVING 适合过滤 SUM(amount) > 1000 这类组级条件。

SELECT city, SUM(amount) AS total_amount
FROM orders
WHERE create_time >= '2026-08-01'
GROUP BY city
HAVING SUM(amount) > 100000
ORDER BY total_amount DESC
LIMIT 20;

优化器会重写物理执行计划，例如谓词下推、分区裁剪、局部聚合和 TopN 下推。逻辑顺序用于理解语义，物理计划用于理解性能。两套视角结合起来，才能同时回答“结果为什么这样”和“查询为什么这么快或这么慢”。

1.5 迁移 SQL 时建立差异台账

建议为每条核心 SQL 记录以下字段：

字段	示例
业务名称	日销售额看板
来源系统	MySQL 8.0
SQL 类别	固定报表
使用函数	`date_format`、`ifnull`、`group_concat`
类型依赖	金额 DECIMAL、时间 DATETIME
结果基线	2026-08-01 样本对账值
Doris 改写	函数或语法调整说明
计划关注点	分区裁剪、聚合下推、Join 分发
性能目标	p95 < 500 ms，QPS 100
状态	待改写 / 已对账 / 已压测 / 已上线

差异台账会在后续版本升级时继续发挥作用。函数签名、隐式转换、时间类型和优化器规则都可能演进，已经验证过的核心 SQL 应进入自动回归，而非依赖人工记忆。

二、数据类型是一份长期数据契约

字段类型会同时影响四件事：

正确性：能否精确表达金额、时间、状态和标识；
性能：参与扫描、比较、聚合和 Join 时需要多少 CPU 与内存；
存储：每行占用空间、压缩效率和索引体积；
演进：后续是否容易扩展、修改或与其他系统交换。

很多早期项目习惯把所有字段都定义成 STRING，希望先把数据存下来再处理。短期建表速度很快，长期会出现日期无法稳定裁剪、金额需要反复转换、脏数据进入主链路、索引无法命中、统计信息失真等问题。

2.1 推荐的选择顺序

设计字段时，按以下顺序提问：

这个字段表达什么业务语义？
是否要求精确值？
合法范围有多大？
是否参加 Key、排序、分区或分桶？
常见过滤方式是等值、范围、全文还是集合运算？
Schema 是否稳定？
数据是否需要跨时区交换？
后续是否需要参与聚合、Join 或索引？

类型越贴近真实语义，后续 SQL 越简单，数据质量也越容易治理。

2.2 一张实用速查表

业务含义	优先类型	说明
小范围状态码	`TINYINT` / `SMALLINT`	节省空间，适合离散状态
一般业务 ID	`BIGINT`	常见整数主标识
超大整数标识	`LARGEINT`	128 位整数
金额、税率、结算值	`DECIMAL(P,S)`	固定小数，精确计算
模型分数、传感器值	`FLOAT` / `DOUBLE`	允许近似误差
业务日期	`DATE`	只表达年月日
本地业务时间	`DATETIME(p)`	无时区信息
全球绝对时刻	`TIMESTAMPTZ(p)`	UTC 存储，按会话时区展示
固定短编码	`CHAR(M)`	固定长度
有明确上限的文本	`VARCHAR(M)`	可作为 Key 等核心列使用
长文本值列	`STRING`	仅适合作为 Value 列
同类型有序集合	`ARRAY<T>`	标签、步骤、向量
动态键值属性	`MAP<K,V>`	扩展属性、稀疏键值
固定嵌套对象	`STRUCT`	字段名称与类型稳定
完整 JSON 对象	`JSON`	JSONB 存储和 JSON 函数
结构持续变化的 JSON	`VARIANT`	热点路径子列化
精确集合与交并补	`BITMAP`	精确去重与集合运算
大规模近似去重	`HLL`	固定状态、约 1% 典型误差

2.3 MySQL 迁移中经常遇到的类型差异

MySQL 与 Doris 的类型名称有大量重合，映射时仍需逐项确认：

Doris 整数类型只支持有符号范围，MySQL UNSIGNED 要检查上界；
Doris 没有 MEDIUMINT 和 YEAR，通常映射到 INT、DATE 或独立年份整数；
MySQL TIMESTAMP 带有自动初始化、自动更新时间和时区转换习惯，迁移到 Doris 时要拆解这些行为；
Doris 的 TIME 主要用于计算，不能作为 OLAP 表列保存；
CHAR(N)、VARCHAR(N) 的长度按字节理解；
Doris 的 STRING 是分析型扩展，MySQL 没有同名类型；
MySQL 的 B+Tree、唯一约束和自增主键概念，需要重新映射到 Doris 数据模型、排序键和分桶设计；
Doris UPDATE 要求 WHERE 条件，高频逐行更新需要重新评估链路。

迁移工具自动生成的 DDL 可以作为起点，正式建表仍要经过人工评审。源库类型只说明数据当前如何保存，目标表还要考虑未来查询、更新、分区、分桶和保留周期。

2.4 SQL Mode、事务和结果口径

Doris 支持部分 SQL Mode，例如 PIPES_AS_CONCAT、NO_BACKSLASH_ESCAPES 和 ONLY_FULL_GROUP_BY。同一段 SQL 在不同 Mode 下可能产生不同解析结果。团队应在连接初始化时固定 Mode，并把设置纳入测试环境和生产环境的配置基线。

事务能力也要结合分析数据库的使用方式理解。查询和单条 DDL 通常以隐式事务执行，多语句事务的能力与 OLTP 数据库存在边界。批量导入、Flink Checkpoint、Stream Load Label 和两阶段提交属于数据接入一致性问题，不能直接套用 MySQL 应用事务的设计。

结果对账建议覆盖四类数据：

全量行数和主键数量；
金额、计数和去重指标；
时间边界、跨天和跨时区样本；
NULL、空字符串、0、负数和极值。

只对几条正常数据执行 SELECT *，很难发现真实迁移风险。边界样本和异常样本应进入长期回归测试。

2.5 六类常见建模失误

失误一：金额使用 DOUBLE。 初期数据量小时看不出问题，累计求和、税费拆分和多次换算后会出现小数误差。核心金额字段应从源头使用 DECIMAL。

失误二：时间全部使用字符串。 字符串可以显示时间，却会增加格式不一致、时区不明和转换失败的风险，也会给分区裁剪与日期函数增加额外成本。

失误三：状态字段没有字典。 1、2、3 在不同系统里含义各异，后续接入者只能猜测。类型设计要配套枚举字典、合法范围和未知值策略。

失误四：所有扩展字段都放进 JSON。 高频过滤字段埋在文档内部，会反复执行路径提取和类型转换。稳定热点字段应提升为顶层列。

失误五：为节省几个字节选取过小整数。 一旦业务增长超出范围，历史数据修改和上下游同步会变得复杂。需要基于三到五年的增长预测选择范围。

失误六：把字段类型当成单表内部决定。 同一个 user_id 在 MySQL、Kafka Schema、Flink、Doris 和 API 中应保持一致。跨系统类型不一致会产生截断、符号位、时区和精度问题。

字段评审应邀请业务、数据开发和应用开发共同参与。业务确认含义，数据开发确认分析与演进，应用开发确认上下游类型映射。

三、数值类型：精确值和近似值必须分开

3.1 整数类型按范围选择

Doris 提供 TINYINT、SMALLINT、INT、BIGINT 和 LARGEINT。它们分别对应 8、16、32、64 和 128 位整数。

选择原则很简单：覆盖业务合法范围，同时留出合理增长空间。

CREATE TABLE order_status_example (
    order_id BIGINT,
    status TINYINT,
    retry_count SMALLINT,
    inventory_delta INT,
    trace_number LARGEINT
)
DUPLICATE KEY(order_id)
DISTRIBUTED BY HASH(order_id) BUCKETS 4
PROPERTIES ("replication_num" = "1");

状态码用 TINYINT 往往足够；订单 ID 通常使用 BIGINT；累计量级可能超过 32 位时使用 BIGINT。不要为了“保险”把所有整数定义成 LARGEINT，更大的类型会增加存储、内存和计算成本。

3.2 FLOAT 与 DOUBLE 适合近似数

FLOAT 和 DOUBLE 遵循 IEEE 754 浮点规则。它们能表达很大的数值范围，代价是部分十进制小数无法被二进制精确保存。

SELECT
    CAST(1.3 AS FLOAT) - CAST(0.7 AS FLOAT),
    CAST(1.3 AS FLOAT) - CAST(0.7 AS FLOAT) = CAST(0.6 AS FLOAT);

第二列表达式可能返回 0。分布式聚合还会受到计算顺序影响，极端数据下多次执行可能出现微小差异。

浮点类型适合：

传感器采样；
机器学习概率与相似度；
科学计算；
对微小误差容忍度较高的指标。

需要谨慎的场景：

金额和税务；
精确结算；
直接等值比较；
作为 Join Key；
结果需要审计复现的聚合。

3.3 DECIMAL 用于固定小数与精确计算

DECIMAL(P,S) 中，P 表示总有效位数，S 表示小数位数。

amount       DECIMAL(18, 2),
tax_rate     DECIMAL(9, 6),
exchange_rate DECIMAL(20, 10)

Doris 4.x 默认 enable_decimal256=false，此时最大精度为 38 位。开启后最高可到 76 位，计算精度提高，同时带来额外性能成本。普通金额通常使用 DECIMAL(18,2) 或结合业务上限确定；金融风险、超大整数乘除和科学高精度场景再评估 Decimal256。

DECIMAL 的乘除法会推导新的精度与小数位。当中间结果超出上限，系统可能缩减 scale 或报告溢出。生产上线前应针对最大值、最小值、乘法、除法、累计求和和负数进行边界测试。

SELECT
    CAST('999999999999.99' AS DECIMAL(18,2))
  * CAST('1000000.000000' AS DECIMAL(18,6));

表结构评审时，金额字段需要记录三项信息：业务最大值、小数位含义、舍入规则。仅写“金额用 DECIMAL”仍然不够。

3.4 BOOLEAN 是独立类型

Doris 的 BOOLEAN 与 MySQL 中常见的 TINYINT(1) 习惯存在差异。Doris 将 BOOLEAN 作为独立类型，合法值是 TRUE 和 FALSE，内部以 0 和 1 存储。

SELECT TRUE, FALSE, NOT TRUE, TRUE AND FALSE;

迁移时要检查 ORM 和 JDBC 是否把布尔列识别为整数。业务状态超过两种时使用枚举整数或字符串，不要把 BOOLEAN 继续扩展成 0、1、2、3。

四、字符串与二进制：长度、Key 能力和实际内容共同决定

4.1 CHAR 适合真正固定的短编码

CHAR(M) 是固定长度字符串，M 范围为 1–255 字节。适合国家码、固定设备编码、固定哈希片段等长度稳定的字段。

CHAR(64) 存放大量长度差异明显的内容，会造成空间浪费。业务名称、订单号、URL 通常更适合 VARCHAR。

4.2 VARCHAR 是大多数业务文本的首选

VARCHAR(M) 为变长字符串，当前 4.x 文档给出的 M 范围是 1–65533 字节。Doris 使用 UTF-8，英文字符通常占 1 字节，中文字符通常占 3 字节，所以 M 是字节上限，不能直接当作中文字符数。

适合 VARCHAR 的字段包括：

订单号；
用户名；
城市、地区和渠道；
URL；
有明确最大长度的业务编码。

Key、分区、分桶和高频过滤字段应尽量使用长度受控的类型。过大的 VARCHAR 上限会增加内存估算和异常数据风险。

4.3 STRING 适合长文本值列

STRING 默认支持 1 MB，可通过 BE 参数调整，理论上最高接近 2 GB。它只能作为 Value 列，不能作为 Key、分区列或分桶列。

适用内容：

日志原文；
文档正文；
长消息；
API 原始响应；
用于全文检索的文本字段。

超大文件、图片、压缩包等二进制对象更适合放入对象存储，Doris 保存 URI、元数据和可分析字段。把大对象直接塞进分析表会影响扫描、Compaction、网络传输和备份。

4.4 VARBINARY 的当前边界

4.x 文档已经定义 VARBINARY，用于字节级存储和比较，适合哈希、加密数据和压缩字节。当前文档同时说明：该类型主要用于外部 Catalog 的二进制字段映射和计算，Doris 内部表暂不支持把它作为可持久化列类型。

遇到二进制字段时，应先明确需求：

只需保存摘要：使用 VARCHAR 存十六进制或 Base64；
需要保存原始文件：对象存储 + URI；
从外部系统联邦查询：评估 VARBINARY 映射能力；
需要参与文本分析：在上游解码成结构化字段。

五、时间类型：先定义业务语义，再讨论格式

时间问题很少源于格式本身，更多来自语义不清。2026-08-16 09:00:00 到底是宁波门店的营业时间，还是全球统一事件发生时刻？两者需要不同建模方式。

5.1 DATE：只表达日历日期

DATE 适合结算日、业务日、生日、分区日期等仅包含年月日的字段。它不保存时区，修改会话 time_zone 不会改变已存值。

日期加减和截断应使用时间函数：

SELECT
    DATE_ADD(order_date, INTERVAL 7 DAY),
    DATE_TRUNC(order_date, 'month');

不要把日期强制转成整数后做算术，这会隐藏闰年、月长和边界问题。

5.2 DATETIME(p)：无时区的本地业务时间

DATETIME(p) 支持 0–6 位小数秒精度。它适合门店开门时间、批次生成时间、某地区业务系统的本地时间等场景。

DATETIME 存储的是已经确定的日历值。会话时区后续发生变化，已存时间不会跟着变化。跨地区系统若把各地本地时间直接写入同一列，查询者很难知道每一行属于哪个时区。

5.3 TIMESTAMPTZ(p)：跨时区绝对时刻

Doris 4.x 当前文档提供 TIMESTAMPTZ(p)，用于带时区意识的事件时刻。写入时统一转换为 UTC，查询时根据会话 time_zone 展示。

SET time_zone = 'Asia/Shanghai';

CREATE TABLE global_event (
    event_id BIGINT,
    occurred_at TIMESTAMPTZ(3),
    event_name VARCHAR(64)
)
DUPLICATE KEY(event_id)
DISTRIBUTED BY HASH(event_id) BUCKETS 4
PROPERTIES ("replication_num" = "1");

适用场景：

跨区域日志；
全球用户行为；
国际支付与交易；
多时区设备遥测；
需要统一时间线的 Trace。

时间治理需要明确四项规范：

源数据是否带时区；
写入会话使用什么 time_zone；
存储列选择 DATETIME 还是 TIMESTAMPTZ；
展示层按哪个时区转换。

一条 SQL 的生命周期