Hive 与 MySQL 常用函数差异对比及语法示例

Hive 和 MySQL 虽然都支持 SQL 标准，但各自的设计目标不同（Hive 用于批处理，MySQL 用于事务），导致许多函数在函数名、参数顺序、返回值上存在明显差异。以下从 7 个类别详细对比，并提供可直接运行的语法示例。

一、字符串函数

功能MySQL 语法Hive 语法差异说明
字符串长度（字符数）CHAR_LENGTH(str)LENGTH(str)Hive 的 LENGTH 返回字符数，MySQL 的 LENGTH 返回字节数
字符串长度（字节数）LENGTH(str)无直接函数Hive 需用 OCTET_LENGTH(str)
截取子串SUBSTRING(str, pos, len) (pos 从1开始)SUBSTR(str, pos, len) 或 SUBSTRING功能相同，Hive 常用 SUBSTR
查找子串位置INSTR(str, substr)INSTR(str, substr)两者都支持，Hive 还支持 LOCATE
正则提取REGEXP_SUBSTR(str, pattern)regexp_extract(str, pattern, idx)Hive 必须指定捕获组索引
正则匹配str REGEXP pattern 或 RLIKEstr RLIKE pattern 或 REGEXP两者都支持 RLIKE
字符串分割SUBSTRING_INDEX(str, delim, count)SPLIT(str, delim) 返回数组Hive 返回 array 类型，需配合 [index] 或 explode
分组拼接GROUP_CONCAT(col ORDER BY col SEPARATOR ',')COLLECT_LIST(col) + CONCAT_WSHive 需两步：先收集成数组，再连接

示例

-- ========== MySQL ==========
-- 长度：字符长度
SELECT CHAR_LENGTH('Hello世界');        -- 7（H e l l o 世 界）
SELECT LENGTH('Hello世界');             -- 9（英文1字节，中文3字节，取决于字符集）

-- 正则提取第一个数字
SELECT REGEXP_SUBSTR('abc123def456', '[0-9]+');   -- '123'

-- 分组拼接：每个部门员工姓名
SELECT dept, GROUP_CONCAT(name ORDER BY name SEPARATOR ',') 
FROM emp GROUP BY dept;

-- ========== Hive ==========
-- 长度：字符长度（注意 LENGTH 就是字符数）
SELECT LENGTH('Hello世界');             -- 7

-- 正则提取（必须指定捕获组）
SELECT regexp_extract('abc123def456', '([0-9]+)', 1);   -- '123'

-- 分组拼接：先收集为数组，再连接
SELECT dept, CONCAT_WS(',', COLLECT_LIST(name)) 
FROM emp GROUP BY dept;

二、日期时间函数

功能MySQL 语法Hive 语法差异说明
当前日期时间NOW(), CURDATE(), CURTIME()CURRENT_TIMESTAMP(), CURRENT_DATE名称不同
日期加减（天）DATE_ADD(date, INTERVAL n DAY)DATE_ADD(date, n)Hive 参数更简单
日期加减（复杂单位）DATE_ADD(date, INTERVAL n MONTH)ADD_MONTHS(date, n)Hive 有专用函数
日期差（天数）DATEDIFF(date1, date2)DATEDIFF(date1, date2)相同（但 Hive 需保证格式）
日期差（其他单位）TIMESTAMPDIFF(unit, start, end)无直接函数Hive 用 unix_timestamp 计算秒再转换
提取年月日YEAR(date), MONTH(date), DAY(date)相同一致
格式化DATE_FORMAT(date, '%Y-%m-%d')DATE_FORMAT(date, 'yyyy-MM-dd')格式字符串不同（MySQL 用 %，Hive 用 Java 格式）
字符串转日期STR_TO_DATE(str, '%Y-%m-%d')TO_DATE(str) 或 FROM_UNIXTIMEHive 自动识别常用格式
时间戳转日期FROM_UNIXTIME(ts)FROM_UNIXTIME(ts)相同
日期转时间戳UNIX_TIMESTAMP(date)UNIX_TIMESTAMP(date)相同

示例

-- ========== MySQL ==========
-- 当前日期
SELECT CURDATE();                    -- '2025-01-15'

-- 日期加1天
SELECT DATE_ADD('2024-01-31', INTERVAL 1 DAY);   -- '2024-02-01'

-- 日期差（小时）
SELECT TIMESTAMPDIFF(HOUR, '2024-01-01 08:00:00', '2024-01-01 20:00:00');  -- 12

-- 格式化
SELECT DATE_FORMAT('2024-01-01', '%Y年%m月%d日');   -- '2024年01月01日'

-- ========== Hive ==========
-- 当前日期
SELECT CURRENT_DATE;                 -- '2025-01-15'

-- 日期加1天
SELECT DATE_ADD('2024-01-31', 1);    -- '2024-02-01'

-- 日期加1月
SELECT ADD_MONTHS('2024-01-31', 1);  -- '2024-02-29'（自动调整）

-- 计算小时差（通过 unix_timestamp）
SELECT (UNIX_TIMESTAMP('2024-01-01 20:00:00') - UNIX_TIMESTAMP('2024-01-01 08:00:00')) / 3600;  -- 12

-- 格式化（注意格式字符串）
SELECT DATE_FORMAT('2024-01-01', 'yyyy年MM月dd日');   -- '2024年01月01日'

三、条件函数

功能MySQL 语法Hive 语法差异说明
简单条件IF(cond, true_val, false_val)相同一致
多分支CASE WHEN ... THEN ... END相同一致
空值替换IFNULL(expr, default)NVL(expr, default)函数名不同
多个空值替换COALESCE(val1, val2, ...)相同一致
判断空值ISNULL(expr)无Hive 直接用 expr IS NULL

示例

-- ========== MySQL ==========
SELECT IFNULL(NULL, 'default');      -- 'default'
SELECT ISNULL(NULL);                 -- 1

-- ========== Hive ==========
SELECT NVL(NULL, 'default');         -- 'default'
-- 没有 ISNULL 函数，用标准语法
SELECT NULL IS NULL;                 -- true

四、聚合函数

功能MySQL 语法Hive 语法差异说明
基本聚合COUNT, SUM, AVG, MAX, MIN相同一致
去重计数COUNT(DISTINCT col)相同（但大数据量下慢）Hive 可用 NDV 或 approx_count_distinct 近似去重
中位数无PERCENTILE(col, 0.5)Hive 支持精确中位数（整数列）
近似分位数无PERCENTILE_APPROX(col, 0.5)Hive 支持近似分位数
分组拼接GROUP_CONCATCOLLECT_LIST + CONCAT_WS见字符串部分

示例

-- ========== MySQL ==========
-- 去重计数
SELECT COUNT(DISTINCT user_id) FROM orders;

-- 中位数（需手动实现）
SELECT AVG(score) FROM (
    SELECT score, ROW_NUMBER() OVER (ORDER BY score) AS rn, COUNT(*) OVER () AS cnt
    FROM scores
) t WHERE rn IN (FLOOR((cnt+1)/2), CEIL((cnt+1)/2));

-- ========== Hive ==========
-- 近似去重计数（大数据量性能好）
SELECT NDV(user_id) FROM orders;          -- 精确但非标准
SELECT approx_count_distinct(user_id) FROM orders;  -- 近似

-- 中位数（直接计算）
SELECT PERCENTILE(score, 0.5) FROM scores;   -- 要求 score 为整数类型

五、类型转换

功能MySQL 语法Hive 语法差异说明
通用转换CAST(expr AS type)相同但类型名不同
类型名示例SIGNED, UNSIGNED, CHARINT, DOUBLE, STRINGHive 类型更接近 Java
字符串转整数CONVERT(expr, SIGNED)CAST(expr AS INT)MySQL 有 CONVERT，Hive 只用 CAST

示例

-- ========== MySQL ==========
SELECT CAST('123' AS SIGNED);        -- 123
SELECT CONVERT('456', SIGNED);       -- 456

-- ========== Hive ==========
SELECT CAST('123' AS INT);           -- 123
SELECT CAST('123.45' AS DOUBLE);     -- 123.45

六、表生成函数（UDTF）与行转列

Hive 支持 EXPLODE 等 UDTF，MySQL 不支持。

功能Hive 语法MySQL 替代方案
数组展开为多行LATERAL VIEW EXPLODE(array) t AS elem使用递归 CTE 或写存储过程
Map 展开LATERAL VIEW EXPLODE(map) t AS key, value无法直接实现
列转行（多列转多行）LATERAL VIEW + 多个 EXPLODE使用 UNION ALL 硬编码

示例

-- ========== Hive ==========
-- 将 "a,b,c" 拆成三行
SELECT word
FROM (SELECT 'a,b,c' AS str) t
LATERAL VIEW EXPLODE(SPLIT(str, ',')) tmp AS word;

-- 结果：
-- a
-- b
-- c

-- ========== MySQL ==========
-- 需要借助递归 CTE（8.0+）
WITH RECURSIVE split AS (
    SELECT 1 AS pos, SUBSTRING_INDEX('a,b,c', ',', 1) AS word,
           SUBSTRING('a,b,c', LENGTH(SUBSTRING_INDEX('a,b,c', ',', 1)) + 2) AS rest
    UNION ALL
    SELECT pos+1, SUBSTRING_INDEX(rest, ',', 1),
           SUBSTRING(rest, LENGTH(SUBSTRING_INDEX(rest, ',', 1)) + 2)
    FROM split WHERE rest != ''
)
SELECT word FROM split;

七、JSON 函数

功能MySQL 5.7+Hive差异说明
提取 JSON 值JSON_EXTRACT(json, path)get_json_object(json, path)函数名不同，路径语法相似
提取多个字段多次调用 JSON_EXTRACTjson_tuple(json, col1, col2, ...)Hive 有专用多列提取
检查有效性JSON_VALID(json)无Hive 无法检查

示例

-- ========== MySQL ==========
SELECT JSON_EXTRACT('{"name":"Alice","age":30}', '$.name');   -- "Alice"
-- 去引号
SELECT JSON_UNQUOTE(JSON_EXTRACT('{"name":"Alice"}', '$.name')); -- Alice

-- ========== Hive ==========
SELECT get_json_object('{"name":"Alice","age":30}', '$.name');   -- "Alice"
-- 提取多个字段
SELECT t.name, t.age
FROM (SELECT '{"name":"Alice","age":30}' AS json) a
LATERAL VIEW json_tuple(a.json, 'name', 'age') t AS name, age;
-- 结果：Alice, 30

八、其他注意事项

项目MySQLHive说明
窗口函数8.0+ 支持0.11+ 支持语法相同
NULL 排序默认 NULL 最小（升序在前）默认 NULL 最大（升序在后）Hive 可用 NULLS FIRST/LAST
执行计划EXPLAINEXPLAIN两者输出格式不同
存储过程支持不支持Hive 不支持存储过程
索引支持 B+树等多种仅有元数据索引（无普通索引）Hive 靠分区和分桶优化

九、快速对照表（常用）

你要做的事MySQLHive
字符串长度（字符）CHAR_LENGTH(s)LENGTH(s)
正则提取REGEXP_SUBSTR(s, p)regexp_extract(s, p, 1)
分组拼接GROUP_CONCAT(col)CONCAT_WS(',', COLLECT_LIST(col))
日期加天DATE_ADD(d, INTERVAL 1 DAY)DATE_ADD(d, 1)
日期差（天）DATEDIFF(d1, d2)DATEDIFF(d1, d2)
日期差（小时）TIMESTAMPDIFF(HOUR, d1, d2)(UNIX_TIMESTAMP(d2)-UNIX_TIMESTAMP(d1))/3600
空值替换IFNULL(x, def)NVL(x, def)
中位数自己实现PERCENTILE(col, 0.5)
字符串拆成多行递归 CTELATERAL VIEW EXPLODE(SPLIT(s, ','))
提取 JSONJSON_EXTRACT(j, '$.key')get_json_object(j, '$.key')
# hive # mysql # hadoop
Logo
AI编程社区

汇聚全球AI编程工具，助力开发者即刻编程。

更多推荐

cover

码道 · 寻艺——从零构建“非遗交互式对话平台“的完整实践

avatar AI编程社区

码道：用 Python + FastAPI 快速构建一个学生信息管理接口：从零到 Swagger 在线文档

在日常开发和教学中，我们经常需要一套简单、可控、能快速上手的后端接口来演示"前后端分离"、练习 Restful API 设计，或者作为前端联调的 Mock 服务。传统做法往往是搭一套完整的 Web 工程：配置数据库、编写 DAO、引入 ORM、做请求分发……一套流程下来，光环境搭建就要花去不少时间，对于想快速验证想法、或者刚入门 API 开发的同学来说，显得过于沉重。

avatar AI编程社区

码道：从零构建学生信息管理系统 API：FastAPI 内存版 CRUD 项目全解析

FastAPI 的强大，很大程度上来自 Pydantic 的校验能力。我们写下类型声明，FastAPI 在运行时自动把它变成边界检查器。name：字符串，长度限制 1 到 50；age：整数，限制在 6 到 120 之间（ge大于等于、le小于等于）；gender：字符串，用正则限定只能是"男"“女”“其他”；email：邮箱格式字符串；grade与major：可选字段，代表班级和专业方向。

avatar AI编程社区