SQL函数兼容性

前言
Mycat对不能下推的SQL函数会在Mycat内运算,其中time类型的精度在mycat内是nanos纳秒级运算而非 微秒级microseconds运算,而在MySQL协议输出结果的时候重新转换为秒.Mycat内的字符编码全是unicode16.

在Mycat2中未定义的函数,如果能下推变成SQL,则不受Mycat的实现影响,比如UDF,它直接在MySQL中执行.

聚合函数

支持

avg

只支持数字类型参数

count

count(distinct)

max

只支持数字类型参数

min

只支持数字类型参数

sum

只支持数字类型参数

single_value

值只能是一个，该函数在解关联子查询时候出现，

any_value

返回结果中任意一个值，常用于解决ONLY_FULL_GROUP_BY的问题

不支持

BIT_AND

BIT_OR

BIT_XOR

GROUP_CONCAT

STD

STDDEV

STDDEV_POP

STDDEV_SAMP

VAR_POP

VAR_SAMP

VARIANCE

数学函数

支持

+

支持字符串相加，只支持数值类型相加

-

只支持数值类型相减

/

只支持数值类型除法

*

只支持数值类型乘法

%

只支持数值类型Modulo

abs

acos

asin

atan

atan2

ceil

ceiling

conv

cos

cot

crc32

degrees

exp

floor

ln

log

log10

log2

mod

pi

pow

power

rand

round

sign

sin

sqrt

tan

truncate

DIV

不支持

RADIANS

日期函数

ADDDATE

SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);//'2008-02-02'
SELECT ADDDATE('2008-01-02', 31);//'2008-02-02'

ADDTIME

SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');//'2008-01-02 01:01:01.000001'
SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');//'03:00:01.999997'

CONVERT_TZ

SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');//'2004-01-01 13:00:00'
SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');//'2004-01-01 22:00:00'

CURDATE

CURRENT_DATE

SELECT CURDATE()//'2008-06-13'
SELECT CURDATE() + 0//20080613

CURTIME

SELECT CURTIME()//'23:50:26'
SELECT CURTIME() + 0;//235026.000000

NOW

CURRENT_TIMESTAMP

DATE

SELECT DATE('2003-12-31 01:02:03');//'2003-12-31'

DATEDIFF

SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');//1
SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');//-31

DATE_ADD

SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);//'2018-05-02'
 SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);//'2017-05-01'
 SELECT DATE_ADD('2020-12-31 23:59:59',INTERVAL 1 SECOND);//'2021-01-01 00:00:00'
 SELECT DATE_ADD('2018-12-31 23:59:59',INTERVAL 1 DAY);//'2019-01-01 23:59:59'
 SELECT DATE_ADD('2100-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND);//'2101-01-01 00:01:00'
SELECT DATE_SUB('2025-01-01 00:00:00',INTERVAL '1 1:1:1' DAY_SECOND);//'2024-12-30 22:58:59'
SELECT DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR);//'1899-12-30 14:00:00'
SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);//'1997-12-02'
SELECT DATE_ADD('1992-12-31 23:59:59.000002', INTERVAL '1.999999' SECOND_MICROSECOND);//'1993-01-01 00:00:01.000001'

DATE_FORMAT

SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');//'Sunday October 2009'
SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');//'22:23:00'
SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');//'4th 00 Thu 04 10 Oct 277'
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');//'22 22 10 10:23:00 PM 22:23:00 00 6'
SELECT DATE_FORMAT('1999-01-01', '%X %V');//'1998 52'
SELECT DATE_FORMAT('2006-06-00', '%d');//'00'

DATE_SUB

DAY

DAYOFMONTH

SELECT DAYOFMONTH('2007-02-03');//3

DAYNAME

SELECT DAYNAME('2007-02-03');//'Saturday'

DAYOFWEEK

SELECT DAYOFWEEK('2007-02-03');//7

DAYOFYEAR

SELECT DAYOFYEAR('2007-02-03');//34

EXTRACT

SELECT EXTRACT(YEAR FROM '2019-07-02');//2019
SELECT EXTRACT(YEAR_MONTH FROM '2019-07-02 01:02:03');//201907
SELECT EXTRACT(DAY_MINUTE FROM '2019-07-02 01:02:03');//20102
SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123');//123

FROM_DAYS

SELECT FROM_DAYS(730669);//2000-07-03

FROM_UNIXTIME

SELECT FROM_UNIXTIME(1447430881);//'2015-11-13 10:08:01'
SELECT FROM_UNIXTIME(1447430881) + 0;//20151113100801
 SELECT FROM_UNIXTIME(1447430881,'%Y %D %M %h:%i:%s %x');//'2015 13th November 10:08:01 2015'

GET_FORMAT

GET_FORMAT(DATE,'USA')    '%m.%d.%Y'
GET_FORMAT(DATE,'JIS')    '%Y-%m-%d'
GET_FORMAT(DATE,'ISO')    '%Y-%m-%d'
GET_FORMAT(DATE,'EUR')    '%d.%m.%Y'
GET_FORMAT(DATE,'INTERNAL')    '%Y%m%d'
GET_FORMAT(DATETIME,'USA')    '%Y-%m-%d %H.%i.%s'
GET_FORMAT(DATETIME,'JIS')    '%Y-%m-%d %H:%i:%s'
GET_FORMAT(DATETIME,'ISO')    '%Y-%m-%d %H:%i:%s'
GET_FORMAT(DATETIME,'EUR')    '%Y-%m-%d %H.%i.%s'
GET_FORMAT(DATETIME,'INTERNAL')    '%Y%m%d%H%i%s'
GET_FORMAT(TIME,'USA')    '%h:%i:%s %p'
GET_FORMAT(TIME,'JIS')    '%H:%i:%s'
GET_FORMAT(TIME,'ISO')    '%H:%i:%s'
GET_FORMAT(TIME,'EUR')    '%H.%i.%s'
GET_FORMAT(TIME,'INTERNAL')    '%H%i%s'
SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR'));//'03.10.2003'
SELECT STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA'));//'2003-10-31'

HOUR

SELECT HOUR('10:05:03');//10
SELECT HOUR('272:59:59');//272

LAST_DAY

SELECT LAST_DAY('2003-02-05');// '2003-02-28'
SELECT LAST_DAY('2004-02-05');//'2004-02-29'
SELECT LAST_DAY('2004-01-01 01:01:01');//'2004-01-31'
SELECT LAST_DAY('2003-03-32');//NULL

MAKEDATE

SELECT MAKEDATE(2011,31), MAKEDATE(2011,32);//'2011-01-31', '2011-02-01'
SELECT MAKEDATE(2011,365), MAKEDATE(2014,365);//'2011-12-31', '2014-12-31'
SELECT MAKEDATE(2011,0);//NULL

MAKETIME

SELECT MAKETIME(12,15,30);//'12:15:30'

MICROSECOND

SELECT MICROSECOND('12:00:00.123456');//123456
SELECT MICROSECOND('2019-12-31 23:59:59.000010');//10

MINUTE

SELECT MINUTE('2008-02-03 10:05:03');//5

MONTH

SELECT MONTH('2008-02-03');//2

MONTHNAME

SELECT MONTHNAME('2008-02-03');// 'February'

PERIOD_ADD

SELECT PERIOD_ADD(200801,2);//200803

PERIOD_DIFF

SELECT PERIOD_DIFF(200802,200703);//11

QUARTER

SELECT QUARTER('2008-04-01');//2

SECOND

SELECT SECOND('10:05:03');//3

SEC_TO_TIME

SELECT SEC_TO_TIME(2378);//'00:39:38'
SELECT SEC_TO_TIME(2378) + 0;//3938

STR_TO_DATE

SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');//'2013-05-01'
SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');//'2013-05-01' SELECT STR_TO_DATE('a09:30:17','a%h:%i:%s');//'09:30:17'
SELECT STR_TO_DATE('a09:30:17','%h:%i:%s');//NULL
SELECT STR_TO_DATE('09:30:17a','%h:%i:%s');//'09:30:17'
SELECT STR_TO_DATE('abc','abc');//'0000-00-00'
SELECT STR_TO_DATE('9','%m');//'0000-09-00'
SELECT STR_TO_DATE('9','%s');//'00:00:09'
SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');//'0000-00-00'
SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');//'2004-04-31'

DATE_SUB

SUBDATE

SELECT DATE_SUB('2008-01-02', INTERVAL 31 DAY);//'2007-12-02'
SELECT SUBDATE('2008-01-02', INTERVAL 31 DAY);//'2007-12-02'
SELECT SUBDATE('2008-01-02 12:00:00', 31);//'2007-12-02 12:00:00'

SUBTIME

SELECT SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002');//'2007-12-30 22:58:58.999997'
 SELECT SUBTIME('01:00:00.999999', '02:00:00.999998');//'-00:59:59.999999'

TIME

SELECT TIME('2003-12-31 01:02:03');//'01:02:03'
SELECT TIME('2003-12-31 01:02:03.000123');//'01:02:03.000123'

TIMEDIFF

SELECT TIMEDIFF('2000:01:01 00:00:00','2000:01:01 00:00:00.000001');//'-00:00:00.000001'
SELECT TIMEDIFF('2008-12-31 23:59:59.000001','2008-12-30 01:01:01.000002');//'46:58:57.999999'

TIMESTAMP

SELECT TIMESTAMP('2003-12-31');//'2003-12-31 00:00:00'
SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');//'2004-01-01 00:00:00'

TIMESTAMPADD

SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');//'2003-01-02 00:01:00'
SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');//'2003-01-09'

TIMESTAMPDIFF

SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');//3
SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');//-1
SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');//128885

TIME_FORMAT

SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');//'100 100 04 04 4'

TIME_TO_SEC

SELECT TIME_TO_SEC('22:23:00');//80580
SELECT TIME_TO_SEC('00:39:38');//2378

TO_DAYS

SELECT TO_DAYS(950501);//728779
SELECT TO_DAYS('2007-10-07');//733321
SELECT TO_DAYS('2008-10-07'), TO_DAYS('08-10-07');//733687, 733687
SELECT TO_DAYS('0000-00-00');//NULL
SELECT TO_DAYS('0000-01-01');//1

TO_SECONDS

SELECT TO_SECONDS(950501);//62966505600
SELECT TO_SECONDS('2009-11-29');//63426672000
SELECT TO_SECONDS('2009-11-29 13:43:32');//63426721412
SELECT TO_SECONDS( NOW() );//63426721458
SELECT TO_SECONDS('0000-00-00');//NULL
SELECT TO_SECONDS('0000-01-01');//8640

UNIX_TIMESTAMP

SELECT UNIX_TIMESTAMP();//1447431666
SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19');//1447431619
SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19.012');//1447431619.012

UTC_DATE

SELECT UTC_DATE(), UTC_DATE() + 0;//'2003-08-14', 20030814

UTC_TIME

SELECT UTC_TIME(), UTC_TIME() + 0;//'18:07:53', 180753.000000

UTC_TIMESTAMP

SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;//'2003-08-14 18:08:04', 20030814180804.000000

WEEK

SELECT WEEK('2008-02-20');//7
SELECT WEEK('2008-02-20',0);//8
SELECT WEEK('2008-02-20',1);//8
SELECT WEEK('2008-12-31',1);//53

WEEKDAY

SELECT WEEKDAY('2008-02-03 22:23:00');//6
SELECT WEEKDAY('2007-11-06');//1

WEEKOFYEAR

SELECT WEEKOFYEAR('2008-02-20');//8

YEAR

SELECT YEAR('1987-01-01');//1987

YEARWEEK

SELECT YEARWEEK('1987-01-01');//198652

转换函数

支持

binary

cast

其中
SELECT CAST (‘abc’ AS BINARY) FROM db1.travelrecord;
视为CAST (‘abc’ AS VARCHAR)

SELECT CAST(‘1’ AS UNSIGNED) FROM db1.travelrecord;
视为CAST (‘abc’ AS DECIMAL)

不支持

CONVERT(expr USING transcoding_name)

mycat内部运算统一是Unicode16

比较函数

支持

in

ISNULL

LEAST

NOT IN

STRCMP

不支持

COALESCE

GREATEST

字符串函数

支持

ASCII

BIN

BIT_LENGTH

CHAR

CHAR_LENGTH

CHARACTER_LENGTH

CONCAT

CONCAT_WS

ELT

EXPORT_SET

FIELD

FORMAT

FROM_BASE64

HEX

INSERT

INSTR

LCASE

LEFT

LENGTH

LIKE

LOCATE

LOWER

LPAD

LTRIM

MAKE_SET

MID

NOT LIKE

NOT REGEXP

OCT

OCTET_LENGTH

ORD

POSITION

QUOTE

REGEXP

REPEAT

REPLACE

REVERSE

RIGHT

RLIKE

RPAD

RTRIM

SOUNDEX

SPACE

STRCMP

SUBSTR

SUBSTRING

SUBSTRING_INDEX

TO_BASE64

TRIM

UCASE

UNHEX

UPPER

WEIGHT_STRING

不支持

FIND_IN_SET

MATCH

LOAD_FILE

SOUNDS LIKE

流程控制函数

支持

when case

if

ifnull

nullif

信息函数

支持

CONNECTION_ID

CURRENT_USER

DATABASE

LAST_INSERT_ID

SCHEMA

SESSION_USER

SYSTEM_USER

USER

VERSION

ROW_COUNT(1.15)

不支持

BENCHMARK

CHARSET

COERCIBILITY

COLLATION

FOUND_ROWS

不支持加密压缩函数
不支持位运算函数
不支持动态字段函数
不支持地理函数
不支持JSON函数
不支持Spider存储引擎函数

时间函数

sleep(1) 单位是秒,该函数会阻塞线程,生产不能使用,1.18支持

窗口函数

v1.17提供

不涉及跨数据库的情况(即单表与全局表),Mycat的窗口函数使用方式与MySQL完全一致.
在涉及跨数据库的情况下,MycatWindow不能下推变成SQL,所以在Mycat2内进行计算汇总结果.

语法

function OVER ([[partition by column1] [order by column2] [RANGE|ROWS BETWEEN start AND end]])

function OVER partition by column1 order by column2 RANGE BETWEEN start AND end
function OVER partition by column1 order by column2 ROWS BETWEEN start AND end

COUNT(value [, value ]) OVER
COUNT() OVER
AVG(numeric) OVER
SUM(numeric) OVER
MAX(value) OVER
MIN(value) OVER
RANK() OVER
DENSE_RANK() OVER
ROW_NUMBER() OVER
FIRST_VALUE(value) OVER
LAST_VALUE(value) OVER
LEAD(value, offset, default) OVER
LAG(value, offset, default) OVER
NTILE(value) OVER

具体语义参考
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html