/mycat2/
Mycat2 Sql兼容性 数学函数
  1. Mycat2 简介
    1. Mycat2 入门
    2. Mycat2 UI
    3. Mycat2 使用限制
    4. Mycat2 安装与启动
    5. Mycat2 应用场景
    6. Mycat2 权限管理
    7. Mycat2 软件架构
    8. Mycat2 软件环境要求
    9. Mycat2 XA事务注意点
    10. Mycat2 Native模式
    11. Mycat2 制作tar安装包
  2. Mycat2 客户端
    1. Mycat2 Loaddate 注解导入CSV数据
    2. Mycat2 连接测试
  3. Mycat2 配置指导
    1. Mycat2 注释配置
    2. Mycat2 配置
      1. Mycat2 使用zookeeper部署多个Mycat2
      2. Mycat2 集群 cluster
      3. Mycat2 数据源 datasource
      4. Mycat2 库 schema
      5. Mycat2 序列号 sequence
      6. Mycat2 服务器 server
      7. Mycat2 配置结构
  4. Mycat2 Sql兼容性
    1. Mycat2 Sql兼容性 简介
    2. Mycat2 Sql兼容性 update
    3. Mycat2 Sql兼容性 DDL
    4. Mycat2 Sql兼容性 delete
    5. Mycat2 Sql兼容性 insert
    6. Mycat2 Sql兼容性 replace
    7. Mycat2 Sql兼容性 select
    8. Mycat2 Sql兼容性 函数
      1. Mycat2 Sql兼容性 聚合函数
      2. Mycat2 Sql兼容性 数学函数
      3. Mycat2 Sql兼容性 窗口函数
    9. Mycat2 Sql兼容性 多语句
    10. Mycat2 Sql兼容性 数值类型
    11. Mycat2 Sql兼容性 服务器预处理
    12. Mycat2 Sql兼容性 管理命令
  5. Mycat2 数据分片
    1. Mycat2 分片算法简介
    2. Mycat2 ER关系
    3. Mycat2 Hash分片算法
      1. Mycat2 哈希分片 MOD_HASH
      2. Mycat2 哈希分片 RANGE_HASH
      3. Mycat2 哈希分片 RIGHT_SHIFT
      4. Mycat2 哈希分片 UNI_HASH
      5. Mycat2 哈希分片 WEEK
      6. Mycat2 哈希分片 YYYYDD
      7. Mycat2 哈希分片 YYYYMM
      8. Mycat2 哈希分片 DD
      9. Mycat2 哈希分片 YYYYWEEK
      10. Mycat2 哈希分片 MM
      11. Mycat2 哈希分片 MMDD
  6. Mycat2 SQL编写指导
    1. Mycat2 全局二级索引
    2. Mycat2 BKAJOIN(in)
    3. Mycat2 BKAJOIN(values)
    4. Mycat2 执行计划管理
    5. Mycat2 索引字段分析
    6. Mycat2 注释
    7. Mycat2 执行计划管理与注释
    8. Mycat2 优化器
    9. Mycat2 元信息统计
    10. Mycat2 Join下推
    11. Mycat2 聚合运算下推
    12. Mycat2 UnionAll
    13. Mycat2 分页排序 MergeSort
    14. Mycat2 Filter&Project&FieldTrimmer
    15. Mycat2 JoinClustering
    16. Mycat2 分区裁剪 Partition-Pruning
    17. Mycat2 路由控制注释
阅读(79) 赞(13)

Mycat2 Sql兼容性 数学函数

2021-09-08 11:50:52 更新

支持

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

← Mycat2 Sql兼容性 聚合函数
Mycat2 Sql兼容性 窗口函数 →

推荐文章

推荐教程

最新教程