DorisSQL与MySQL函数与语法对照差异篇

1.日期函数

时区.

mysql -> convert_tz(dt,from_tz,to_tz)
doris -> CONVERT_TZ(DATETIME dt, VARCHAR from_tz, VARCHAR to_tz)

获取当前的日期，以DATE类型返回.

mysql -> CURDATE()
doris -> CURDATE()

获得当前的时间,返回datatime类型.

mysql -> NOW()
doris -> CURRENT_TIMESTAMP()

获得当前的时间，以TIME类型返回.

mysql -> NOW()+DATE_FORMAT(date,format)
doris -> current_time()

向日期添加指定的时间间隔.

mysql -> DATE_ADD(date,INTERVAL expr type)
doris -> DATE_ADD(DATETIME date,INTERVAL expr type)

将日期类型按照format的类型转化为字符串.

mysql -> DATE_FORMAT(date,format)
doris -> DATE_FORMAT(DATETIME date, VARCHAR format)
doris参数配置表:https://doris.apache.org/master/zh-CN/sql-reference/sql-functions/date-time-functions/date_format.html#description

从日期减去指定的时间间隔.

mysql -> DATE_SUB(date,INTERVAL expr type)
doris -> DATE_SUB(DATETIME date,INTERVAL expr type)

计算expr1 - expr2，结果精确到天.

mysql -> DATEDIFF(date1,date2)
doris -> DATEDIFF(DATETIME expr1,DATETIME expr2)

获得日期中的天信息.

mysql -> DAY(d),DAYOFMONTH(date)
doris -> DAY(DATETIME date),DAYOFMONTH(DATETIME date)

返回日期对应的日期名字.

mysql -> DAYNAME(DATE)
doris -> DAYNAME(DATE)

返回日期的工作日索引值.

mysql -> dayofweek(DATETIME date)
doris -> dayofweek(DATETIME date)

获得日期中对应当年中的哪一天.

mysql -> DAYOFYEAR(DATETIME date)
doris -> DAYOFYEAR(DATETIME date)

获得日期中的小时的信息.

mysql -> HOUR(DATETIME date)
doris -> HOUR(DATETIME date)

获得日期中的分钟的信息.

mysql -> MINUTE(DATETIME date)
doris -> MINUTE(DATETIME date)

将整数表达式间隔添加到日期或日期时间表达式datetime_expr中

mysql -> TIMESTAMPADD(unit, interval, DATETIME datetime_expr)
doris -> TIMESTAMPADD(unit, interval, DATETIME datetime_expr)

返回datetime_expr2−datetime_expr1，其中datetime_expr1和datetime_expr2是日期或日期时间表达式.

mysql -> TIMESTAMPDIFF(unit,DATETIME datetime_expr1, DATETIME datetime_expr2)
doris -> TIMESTAMPDIFF(unit,DATETIME datetime_expr1, DATETIME datetime_expr2)

将 Date 或者 Datetime 类型转化为 unix 时间戳.

mysql -> UNIX_TIMESTAMP(DATETIME date)
doris -> UNIX_TIMESTAMP(DATETIME date)

获得一年中的第几周.

mysql -> WEEKOFYEAR(DATETIME date)
doris -> WEEKOFYEAR(DATETIME date)

返回date类型的year部分.

mysql -> YEAR(DATETIME date)
doris -> YEAR(DATETIME date)

2.字符串函数

返回字符串的长度，对于多字节字符，返回字符数.

mysql -> char_length(VARCHAR str)
doris -> char_length(VARCHAR str)

将多个字符串连接起来, 如果参数中任意一个值是 NULL，那么返回的结果就是 NULL.

mysql -> concat(VARCHAR,...)
doris -> concat(VARCHAR,...)

使用第一个参数 sep 作为连接符，将第二个参数以及后续所有参数拼接成一个字符串.

mysql -> concat_ws(VARCHAR sep, VARCHAR str,...)
doris -> concat_ws(VARCHAR sep, VARCHAR str,...)

如果字符串以指定后缀结尾，返回true。否则，返回false

mysql -> like
doris -> ENDS_WITH (VARCHAR str, VARCHAR suffix)

如果字符串以指定前缀开头，返回true。否则，返回false.

mysql -> like
doris -> STARTS_WITH (VARCHAR str, VARCHAR prefix)

返回 strlist 中第一次出现 str 的位置（从1开始计数）。strlist 是用逗号分隔的字符串.

mysql -> find_in_set(VARCHAR str, VARCHAR strlist)
doris -> find_in_set(VARCHAR str, VARCHAR strlist)

解析并获取 json 字符串内指定路径的浮点型内容.

mysql -> json_extract
doris -> get_json_double(VARCHAR json_str, VARCHAR json_path)

解析并获取 json 字符串内指定路径的整型内容.

doris -> get_json_int(VARCHAR json_str, VARCHAR json_path)

解析并获取 json 字符串内指定路径的字符串内容.

doris -> get_json_string(VARCHAR json_str, VARCHAR json_path)

该函数是类似于 sum() 的聚合函数，group_concat 将结果集中的多行结果连接成一个字符串.

mysql -> group_concat(VARCHAR str[, VARCHAR sep])
doris -> group_concat(VARCHAR str[, VARCHAR sep])

返回字符串的字节.

mysql -> length(VARCHAR str)
doris -> length(VARCHAR str)

返回 substr 在 str 中出现的位置（从1开始计数）.

mysql -> locate(VARCHAR substr, VARCHAR str[, INT pos])
doris -> locate(VARCHAR substr, VARCHAR str[, INT pos])

将参数中所有的字符串都转换成小写.

mysql -> lower(VARCHAR str)
doris -> lower(VARCHAR str)

将数字按照货币格式输出，整数部分每隔3位用逗号分隔，小数部分保留2位.

doris -> money_format(Number)

如果字符串为空字符串或者NULL，返回true。否则，返回false.

mysql -> case when then end
doris -> NULL_OR_EMPTY (VARCHAR str)

将字符串反转，返回的字符串的顺序和源字符串的顺序相反.

mysql -> reverse(VARCHAR str)
doris -> reverse(VARCHAR str)

对字符串 str 进行模糊匹配，匹配上的则返回 true，没匹配上则返回 false.

mysql -> like(VARCHAR str, VARCHAR pattern)
doris -> like(VARCHAR str, VARCHAR pattern)

3.聚合函数

返回类似于 COUNT(DISTINCT col) 结果的近似值聚合函数.

ps:对比COUNT与DISTINCT组合使用效率更高

mysql -> COUNT(DISTINCT expr,[expr...])
doris -> APPROX_COUNT_DISTINCT(expr)

用于返回选中字段的平均值.

mysql -> AVG([DISTINCT] expr)
doris -> AVG([DISTINCT] expr)

用于返回满足要求的行的数目.

mysql -> COUNT([DISTINCT] expr)
doris -> COUNT([DISTINCT] expr)

SQL语法差异：

doris中不支持分组列再使用distinct

MSYQL:

select DISTINCT mid from order_card_detail GROUP BY mid

DORIS：

select mid from order_card_detail GROUP BY mid

doris执行异常：[Err] 1064 - errCode = 2, detailMessage = cannot combine SELECT DISTINCT with aggregate functions or GROUP BY

mysql中 where后可直接 locate() 判断，doris中需判断

MYSQL:

select mid from order_card_detail where LOCATE(mid,'4')

DORIS:

select mid from order_card_detail where LOCATE(mid,'4')>0

doris执行异常：[Err] 1064 - errCode = 2, detailMessage = WHERE clause requires return type 'BOOLEAN'. Actual type is 'INT'.

doris 中编写sql不支持 #注释

MYSQL:

#1233
select * from hopsonone_point.members_points_detail limit 100

DORIS:

--1233
select * from hopsonone_point.members_points_detail limit 100

doris执行异常：[Err] 1064 - errCode = 2, detailMessage = Please check your sql, we meet an error when parsing.

mysql中支持部分多表联查分组后跟部分字段，doris需将非聚合查询字段都放在分组条件后

MYSQL:

SELECT m_id,behavior,SUM(point)
FROM (
   select m_id,behavior,point from hopsonone_point.members_points_detail limit 100
) t1
GROUP BY m_id;

DORIS:

SELECT m_id,behavior,SUM(point)
FROM (
   select m_id,behavior,point from hopsonone_point.members_points_detail limit 100
) t1
GROUP BY m_id,behavior;

doris执行异常：[Err] 1064 - errCode = 2, detailMessage = select list expression not produced by aggregation output (missing from GROUP BY clause?): behavior