一、大小寫特別敏感 大寫欄位需要用“”引號（pg欄位名使用“”，MySQL欄位名使用``） ******表名以及欄位名如果是小寫但是為關鍵字，比如name，則也需使用""； 二、分頁 limit ${limit} offset ${start} 三、判斷條件等號前後欄位類型需要一致 類型轉換相關函數 ...
一、大小寫特別敏感
大寫欄位需要用“”引號（pg欄位名使用“”，MySQL欄位名使用``）
******表名以及欄位名如果是小寫但是為關鍵字，比如name，則也需使用""；
二、分頁
limit ${limit} offset ${start}
三、判斷條件等號前後欄位類型需要一致
類型轉換相關函數
pg使用文檔：https://www.runoob.com/postgresql/postgresql-functions.html
|
函數 |
返回類型 |
描述 |
實例 |
|
to_char(timestamp, text) |
text |
將時間戳轉換為字元串 |
to_char(current_timestamp, 'HH12:MI:SS') |
|
to_char(interval, text) |
text |
將時間間隔轉換為字元串 |
to_char(interval '15h 2m 12s', 'HH24:MI:SS') |
|
to_char(int, text) |
text |
整型轉換為字元串 |
to_char(125, '999') |
|
to_char(double precision, text) |
text |
雙精度轉換為字元串 |
to_char(125.8::real, '999D9') |
|
to_char(numeric, text) |
text |
數字轉換為字元串 |
to_char(-125.8, '999D99S') |
|
to_date(text, text) |
date |
字元串轉換為日期 |
to_date('05 Dec 2000', 'DD Mon YYYY') |
|
to_number(text, text) |
numeric |
轉換字元串為數字 |
to_number('12,454.8-', '99G999D9S') |
|
to_timestamp(text, text) |
timestamp |
轉換為指定的時間格式 time zone convert string to time stamp |
to_timestamp('05 Dec 2000', 'DD Mon YYYY') |
|
to_timestamp(double precision) |
timestamp |
把UNIX紀元轉換成時間戳 |
to_timestamp(1284352323) |
四、日期格式轉換（格式跟oracle類似）
eg：
時間轉字元串：to_char(now(),'yyyy-mm-dd hh24:mi:ss')；
|
占位符 |
描述 |
|
HH |
一天的小時數(01-12) |
|
HH12 |
一天的小時數(01-12) |
|
HH24 |
一天的小時數(00-23) |
|
MI |
分鐘(00-59) |
|
SS |
秒(00-59) |
|
MS |
毫秒(000-999) |
|
US |
微秒(000000-999999) |
|
AM |
正午標識(大寫) |
|
Y,YYY |
帶逗號的年(4和更多位) |
|
YYYY |
年(4和更多位) |
|
YYY |
年的後三位 |
|
YY |
年的後兩位 |
|
Y |
年的最後一位 |
|
MONTH |
全長大寫月份名(空白填充為9字元) |
|
Month |
全長混合大小寫月份名(空白填充為9字元) |
|
month |
全長小寫月份名(空白填充為9字元) |
|
MON |
大寫縮寫月份名(3字元) |
|
Mon |
縮寫混合大小寫月份名(3字元) |
|
mon |
小寫縮寫月份名(3字元) |
|
MM |
月份號(01-12) |
|
DAY |
全長大寫日期名(空白填充為9字元) |
|
Day |
全長混合大小寫日期名(空白填充為9字元) |
|
day |
全長小寫日期名(空白填充為9字元) |
|
DY |
縮寫大寫日期名(3字元) |
|
Dy |
縮寫混合大小寫日期名(3字元)dy縮寫小寫日期名(3字元) |
|
DDD |
一年裡的日子(001-366) |
|
DD |
一個月里的日子(01-31) |
|
D |
一周里的日子(1-7；周日是1) |
|
W |
一個月里的周數(1-5)(第一周從該月第一天開始) |
|
WW |
一年裡的周數(1-53)(第一周從該年的第一天開始) |
轉時間格式
to_date( #{req_tm},'yyyy-mm-dd')
select now()::varchar(19);
select cast( '2018-03-22 06:15:16'as timestamp)
|
函數 |
返回類型 |
描述 |
例子 |
|
to_char(timestamp, text) |
text |
把時間戳轉換成字串 |
to_char(current_timestamp, 'HH12:MI:SS') |
|
to_char(interval, text) |
text |
把時間間隔轉為字串 |
to_char(interval '15h 2m 12s', 'HH24:MI:SS') |
|
to_char(int, text) |
text |
把整數轉換成字串 |
to_char(125, '999') |
|
to_char(double precision, text) |
text |
把實數/雙精度數轉換成字串 |
to_char(125.8::real, '999D9') |
|
to_char(numeric, text) |
text |
把numeric轉換成字串 |
to_char(-125.8, '999D99S') |
|
to_date(text, text) |
date |
把字串轉換成日期 |
to_date('05 Dec 2000', 'DD Mon YYYY') |
|
to_timestamp(text, text) |
timestamp |
把字串轉換成時間戳 |
to_timestamp('05 Dec 2000', 'DD Mon YYYY') |
|
to_timestamp(double) |
timestamp |
把UNIX紀元轉換成時間戳 |
to_timestamp(200120400) |
|
to_number(text, text) |
numeric |
把字串轉換成numeric |
to_number('12,454.8-', '99G999D9S') |
五、實現MySQL中的GROUP_CONCAT()方法
使用：array_to_string(GROUP_CONCAT(DISTINCT cgu.group_id),',')
創建自定義聚合函數
CREATE AGGREGATE group_concat(anyelement)
(
sfunc = array_append, -- 每行的操作函數，將本行append到數組裡
stype = anyarray, -- 聚集後返回數組類型
initcond = '{}' -- 初始化空數組
);
六、null替換
coalesce(pmodule_id,'')
七、模糊查詢
SELECT * FROM public."user"
WHERE UPPER(username) LIKE UPPER('%' || #{username} || '%');
SELECT * FROM public."user"
WHERE CONCAT(username) LIKE CONCAT('%', #{username}, '%');
SELECT * FROM public."user"
WHERE username LIKE '%' || #{username} || '%';
八、實現MySQL中的主鍵自增功能
為欄位添加自增功能時需要創建序列
--創建序列欄位自增
CREATE SEQUENCE imp_item_status_map_seq --序列名
START WITH 200 --起始值
INCREMENT BY 1 --增量
NO MINVALUE
NO MAXVALUE
CACHE 1;
--刪除序列
DROP SEQUENCE imp_item_status_map_seq cascade;
--將表中欄位關聯序列
--mapping_id 需要關聯表中的欄位
alter table imp_item_status_map alter column mapping_id set default nextval('imp_item_status_map_seq');
九、
--創建類似UUID（）函數方法
create or replace function random_string(integer)
returns text as
$body$
select array_to_string(array(select substring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz' FROM (ceil(random()*62))::int FOR 1) FROM generate_series(1, $1)), '');
$body$
language sql volatile;
