# 函数 #### 支持的函数 表1操作符函数 | 函数表达式 | 示例 | |:---|:---| | IN | SELECT \* FROM Products WHERE vendor_id IN ( 'V000001', 'V000010' ) ORDER BY product_price; | | NOT IN | SELECT product_id, product_name FROM Products WHERE NOT vendor_id IN ('V000001', 'V000002') ORDER BY product_id; | | BETWEEN | SELECT id, product_id, product_name, product_price FROM Products WHERE id BETWEEN 000005 AND 000034 ORDER BY id; | | NOT...BETWEEN | SELECT product_id, product_name FROM Products WHERE NOT vendor_id BETWEEN 'V000002' and 'V000005' ORDER BY product_id; | | IS NULL | SELECT product_name FROM Products WHERE product_price IS NULL; | | IS NOT NULL | SELECT id, product_name FROM Products WHERE product_price IS NOT NULL ORDER BY id; | | AND | SELECT \* FROM Products WHERE vendor_id = 'V000001' AND product_price \<= 4000 ORDER BY product_price; | | OR | SELECT \* FROM Products WHERE vendor_id = 'V000001' OR vendor_id = 'V000009'; | | NOT | SELECT product_id, product_name FROM Products WHERE NOT vendor_id = 'V000002'; | | LIKE | SELECT \* FROM Products WHERE product_name LIKE 'NAME%' ORDER BY product_name; | | NOT LIKE | SELECT \* FROM Products WHERE product_name NOT LIKE 'NAME%' ORDER BY product_name; | | CONCAT | SELECT product_id, product_name, Concat( product_id , '(', product_name ,')' ) AS product_test FROM Products ORDER BY product_id; | | + | SELECT 3 \* 2+5-100/50; | | - | SELECT 3 \* 2+5-100/50; | | \* | SELECT order_num, product_id, quantity, item_price, quantity\*item_price AS expanded_price FROM OrderItems WHERE order_num BETWEEN 000009 AND 000028 ORDER BY order_num; | | / | SELECT 3 \* 2+5-100/50; | | UPPER | SELECT id, product_id, UPPER(product_name) FROM Products WHERE id \> 10 ORDER BY product_id; | | LOWER | SELECT id, product_id, LOWER(product_name) FROM Products WHERE id \<= 10 ORDER BY product_id; | | SOUNDEX | SELECT \* FROM Vendors WHERE SOUNDEX(vendor_name) = SOUNDEX('test') ORDER BY vendor_name; | | IFNULL | SELECT IFNULL(product_id, 0) FROM Products; | 表2时间日期函数 | 函数表达式 | 示例 | 支持范围 | |:---|:---|:---| | DAY() | SELECT \* FROM TAB_DATE WHERE DAY(date)=21; SELECT \* FROM TAB_DATE WHERE date='2018-12-21'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2018-05-22'); | - | | MONTH() | SELECT \* FROM TAB_DATE WHERE MONTH(date)=12; SELECT \* FROM TAB_DATE WHERE date='2018-12-21'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2018-05-22'); | - | | YEAR() | SELECT \* FROM TAB_DATE WHERE YEAR(date)=2018; SELECT \* FROM TAB_DATE WHERE date='2018-12-21'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2018-05-22'); | - | | TIME() | SELECT \* FROM TAB_DATE WHERE TIME(date)='01:02:03'; SELECT \* FROM TAB_DATE WHERE date='2021-01-01 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03'); | 参数需为合法的时间、日期时间表达式或字符串。 | | TIME_TO_SEC() | SELECT \* FROM TAB_DATE WHERE TIME_TO_SEC(date)=3603; SELECT \* FROM TAB_DATE WHERE date='2021-01-01 01:00:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:00:03'); | 参数需为合法的时间、日期时间表达式或字符串。 | | SEC_TO_TIME() | SELECT \* FROM TAB_DATE WHERE SEC_TO_TIME(date)='00:01:00'; SELECT \* FROM TAB_DATE WHERE date=60; INSERT INTO TAB_DATE(id,date) VALUES(1,60); | 参数需为数值或可转化为数值的字符串。 | | SECOND() | SELECT \* FROM TAB_DATE WHERE SECOND(date)=3; SELECT \* FROM TAB_DATE WHERE date='2021-01-01 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03'); | 参数需为合法的时间、日期时间表达式或字符串。 | | MINUTE() | SELECT \* FROM TAB_DATE WHERE MINUTE(date)=2; SELECT \* FROM TAB_DATE WHERE date='2021-01-01 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03'); | 参数需为合法的时间、日期时间表达式或字符串。 | | HOUR() | SELECT \* FROM TAB_DATE WHERE HOUR(date)=1; SELECT \* FROM TAB_DATE WHERE date='2021-01-01 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03'); | 参数需为合法的时间、日期时间表达式或字符串。 | | DAYNAME() | SELECT \* FROM TAB_DATE WHERE DAYNAME(date)='Friday'; SELECT \* FROM TAB_DATE WHERE date='2021-01-01 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03'); | 参数需为合法的日期、日期时间表达式或字符串。 | | MONTHNAME() | SELECT \* FROM TAB_DATE WHERE MONTHNAME(date)='January'; SELECT \* FROM TAB_DATE WHERE date='2021-01-01 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03'); | 参数需为合法的日期、日期时间表达式或字符串。 | | LAST_DAY() | SELECT \* FROM TAB_DATE WHERE LAST_DAY(date)='2021-01-31'; SELECT \* FROM TAB_DATE WHERE date='2021-01-01 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03'); | 参数需为合法的日期、日期时间表达式或字符串。 | | DAYOFWEEK() | SELECT \* FROM TAB_DATE WHERE DAYOFWEEK(date)=6; SELECT \* FROM TAB_DATE WHERE date='2021-01-01 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03'); | 参数需为合法的日期、日期时间表达式或字符串。 | | DAYOFMONTH() | SELECT \* FROM TAB_DATE WHERE DAYOFWEEK(date)=6; SELECT \* FROM TAB_DATE WHERE date='2021-01-01 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03'); | 参数需为合法的日期、日期时间表达式或字符串。 | | DAYOFYEAR() | SELECT \* FROM TAB_DATE WHERE DAYOFYEAR(date)=365; SELECT \* FROM TAB_DATE WHERE date='2021-12-31 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-12-31 01:02:03'); | 参数需为合法的日期、日期时间表达式或字符串。 | | WEEKOFYEAR() | SELECT \* FROM TAB_DATE WHERE WEEKOFYEAR(date)=53; SELECT \* FROM TAB_DATE WHERE date='2021-12-31 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-12-31 01:02:03'); | 参数需为合法的日期、日期时间表达式或字符串。 | | DATE_ADD( date, INTERVAL expr unit ) | SELECT \* FROM TAB_DATE WHERE DATE_ADD(date, INTERVAL 1 YEAR)='2022-01-01 01:02:03'; SELECT \* FROM TAB_DATE WHERE date='2021-01-01 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03'); | - date参数需为合法的时间、日期、日期时间表达式或字符串。 - expr为从date开始加减运算的间隔值，要求为整数或可转化为整数的字符串。 - unit为单位，暂时可选SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR。 - 当date为日期类型时，"1000-01-01"是下边界。在运算时如果存在越界行为，运算可能出错。 - date参数最高支持毫秒精度。 | | DATE_SUB(date, INTERVAL expr unit) | SELECT \* FROM TAB_DATE WHERE DATE_SUB(date, INTERVAL -1 DAY)='2021-01-02 01:02:03'; SELECT \* FROM TAB_DATE WHERE date='2021-01-02 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-02 01:02:03'); | - date参数需为合法的时间、日期、日期时间表达式或字符串。 - expr为从date开始加减运算的间隔值，要求为整数或可转化为整数的字符串。 - unit为单位，暂时可选SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR。 - 当date为日期类型时，"1000-01-01"是下边界。在运算时如果存在越界行为，运算可能出错。 - date参数最高支持毫秒精度。 | ![](https://support.huaweicloud.com/usermanual-ddm/public_sys-resources/caution_3.0-zh-cn.png) DDM 和 DN实例时区不一致的情况下，如果用户的 SQL 中使用了时间函数，若时间函数下推，返回的是 DN实例所在时区的时间，如果时间函数是在 DDM 侧计算，则返回的是 DDM 的时区。 表3数学函数 | 函数表达式 | 示例 | 支持范围 | |:---|:---|:---| | SQRT() | SELECT id, product_price, SQRT(product_price) AS price_sqrt FROM Products WHERE product_price \< 4000 ORDER BY product_price; | - | | AVG() | SELECT AVG(product_price) AS avg_product FROM Products; | - | | COUNT() | SELECT COUNT(\*) AS num_product FROM Products; | - | | MAX() | SELECT id, product_id, product_name, MAX(product_price) AS max_price FROM Products ORDER BY id; | - | | MIN() | SELECT id, product_id, product_name, MIN(product_price) AS min_price FROM Products ORDER BY id; | - | | SUM() | SELECT SUM(product_price) AS sum_product FROM Products; | - | | ROUND() | SELECT ROUND(product_price) AS round_product FROM Products; | 参数需为数值或可转化为数值的字符串。 | | SIN() | SELECT SIN(x) AS sin_x FROM math_tbl; | 参数需为数值或可转化为数值的字符串。 | | COS() | SELECT COS(x) AS cos_x FROM math_tbl; | 参数需为数值或可转化为数值的字符串。 | | TAN() | SELECT TAN(x) AS tan_x FROM math_tbl; | 参数需为数值或可转化为数值的字符串。 | | COT() | SELECT COT(x) AS cot_x FROM math_tbl; | 参数需为数值或可转化为数值的字符串。 | | FLOOR() | SELECT FLOOR(product_price) AS floor_product FROM Products; | 参数需为数值或可转化为数值的字符串。 | | CEILING() | SELECT CEILING(product_price) AS ceiling_product FROM Products; | 参数需为数值或可转化为数值的字符串。 | | ABS() | SELECT ABS(x) AS abs_x FROM math_tbl; | 参数的范围为-9223372036854775807到9223372036854775807，超过此范围会报错。 | | LOG() | SELECT LOG(x) AS log_x FROM math_tbl; | 参数需为大于0的数值或可转化为相应数值的字符串。 | | LN() | SELECT LN(x) AS ln_x FROM math_tbl; | 参数需为大于0的数值或可转化为相应数值的字符串。 | | EXP() | SELECT EXP(x) AS exp_x FROM math_tbl; | 参数的范围为-∞到709，超过此范围会报错。 | 表4字符串函数 | 函数表达式 | 示例 | 支持范围 | |:---|:---|:---| | TRIM() | SELECT TRIM(' hello, world ') AS trim_character FROM Character; | 参数需为字符串相关类型。 | ![](https://support.huaweicloud.com/usermanual-ddm/public_sys-resources/note_3.0-zh-cn.png) 使用前，请先查看该函数是否在支持范围，建议使用支持范围内的函数。如果在范围外使用，返回结果与MySQL中的返回结果可能不一致。 #### 不支持的函数 表5函数的限制 | 函数 | 限制条件 | |:---|:---| | ROW_COUNT() | DDM 暂不支持ROW_COUNT()函数。 | | COMPRESS() | DDM 暂不支持COMPRESS()函数。如果无法确认函数是否能下推到RDS，请不要使用该函数。 | | SHA() | DDM 暂不支持SHA()函数。如果无法确认函数是否能下推到RDS，请不要使用该函数。 | | SHA1() | DDM 暂不支持SHA1()函数。如果无法确认函数是否能下推到RDS，请不要使用该函数。 | | MD5() | DDM 暂不支持MD5()函数。如果无法确认函数是否能下推到RDS，请不要使用该函数。 | | AES_ENCRYPT() | DDM 暂不支持AES_ENCRYPT()函数。如果无法确认函数是否能下推到RDS，请不要使用该函数。 | | AES_DECRYPT() | DDM 暂不支持AES_DECRYPT()函数。如果无法确认函数是否能下推到RDS，请不要使用该函数。 | | YEARWEEK() | DDM 暂不支持YEARWEEK()函数。如果无法确认函数是否能下推到RDS，请不要使用该函数。 | | TIME_FORMAT() | DDM暂不支持TIME_FORMAT()函数。如果无法确认函数是否能下推到RDS，请不要使用该函数，建议使用DATE_FORMAT()函数。 |