描述

在不同的进制之间转换数字。

语法

conv( p_num text, p_from_base int4, p_to_base int4) RETURNS text
    

常规规则

示例

在以下示例中，'123456'从10进制转换为16进制是1E240。

select conv('123456', 10, 16);
conv  
-------
1E240
(1 row)
    

描述

返回参数的以10为底的对数。

语法

log10(p1 numeric) RETURNS numeric
log10(p1 double precision) RETURNS double precision
    

常规规则

示例

在以下示例中，返回参数2的以10为底的对数。

select log10(2);
      log10        
--------------------
0.3010299956639812
(1 row)
    

描述

返回参数的以2为底的对数。

语法

log2(p_num numeric) RETURNS numeric
    

常规规则

示例

在以下示例中，返回参数64的以2为底的对数。

select log2(64);
        log2        
--------------------
6.0000000000000000
(1 row)
    

描述

返回范围在0和1.0之间的随机浮点值v。

语法

rand(p_seed int default null) RETURNS double precision
    

常规规则

示例

在以下示例中，RAND(123)产生的值序列在出现的两个位置都相同。

select rand(123);
        rand
---------------------
 0.05425240159967615
(1 row)
        

描述

截取指定小数位数。

语法

TRUNCATE(SMALLINT X,INTEGER D) returns INTEGER
TRUNCATE(INTEGER X,INTEGER D) returns INTEGER
TRUNCATE(BIGINT X,INTEGER D) returns BIGINT
TRUNCATE(DOUBLE PRECISION X,INTEGER D) returns DOUBLE PRECISION
TRUNCATE(NUMERIC,INTEGER D) returns NUMERIC
    

通用规则

示例

在以下示例中，返回将"1.223"截取到小数点后1位的结果。

SELECT TRUNCATE(1.223,1);
  truncate
----------
      1.2
(1 row)
        

描述

返回索引号处的字符串。

语法

elt(str_pos int, VARIADIC strlist text[]) RETURNS text 
    

通用规则

示例

在以下例子中，返回第 2 个元素 'Bb'。

select elt( 2, 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
elt
-----
Bb
(1 row)
    

描述

第一个参数在后续参数中的位置（索引）。

语法

field(str text, VARIADIC strlist text[]) RETURNS bigint
field(str numeric, VARIADIC strlist numeric[]) RETURNS bigint   
    

一般规则

示例

在以下例子中，返回字符串 'Bb' 在字符串列表 'Aa', 'Bb', 'Cc', 'Dd', 'Ff' 中的索引（位置） 2。

select field('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
field
-------
    2
(1 row)
    

描述

第一个参数在第二个参数中的位置（索引）。

语法

FIND_IN_SET(str "any",strlist TEXT) returns INTEGER
    

一般规则

示例

在以下例子中，返回字符串 'a,b,c,d' 中字符 'b' 的位置（索引）。

SELECT FIND_IN_SET('b','a,b,c,d');
  find_in_set
-------------
            2
(1 row)
    

描述

解码 base64 编码的字符串并返回结果。

语法

from_base64(p_str text) RETURNS text
    

一般规则

示例

在以下例子中，解码 base64 编码的字符串 'YWJj' 并返回结果 'abc'。

select from_base64('YWJj');
from_base64
-------------
abc
(1 row)
    

描述

在指定位置插入子字符串，直到指定的字符数。

语法

          insert(p_source text, p_pos bigint, p_len bigint, p_replacement text) RETURNS text
        

通用规则

示例

在下面的示例中，在指定位置3插入子字符串'What'，直到指定的字符数4。

select insert('Quadratic', 3, 4, 'What');
  insert
-----------
QuWhattic
(1 row)
    

描述

获取子字符串的第一次出现的位置。

语法

          instr(str text, patt text) RETURNS int
        

通用规则

示例

在下面的示例中，在字符串'abc124deff4de'中，子字符串'4de'的第一次出现的位置是6。

select instr('abc124deff4de', '4de');
 instr 
-------
     6
(1 row)
    

描述

LOWER()的同义词。

语法

lcase(p_source text) RETURNS text
    

通用规则

示例

在下面的示例中，返回字符串'quadratic'，其中所有字符都已更改为小写。

select lcase('QuadRatic');
  lcase
-----------
quadratic
(1 row)
    

描述

返回子字符串第一次出现的位置。

语法

LOCATE(substr TEXT,str TEXT) returns INTEGER
LOCATE(substr TEXT,str TEXT,POS INTEGER) returns INTEGER
    

通用规则

示例

在下面的示例中，返回'foobarbar'中'bar'在位置5开始的出现位置。

SELECT LOCATE('bar', 'foobarbar', 5);
  locate 
--------
      7
(1 row)
    

描述

从指定位置开始返回一个子字符串。

语法

mid(p_source text, p_pos bigint, p_len bigint) RETURNS text
    

通用规则

示例

在下面的示例中，从指定位置 2 开始返回子字符串 'ongy'。

select mid('Hongye', 2, 4);
mid
------
ongy
(1 row)
    

描述

返回指定数量的空格字符串。

语法

          space(p_num int) RETURNS text
        

通用规则

示例

在下面的示例中，返回指定数量的空格字符串。

select concat('|', space(11), '|');
    concat     
---------------
|           |
(1 row)
    

描述

比较两个字符串。

语法

strcmp(p1 text, p2 text) RETURNS int
    

通用规则

示例

在下面的示例中，如果第一个参数按照当前的排序顺序小于第二个参数，则返回 -1。

select strcmp('text', 'text2');
strcmp 
--------
    -1
(1 row)
    

描述

将参数转换为 base-64 字符串并返回。

语法

to_base64(p_str text) RETURNS text
    

通用规则

示例

在下面的示例中，将参数 'abc' 转换为 base-64 字符串 'YWJj' 并返回。

select to_base64('abc');
to_base64 
-----------
YWJj
(1 row)
    

描述

UPPER() 的同义词。

语法

ucase(p_source text) RETURNS text
    

通用规则

示例

在下面的示例中，将字符串 'QUADRATIC' 中的所有字符根据当前字符集映射更改为大写并返回。

select ucase('QuadRatic');
  ucase   
-----------
QUADRATIC
(1 row)
    

描述

返回按指定要求截取的子字符串。

语法

substr(str text, pos int) RETURNS text
substr(str text, pos int, len int) RETURNS text
    

通用规则

示例

在下面的示例中，结果为 bc。

select substr('abc',-2,2);
 substr
--------
 bc
(1 row)
    

描述

根据指定要求返回子字符串。

语法

substring(str text, pos int) RETURNS text
substring(str text, pos int, len int) RETURNS text
    

一般规则

示例

在以下的示例中，结果是 bc。

select substring('abc',-2,2);
 substring
--------
 bc
(1 row)
    

描述

从指定字符串中返回指定分隔符出现n次数前的子字符串

语法

substring_index(str varchar, delim varchar, count integer) RETURNS varchar
    

一般规则

示例

lightdb@test_m=# SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
 substring_index 
-----------------
 www.mysql
(1 row)

lightdb@test_m=# SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
 substring_index 
-----------------
 mysql.com
(1 row)
    

描述

减去两个日期。

语法

DATEDIFF(expr1 timestamp,expr2 timestamp) RETURN integer
DATEDIFF(expr1 text,expr2 text) RETURN integer
DATEDIFF(expr1 timestamptz,expr2 timestamptz) RETURN integer
DATEDIFF(expr1 timestamptz,expr2 timestamp) RETURN integer
DATEDIFF(expr1 timestamp,expr2 timestamptz) RETURN integer
DATEDIFF(expr1 date, expr2 date) RETURN integer
DATEDIFF(expr1 date, expr2 numeric) RETURN integer
DATEDIFF(expr1 numeric, expr2 date) RETURN integer
DATEDIFF(expr1 numeric, expr2 numeric) RETURN integer
        

一般规则

示例

在以下示例中，返回 1，因为 '2007-12-31' 减去 '2007-12-30' 等于 1

SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
datediff 
----------
 1
(1 row)
SELECT DATEDIFF(cast('2007-12-31 23:59:59' as datetime),cast('2007-12-30' as datetime));
 datediff 
----------
        1
(1 row)
SELECT DATEDIFF(cast('2007-12-31 23:59:59' as date),cast('2007-12-30' as date));
 datediff 
----------
        1
(1 row)
        

描述

日期加法。

语法

DATE_ADD(expr1 timestamptz, expr2 interval) RETURN date
DATE_ADD(expr1 text, expr2 interval) RETURN date
        

一般规则

示例

在下面的例子中，返回'2020-01-02'，因为'20200101'加上1天。

select date_add('20200101', interval 1 day);
  date_add  
------------
2020-01-02
(1 row)
    

描述

日期格式显示。

语法

DATE_FORMAT(expr1 timestamp, expr2 text) RETURN text
DATE_FORMAT(expr1 timestamptz, expr2 text) RETURN text
DATE_FORMAT(expr1 text, expr2 text) RETURN text
        

通用规则

例子

在下面的例子中，返回'2021/12/01 11,59,59'，因为expr2是'%Y/%m/%d %h,%i,%s'格式。

select date_format('20211201 11:59:59','%Y/%m/%d %h,%i,%s');
      date_format     
---------------------
  2021/12/01 11,59,59
(1 row)
    

Table 10.107 显示了可用于格式化date_format值的模板模式。

描述

日期减法。

语法

DATE_SUB(expr1 timestamptz, expr2 interval) RETURN date
DATE_SUB(expr1 text, expr2 interval) RETURN date
        

一般规则

示例

在以下示例中，“20200101” 减去一天后返回“2019-12-31”

select date_sub('20200101', interval 1 day);
  date_sub  
------------
2019-12-31
(1 row)
    

描述

返回月份日期。

语法

DAY(expr1 text) RETURN int2
DAY(expr1 timestamptz) RETURN int4
        

一般规则

示例

在以下示例中，“2011/12/01” 的日期是 “1”，因此返回 “1”。

select day('2011/12/01');
day 
-----
  1
(1 row)
    

描述

返回星期几。

语法

DAYOFWEEK(expr1 text) RETURN int4
DAYOFWEEK(expr1 timestamptz) RETURN int4
        

一般规则

示例

在以下示例中，“20200101” 是星期三，因此返回 “4”。

select dayofweek(20200101);
  dayofweek 
-----------
          4
(1 row)
    

描述

根据 UNIX 时间戳返回时间格式。

语法

FROM_UNIXTIME(expr1 text) RETURN timestamp
FROM_UNIXTIME(expr1 int8) RETURN timestamp
FROM_UNIXTIME(expr1 text, expr2 text) RETURN text
FROM_UNIXTIME(expr1 int8, expr2 text) RETURN text
    

一般规则

示例

在以下示例中，根据 UNIX 时间戳 20220910 返回时间戳。

select from_unixtime(20220910);
    from_unixtime    
---------------------
  1970-08-23 08:55:10
(1 row)
select from_unixtime(20220910, '%Y/%m/%d %h,%i,%s');
    from_unixtime    
---------------------
  1970/08/23 08,55,10
(1 row)
    

Table 10.108 显示了可用于格式化 from_unixtime 值的模板模式。

描述

将秒转换为“hh:mm:ss”格式。

语法

sec_to_time(p_secs int) RETURNS interval
    

通用规则

示例

在下面的例子中，将秒数 80580 转换成 'hh:mm:ss' 格式的时间，即 22:23:00。

select mysql.sec_to_time(80580);
sec_to_time
-------------
22:23:00
(1 row)
    

描述

将字符串转换为时间值。这是 DATE_FORMAT() 函数的反函数。它接受一个字符串 str 和一个格式字符串 format。 如果格式字符串包含日期和时间部分，STR_TO_DATE() 返回一个 DATETIME 值。如果字符串仅包含日期或时间部分， 则返回 DATE 或 TIME 值。如果从 str 中提取的日期、时间或日期时间值是非法的，STR_TO_DATE() 将返回错误。

语法

STR_TO_DATE(datetime_str text, datetime_format text) RETURN datetime
STR_TO_DATE(date_str text, date_format text) RETURN date
STR_TO_DATE(time_str text, time_format text) RETURN time
    

通用规则

示例

在下面的例子中，根据时间戳 '2022/09/10 11,13,13' 和格式字符串 '%Y/%m/%d %h,%i,%s' 返回 '2022-09-10 11:13:13'。 如果格式字符串包含日期和时间部分，则返回一个 DATETIME 值。

select str_to_date('2022/09/10 11,13,13', '%Y/%m/%d %h,%i,%s');
      str_to_date     
---------------------
  2022-09-10 11:13:13
(1 row)
    

在下面的例子中，因为格式字符串仅包含日期部分，所以返回一个 DATE 值。

select str_to_date('2022/09/10', '%Y/%m/%d');
      str_to_date     
---------------------
  2022-09-10
(1 row)
    

在下面的例子中，因为格式字符串仅包含时间部分，所以返回一个 TIME 值。

select str_to_date('09:00:59', '%h:%i:%s');
      str_to_date     
---------------------
  09:00:59
(1 row)
    

如果启用了 NO_ZERO_DATE SQL 模式，则不允许使用零日期。在这种情况下，STR_TO_DATE() 返回 NULL。

set lightdb_sql_mode = 'no_zero_date';
select str_to_date('2022/09/0', '%Y/%m/%d');
    str_to_date 
-------------
 
(1 row)
    

Table 10.109 显示了可用于格式化 STR_TO_DATE 值的模板模式。

描述

返回函数执行时的时间。

语法

sysdate() RETURNS timestamp(0)
    

一般规则

示例如下:

select sysdate;
       sysdate
---------------------
 2023-05-06 16:06:59
(1 row)
    

描述

将参数转换为秒并返回。

语法

          time_to_sec(p_time time) RETURNS int
        

通用规则

示例

在以下示例中，将时间参数 '22:23:00' 转换为秒数 80580 并返回。

select time_to_sec('22:23:00');
time_to_sec 
-------------
      80580
(1 row)
    

描述

计算时间差。

语法

timediff(p_date1 timestamptz, p_date2 timestamptz) RETURNS interval
    

通用规则

示例

在以下示例中，将 '2000-01-01 00:00:00' 和 '2000-01-01 00:00:00.000001' 相减并将结果表示为时间值。

      select mysql.timediff('2000-01-01 00:00:00', '2000-01-01 00:00:00.000001');
          timediff
      ------------------
      -00:00:00.000001
      (1 row)
    

描述

从日期时间表达式中减去一个时间间隔。

语法

timestampdiff( p_unit text, p_ts1  text, p_ts2  text) RETURNS bigint
timestampdiff( p_unit text, p_ts1  text, p_ts2  timestamptz) RETURNS bigint
timestampdiff( p_unit text, p_ts1  timestamptz, p_ts2  text) RETURNS bigint
timestampdiff( p_unit text, p_ts1  timestamptz, p_ts2  timestamptz) RETURNS bigint
    

通用规则

示例

在以下示例中，返回 '1911-11-11 11:23:45.123456' − '2021-12-12 12:12:12.654321'::timestamp，结果（整数）由 MICROSECOND 给出。

select timestampdiff ('MICROSECOND', '1911-11-11 11:23:45.123456'::timestamp, '2021-12-12 12:12:12.654321'::timestamp);
  timestampdiff   
------------------
3474060507530865
(1 row)

select timestampdiff (MICROSECOND, '1911-11-11 11:23:45.123456'::timestamp, '2021-12-12 12:12:12.654321'::timestamp);
timestampdiff
------------------
3474060507530865
(1 row)
    

描述

返回将日期参数转换为天数的结果。

语法

to_days(p_date timestamp) RETURNS bigint
to_days(p_date timestamptz) RETURNS bigint
to_days( p_date text) RETURNS bigint
to_days( p_date text, p_format text) RETURNS bigint
to_days(p_date bigint) RETURNS bigint
    

一般规则

示例

在以下示例中，返回将日期参数 '1111-11-11' 转换为 406098 天的结果。

select to_days('1111-11-11');
to_days 
---------
  406098
(1 row)
    

描述

返回将日期或日期时间参数转换为自公元 0 年以来的秒数。

语法

to_seconds(p_date timestamp) RETURNS bigint
to_seconds(p_date timestamptz) RETURNS bigint
    

一般规则

示例

在以下示例中，返回将日期或日期时间参数 '2009-11-29' 转换为自公元 0 年以来的秒数 63426672000 的结果。

select to_seconds('2009-11-29'::timestamp);
to_seconds  
-------------
63426672000
(1 row)
    

描述

返回根据时间格式的 UNIX 时间戳。

语法

UNIX_TIMESTAMP() RETURN int8
UNIX_TIMESTAMP(expr1 timestamptz) RETURN int8
UNIX_TIMESTAMP(expr1 text) RETURN int8
    

一般规则

示例

在下面的示例中，返回对应参数20200101的Unix时间戳“1577808000”。

select unix_timestamp(20200101);
  unix_timestamp 
----------------
      1577808000
(1 row)
select unix_timestamp();
  unix_timestamp 
----------------
      1663051334
(1 row)
    

描述

根据时间返回当年的周数。

语法

WEEKOFYEAR(expr1 timestamptz) RETURN int4
WEEKOFYEAR(expr1 text) RETURN int4
    

一般规则

示例

在下面的示例中，返回“36”，因为“20220910”是一年中的第36周。

select weekofyear('20220910');
  weekofyear 
------------
          36
(1 row)
    

描述

返回日期的年份。

语法

YEAR(expr1 timestamptz) RETURN int4
YEAR(expr1 text) RETURN int4
    

一般规则

示例

在下面的示例中，返回“2020”，因为“20200101”的年份是2020。

select year(20200101);
  year 
------
  2020
(1 row)
select year('200101');
  year 
------
  2020
(1 row)
    

描述

返回日期代表的季度，数字范围为1 ~ 4。

语法

QUARTER(timestamp) RETURN int4
    

一般规则

示例

在下面的例子中，因为日期是'20201001'，即10月，所以返回第4季度。

 select quarter(cast('20201001'as date));
 quarter 
---------
       4
(1 row)

select quarter(cast('20201001'as datetime));
 quarter 
---------
       4
(1 row)

select quarter('20201001');
 quarter 
---------
       4
(1 row)

    

描述

返回参数所在月份的最后一天的日期值

语法

LAST_DAY(date) RETURN date
LAST_DAY(text) RETURN date
    

一般规则

示例

在下面的例子中，如果参数可用，参数所在月份的最后一天的日期值被返回:

lightdb@test_m=# SELECT LAST_DAY('2003-02-05');
  last_day  
------------
 2003-02-28
(1 row)

lightdb@test_m=# SELECT LAST_DAY('2004-02-05');
  last_day  
------------
 2004-02-29
(1 row)

lightdb@test_m=# SELECT LAST_DAY('2004-01-01 01:01:01');
  last_day  
------------
 2004-01-31
(1 row)

lightdb@test_m=# SELECT LAST_DAY('2003-03-32');
ERROR:  date/time field value out of range: "2003-03-32"
HINT:  Perhaps you need a different "datestyle" setting.
CONTEXT:  SQL function "last_day" statement 1
lightdb@test_m=# 
    

描述

以 'YYYY-MM-DD' 格式返回当前日期.

语法

CURDATE() RETURN date
    

一般规则

示例

lightdb@test_m=# select curdate();
  curdate   
------------
 2023-12-06
(1 row)

lightdb@test_m=# select curdate()+1;
  ?column?  
------------
 2023-12-07
(1 row)

lightdb@test_m=# select curdate()/2;
ERROR:  operator does not exist: date / integer
LINE 1: select curdate()/2;
                        ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
lightdb@test_m=# select curdate()*2;
ERROR:  operator does not exist: date * integer
LINE 1: select curdate()*2;
                        ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
lightdb@test_m=# select curdate()-2;
  ?column?  
------------
 2023-12-04
(1 row)

    

描述

计算列表中的每个值（列表可能为空），返回包含这些值的 JSON 数组。

语法

JSON_ARRAY( VARIADIC "any" ) RETURNS jsonb
        

一般规则

示例

SELECT JSON_ARRAY(1, 'abc', NULL, TRUE, NOW());
                         json_array                         
------------------------------------------------------------
 [1, "abc", null, true, "2023-05-31T19:44:44.881055+08:00"]
(1 row)
        

描述

计算列表中的每个键值对（列表可能为空），返回包含这些键值对的 JSON 数组。

语法

JSON_OBJECT( VARIADIC "any" ) RETURNS jsonb
      

一般规则

示例

SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
         json_object          
------------------------------
 {"id": 87, "name": "carrot"}
(1 row)

SELECT JSON_OBJECT('id', 87, 'name', 'carrot', 'id', 99);
         json_object          
------------------------------
 {"id": 99, "name": "carrot"}
(1 row)
        

描述

通过 JSON 路径从 JSON 文档中选择匹配的数据，并返回。

语法

JSON_EXTRACT(json_doc jsonb, path jsonpath) RETURNS jsonb
        

一般规则

示例

SELECT JSON_EXTRACT('{"k1":11,"k2":22,"k2 2":33}','$.k1');
 json_extract 
--------------
 11
(1 row)

SELECT JSON_EXTRACT('{"k1":11,"k2":22,"k2 2":33}','$."k2 2"');
 json_extract 
--------------
 33
(1 row)

SELECT JSON_EXTRACT('{"a":{"c":20, "d":{"b":{"b":30, "c":40}, "e":{"b":50}}}, "c":{"b":60, "c":70}, "b":80}','$.a.c');
 json_extract 
--------------
 20
(1 row)

SELECT JSON_EXTRACT('[10,"str", null, true]','$[1]');
 json_extract 
--------------
 "str"
(1 row)    
SELECT JSON_EXTRACT('[10,"str", null, true]','$[last]');
 json_extract 
--------------
 true
(1 row)

SELECT JSON_EXTRACT('[10,"str", null, true]','$[last-2]');
 json_extract 
--------------
 "str"
(1 row)
        

以下的示例中，有多个值匹配，所以返回的是数组。

SELECT JSON_EXTRACT('[10,"str", null, true]','$[1 to 3]');
    json_extract     
---------------------
 ["str", null, true]
(1 row)

SELECT JSON_EXTRACT('[10,"str", null, true]','$[*]');
      json_extract       
-------------------------
 [10, "str", null, true]
(1 row)

SELECT JSON_EXTRACT('[10]','$[*]');
 json_extract 
--------------
 [10]
(1 row)

SELECT JSON_EXTRACT('{"k1":1,"k2":"22","k2 2":[3,33]}','$.*');
    json_extract    
--------------------
 [1, "22", [3, 33]]
(1 row)
        

描述

判断 JSON 文档是否包含指定的 JSON 路径，返回 true 或 false。

语法

JSON_CONTAINS(json_doc jsonb, json_val jsonb, path jsonpath) RETURNS bool
        

一般规则

示例

SELECT JSON_CONTAINS('{"a": 111, "b": 222, "c": {"d": 4444}}', '111', '$.a');
 json_contains 
---------------
 t
(1 row)

SELECT JSON_CONTAINS('{"a": "111", "b": 222, "c": {"d": 4444}}', '111', '$.a');
 json_contains 
---------------
 f
(1 row)

SELECT JSON_CONTAINS('{"a": "111", "b": 222, "c": {"d": 4444}}', '" 111"', '$.a');
 json_contains 
---------------
 f
(1 row)

SELECT JSON_CONTAINS('{"a": 111, "b": 222, "c": {"d": 4444}}', '{"d" :   4444 }', '$.c');
 json_contains 
---------------
 t
(1 row)
        

描述

判断 JSON 文档是否包含指定的一个或多个 JSON 路径，返回 true 或 false。

语法

JSON_CONTAINS_PATH(json_doc jsonb, one_or_all text, variadic jsonpath[]) RETURNS bool
        

一般规则

示例

SELECT JSON_CONTAINS_PATH('{"a": 111, "b": 222, "c": {"d": 4444}}', 'one', '$.a', '$.z');
 json_contains_path 
--------------------
 t
(1 row)

SELECT JSON_CONTAINS_PATH('{"a": 111, "b": 222, "c": {"d": 4444}}', 'one', '$.d');
 json_contains_path 
--------------------
 f
(1 row)


SELECT JSON_CONTAINS_PATH('{"a": 111, "b": 222, "c": {"d": 4444}}', 'all', '$a', '$.b', '$.c');
 json_contains_path 
--------------------
 t
(1 row)

SELECT JSON_CONTAINS_PATH('{"a": 111, "b": 222, "c": {"d": 4444}}', 'all', '$a', '$.b', '$.d');
 json_contains_path 
--------------------
 f
(1 row)
        

描述

格式化 JSON 文档。

语法

JSON_PRETTY(json_doc jsonb) RETURNS text
        

一般规则

示例

SELECT JSON_PRETTY('{"a":{"b":{"z":1},"c":[5,{"z":2}],"z":3}, "z":4}');
      json_pretty       
------------------------
 {                     +
     "a": {            +
         "b": {        +
             "z": 1    +
         },            +
         "c": [        +
             5,        +
             {         +
                 "z": 2+
             }         +
         ],            +
         "z": 3        +
     },                +
     "z": 4            +
 }
(1 row)
        

描述

从 JSON 文档中删除数据，返回修改后的结果。

语法

JSON_REMOVE(jsonb, jsonpath) RETURNS jsonb
        

一般规则

示例

SELECT JSON_REMOVE('{"a": 1, "b": [1,2,3], "c": {"d": 4}}', '$.c.d');
            json_remove            
-----------------------------------
 {"a": 1, "b": [1, 2, 3], "c": {}}
(1 row)

SELECT JSON_REMOVE('{"a": 1, "b": [1,2,3], "c": {"d": 4}}', '$.b[last]');
             json_remove              
--------------------------------------
 {"a": 1, "b": [1, 2], "c": {"d": 4}}
(1 row)

SELECT JSON_REMOVE('{"a": 1, "b": [1,2,3], "c": {"d": 4}}', '$.c.z');
               json_remove               
-----------------------------------------
 {"a": 1, "b": [1, 2, 3], "c": {"d": 4}}
(1 row)
        

和 MySQL 不同的时，在通过句号 . 访问 JSON 对象时， 如果未使用双引号将对象名称引起来，并且该名称是合法的 ECMAScript 标识符名，但包含了部分特殊字符（比如 $），LightDB 会提示错误。

SELECT JSON_REMOVE('{"a": 1, "b": [1,2,3], "c": {"d": 4}}', '$.$z');
        

描述

向 JSON 文档中插入数据，返回修改后的结果。

语法

JSON_INSERT(jsonb, jsonpath, anyelement) RETURNS jsonb
JSON_INSERT(jsonb, jsonpath, text) RETURNS jsonb
        

一般规则

示例

SELECT JSON_INSERT('{ "a": 1, "b": [2, 3]}', '$.a', 10);
      json_insert      
-----------------------
 {"a": 1, "b": [2, 3]}
(1 row)

SELECT JSON_INSERT('{ "a": 1, "b": [2, 3]}', '$.c', 200);
           json_insert           
---------------------------------
 {"a": 1, "b": [2, 3], "c": 200}
(1 row)
        

描述

替换 JSON 文档的数据，返回修改后的结果。

语法

JSON_REPLACE(jsonb, jsonpath, anyelement) RETURNS jsonb
JSON_REPLACE(jsonb, jsonpath, text) RETURNS jsonb
        

一般规则

示例

SELECT JSON_REPLACE('{ "a": 1, "b": [2, 3]}', '$.a', 10);
      json_replace      
------------------------
 {"a": 10, "b": [2, 3]}
(1 row)

SELECT JSON_REPLACE('{ "a": 1, "b": [2, 3]}', '$.c', 'null');
        json_replace        
----------------------------
 {"a": 1, "b": [2, 3]}
(1 row)

SELECT JSON_REPLACE('100', '$','{ "a": 1, "b": [2, 3]}'::jsonb);
     json_replace      
-----------------------
 {"a": 1, "b": [2, 3]}
(1 row)
      

和 MySQL 不同的时，在通过句号 . 访问 JSON 对象时， 如果未使用双引号将对象名称引起来，并且该名称是非法的 ECMAScript 标识符名，LightDB 不会提示错误而是返回原值。

SELECT JSON_REPLACE('{ "a": 1, "b": [2, 3]}', '$.b.2z', 4);
     json_replace      
-----------------------
 {"a": 1, "b": [2, 3]}
(1 row)
      

描述

向 JSON 文档中插入数据或替换 JSON 文档的数据，返回修改后的结果。

语法

JSON_SET(jsonb, jsonpath, anyelement) RETURNS jsonb
JSON_SET(jsonb, jsonpath, text) RETURNS jsonb
        

一般规则

示例

SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', '$.a', 10);
        json_set        
------------------------
 {"a": 10, "b": [2, 3]}
(1 row)

SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', '$.c', '[true, false]');
                  json_set                   
---------------------------------------------
 {"a": 1, "b": [2, 3], "c": "[true, false]"}
(1 row)

SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', '$.b.z', 4);
       json_set        
-----------------------
 {"a": 1, "b": [2, 3]}
(1 row)
        

描述

如果expr1为TRUE，则IF（）返回expr2。否则，返回expr3。

语法

IF(expr1,expr2,expr3)
        

一般规则

示例

在以下示例中，返回3，因为1 > 2的值为false。

SELECT IF(1>2,2,3);
  if
----
  3
(1 row)
        

描述

为了与MySQL模式兼容，仅在BY模式下的分组行为语法。

语法

          ANY_VALUE(anyelement)
        

通用规则

示例

在以下示例中，如果id是一个非索引列，则以下查询将失败：

select co1, co2, max(co3) from t1 group by id;
ERROR:  column "t1.co1" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select co1, co2, max(co3) from t1 group by id;
              ^
      

使用ANY_VALUE()来引用co1和co2：

select any_value(co1) as co1, any_value(co2) as co2, max(co3) from t1 group by id;
co1 | co2 | max 
-----+-----+-----
  6 |   9 |  30
  4 |   5 |   5
  3 |   3 |   4
  2 |   3 |   4
(4 rows)
      

描述

返回字符串值的连接，以分隔符为分隔符。

语法

GROUP_CONCAT( [DISTINCT] column_name1 [ORDER BY {unsigned_integer | column_name2 | expr} [ASC | DESC] [SEPARATOR delimiter TEXT]) returns TEXT
      

通用规则

示例

在以下示例中，返回以':'为分隔符的表t中列col1的值的结果。

select GROUP_CONCAT(col1 separator ':') from (values('BBBB',1),('AAAA',1),('CCCC',2)) as t(col1 ,col2) group by col2;
group_concat
--------------
BBBB:AAAA
CCCC
(2 rows)
      

描述

同current_schema()的同义词。在MySQL中，模式的概念类似于数据库。您可以通过设置search_path来指定当前模式。

语法

DATABASE() returns TEXT
      

一般规则

示例

在下面的示例中，当前模式为lightdb。

select database();
  database 
----------
  lighdb
(1 row)
      

描述

将二进制UUID转换为字符串。

语法

bin_to_uuid(p_uuid bytea, swap_flag int4 default 0) RETURNS uuid
    

一般规则

示例

在下面的示例中，将二进制UUID“\x0768a47dd355372b4926ee668b6cb443”转换为字符串“0768a47d-d355-372b-4926-ee668b6cb443”。

select bin_to_uuid('\x0768a47dd355372b4926ee668b6cb443');
            bin_to_uuid              
--------------------------------------
0768a47d-d355-372b-4926-ee668b6cb443
(1 row)
    

描述

压缩字符串并返回二进制数据。

语法

COMPRESS(expr1 text) RETURN bytea
    

一般规则

示例

在以下示例中，将'abc123'压缩为二进制数据'\x06000000616263313233'。

select compress('abc123');
        compress        
------------------------
  \x06000000616263313233
(1 row)
    

描述

解压由compress()函数压缩的二进制数据，并恢复原始字符串。

语法

UNCOMPRESS(expr1 bytea) RETURN text
    

通用规则

示例

在以下示例中，将二进制数据'\x06000000616263313233'解压为bCompress 'abc123'。

select uncompress(compress('abc123'));
  uncompress 
------------
  abc123
(1 row)
    

Description

生成一个36字节的uuid字符串

Syntax

      uuid() RETURNS text
    

General rules

Example

以下示例生成一个uuid字符串

select uuid();
                 uuid                 
--------------------------------------
 f31f29e5-481f-4666-aa74-8cee3aa44545
(1 row)

    

描述

将字符串UUID转换为二进制。

语法

uuid_to_bin(p_uuid uuid, swap_flag int4 default 0) RETURNS bytea
    

通用规则

示例

在以下示例中，将字符串UUID '0768a47d-d355-372b-4926-ee668b6cb443'转换为二进制'\x0768a47dd355372b4926ee668b6cb443'。

select uuid_to_bin('0768a47d-d355-372b-4926-ee668b6cb443'::uuid)::text;
            uuid_to_bin             
------------------------------------
\x0768a47dd355372b4926ee668b6cb443
(1 row)
    

描述

根据参数是否为空，返回相应的值。

语法

IFNULL(expr1 anycompatible, expr2 anycompatible) RETURNS anycompatible
IFNULL(expr1 text, expr2 text) RETURNS text
    

通用规则

示例

在以下示例中，当第一个参数为null时，返回第二个参数15。否则，返回第一个参数1。

select ifnull(1, 15);
  ifnull 
--------
      1
(1 row)
select ifnull(null, 15);
  ifnull 
--------
      15
(1 row)
    

描述

测试参数是否为NULL。

语法

isnull(text) RETURNS bool
isnull(numeric) RETURNS bool
isnull(timestamptz) RETURNS bool
    

通用规则

例子

在下面的例子中，expr不为NULL，ISNULL()返回false。

select isnull(0)::text;
isnull 
--------
false
(1 row)
    

描述

除法运算符。被零除会产生一个NULL结果，报告一个警告日志。

例子

除法运算符示例。被零除会产生一个NULL结果，报告一个警告日志。

select 2 / 4;
  ?column?        
------------------------
 0.50000000000000000000
(1 row)

select 1.0 / 2;
  ?column?        
------------------------
 0.50000000000000000000
(1 row)

select 2 / 0;
WARNING:  division by zero
 ?column? 
----------
         
(1 row)
    

描述

等于操作符，支持varchar与bool或text。相等返回true，不相等返回false。

示例

select '1'::varchar = true;
?column?
----------
  t
(1 row)

select '1'::varchar = '1'::text;
?column?
----------
  t
(1 row)

create table a(b varchar(5), c int);
insert into a values(true, 1);
insert into a values('true', 2);

select * from a;
  b   | c
------+---
 1    | 1
 true | 2
(2 rows)

select * from a where b = true;
 b | c
---+---
 1 | 1
(1 row)

select * from a where b = 'true';
  b   | c
------+---
 true | 2
(1 row)

描述

不等于操作符，支持varchar与bool或text。不相等返回true，相等返回false。

示例

select '1' != true;
?column?
----------
  f
(1 row)

select '1'::varchar != '1'::text;
?column?
----------
  f
(1 row)

create table a(b varchar(5), c int);
insert into a values(true, 1);
insert into a values('true', 2);

select * from a;
  b   | c
------+---
 1    | 1
 true | 2
(2 rows)

select * from a where b != true;
  b   | c
------+---
 true | 2
(1 row)

select * from a where b != 'true';
 b | c
---+---
 1 | 1
(1 row)

描述

此前，bool类型转换为varchar形式上保持不变，如true转换成'true'或false转换成'false'。Myfce改变上述规则，与mysql兼容，转换bool类型true至字符串'1'及false至字符串'0'。

示例

create table tb (a varchar(5), b int);      
insert into tb values('true', 2);		      -- 插入字符串'true'，与期望一致
insert into tb values(true, 1);           -- 插入布尔类型true，而期望插入字符串'1'
insert into tb values(1, 3);			        -- 插入数值1，而期望插入字符串'1'
insert into tb values('1', 4);			      -- 插入字符串'1'，与期望一致
insert into tb values(false, 5);			    -- 插入布尔类型false，而期望插入字符串'0'
insert into tb values('false', 6);		    -- 插入字符串'false'，与期望一致
insert into tb values(0, 7);			        -- 插入数值0，而期望插入字符串'0'
insert into tb values('0', 8);			      -- 插入字符串'0'，与期望一致
select * from tb;
a   | b
-------+---
 true  | 2
 1     | 1
 1     | 3
 1     | 4
 0     | 5
 false | 6
 0     | 7
 0     | 8
(8 rows)

描述

转换expr表达式为unsigned bigint 值.

通用规则

示例

lightdb@test_m=# select cast(123 as unsigned) as unsigned;
 unsigned 
----------
      123
(1 row)

lightdb@test_m=# select cast(-123 as unsigned) as unsigned;
       unsigned       
----------------------
 18446744073709551493
(1 row)

lightdb@test_m=# select cast(18446744073709551615 as unsigned) as unsigned;
       unsigned       
----------------------
 18446744073709551615
(1 row)

lightdb@test_m=# select cast(18446744073709551616 as unsigned) as unsigned;
WARNING:  unsigned bigint out of range, Truncated
       unsigned       
----------------------
 18446744073709551615
(1 row)

lightdb@test_m=# select cast(-9223372036854775808 as unsigned) as unsigned;
      unsigned       
---------------------
 9223372036854775808
(1 row)

-- mysql5.7为 0 ，mysql8.0: 9223372036854775808
lightdb@test_m=# select cast(-9223372036854775809 as unsigned) as unsigned;
WARNING:  unsigned bigint out of range, Truncated
      unsigned       
---------------------
 9223372036854775808
(1 row)

lightdb@test_m=# select cast(1.4 as unsigned) as unsigned;
 unsigned 
----------
        1
(1 row)

lightdb@test_m=# select cast(1.5 as unsigned) as unsigned;
 unsigned 
----------
        2
(1 row)

lightdb@test_m=# select cast(1.9 as unsigned) as unsigned;
 unsigned 
----------
        2
(1 row)

-- mysql5.7为 0 ，mysql8.0: 18446744073709551614
lightdb@test_m=# select cast(-1.9 as unsigned) as unsigned;
       unsigned       
----------------------
 18446744073709551614
(1 row)

-- mysql5.7为 0 ，mysql8.0: 18446744073709551614
lightdb@test_m=# select cast(-1.5 as unsigned) as unsigned;
       unsigned       
----------------------
 18446744073709551614
(1 row)

-- mysql5.7为 0 ，mysql8.0: 18446744073709551615
lightdb@test_m=# select cast(-1.4 as unsigned) as unsigned;
       unsigned       
----------------------
 18446744073709551615
(1 row)

-- mysql: 1
lightdb@test_m=# select cast('1.4' as unsigned) as unsigned;
 unsigned 
----------
        1
(1 row)

-- mysql: 1
lightdb@test_m=# select cast('1.5' as unsigned) as unsigned;
 unsigned 
----------
        2
(1 row)

-- mysql: 1
lightdb@test_m=# select cast('1.9' as unsigned) as unsigned;
 unsigned 
----------
        2
(1 row)

-- mysql: 18446744073709551615
lightdb@test_m=# select cast('-1.4' as unsigned) as unsigned;
       unsigned       
----------------------
 18446744073709551615
(1 row)

-- mysql: 18446744073709551615
lightdb@test_m=# select cast('-1.5' as unsigned) as unsigned;
       unsigned       
----------------------
 18446744073709551614
(1 row)

-- mysql: 18446744073709551615
lightdb@test_m=# select cast('-1.9' as unsigned) as unsigned;
       unsigned       
----------------------
 18446744073709551614
(1 row)

lightdb@test_m=# select cast(2e2 as unsigned) as unsigned;
 unsigned 
----------
      200
(1 row)

lightdb@test_m=# select cast('2e2' as unsigned) as unsigned;
 unsigned 
----------
      200
(1 row)

lightdb@test_m=# create table test_c(key1 float4);
CREATE TABLE
lightdb@test_m=# insert into test_c values('-1.6');
INSERT 0 1
lightdb@test_m=# insert into test_c values('-1.4');
INSERT 0 1
lightdb@test_m=# insert into test_c values('-1.5');
INSERT 0 1
lightdb@test_m=# select cast(key1 as unsigned) , key1 from test_c;
   numeric2unsigned   | key1 
----------------------+------
 18446744073709551614 | -1.6
 18446744073709551615 | -1.4
 18446744073709551614 | -1.5
(3 rows)

lightdb@test_m=# select cast(cast('20121212' as date) as unsigned);
ERROR:  cannot cast type date to numeric
LINE 1: select cast(cast('20121212' as date) as unsigned);
               ^
lightdb@test_m=# 

描述

hex函数将bytea类型数据转成text类型。

语法

hex(data bytea) returns text
    

示例

select hex('\x0123456789abcdef');
       hex        
------------------
 0123456789abcdef
(1 row)

描述

unhex函数将varchar类型数据转成bytea类型。

语法

unhex(data varchar) returns bytea
    

示例

select unhex('0123456789abcdef');
       unhex        
--------------------
 \x0123456789abcdef
(1 row)

描述

AES加密函数。

语法

aes_encrypt(data bytea, key bytea) returns bytea
aes_encrypt(data varchar, key varchar) returns bytea
    

示例

select aes_encrypt('world', '123456');
            aes_encrypt             
------------------------------------
 \x85400d3bbeeeb51259f497fcb4bee03e
(1 row)

描述

AES解密函数。

语法

aes_decrypt(data bytea, key bytea) returns bytea
aes_decrypt(data varchar, key varchar) returns bytea
aes_decrypt(data bytea, key varchar) returns bytea
    

示例

select aes_decrypt(aes_encrypt('world', '123456'), '123456');
 aes_decrypt  
--------------
 \x776f726c64
(1 row)