Mycat2 Sql兼容性 数学函数

支持

• +: 支持字符串相加，只支持数值类型相加
• -: 只支持数值类型相减
• /: 只支持数值类型除法
• *: 只支持数值类型乘法
• %: 只支持数值类型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支持