You need to enable JavaScript to run this app.
导航
MySQL 函数
最近更新时间:2024.02.06 15:16:27首次发布时间:2024.02.06 15:16:27

将方言类型设置为 MySQL

通过设置dialect_type='MYSQL',可以按照 MySQL 方法执行下面列出的函数。

ENABLE OPTIMIZER=1
SET dialect_type='MYSQL';
or
[query] + SETTINGS dialect_type='MYSQL';
DESC TABLE t1 SETTINGS dialect_type='MYSQL';
or
CLICKHOUSE_CLIENT --dialect_type=MYSQL
  • format()
  • locate()
  • adddate()
  • date_sub()
  • datediff()
  • dayofweek(): 在 MySQL 中,默认的第一天是星期日。 在 ByteHouse 中,默认第一天是星期一。
  • concat_ws()

控制流函数

CASE

  • 语法:
    • 用法1: CASE value WHEN comparison_value THEN result [ELSE result] END
    • 用法2:CASE WHEN condition THEN result [ELSE result] END
  • 描述: 满足条件时返回一个值。 如果不满足条件,则返回 ELSE 结果;如果未提供 ELSE,则返回 NULL。 ****

IF 函数

  • 语法: IF(expr1, expr2, expr3)
  • 描述: 如果 expr1 为 TRUE,则返回 expr2,否则返回 expr3。 需要三个参数,在两个参数场景中使用 NULL 作为可选的第三个参数。

IFNULL 函数

  • 语法: IFNULL(expr1, expr2)
  • 描述: 如果不为 NULL,则返回 expr1; 否则,返回 expr2。

NULLIF 函数

  • 语法: NULLIF(expr1, expr2)
  • 描述: 如果 expr1 等于 expr2,则返回 NULL; 否则,返回 expr1。

数字函数

基本算术运算符

  • Addition (+):计算两个数字的和。
  • Subtraction (-):计算两个数字之间的差。
  • Multiplication (*):将两个数字相乘。
  • Division (/):一个数字除以另一个数字。
  • Integer Division (div):将一个数除以另一个数,返回商的整数部分。
  • Modulo (% or mod):返回除法运算的余数。

数学函数

  • Absolute (abs):返回数字的绝对值。
  • Arc Cosine (acos):计算数字的反余弦。
  • Arc Sine (asin):计算数字的反正弦。
  • Arc Tangent (atan):求数字的反正切。
  • Arc Tangent of Two Numbers (atan2):返回其参数商的反正切。
  • Ceiling (ceiling or ceil):将数字向上舍入到最接近的整数。
  • Cosine (cos):计算数字的余弦。
  • Cotangent (cot):计算数字的余切。
  • CRC32 (crc32):计算字符串的循环冗余校验值。
  • Degrees (degrees):将弧度转换为度数。
  • Exponential (exp):计算数字的指数。
  • Floor (floor):将数字向下舍入到最接近的整数。
  • Natural Logarithm (ln):计算数字的自然对数。
  • Logarithm (log):计算数字的对数。 使用 log_with_base 来表示有底数的对数。
  • Base-2 Logarithm (log2):计算数字的以 2 为底的对数。
  • Base-10 Logarithm (log10):计算数字以 10 为底的对数。
  • Pi (pi):返回 π 的值。
  • Power (power or pow):将一个数字求另一个数字的幂。
  • Radians (radians):将角度转换为弧度。
  • Random Number (rand):生成随机数。 对 0 到 1 之间的数字使用 randCanonical。
  • Round (round):将数字四舍五入到指定的小数位数。
  • Sign (sign):确定数字的符号,返回-1、0或1。
  • Sine (sin):计算数字的正弦。
  • Square Root (sqrt):计算数字的平方根。
  • Tangent (tan):计算数字的正切。

日期时间函数

日期时间函数运算

  • ADDDATE():向日期添加特定时间间隔。
  • SUBDATE():从日期中减去特定时间间隔。
  • ADDTIME():将特定时间间隔添加到时间或日期时间值。
  • SUBTIME():从时间或日期时间值中减去特定时间间隔。
  • DATE_SUB():从日期中减去时间间隔。
  • DATE_ADD():向日期添加时间间隔。

日期和时间提取

  • CURDATE():返回当前日期。
  • CURTIME():返回当前时间。
  • NOW():返回当前日期和时间。
  • SYSDATE():返回系统的当前日期和时间。
  • EXTRACT():提取用户指定的日期的一部分。
  • YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND():从日期/时间值中提取特定组件。

格式函数

  • DATE_FORMAT():按指定格式设置日期。
  • TIME_FORMAT():按指定格式设置时间。

转换函数

  • STR_TO_DATE():根据指定格式将字符串转换为日期。
  • UNIX_TIMESTAMP():将日期转换为 UNIX 时间戳。
  • FROM_UNIXTIME():将 UNIX 时间戳转换为日期。

时区函数

  • CONVERT_TZ():将日期时间从一个时区转换为另一时区。

其他函数

  • DATEDIFF():返回两个日期之间的天数。
  • TIMEDIFF():返回两个时间/日期时间表达式之间的差异。
  • DATE(), TIME():从日期时间表达式中提取日期或时间部分。
  • DAYNAME(), MONTHNAME():返回日期中的星期几或月份的名称。
  • WEEK(), YEARWEEK():返回日期的周数。

字符串函数

操作和分析

  • ASCII():返回字符串中第一个字符的 ASCII 值。
  • BIN():将数字转换为二进制字符串。
  • BIT_LENGTH():返回字符串的位长度。
  • CHAR():从一系列 ASCII 值构造一个字符串。
  • CHAR_LENGTH():返回字符串中的字符数。
  • CONCAT():将两个或多个字符串连接成一个字符串。
  • CONCAT_WS():用指定的分隔符连接字符串。
  • ELT():返回字符串列表中的第 N 个元素。
  • EXPORT_SET():返回表示值中设置的位的字符串。
  • FIELD():返回值列表中值的索引。
  • FIND_IN_SET():在以逗号分隔的字符串列表中查找字符串的位置。
  • FORMAT():将数字格式化为指定的小数位数。
  • FROM_BASE64():解码 Base64 编码的字符串。
  • HEX():将字符串或数字转换为十六进制。
  • INSTR():返回字符串中子字符串第一次出现的位置。
  • LCASE() or LOWER():将字符串转换为小写。
  • LEFT():返回指定长度的字符串的最左边部分。
  • LENGTH():返回字符串的字节长度。
  • LIKE:使用 SQL 简单正则表达式比较来执行模式匹配。
  • LOCATE():返回字符串中子字符串第一次出现的位置。
  • LPAD():用另一个字符串向左填充一个字符串,直到一定长度。
  • LTRIM():删除字符串中的前导空格。
  • MAKE_SET():构造一组逗号分隔的字符串,对应于给定值中设置的位。
  • MID(), SUBSTR(), SUBSTRING():从字符串中返回子字符串。
  • OCT():将数字转换为八进制字符串。
  • ORD():返回字符串最左边字符的代码。
  • POSITION():LOCATE() 的同义词。
  • QUOTE():转义字符串中的特殊字符以在 SQL 语句中使用。
  • REPEAT():将字符串重复指定的次数。
  • REPLACE():用另一个子字符串替换出现的指定子字符串。
  • REVERSE():反转字符串。
  • RIGHT():返回指定长度字符串的最右边部分。
  • RPAD():用另一个字符串右填充一个字符串,达到一定的长度。
  • RTRIM():从字符串中删除尾随空格。
  • SOUNDEX():从字符串返回 soundex 字符串。
  • SPACE():返回指定数量的空格字符的字符串。
  • STRCMP():比较两个字符串。
  • SUBSTRING_INDEX():返回字符串中指定分隔符出现次数之前的子字符串。
  • TO_BASE64():使用 base64 对字符串进行编码。
  • TRIM():从字符串中删除前导和尾随空格。
  • UCASE() or UPPER():将字符串转换为大写。
  • UNHEX():将十六进制字符串转换为常规字符串。

聚合函数

平均值 AVG (Average)

  • 计算列中数值的平均值。
  • ByteHouse 对于空表返回 nan,这与 MySQL 和 ADB 不同,后者返回 NULL。

BIT_AND, BIT_OR, BIT_XOR 位与、位或、位异或

  • 对列的值执行按位运算。
  • BIT_AND 返回所有值的按位与。
  • BIT_OR 返回所有值的按位或。
  • BIT_XOR 返回所有值的按位异或。

COUNT 计数

  • 计算符合指定条件的行数。
  • COUNT(DISTINCT xx) 对唯一值进行计数。
  • COUNT(ALL xx) 对所有值进行计数,包括重复项。

MAX and MIN 最大值与最小值

  • 分别返回列中的最大值和最小值。
  • 可应用于各种数据类型,包括日期和字符串。
  • 对于插入 NULL 值的数字类型,行为可能因系统而异。

STD, STDDEV_POP, and VAR, VAR_POP, VAR_SAMP

  • 计算标准差和方差。
  • STD 和 STDDEV_POP 返回总体标准差。
  • VAR_POP 返回总体方差。
  • VAR_SAMP 返回样本方差。
  • 空集的精度和处理可能会有所不同,ByteHouse 对于空集返回 nan

SUM 求和

  • 计算数字列的总和。

特别注意事项

  • 转换和默认值:当对具有隐式转换或默认值的数据类型执行聚合函数时,不同 SQL 系统的结果可能会有很大差异。 例如,ByteHouse 对数字默认值的处理可能与 MySQL 和 ADB 不同,从而影响 MIN 和 MAX 等函数。
  • NULL 的处理:聚合函数对 NULL 值的处理会影响结果。 在 ByteHouse 中,某些函数(例如 MIN 和 MAX)可能会返回 0 或实际值,而 MySQL 或 ADB 将返回 NULL。
  • 精度问题:在计算标准差或方差时,由于浮点数的内部表示,可能会出现精度问题。

暂不支持

暂不支持MySQL以下函数:

  • convert_tz() : 当前不支持GMT/MET这种时区参数

与 MySQL 不一致

函数差异

repeat(),
rpad(), sapce()

参数传负数时会报错,MySQL上返回空或NULL

exp()

精度差异

ln()

精度差异

bin()

ByteHouse返回结果会比MySQL多了0000的前缀

from_base64()

传入错误的base64值会报错,MySQL上返回NULL

cast()

和ADB有差异,部分用法MySQL也不支持,转成boolean类型的结果是true/false,而不是1/0

avg()

avg()作用在空表上,ByteHouse返回nan,mysql返回NULL

min(),
max()

ByteHouse的默认值和MySQL不一致,数值类型默认值是0,MySQL上默认值为NULL,这时候计算min(),max()可能会受到这个影响导致结果不一致。可以在建表时加上Nullable()来规避此问题。

localtime()

结果比MySQL上少了年月日

str_to_date()

格式化参数和传入时间没有对齐的话,返回值有区别
str_to_date('2022-01-06 10:20:30','%Y-%m-%d'),ByteHouse 返回 NULL,MySQL返回 2022-01-06

用法与MySQL有差异

以下函数的用法与MySQL有差异,使用时需注意:

函数名

差异

解 法

if

仅支持3个参数,2个参数需要改写

log()

不支持两个参数log(10,100)

新增log_with_base()来支持2个参数的用法

rand()

在 MySQL 上,它返回 [0,1) 范围内的随机数,而在 ByteHouse 上,它返回整个 UInt32 范围内的随机数

要在 ByteHouse 中返回 [0,1) 范围内的随机数,请使用 randCanonical

encrypt()

在 MySQL 中,两个参数就足够了,但在 ByteHouse 中,需要三个参数。 第一个参数指定加密算法

encrypt('aes-128-ecb','abdABC123','1234567890123456');

data_format()

在ByteHouse中,现有客户要求使用data_format来模仿Hive的格式

在ByteHouse中,使用data_format_mysql代替data_format