[SQL兼容性]函数

SQL函数兼容性

前言

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

聚合函数

支持

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