📚 课程目录第3章日志数据结构化

3. 日志数据结构化：字段提取、类型转换、空值处理

日志原始数据就像一团乱麻——时间戳格式五花八门，字段名可能叫 msg 也可能叫 message，有的行还缺字段。不做结构化，你没法聚合、没法过滤、没法关联。所以我一直强调：结构化是日志分析的第一道坎。过了这道坎，后面都是常规SQL操作。

这一章我们聚焦三个硬功夫：字段提取（从无结构文本里抠出关键信息）、类型转换（把字符串当数字用）、空值处理（别让NULL毁了你的聚合）。

3.1 字段提取：从无结构到有结构

我见过不少日志格式：JSON 的、CSV 的、key=value 的，还有最头疼的——纯自由文本。字段提取的核心就是利用分隔符或模式把目标字符串切出来。

3.1.1 利用字符串函数

如果你的日志有固定的分隔符（比如空格、逗号、竖线），直接用 SPLIT_PART 或 SUBSTRING_INDEX 最省事。举个例子，一条 Nginx 访问日志：

192.168.1.10 - - [10/Oct/2023:13:55:36 +0800] "GET /api/users HTTP/1.1" 200 1234

我想提取状态码（第8列按空格拆分）。在 PostgreSQL 里可以这样写：

SELECT SPLIT_PART(log_line, ' ', 8) AS status_code
FROM raw_logs;

在 MySQL 里用 SUBSTRING_INDEX 组合两次也能达到同样效果。我在实际项目中更推荐用 SPLIT_PART，因为可读性强，调一调参数就能拿不同字段。

注意：如果日志里某个字段本身包含分隔符（比如 URL 里有问号），直接按位置拆分可能把字段内部也切开了。这种情况我建议先用正则或者走 JSON 解析。

3.1.2 正则表达式提取

当分隔符不固定，或者你想提取的内容在文本深处，正则就派上用场了。MySQL 的 REGEXP_SUBSTR、PostgreSQL 的 regexp_matches、SQL Server 的 PATINDEX + SUBSTRING 都是好工具。

还是上面那条日志，我想取请求方法（GET/POST等）和请求路径。正则写法：

-- PostgreSQL
SELECT (regexp_matches(log_line, '"([A-Z]+) ([^"]+)'))[1] AS method,
       (regexp_matches(log_line, '"([A-Z]+) ([^"]+)'))[2] AS path
FROM raw_logs;

我个人习惯把提取逻辑封装成视图或 UDF，不然每次写冗长的正则容易出错。嗯，这里要注意：正则太复杂会影响查询性能。如果日志量达到亿级，尽量用 SPLIT_PART 解决，正则留给那些确实没法拆的情况。

3.2 类型转换：让数据真正可用

提取出来的字段默认都是字符串。你想对时间排序？对数字求和？统统不行。所以类型转换几乎是必须的一步。

核心原则：只要能保证数据质量，尽早转。最好在 ETL 阶段就转好，不要等到汇总查询时再临时 CAST。

目标类型	MySQL 语法	PostgreSQL 语法	说明
整数	`CAST(field AS signed)`	`field::integer` 或 `CAST(field AS int)`	字符串里不能有非数字字符
浮点数	`CAST(field AS decimal(10,2))`	`field::numeric`	注意科学计数法的坑
日期	`STR_TO_DATE(field, format)`	`TO_TIMESTAMP(field, format)`	格式字符串要精确匹配
时间戳	`UNIX_TIMESTAMP(field)`	`EXTRACT(EPOCH FROM field::timestamp)`	统一为 Unix 时间戳更方便运算

我记得有一次排查线上问题，发现某张日志表的 response_time 字段用字符串存着，里面混了几条 "timeout" 文本。结果直接 CAST 报错了，整个查询崩掉。后来我加了一层 TRY_CAST（部分数据库支持）或者先过滤掉非法值。

小技巧：PostgreSQL 的 :: 语法比 CAST() 更简洁，我写高并发的管道查询时都用 ::。MySQL 8.0+ 也开始支持 :: 了。

3.2.1 时间戳标准化

日志时间格式最常见的就是 [30/Jan/2024:15:04:05 +0800] 这种 Apache 风格。不同数据库处理方式略有不同，但万变不离其宗——先提取出日期部分，再用格式化函数转。

-- PostgreSQL
SELECT TO_TIMESTAMP(
         SUBSTRING(log_line FROM '\[([^\]]+)\]'),
         'DD/Mon/YYYY:HH24:MI:SS'
       ) AS log_time
FROM raw_logs;

为什么强调标准化？你想想看，如果每条日志的时间戳存成不同格式，你按 HOUR 分组根本对不齐。我通常都转成 Unix 时间戳（int64），做聚合时直接用 FLOOR(ts / 3600) 之类的算术，效率比字符串比较高一个数量级。

3.3 空值处理：别让NULL坑了你

空值是日志分析里最隐蔽的杀手。明明查到 100 条数据，一 SUM 出来只有 50 条——因为剩下的全是 NULL。我刚开始做日志分析时吃过这个亏，辛辛苦苦写了一下午报表，结果老板问为什么数据少了一半……

核心原则：任何聚合函数（SUM、AVG、COUNT）默认会忽略 NULL，但算术表达式（比如 a + b）如果有一个是 NULL，结果就是 NULL。这完全不一样。

3.3.1 填充默认值：COALESCE 和 IFNULL

COALESCE 返回第一个非 NULL 参数。我常用它给缺失字段补默认值：

SELECT COALESCE(status_code, '999') AS status_code,
       COALESCE(response_time_ms, 0) AS response_time
FROM logs;

MySQL 也支持 IFNULL(expr, default)，其实效果一样。我建议统一用 COALESCE，因为它是 SQL 标准，换数据库不用改代码。

3.3.2 判断是否空值

判断空值不能用 = NULL，要用 IS NULL 或 IS NOT NULL。这是个基本常识，但我在 Code Review 里还是经常看到。

-- 错误写法
SELECT * FROM logs WHERE status_code = NULL;

-- 正确写法
SELECT * FROM logs WHERE status_code IS NULL;

3.3.3 聚合中的 NULL 陷阱

我想额外提一点：COUNT(column) 和 COUNT(*) 对 NULL 的处理不一样。COUNT(*) 统计行数，包括全 NULL 的行；COUNT(column) 只统计该列非 NULL 的行数。很多人用 COUNT(1) 替代 COUNT(*)，其实效果一样，但 COUNT(column) 的语义要小心。

我的习惯：需要统计记录数时一律用 COUNT(*)，需要统计某个字段有值的记录数才用 COUNT(column)。这样语义清晰，不容易出错。

3.4 实战：一条日志的结构化全流程

结合上面三点，我写一个完整的例子。假设原始日志长这样：

2024-01-30 15:04:05,192 [http-nio-8080-exec-3] ERROR com.example.OrderService - Failed to process order: orderId=10086, userId=9527, cost=1234ms

我们需要提取：时间戳、日志级别、线程名、类名、orderId、userId、cost（毫秒）。然后做类型转换：时间戳转成 Unix 毫秒，cost 转成整数。空值处理：如果 cost 字段缺失，默认为 0。

-- PostgreSQL 实战
SELECT 
  -- 1. 字段提取（正则）
  (regexp_matches(raw_line, '^([\d\- :,]+)'))[1] AS raw_time,
  (regexp_matches(raw_line, '\s(\w+)\s'))[1] AS log_level,
  (regexp_matches(raw_line, '\[([^\]]+)\]'))[1] AS thread_name,
  (regexp_matches(raw_line, '\s([A-Za-z.]+)\s-'))[1] AS class_name,
  (regexp_matches(raw_line, 'orderId=(\d+)'))[1] AS order_id,
  (regexp_matches(raw_line, 'userId=(\d+)'))[1] AS user_id,
  (regexp_matches(raw_line, 'cost=(\d+)ms'))[1] AS cost_str

  -- 2. 类型转换（在子查询或 CTE 中完成）
FROM logs_raw;

-- 外层做类型转换和空值处理
WITH extracted AS (
  -- 上面的提取逻辑
)
SELECT 
  EXTRACT(EPOCH FROM TO_TIMESTAMP(raw_time, 'YYYY-MM-DD HH24:MI:SS,MS')) * 1000 AS ts_ms,
  log_level,
  thread_name,
  class_name,
  CAST(order_id AS bigint) AS order_id,
  CAST(user_id AS bigint) AS user_id,
  COALESCE(CAST(cost_str AS integer), 0) AS cost_ms
FROM extracted;

你看到没？整个流程就是：提取 → 转换 → 补空。这三步贯穿始终，缺一不可。我在实际生产中会把上面逻辑写成物化视图，每天增量刷新一次，这样下游查询直接拿结构化数据就好，不用每次都跑正则。

好了，本章内容就这些。记住：结构化做好了，后续的关联分析、聚合统计就是顺水推舟的事。千万千万别懒在第一步。