Hive 与 MySQL 常用函数差异对比

隐于花海，等待花开

338人浏览 · 2026-04-11 17:09:19

隐于花海，等待花开 · 2026-04-11 17:09:19 发布

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` 或 `RLIKE`	`str 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_WS`	Hive 需两步：先收集成数组，再连接

示例

-- ========== 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_UNIXTIME`	Hive 自动识别常用格式
时间戳转日期	`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_CONCAT`	`COLLECT_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`, `CHAR`	`INT`, `DOUBLE`, `STRING`	Hive 类型更接近 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_EXTRACT`	`json_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

八、其他注意事项

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

九、快速对照表（常用）

你要做的事	MySQL	Hive
字符串长度（字符）	`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)`
字符串拆成多行	递归 CTE	`LATERAL VIEW EXPLODE(SPLIT(s, ','))`
提取 JSON	`JSON_EXTRACT(j, '$.key')`	`get_json_object(j, '$.key')`