日期时间函数
日期时间函数主要用来显示有关日期和时间的信息。
CURDATE
声明
CURDATE()
说明
返回当前日期,不含时间部分。
例子
OceanBase (root@test)> SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2018-05-05 |
+------------+
1 row in set (0.00 sec)
CURRENT_DATE
声明
CURRENT_DATE()
CURRENT_DATE
说明
同CURDATE()
。
CURRENT_TIME
声明
CURRENT_TIME([scale])
说明
返回当前时间,不含日期部分。
scale
有效值为0到6,表示微秒部分精度,默认值为0。
例子
OceanBase (root@test)> SELECT CURRENT_TIME(6);
+-----------------+
| CURRENT_TIME(6) |
+-----------------+
| 11:11:45.215311 |
+-----------------+
1 row in set (0.01 sec)
CURRENT_TIMESTAMP
声明
CURRENT_TIMESTAMP([scale])
说明
返回当前日期时间,考虑时区设置。
scale
有效值为0到6,表示微秒部分精度,默认值为0。
例子
OceanBase (root@test)> SELECT CURRENT_TIMESTAMP(6);
+----------------------------+
| CURRENT_TIMESTAMP(6) |
+----------------------------+
| 2018-05-05 11:35:39.177764 |
+----------------------------+
1 row in set (0.01 sec)
CURTIME
声明
CURTIME()
说明
同CURRENT_TIME()
。
DATE_ADD
声明
DATE_ADD(date, INTERVAL expr unit)
说明
日期时间的算术计算。
date
表示日期时间基准,日期部分必选,时间部分可选。expr
表示时间间隔,可以为负值。unit
表示时间间隔单位。
以下为所有时间间隔单位列表:
单位 | 类型 | 含义 | 格式 |
MICROSECOND | 独立 | 微秒 | MICROSECONDS |
SECOND | 独立 | 秒 | SECONDS |
MINUTE | 独立 | 分钟 | MINUTES |
HOUR | 独立 | 小时 | HOURS |
DAY | 独立 | 天 | DAYS |
WEEK | 独立 | 周 | WEEKS |
MONTH | 独立 | 月 | MONTHS |
QUARTER | 独立 | 季度 | QUARTERS |
YEAR | 独立 | 年 | YEARS |
SECOND_MICROSECOND | 组合 | 秒到微秒 | ‘SECONDS.MICROSECONDS’ |
MINUTE_MICROSECOND | 组合 | 分钟到微秒 | ‘MINUTES:SECONDS.MICROSECONDS’ |
MINUTE_SECOND | 组合 | 分钟到秒 | ‘MINUTES:SECONDS’ |
HOUR_MICROSECOND | 组合 | 小时到微秒 | ‘HOURS:MINUTES:SECONDS.MICROSECONDS’ |
HOUR_SECOND | 组合 | 小时到秒 | ‘HOURS:MINUTES:SECONDS’ |
HOUR_MINUTE | 组合 | 小时到分钟 | ‘HOURS:MINUTES’ |
DAY_SECOND | 组合 | 天到秒 | ‘DAYS HOURS:MINUTES:SECONDS’ |
DAY_MINUTE | 组合 | 天到分钟 | ‘DDAYSD HOURS:MINUTES’ |
DAY_HOUR | 组合 | 天到小时 | ‘DAYS HOURS’ |
YEAR_MONTH | 组合 | 年到月 | ‘YEARS-MONTHS’ |
例子
OceanBase (root@test)> SELECT
-> DATE_ADD(NOW(), INTERVAL 5 DAY),
-> DATE_ADD('2014-01-10', INTERVAL 5 MICROSECOND),
-> DATE_ADD('2014-01-10', INTERVAL 5 SECOND),
-> DATE_ADD('2014-01-10', INTERVAL 5 MINUTE),
-> DATE_ADD('2014-01-10', INTERVAL 5 HOUR),
-> DATE_ADD('2014-01-10', INTERVAL 5 DAY),
-> DATE_ADD('2014-01-10', INTERVAL 5 WEEK),
-> DATE_ADD('2014-01-10', INTERVAL 5 MONTH),
-> DATE_ADD('2014-01-10', INTERVAL 5 QUARTER),
-> DATE_ADD('2014-01-10', INTERVAL 5 YEAR),
-> DATE_ADD('2014-01-10', INTERVAL '5.000005' SECOND_MICROSECOND),
-> DATE_ADD('2014-01-10', INTERVAL '05:05.000005' MINUTE_MICROSECOND),
-> DATE_ADD('2014-01-10', INTERVAL '05:05' MINUTE_SECOND),
-> DATE_ADD('2014-01-10', INTERVAL '05:05:05.000005' HOUR_MICROSECOND),
-> DATE_ADD('2014-01-10', INTERVAL '05:05:05' HOUR_SECOND),
-> DATE_ADD('2014-01-10', INTERVAL '05:05' HOUR_MINUTE),
-> DATE_ADD('2014-01-10', INTERVAL '01 05:05:05.000005' DAY_MICROSECOND),
-> DATE_ADD('2014-01-10', INTERVAL '01 05:05:05' DAY_SECOND),
-> DATE_ADD('2014-01-10', INTERVAL '01 05:05' DAY_MINUTE),
-> DATE_ADD('2014-01-10', INTERVAL '01 05' DAY_HOUR),
-> DATE_ADD('2014-01-10', INTERVAL '1-01' YEAR_MONTH)
-> \G
*************************** 1. row ***************************
DATE_ADD(NOW(), INTERVAL 5 DAY): 2018-05-10 14:54:52
DATE_ADD('2014-01-10', INTERVAL 5 MICROSECOND): 2014-01-10 00:00:00.000005
DATE_ADD('2014-01-10', INTERVAL 5 SECOND): 2014-01-10 00:00:05
DATE_ADD('2014-01-10', INTERVAL 5 MINUTE): 2014-01-10 00:05:00
DATE_ADD('2014-01-10', INTERVAL 5 HOUR): 2014-01-10 05:00:00
DATE_ADD('2014-01-10', INTERVAL 5 DAY): 2014-01-15
DATE_ADD('2014-01-10', INTERVAL 5 WEEK): 2014-02-14
DATE_ADD('2014-01-10', INTERVAL 5 MONTH): 2014-06-10
DATE_ADD('2014-01-10', INTERVAL 5 QUARTER): 2015-04-10
DATE_ADD('2014-01-10', INTERVAL 5 YEAR): 2019-01-10
DATE_ADD('2014-01-10', INTERVAL '5.000005' SECOND_MICROSECOND): 2014-01-10 00:00:05.000005
DATE_ADD('2014-01-10', INTERVAL '05:05.000005' MINUTE_MICROSECOND): 2014-01-10 00:05:05.000005
DATE_ADD('2014-01-10', INTERVAL '05:05' MINUTE_SECOND): 2014-01-10 00:05:05
DATE_ADD('2014-01-10', INTERVAL '05:05:05.000005' HOUR_MICROSECOND): 2014-01-10 05:05:05.000005
DATE_ADD('2014-01-10', INTERVAL '05:05:05' HOUR_SECOND): 2014-01-10 05:05:05
DATE_ADD('2014-01-10', INTERVAL '05:05' HOUR_MINUTE): 2014-01-10 05:05:00
DATE_ADD('2014-01-10', INTERVAL '01 05:05:05.000005' DAY_MICROSECOND): 2014-01-11 05:05:05.000005
DATE_ADD('2014-01-10', INTERVAL '01 05:05:05' DAY_SECOND): 2014-01-11 05:05:05
DATE_ADD('2014-01-10', INTERVAL '01 05:05' DAY_MINUTE): 2014-01-11 05:05:00
DATE_ADD('2014-01-10', INTERVAL '01 05' DAY_HOUR): 2014-01-11 05:00:00
DATE_ADD('2014-01-10', INTERVAL '1-01' YEAR_MONTH): 2015-02-10
1 row in set (0.01 sec)
DATE_FORMAT
声明
DATE_FORMAT(date, format)
说明
将日期时间以指定格式输出。
date
表示日期时间。format
表示输出格式。
以下为所有输出格式列表:
格式符 | 含义 | 格式 |
%a | 星期的英文缩写。 | Sun..Sat |
%b | 月份的英文缩写。 | Jan..Dec |
%c | 月份的数字形式。 | 1..12 |
%D | 日的英文缩写。 | 1st..31st |
%d | 日的数字形式。 | 01..31 |
%e | 日的数字形式。 | 1.. 31 |
%f | 微秒。 | 000000..999999 |
%H | 小时。 | 00..23 |
%h | 小时。 | 01..12 |
%I | 小时。 | 01..12 |
%i | 分钟。 | 00..59 |
%j | 一年中的第几天。 | 001..366 |
%k | 小时。 | 0..23 |
%l | 小时。 | 0..12 |
%M | 月份的英文名称。 | January..December |
%m | 月份的数字形式。 | 01..12 |
%p | 上午或下午。 | AM/PM |
%r | 12小时制时间。 | hh:mm:ss AM/PM |
%S | 秒。 | 00..59 |
%s | 秒。 | 00..59 |
%T | 24小时制时间。 | hh:mm:ss |
%U | 一年中的第几周,周日为每周第一天。 | 00..53 |
%u | 一年中的第几周,周一为每周第一天。 | 00..53 |
%V | 一年中的第几周,周日为每周第一天(和%X 同时使用)。 | 01..53 |
%v | 一年中的第几周,周一为每周第一天(和%x 同时使用)。 | 01..53 |
%W | 星期的英文名称。 | Sunday..Saturday |
%w | 一周中的第几天。 | 0=Sunday..6=Saturday |
%X | 一周所属的年份,周日为每周第一天(和%V 同时使用)。 | |
%x | 一周所属的年份,周一为每周第一天(和%v 同时使用)。 | |
%Y | 四位年份。 | |
%y | 两位年份。 | |
%% | 文字字符,输出一个% 。 |
例子
OceanBase (root@test)> SELECT
-> DATE_FORMAT('2014-01-01', '%Y-%M-%d'),
-> DATE_FORMAT('2014-01-01', '%X-%V'),
-> DATE_FORMAT('2014-01-01', '%U')
-> \G
*************************** 1. row ***************************
DATE_FORMAT('2014-01-01', '%Y-%M-%d'): 2014-January-01
DATE_FORMAT('2014-01-01', '%X-%V'): 2013-52
DATE_FORMAT('2014-01-01', '%U'): 00
1 row in set (0.01 sec)
DATE_SUB
声明
DATE_SUB(date, INTERVAL expr unit)
说明
日期时间的算术计算。
请参考DATE_ADD()
。
DATEDIFF
声明
DATEDIFF(date1, date2)
说明
返回date1
和date2
之间的天数。
计算中只用到参数的日期部分,忽略时间部分。
例子
OceanBase (root@test)> SELECT DATEDIFF('2015-06-19','1994-12-17');
+-------------------------------------+
| DATEDIFF('2015-06-19','1994-12-17') |
+-------------------------------------+
| 7489 |
+-------------------------------------+
1 row in set (0.01 sec)
EXTRACT
声明
EXTRACT(unit FROM date)
说明
以整数类型返回date
的指定部分值。如果指定多个部分,则将所有值按顺序拼接。
unit
请参考DATE_ADD()
。当unit
为WEEK
时,请参考DATE_FORMAT()
的%U
说明。
例子
SELECT EXTRACT(WEEK FROM '2013-01-01'),
EXTRACT(WEEK FROM '2013-01-06'),
EXTRACT(YEAR_MONTH FROM '2012-03-09'),
EXTRACT(DAY FROM NOW())\G;
* 1. row *
EXTRACT(WEEK FROM '2013-01-01'): 0
EXTRACT(WEEK FROM '2013-01-06'): 1
EXTRACT(YEAR_MONTH FROM '2012-03-09'): 201203
EXTRACT(DAY FROM NOW()): 18
1 row in set (0.00 sec)
FROM_DAYS
声明
FROM_DAYS(N)
说明
返回指定天数N
对应的DATE
值。天数指距离0000-01-01
的天数。
例子
OceanBase (root@test)> SELECT FROM_DAYS(736271), FROM_DAYS(700000);
+-------------------+-------------------+
| FROM_DAYS(736271) | FROM_DAYS(700000) |
+-------------------+-------------------+
| 2015-11-04 | 1916-07-15 |
+-------------------+-------------------+
1 row in set (0.00 sec)
FROM_UNIXTIME
声明
FROM_UNIXTIME(unix_timestamp)
FROM_UNIXTIME(unix_timestamp, format)
说明
- 不指定
format
参数时,返回DATETIME
类型值,不考虑时区。 - 指定
format
参数时,返回指定格式的日期时间字符串。
unix_timestamp
指UNIX时间戳,即距离1970-01-01 00:00:00.000000
的微秒数。
format
支持的格式已在DATE_FORMAT()
说明中列出。
例子
OceanBase (root@test)> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x');
+---------------------------------------------------------+
| FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x') |
+---------------------------------------------------------+
| 2018 5th May 08:41:26 2018 |
+---------------------------------------------------------+
1 row in set (0.01 sec)
MONTH
声明
MONTH(date)
说明
返回date
的月份信息。
例子
OceanBase (root@test)> SELECT MONTH('2008-02-03');
+---------------------+
| MONTH('2008-02-03') |
+---------------------+
| 2 |
+---------------------+
1 row in set (0.01 sec)
NOW
声明
NOW([scale])
说明
同CURRENT_TIMESTAMP()
。
PERIOD_DIFF
声明
PERIOD_DIFF(p1, p2)
说明
以月份位单位返回两个日期之间的间隔。日期只能包含年月信息,格式只能为YYYYMM
或YYMM
。
例子
OceanBase (root@test)> SELECT PERIOD_DIFF(200802, 200703);
+----------------------------+
| PERIOD_DIFF(200802,200703) |
+----------------------------+
| 11 |
+----------------------------+
1 row in set (0.01 sec)
STR_TO_DATE
声明
STR_TO_DATE(str, format)
说明
使用format
将str
转换为DATETIME
值、DATE
值、或TIME
值。返回值类型取决于format
包含日期时间的哪些部分。
format
支持的格式已在DATE_FORMAT()
说明中列出。
例子
OceanBase (root@test)> SELECT STR_TO_DATE('2014-Jan-1st 5:5:5 pm', '%Y-%b-%D %r');
+-----------------------------------------------------+
| STR_TO_DATE('2014-Jan-1st 5:5:5 pm', '%Y-%b-%D %r') |
+-----------------------------------------------------+
| 2014-01-01 05:05:05 |
+-----------------------------------------------------+
1 row in set (0.01 sec)
TIME
声明
TIME(datetime)
说明
以TIME
类型返回datetime
的时间信息。
例子
OceanBase (root@test)> SELECT TIME('2003-12-31 01:02:03');
+-----------------------------+
| TIME('2003-12-31 01:02:03') |
+-----------------------------+
| 01:02:03.000000 |
+-----------------------------+
1 row in set (0.01 sec)
TIME_TO_USEC
声明
TIME_TO_USEC(date)
说明
将date
值转换为距离1970-01-01 00:00:00.000000
的微秒数,考虑时区信息。
date
可以表示日期,也可以表示日期时间。
例子
OceanBase (root@test)> SELECT TIME_TO_USEC('2014-03-25'), TIME_TO_USEC(NOW());
+----------------------------+---------------------+
| TIME_TO_USEC('2014-03-25') | TIME_TO_USEC(NOW()) |
+----------------------------+---------------------+
| 1395676800000000 | 1525528100000000 |
+----------------------------+---------------------+
1 row in set (0.01 sec)
TIMEDIFF
声明
TIMEDIFF(date1, date2)
说明
以TIME
类型返回两个日期时间的时间间隔。
例子
OceanBase (root@test)> SELECT
-> TIMEDIFF('2015-06-06 12:12:12', '2014-06-05 11:11:11'),
-> TIMEDIFF('2015-06-06 12:12:12', '2015-06-05 11:11:11')
-> \G
*************************** 1. row ***************************
TIMEDIFF('2015-06-06 12:12:12', '2014-06-05 11:11:11'): 838:59:59
TIMEDIFF('2015-06-06 12:12:12', '2015-06-05 11:11:11'): 25:01:01
1 row in set (0.00 sec)
TIMESTAMPDIFF
声明
TIMESTAMPDIFF(unit, date1, date2)
说明
以unit
为单位返回两个日期时间的间隔。unit
只能为DATE_ADD()
中的独立单位。
例子
OceanBase (root@test)> SELECT
-> TIMESTAMPDIFF(SECOND, NOW(), '2011-01-01 11:11:11'),
-> TIMESTAMPDIFF(DAY, '2011-01-01 11:11:11', NOW())
-> \G
*************************** 1. row ***************************
TIMESTAMPDIFF(SECOND, NOW(), '2011-01-01 11:11:11'): -231677498
TIMESTAMPDIFF(DAY, '2011-01-01 11:11:11', NOW()): 2681
1 row in set (0.00 sec)
TIMESTAMPADD
声明
TIMESTAMPADD(unit, interval_expr, date)
说明
日期时间的算术计算。
同DATE_ADD()
,但unit
只能为独立单位。
例子
OceanBase (root@test)> SELECT
-> TIMESTAMPADD(DAY, -5, '2010-01-01 00:00:00'),
-> DATE_ADD('2010-01-01 00:00:00', INTERVAL -5 DAY)
-> \G
*************************** 1. row ***************************
TIMESTAMPADD(DAY, -5, '2010-01-01 00:00:00'): 2009-12-27 00:00:00
DATE_ADD('2010-01-01 00:00:00', INTERVAL -5 DAY): 2009-12-27 00:00:00
1 row in set (0.01 sec)
TO_DAYS
声明
TO_DAYS(date)
说明
返回指定date
值对应的天数。天数指距离0000-01-01
的天数。
例子
OceanBase (root@test)> SELECT TO_DAYS('2015-11-04'), TO_DAYS('20151104');
+-----------------------+---------------------+
| TO_DAYS('2015-11-04') | TO_DAYS('20151104') |
+-----------------------+---------------------+
| 736271 | 736271 |
+-----------------------+---------------------+
1 row in set (0.01 sec)
USEC_TO_TIME
声明
USEC_TO_TIME(usec)
说明
将usec
值转换为TIMESTAMP
类型值。
usec
表示距离1970-01-01 00:00:00.000000
的微秒数,考虑时区信息。
例子
OceanBase (root@test)> SELECT USEC_TO_TIME(1);
+----------------------------+
| USEC_TO_TIME(1) |
+----------------------------+
| 1970-01-01 08:00:00.000001 |
+----------------------------+
1 row in set (0.00 sec)
UNIX_TIMESTAMP
声明
UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)
说明
- 不指定
date
参数时,返回当前时间距离’1970-01-01 00:00:00’的秒数,考虑时区。 - 指定
date
参数时,返回指定时间距离’1970-01-01 00:00:00’的秒数,考虑时区。
例子
OceanBase (root@test)> SELECT UNIX_TIMESTAMP(), TIME_TO_USEC(NOW());
+------------------+---------------------+
| UNIX_TIMESTAMP() | TIME_TO_USEC(NOW()) |
+------------------+---------------------+
| 1525570561 | 1525570561000000 |
+------------------+---------------------+
1 row in set (0.01 sec)
OceanBase (root@test)> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
+---------------------------------------+
| UNIX_TIMESTAMP('1997-10-04 22:23:00') |
+---------------------------------------+
| 875974980 |
+---------------------------------------+
1 row in set (0.01 sec)
UTC_TIMESTAMP
声明
UTC_TIMESTAMP()
说明
返回当前UTC时间。
例子
OceanBase (root@test)> SELECT UTC_TIMESTAMP();
+---------------------+
| UTC_TIMESTAMP() |
+---------------------+
| 2018-05-06 01:38:32 |
+---------------------+
1 row in set (0.01 sec)
YEAR
声明
YEAR(date)
说明
返回date
值的年份信息。
例子
OceanBase (root@test)> SELECT YEAR('1987-01-01');
+--------------------+
| YEAR('1987-01-01') |
+--------------------+
| 1987 |
+--------------------+
1 row in set (0.00 sec)
字符串函数
CONCAT
声明
CONCAT(str1, .., strN)
说明
把多个字符串连接成一个字符串。参数中包含NULL
时,返回NULL
。
例子
OceanBase (root@test)> SELECT
-> CONCAT('test','OceanBase', '1.0'),
-> CONCAT('test','OceanBase', NULL)
-> \G
*************************** 1. row ***************************
CONCAT('test','OceanBase', '1.0'): testOceanBase1.0
CONCAT('test','OceanBase', NULL): NULL
1 row in set (0.01 sec)
CONCAT_WS
声明
CONCAT_WS(separator, str1, .., strN)
说明
把多个字符串连接成一个字符串,相邻字符串间使用separator
分隔。参数中包含NULL
时,将忽略NULL
。
例子
OceanBase (root@test)> SELECT
-> CONCAT_WS('_', 'First', 'Second'),
-> CONCAT_WS('_', 'First', NULL, 'Second')
-> \G
*************************** 1. row ***************************
CONCAT_WS('_', 'First', 'Second'): First_Second
CONCAT_WS('_', 'First', NULL, 'Second'): First_Second
1 row in set (0.00 sec)
SUBSTR
声明
SUBSTR(str, pos)
SUBSTR(str, pos, len)
SUBSTR(str FROM pos)
SUBSTR(str FROM pos FOR len)
说明
返回str
的子字符串,起始位置为pos
,长度为len
。参数中包含NULL
时,返回NULL
。
不指定
len
时,返回的子字符串从pos
位置开始到str
结尾。pos
值为负数时,从str
尾部向头部倒序确定起始位置。len
小于等于0,或pos
指定的起始位置非法时,返回空字符串。
例子
OceanBase (root@test)> SELECT
-> SUBSTR('abcdefg', 3),
-> SUBSTR('abcdefg', 3, 2),
-> SUBSTR('abcdefg', -3),
-> SUBSTR('abcdefg', 3, -2),
-> SUBSTR('abcdefg' from -4 for 2)
-> \G
*************************** 1. row ***************************
SUBSTR('abcdefg', 3): cdefg
SUBSTR('abcdefg', 3, 2): cd
SUBSTR('abcdefg', -3): efg
SUBSTR('abcdefg', 3, -2):
SUBSTR('abcdefg' from -4 for 2): de
1 row in set (0.01 sec)
SUBSTRING
声明
SUBSTRING(str, pos)
SUBSTRING(str, pos, len)
SUBSTRING(str FROM pos)
SUBSTRING(str FROM pos FOR len)
说明
同SUBSTR
。
TRIM
声明
TRIM([[{BOTH | LEADING | TRAILING}] [remstr] FROM] str)
说明
删除字符串所有前缀和/或后缀,默认为BOTH
。参数中包含NULL
时,返回NULL
。
例子
OceanBase (root@test)> SELECT
-> TRIM(' bar '),
-> TRIM(LEADING 'x' FROM 'xxxbarxxx'),
-> TRIM(BOTH 'x' FROM 'xxxbarxxx'),
-> TRIM(TRAILING 'x' FROM 'xxxbarxxx')
-> \G
*************************** 1. row ***************************
TRIM(' bar '): bar
TRIM(LEADING 'x' FROM 'xxxbarxxx'): barxxx
TRIM(BOTH 'x' FROM 'xxxbarxxx'): bar
TRIM(TRAILING 'x' FROM 'xxxbarxxx'): xxxbar
1 row in set (0.01 sec)
LTRIM
声明
LTRIM(str)
说明
删除字符串左侧的空格。
RTRIM
声明
RTRIM(str)
说明
删除字符串右侧的空格。
ASCII
声明
ASCII(str)
说明
返回字符串最左侧字符的ASCII码。
ORD
声明
ORD(str)
说明
返回字符串最左侧字符的字符码。单字节编码如果最左侧字符是多字节编码字符,返回值计算规则如下:
(1st byte code)
+ (2nd byte code * 256)
+ (3rd byte code * 256^2) ...
例子
OceanBase (root@test)> SELECT ORD('中国');
+---------------+
| ORD('中国') |
+---------------+
| 14989485 |
+---------------+
1 row in set (0.01 sec)
LENGTH
声明
LENGTH(str)
说明
返回str
的字节长度。
例子
OceanBase (root@test)> SELECT LENGTH('中国'), LENGTH('hello');
+------------------+-----------------+
| LENGTH('中国') | LENGTH('hello') |
+------------------+-----------------+
| 6 | 5 |
+------------------+-----------------+
1 row in set (0.01 sec)
CHAR_LENGTH
声明
CHAR_LENGTH(str)
说明
返回字符串包含的字符数。
例子
OceanBase (root@test)> SELECT CHAR_LENGTH('中国'), CHAR_LENGTH('hello');
+-----------------------+----------------------+
| CHAR_LENGTH('中国') | CHAR_LENGTH('hello') |
+-----------------------+----------------------+
| 2 | 5 |
+-----------------------+----------------------+
1 row in set (0.00 sec)
UPPER
声明
UPPER(str)
说明
将字符串中的小写字母转化为大写字母。
例子
OceanBase (root@test)> SELECT UPPER('OceanBase您好!');
+-----------------------------+
| UPPER('OceanBase您好!') |
+-----------------------------+
| OCEANBASE您好! |
+-----------------------------+
1 row in set (0.01 sec)
LOWER
声明
LOWER(str)
说明
将字符串中的大写字母转化为小写字母。
例子
OceanBase (root@test)> SELECT LOWER('OceanBase您好!');
+-----------------------------+
| LOWER('OceanBase您好!') |
+-----------------------------+
| oceanbase您好! |
+-----------------------------+
1 row in set (0.01 sec)
HEX
声明
HEX(str)
说明
将数字或字符串转化为十六进制字符串。
例子
OceanBase (root@test)> SELECT HEX(255), HEX('abc');
+----------+------------+
| HEX(255) | HEX('abc') |
+----------+------------+
| FF | 616263 |
+----------+------------+
1 row in set (0.00 sec)
UNHEX
声明
UNHEX(str)
说明
将十六进制字符串转化为正常字符串。
例子
OceanBase (root@test)> SELECT UNHEX('4f6365616e42617365');
+-----------------------------+
| UNHEX('4f6365616e42617365') |
+-----------------------------+
| OceanBase |
+-----------------------------+
1 row in set (0.00 sec)
MD5
声明
MD5(str)
说明
返回字符串的MD5值。
例子
OceanBase (root@test)> SELECT MD5(1);
+----------------------------------+
| MD5(1) |
+----------------------------------+
| c4ca4238a0b923820dcc509a6f75849b |
+----------------------------------+
1 row in set (0.00 sec)
INT2IP
声明
INT2IP(int_value)
说明
将整数内码转换成IP地址。
例子
OceanBase (root@test)> SELECT
-> INT2IP(16777216),
-> HEX(16777216),
-> INT2IP(1)
-> \G
*************************** 1. row ***************************
INT2IP(16777216): 1.0.0.0
HEX(16777216): 1000000
INT2IP(1): 0.0.0.1
1 row in set (0.01 sec)
IP2INT
声明
IP2INT('ip_addr')
说明
将IP地址转换成整数内码。
例子
OceanBase (root@test)> SELECT
-> IP2INT('0.0.0.1'),
-> HEX(IP2INT('0.0.0.1')),
-> HEX(IP2INT('1.0.0.0'))
-> \G
*************************** 1. row ***************************
IP2INT('0.0.0.1'): 1
HEX(IP2INT('0.0.0.1')): 1
HEX(IP2INT('1.0.0.0')): 1000000
1 row in set (0.01 sec)
LIKE
声明
str1 [NOT] LIKE str2 [ESCAPE str3]
说明
字符串通配符匹配。参数中包含NULL
时,返回NULL
。
通配符包括:
%
:匹配任意长度字符串。_
:匹配单个字符。
ESCAPE用于定义转义符,如果str2中包含str3,在匹配时str3后的字符将做为普通字符处理。
例子
OceanBase (root@test)> SELECT 'ab%' LIKE 'abc%' ESCAPE 'c';
+------------------------------+
| 'ab%' LIKE 'abc%' ESCAPE 'c' |
+------------------------------+
| 1 |
+------------------------------+
1 row in set (0.01 sec)
REGEXP
声明
str [NOT] REGEXP | RLIKE pat
说明
正则匹配。参数中包含NULL
时,返回NULL
。
例子
OceanBase (root@test)> SELECT
-> 1234 REGEXP 1,
-> 'hello' RLIKE 'h%'
-> \G
*************************** 1. row ***************************
1234 REGEXP 1: 1
'hello' RLIKE 'h%': 0
1 row in set (0.01 sec)
REPEAT
声明
REPEAT(str, count)
说明
返回str
重复count
次组成的字符串。count
小于等于0时,返回空字符串。参数中包含NULL
时,返回NULL
。
例子
OceanBase (root@test)> SELECT
-> REPEAT('1', -1),
-> REPEAT(null, null),
-> REPEAT('Abc', 4)
-> \G
*************************** 1. row ***************************
REPEAT('1', -1):
REPEAT(null, null): NULL
REPEAT('Abc', 4): AbcAbcAbcAbc
1 row in set (0.01 sec)
SPACE
声明
SPACE(N)
说明
返回包含N
个空格的字符串。
SUBSTRING_INDEX
声明
SUBSTRING_INDEX(str, delim, count)
说明
在定界符 delim 以及 count 出现前,从字符串 str 返回字符串。若 count 为正值,则返回最终定界符(从左边开始)左边的一切内容。若 count 为负值,则返回定界符(从右边开始)右边的一切内容。任意一个参数为NULL,返回NULL;若 str 或 delim 为空字符串,则返回空串;若 count = 0, 返回空串。
str, delim, count 参数支持数值与字符串的隐式转换。
例子
Oceanbase>select substring_index('abcdabc', 'abc', 0), substring_index('abcdabc', 'abc', 1), substring_index('abcdabc', 'abc', 2), substring_index('abcdabc', 'abc', 3), substring_index('abcdabc', 'abc', -1), substring_index('abcdabc', 'abc', -2), substring_index('abcdabc', 'abc', -3)\G;
* 1. row *
substring_index('abcdabc', 'abc', 0):
substring_index('abcdabc', 'abc', 1):
substring_index('abcdabc', 'abc', 2): abcd
substring_index('abcdabc', 'abc', 3): abcdabc
substring_index('abcdabc', 'abc', -1):
substring_index('abcdabc', 'abc', -2): dabc
substring_index('abcdabc', 'abc', -3): abcdabc
1 row in set (0.00 sec)
LOCATE
声明
LOCATE(substr,str) , LOCATE(substr,str,pos)
说明
第一个语法返回字符串 str 中子字符串 substr 的第一个出现位置。第二个语法返回字符串 str 中子字符串 substr 的第一个出现位置, 起始位置在 pos。如若 substr 不在 str 中,则返回值为 0。
例子
Oceanbase>SELECT LOCATE('bar', 'foobarbar');
-> 4
Oceanbase>SELECT LOCATE('xbar', 'foobar');
-> 0
Oceanbase>SELECT LOCATE('bar', 'foobarbar',5);
-> 7
POSITION
声明
POSITION(substr IN str)
说明
与LOCATE同义。
INSTR
声明
INSTR(str,substr)
说明
返回字符串 str 中子字符串的第一个出现位置。这和 LOCATE() 的双参数形式相同,除非参数的顺序被颠倒。
例子
Oceanbase>SELECT INSTR('foobarbar', 'bar');
-> 4
Oceanbase>SELECT INSTR('xbar', 'foobar');
-> 0
REPLACE
声明
REPLACE(str, from_str, to_str)
说明
返回字符串 str 以及所有被字符 to_str 替代的字符串 from_str。
例子
Oceanbase>SELECT REPLACE('abc.efg.gpg.nowdew.abc.dabc.e', 'abc.', 'www');
+---------------------------------------------------------+
| REPLACE('abc.efg.gpg.nowdew.abc.dabc.e', 'abc.', 'www') |
+---------------------------------------------------------+
| wwwefg.gpg.nowdew.wwwdwwwe |
+---------------------------------------------------------+
1 row in set (0.00 sec)
FIELD
声明
FIELD(str,str1,str2,str3,…)
说明
返回参数 str 在 str1, str2, str3,… 列表中的索引位置(从1开始的位置)。在找不到 str 的情况下,返回值为0。
如果所有的对于 FIELD() 的参数均为字符串,则所有参数均按照字符串进行比较。如果所有的参数均为数字,则按照数字进行比较。否则,参数均按照 double 类型进行比较。
如果 str 为 NULL,则返回值为 0,原因是 NULL 不能同任何值进行同等比较。FILED() 是 ELT() 的补数。
例子
Oceanbase>select field('abc','abc1','abc2','abc','abc4','abc'), field(NULL, 'null1', NULL);
+-----------------------------------------------+----------------------------+
| field('abc','abc1','abc2','abc','abc4','abc') | field(NULL, 'null1', NULL) |
+-----------------------------------------------+----------------------------+
| 3 | 0 |
+-----------------------------------------------+----------------------------+
1 row in set (0.00 sec)
ELT
声明
ELT(N, str1, str2, str3,…)
说明
若 N=1,则返回值为 str1, 若 N=2,则返回值为 str2,以此类推。若 N 小于 1 或大于参数的数目,则返回值为NULL。ELT() 是 FIELD() 的补数。
例子
Oceanbase>select elt(3, 'abc1', 'abc2', 'abc', 'abc4', 'abc'), elt(0, 'null1', NULL);
+----------------------------------------------+-----------------------+
| elt(3, 'abc1', 'abc2', 'abc', 'abc4', 'abc') | elt(0, 'null1', NULL) |
+----------------------------------------------+-----------------------+
| abc | NULL |
+----------------------------------------------+-----------------------+
1 row in set (0.00 sec)
INSERT
声明
INSERT (str1,pos,len,str2)
说明
返回字符串 str1, 字符串中起始于 pos 位置,长度为 len 的子字符串将被 str2 取代。如果 pos 超过字符串长度,则返回值为原始字符串。 假如 len 的长度大于其它字符串的长度,则从位置 pos 开始替换。若任何一个参数为 null,则返回值为 NULL。这个函数支持多字节字元。
str1 和 str2 必须是字符串,pos 和 len 必须是整数。任意参数为 NULL,结果总为 NULL。
str1 和 str2 中的文字符被当做字节流看待。
pos 值为负数或者大于 str1 长度时,返回 str1;
- 当 len 小于 0,或大于 str1 的长度时,返回结果为 str1 从开头到 pos 位置的串,和 str2 的组合串。
例子
Oceanbase>select insert('Quadratic',-2,100,'What'), insert('Quadratic',7,3,'What'),
-> insert('Quadratic',-1,3,'What'), insert('Quadratic',10,3,'What'), insert('Quadratic',5,-1,''),
-> insert('Quadratic',7,-1,'What')\G;
* 1. row *
insert('Quadratic',-2,100,'What'): Quadratic
insert('Quadratic',7,3,'What'): QuadraWhat
insert('Quadratic',-1,3,'What'): Quadratic
insert('Quadratic',10,3,'What'): Quadratic
insert('Quadratic',5,-1,''): Quad
insert('Quadratic',7,-1,'What'): QuadraWhat
1 row in set (0.01 sec)
LPAD
声明
LPAD(str, len, padstr)
说明
用指定字符串padstr,在左侧填充字符串str到指定长度len。如果str长度大于len,则str被截断。
例子
OceanBase > SELECT LPAD('hi',4,'??');
+-------------------+
| LPAD('hi',4,'??') |
+-------------------+
| ??hi |
+-------------------+
1 row in set (0.01 sec)
OceanBase > SELECT LPAD('hi',1,'??');
+-------------------+
| LPAD('hi',1,'??') |
+-------------------+
| h |
+-------------------+
1 row in set (0.00 sec)
RPAD
声明
RPAD(str, len, padstr)
说明
用指定字符串padstr,在右侧填充字符串str到指定长度len。如果str长度大于len,则str被截断。
例子
OceanBase (root@test)> SELECT RPAD('hi',4,'??');
+-------------------+
| RPAD('hi',4,'??') |
+-------------------+
| hi?? |
+-------------------+
1 row in set (0.00 sec)
OceanBase (root@test)> SELECT RPAD('hi',1,'??');
+-------------------+
| RPAD('hi',1,'??') |
+-------------------+
| h |
+-------------------+
1 row in set (0.00 sec)
UUID
声明
uuid()
说明
生成一个全局唯一ID。
例子
OceanBase (root@test)> select uuid();
+--------------------------------------+
| uuid() |
+--------------------------------------+
| f756a1f6-4de6-11e8-90af-90b11c53e421 |
+--------------------------------------+
1 row in set (0.00 sec)
BIN
声明
bin(N)
说明
返回数字N的二进制形式。
例子
OceanBase > SELECT BIN(12);
+---------+
| BIN(12) |
+---------+
| 1100 |
+---------+
1 row in set (0.00 sec)
QUOTE
声明
quote(str)
说明
引用一个字符串以产生一个结果可以作为SQL语句中正确地转义数据值。返回的字符串由单引号和单引号的各个实例封闭(‘’),反斜线(‘’),ASCII NUL 和 Control-Z前面加一个反斜杠。如果参数为NULL,则返回值是词 ‘NULL’ 不包围单引号。
例子
OceanBase > SELECT QUOTE('Don\'t!');
+------------------+
| QUOTE('Don\'t!') |
+------------------+
| 'Don\'t!' |
+------------------+
1 row in set (0.00 sec)
OceanBase > SELECT QUOTE(NULL);
+-------------+
| QUOTE(NULL) |
+-------------+
| NULL |
+-------------+
1 row in set (0.00 sec)
REGEXP_SUBSTR
声明
regexp_substr(str,pattern,[position[,occurrence[,match_param[,subexpr]]]])
说明
在str中搜索匹配正则表达式pattern的子串,子串不存在返回NULL。这个函数支持多字节字元。除match_param外,其他任意参数为NULL,结果为NULL。
str 待搜索的字符串,支持多字节字元。
pattern 正则表达式,正则表达式规则兼容mysql。
position [可选] 搜索的开始位置。必须是大于0的正整数,小于等于0报错,输入为NULL返回NULL,默认值为1,表示从第一个字符开始搜索。
occurrence [可选] 返回第几次匹配的值。必须是大于等于0的正整数,小于等于0报错,输入为NULL返回NULL,默认值是1,表示返回第一次匹配的结果。
match_param [可选] 搜索参数,字符串类型,目前仅支持两个字符 ‘i’,‘c’,i: 代表忽略大小写 c: 代表不忽略,其他字符报错。默认值根据str字符集的collection决定。 为NULL时使用默认值。
- subexpr [可选] 返回正则表达式中第几个组的值。大于等于0的整数,小于0则报错。默认值是0,表示返回满足整个pattern的子串。
例子
oceanbase> select regexp_substr('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 1) from dual;
+----------------------------------------------------------------------------------+
| regexp_substr('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 1) |
+----------------------------------------------------------------------------------+
| have |
+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
oceanbase> select regexp_substr('foothebar', 'foo(.*)(bar)', 1, 1, 'c', 1) from dual;
+----------------------------------------------------------+
| regexp_substr('foothebar', 'foo(.*)(bar)', 1, 1, 'c', 1) |
+----------------------------------------------------------+
| the |
+----------------------------------------------------------+
1 row in set (0.01 sec)
转换函数
CAST
声明
CAST(expr AS type)
说明
将某种数据类型的表达式显式转换为另一种数据类型。
将 expr 字段值转换为 type 数据类型。
参数说明:
expr : 表示任何有效的SQL表达式。
AS : 用于分隔两个参数,在AS之前的是要处理的数据,在AS之后是要转换的数据类型。
type : 表示目标系统所提供的数据类型。可以是以下值其中的一个:
CHAR[(N)] (CHAR[N]会使 cast 使用该参数的不多于N 个字符)
DATE
DATETIME
DECIMAL
SIGNED [INTEGER]
TIME
- UNSIGNED [INTEGER]
在使用CAST函数进行数据类型转换时,在下列情况下能够被接受:
两个表达式的数据类型完全相同;
两个表达式可隐式转换;
- 必须显式转换数据类型。
如果试图进行不可能的转换,OceanBase将显示一条错误信息。
如果转换时没有指定数据类型的长度,则使用 OceanBase系统内部最大长度。如varchar是262,143字节,number是65个bit位的浮动精度。
支持带符号和无符号的64比特值的运算。若你正在使用数字操作符 (如 +) 而其中一个操作数为无符号整数,则结果为无符号。可使用SIGNED 和UNSIGNED cast操作符来覆盖它。将运算分别派给带符号或无符号64比特整数。
假如任意一个操作数为一个浮点值,则结果为一个浮点值。
例子
Oceanbase>SELECT CAST(123 AS BOOL);
+-------------------+
| CAST(123 AS bool) |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.00 sec)
Oceanbase>select cast(1-2 as unsigned), cast(cast(1-2 as unsigned) as signed);
+-----------------------+---------------------------------------+
| cast(1-2 as unsigned) | cast(cast(1-2 as unsigned) as signed) |
+-----------------------+---------------------------------------+
| 18446744073709551615 | -1 |
+-----------------------+---------------------------------------+
1 row in set (0.00 sec)
Oceanbase>SELECT CAST(1 AS UNSIGNED) - 2.0;
+---------------------------+
| CAST(1 AS UNSIGNED) - 2.0 |
+---------------------------+
| -1.0 |
+---------------------------+
1 row in set (0.00 sec)
Oceanbase>select cast(0 as date);
+-----------------+
| cast(0 as date) |
+-----------------+
| 0000-00-00 |
+-----------------+
1 row in set (0.00 sec)
数学函数
ROUND
声明
ROUND(X), ROUND(X,D)
说明
返回一个数值,四舍五入到指定的长度或精度。
返回参数X, 其值接近于最近似的整数。在有两个参数的情况下,返回X,其值保留到小数点后D位,而第D位的保留方式为四舍五入。若要接保留X值小数点左边的D位,可将D设为负值。
返回值的类型同第一个自变量相同(假设它是一个整数、双精度数或小数)。这意味着对于一个整数参数,结果也是一个整数(无小数部分)。
- 对于准确值数字,ROUND()使用“四舍五入” 或“舍入成最接近的数” 的规则:对于一个分数部分为 .5或大于 .5的值,正数则上舍入到邻近的整数值,负数则下舍入临近的整数值。(换言之, 其舍入的方向是数轴上远离零的方向)。对于一个分数部分小于.5 的值,正数则下舍入下一个整数值,负数则下舍入邻近的整数值,而正数则上舍入邻近的整数值。
- 对于近似值数字, ROUND()遵循银行家规则“四舍—大于五入—五取最接近的偶数”的规则: 一个带有任何小数部分的值会被舍入成最接近的偶数整数。
例子
Oceanbase>select round(2.15,2);
+---------------+
| round(2.15,2) |
+---------------+
| 2.15 |
+---------------+
1 row in set (0.00 sec)
Oceanbase>select round(2555e-2,1);
+------------------+
| round(2555e-2,1) |
+------------------+
| 25.6 |
+------------------+
1 row in set (0.01 sec)
Oceanbase>select round(25e-1), round(25.3e-1),round(35e-1);
+--------------+----------------+--------------+
| round(25e-1) | round(25.3e-1) | round(35e-1) |
+--------------+----------------+--------------+
| 3 | 3 | 4 |
+--------------+----------------+--------------+
1 row in set (0.00 sec)
CEIL
声明
CEIL(expr)
说明
返回大于或者等于指定表达式的最小整数。
还支持比较运算,结果为BOOL值,被转化为数字类型处理,产生的结果为1(TRUE)、0 (FALSE);
如果输入NULL,返回值为NULL。
如果输入纯数字的字符串,支持自动转换成数字类型。
返回值会被转化为一个BIGINT。
例子
Oceanbase>select ceil(1.2), ceil(-1.2), ceil(1+1.5), ceil(1=1),ceil(1<1),ceil(null);
+-----------+------------+-------------+-----------+-----------+------------+
| ceil(1.2) | ceil(-1.2) | ceil(1+1.5) | ceil(1=1) | ceil(1<1) | ceil(null) |
+-----------+------------+-------------+-----------+-----------+------------+
| 2 | -1 | 3 | 1 | 0 | NULL |
+-----------+------------+-------------+-----------+-----------+------------+
1 row in set (0.00 sec)
Oceanbase>select ceil(name);
ERROR 1166 (42703): Unkown column name 'name'
Oceanbase>select ceil('2');
+-----------+
| ceil('2') |
+-----------+
| 2 |
+-----------+
1 row in set (0.00 sec)
FLOOR
声明
FLOOR(expr)
说明
和CEIL(expr)函数功能类似,返回小于或者等于指定表达式的最大整数。
还支持比较运算,结果为BOOL值,被转化为数字类型处理,产生的结果为1(TRUE)、0 (FALSE);
如果输入NULL,返回值为NULL。
如果输入纯数字的字符串,支持自动转换成数字类型。
返回值会被转化为一个BIGINT。
例子
Oceanbase>select floor(1.2), floor(-1.2), floor(1+1.5), floor(1=1),floor(1<1),floor(null);
+------------+-------------+--------------+------------+------------+-------------+
| floor(1.2) | floor(-1.2) | floor(1+1.5) | floor(1=1) | floor(1<1) | floor(null) |
+------------+-------------+--------------+------------+------------+-------------+
| 1 | -2 | 2 | 1 | 0 | NULL |
+------------+-------------+--------------+------------+------------+-------------+
1 row in set (0.00 sec)
Oceanbase>select floor(name);
ERROR 1166 (42703): Unkown column name 'name'
Oceanbase>select floor('2');
+------------+
| floor('2') |
+------------+
| 2 |
+------------+
1 row in set (0.00 sec)
ABS
声明
ABS(expr)
说明
绝对值函数,求表达式绝对值,函数返回值类型与数值表达式的数据类型相同。
还支持比较运算,结果为BOOL值,被转化为数字类型处理,产生的结果为1(TRUE)、0 (FALSE);
如果输入NULL,返回值为NULL。
如果输入纯数字的字符串,支持自动转换成数字类型。
返回值会被转化为一个BIGINT。
例子
Oceanbase>select abs(5), abs(-5.777), abs(0), abs(1/2), abs(1-5);
+--------+-------------+--------+----------+----------+
| abs(5) | abs(-5.777) | abs(0) | abs(1/2) | abs(1-5) |
+--------+-------------+--------+----------+----------+
| 5 | 5.777 | 0 | 0.5000 | 4 |
+--------+-------------+--------+----------+----------+
1 row in set (0.00 sec)
NEG
声明
NEG(expr)
说明
求补函数,对操作数执行求补运算:用零减去操作数,然后结果返回操作数。
支持比较运算,结果为BOOL值,被转化为数字类型处理,产生的结果为1(TRUE)、0 (FALSE),再对结果求补。
例子
Oceanbase>select neg(1), neg(1+1), neg(2*3), neg(1=1), neg(5<1);
+--------+----------+----------+----------+----------+
| neg(1) | neg(1+1) | neg(2*3) | neg(1=1) | neg(5<1) |
+--------+----------+----------+----------+----------+
| -1 | -2 | -6 | -1 | 0 |
+--------+----------+----------+----------+----------+
1 row in set (0.01 sec)
SIGN
声明
SIGN(X)
说明
SIGN(X)返回参数作为-1、 0或1的符号,该符号取决于X的值为负、零或正。
支持比较运算,结果为BOOL值,被转化为数字类型处理,产生的结果为1(TRUE)、0 (FALSE);
如果输入NULL,返回值为NULL。
支持浮点数、十六进制数。
例子
Oceanbase>SELECT SIGN(-32), SIGN(0), SIGN(234);
+-----------+---------+-----------+
| SIGN(-32) | SIGN(0) | SIGN(234) |
+-----------+---------+-----------+
| -1 | 0 | 1 |
+-----------+---------+-----------+
1 row in set (0.01 sec)
Oceanbase>select sign(null),sign(false),sign(0x01);
+------------+-------------+------------+
| sign(null) | sign(false) | sign(0x01) |
+------------+-------------+------------+
| NULL | 0 | 1 |
+------------+-------------+------------+
1 row in set (0.00 sec)
CONV
声明
CONV(N, from_base, to_base)
说明
不同数基间转换数字。返回值为一个字符串,由 from_base 基转化为 to_base 基。输入参数N可以是一个整数或字符串。最小基数为 2,而最大基数则为 36。如果 to_base 是一个负数,则N被看作一个带符号数。否则,N 被看作无符号数。from_base 如果是负数,则被当作整数处理,符号被忽略。N 参数仅支持 int 类型和字符串类型输入;from_base 和 to_base 参数仅支持十进制int类型输入,且取值范围为 [-36, -2] U [2, 36]。
非法输入将导致报错,其中非法输入包括以下情况:
from_base 或者 to_base 不是一个合法的十进制int类型输入;
from_base 或者 to_base 超出 [-36,-2] U [2,36] 的取值范围;
N 不是一个合法的数字表示,例如取值超出 0~9,a~z,A~Z 的字符范围;
N 超出了from_base 基的取值范围,例如 from_base 为 2,而 N 取值为 3;
- N 的取值超出了 int64 的最大表示范围,即 [-9223372036854775807, 9223372036854775807]。
例子
Oceanbase>select conv(9223372036854775807,10,2);
+-----------------------------------------------------------------+
| conv(9223372036854775807,10,2) |
+-----------------------------------------------------------------+
| 111111111111111111111111111111111111111111111111111111111111111 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)
Oceanbase>select conv('-acc',21,-7);
+--------------------+
| conv('-acc',21,-7) |
+--------------------+
| -16425 |
+--------------------+
1 row in set (0.00 sec)
MOD
声明
MOD(N,M)
说明
取余函数。MOD(N,M), N % M, N MOD M 三种形式是等效的。
MOD()对于带有小数部分的数值也起作用,它返回除法运算后的精确余数。
N,M 中任何一个参数为 NULL,返回值都为 NULL。M 为 0 是,也返回 NULL。
例子
Oceanbase>select mod(29,19), 29 mod 19, 29 % 19;
+------------+-----------+---------+
| mod(29,19) | 29 mod 19 | 29 % 19 |
+------------+-----------+---------+
| 10 | 10 | 10 |
+------------+-----------+---------+
1 row in set (0.00 sec)
POW
声明
POW(X,Y)
说明
返回X的Y次方。
X与Y中任何一个参数为NULL,返回值都为NULL。
例子
Oceanbase>select pow(4,2), pow(4,-2), pow(1,null);
+----------+-----------+-------------+
| pow(4,2) | pow(4,-2) | pow(1,null) |
+----------+-----------+-------------+
| 16 | 0.0625 | NULL |
+----------+-----------+-------------+
1 row in set (0.00 sec)
POWER
声明
POWER(X,Y)
说明
POWER(X,Y)和POW(X,Y)是同义函数。
RAND
声明
rand([N])
说明
RAND([N]) 函数接受0个或者1个参数(N被称为随机数种子),返回一个范围是[0,1.0)的随机浮点数。如果想获得一个范围在 [i, j) 的随机整数,可以使用表达式 FLOOR(I + RAND() * (j - i))。
如果参数N不指定,那么在执行之前会使用随机种子初始化,之后RAND()会根据这个初始化值来生成随机数,所以RAND()每次会生成不同的随机数序列。
如果参数N指定,那么N会作为随机数种子来生成随机数。根据N是否是常量,分成以下两种情况
如果N是常量,那么N在执行之前会被当成随机种子初始化,之后RAND(N)会根据这个初始化值来生成随机数。相同的N值会生成相同的随机数序列。
如果N是变量(比如N是一个列值),那么N会在每次执行的时候作为随机数种子来生成随机数。相同的N值会生成相同的随机数。
除了出现在Select语句中,RAND([N])也可以出现在where,order by和group by语句中,它的执行方式还是按照上述规则执行。比如,如果想随机的对一个表进行排序,可以使用如下SQL:select from t1 order by rand()。如果想随机取样一个表的100行,可以使用如下SQL:select from t1 order by rand() limit 100。
例子
mysql> select a, b, rand() from t3;
+------+------+---------------------+
| a | b | rand() |
+------+------+---------------------+
| 1 | 1 | 0.641815407799385 |
| 2 | 2 | 0.16825051248841966 |
| 3 | 3 | 0.9158063697775886 |
+------+------+---------------------+
3 rows in set (0.00 sec)
mysql> select a, b, rand() from t3;
+------+------+---------------------+
| a | b | rand() |
+------+------+---------------------+
| 1 | 1 | 0.07428034215632857 |
| 2 | 2 | 0.6239826321825224 |
| 3 | 3 | 0.897072165177271 |
+------+------+---------------------+
3 rows in set (0.00 sec)
mysql> select a, b, rand(3) from t3;
+------+------+---------------------+
| a | b | rand(3) |
+------+------+---------------------+
| 1 | 1 | 0.9057697559760601 |
| 2 | 2 | 0.37307905813034536 |
| 3 | 3 | 0.14808605345719125 |
+------+------+---------------------+
3 rows in set (0.00 sec)
mysql> select a, b, rand(3) from t3;
+------+------+---------------------+
| a | b | rand(3) |
+------+------+---------------------+
| 1 | 1 | 0.9057697559760601 |
| 2 | 2 | 0.37307905813034536 |
| 3 | 3 | 0.14808605345719125 |
+------+------+---------------------+
3 rows in set (0.00 sec)
mysql> select a, b, rand(a), rand(b) from t3;
+------+------+---------------------+---------------------+
| a | b | rand(a) | rand(b) |
+------+------+---------------------+---------------------+
| 1 | 1 | 0.40540353712197724 | 0.40540353712197724 |
| 2 | 2 | 0.6555866465490187 | 0.6555866465490187 |
| 3 | 3 | 0.9057697559760601 | 0.9057697559760601 |
+------+------+---------------------+---------------------+
3 rows in set (0.00 sec)
比较函数
GREATEST
声明
GREATEST(value1, ...)
说明
返回参数的最大值,和函数LEAST()相对。
参数至少为两个(一个参数将会报错);如果参数中有NULL, 返回值为NULL。
当参数中同时存在数值和字符时,把字符隐式转换为数值类型处理,不能转换的报错。
例子
Oceanbase>select greatest(2,1), greatest('2',1,0), greatest('a','b','c'), greatest('a', NULL, 'c'), greatest('2014-05-15','2014-06-01')\G
* 1. row *
greatest(2,1): 2
greatest('2',1,0): 2
greatest('a','b','c'): c
greatest('a', NULL, 'c'): NULL
greatest('2014-05-15','2014-06-01'): 2014-06-01
1 row in set (0.01 sec)
Oceanbase>select greatest(2);
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'greatest'
LEAST
声明
LEAST(value1, ...)
说明
返回参数的最小值,和函数GREATEST()相对。
参数至少为两个;如果参数中有NULL,返回值为NULL。
当参数中同时存在数值和字符时,把字符隐式转换为数值类型处理,不能转换的报错。
例子
Oceanbase>select least(2, null), least('2',4,9), least('a','b','c'), least('a',NULL,'c'), least('2014-05-15','2014-06-01')\G;
* 1. row *
least(2, null): NULL
least('2',4,9): 2
least('a','b','c'): a
least('a',NULL,'c'): NULL
least('2014-05-15','2014-06-01'): 2014-05-15
1 row in set (0.01 sec)
Oceanbase>select least(2);
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'least'
ISNULL
声明
ISNULL(expr)
说明
如果参数 expr 为 NULL,那么 ISNULL() 的返回值为 1,否则范围值为 0。
ISNULL() 函数可以用来替代针对NULL的等值(=)比较。(使用=的NULL值比较通常都是错误的。)
ISNULL() 函数同 IS NULL 比较操作符具有一些相同的特性。
例子
Oceanbase>SELECT ISNULL(null), ISNULL('test'), ISNULL(123.456), ISNULL('10:00');
+--------------+----------------+-----------------+-----------------+
| ISNULL(null) | ISNULL('test') | ISNULL(123.456) | ISNULL('10:00') |
+--------------+----------------+-----------------+-----------------+
| 1 | 0 | 0 | 0 |
+--------------+----------------+-----------------+-----------------+
1 row in set (0.01 sec)
Oceanbase>SELECT ISNULL(null+1);
+----------------+
| ISNULL(null+1) |
+----------------+
| 1 |
+----------------+
1 row in set (0.00 sec)
流程控制函数
CASE
声明
CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END
OR
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
说明
在第一个方案的返回结果中,value = compare-value。而第二个方案的返回结果是第一种条件为真。如果没有匹配的结果值,则返回结果为 ELSE 后的结果,如果没有 ELSE 部分,则返回值为 NULL。
例子
Oceanbase>select CASE 'b' when 'a' then 1 when 'b' then 2 END;
+----------------------------------------------+
| CASE 'b' when 'a' then 1 when 'b' then 2 END |
+----------------------------------------------+
| 2 |
+----------------------------------------------+
1 row in set (0.01 sec)
Oceanbase>select CASE concat('a','b') when concat('ab','') then 'a' when 'b' then 'b' end;
+--------------------------------------------------------------------------+
| CASE concat('a','b') when concat('ab','') then 'a' when 'b' then 'b' end |
+--------------------------------------------------------------------------+
| a |
+--------------------------------------------------------------------------+
1 row in set (0.01 sec)
Oceanbase>select case when 1>0 then 'true' else 'false' end;
+--------------------------------------------+
| case when 1>0 then 'true' else 'false' end |
+--------------------------------------------+
| true |
+--------------------------------------------+
1 row in set (0.00 sec)
IF
声明
IF(expr1,expr2,expr3)
说明
如果 expr1 的值为 TRUE(即:expr1<>0 且 expr1<>NULL),返回结果为 expr2; 否则返回结果为 expr3。
IF() 返回结果可以是数值或字符串类型,它取决于使用的内容。
如果 expr2, expr3 中只有一个明确是 NULL,则 IF() 函数的结果类型为非 NULL 表达式的结果类型。
例子
Oceanbase>select if(5>6, 'T','F'), if (5>6, 1, 0), if(null, 'True', 'False'), if(0, 'True', 'False')\G
* 1. row *
if(5>6, 'T','F'): F
if (5>6, 1, 0): 0
if(null, 'True', 'False'): False
if(0, 'True', 'False'): False
1 row in set (0.01 sec)
IFNULL
声明
IFNULL(expr1, expr2)
说明
假设 expr1 不为 NULL,则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。 IFNULL() 的返回值是数值或字符串,具体情况取决于其所使用的语境。
IF()的默认返回值类型按照以下方式计算:
表达式 | 返回值 |
---|---|
expr2 或 expr3 返回值为一个字符串。 | 字符串 |
expr2 或 expr3 返回值为一个浮点值。 | 浮点 |
expr2 或 expr3 返回值为一个整数。 | 整数 |
假如 expr2 和 expr3 都是字符串,且其中任何一个字符串区分大小写,则返回结果是区分大小写。
例子
Oceanbase>SELECT IFNULL('abc', null), IFNULL(NULL+1, NULL+2), IFNULL(1/0, 0/1);
+---------------------+------------------------+------------------+
| IFNULL('abc', null) | IFNULL(NULL+1, NULL+2) | IFNULL(1/0, 0/1) |
+---------------------+------------------------+------------------+
| abc | NULL | 0.0000 |
+---------------------+------------------------+------------------+
1 row in set (0.01 sec)
NULLIF
声明
NULLIF(expr1, expr2)
说明
如果 expr1 = expr2 成立,那么返回值为 NULL,否则返回值为 expr1。这和CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END相同。
注意,如果参数不相等,则两次求得的值为 expr1。
例子
Oceanbase>SELECT NULLIF('ABC', 123), NULLIF('123',123), NULLIF(NULL, 'abc');
+--------------------+-------------------+---------------------+
| NULLIF('ABC', 123) | NULLIF('123',123) | NULLIF(NULL, 'abc') |
+--------------------+-------------------+---------------------+
| ABC | NULL | NULL |
+--------------------+-------------------+---------------------+
1 row in set, 1 warning (0.01 sec)
ORA_DECODE
声明
ora_decode(条件, 值1, 返回值1, 值2, 返回值2, ... 值n, 返回值n, 缺省值)
说明
ORA_DECODE()函数功能等同于Oracle的DECODE()函数功能(OceanBase 1.0兼容MySQL,同时也增加了部分Oracle的函数,这部分Oracle同功能的函数在OceanBase 1.0中被统一命名以ORA_开头 )。
该函数的含义如下:
IF 条件=值1
THEN RETURN(返回值1)
ELSIF 条件=值2
THEN RETURN(返回值2)
......
ELSIF 条件=值n
THEN RETURN(返回值n)
ELSE RETURN(缺省值)
END IF