Mysql语法差异
MySQL 是使用非常广泛的开源关系型数据库，它非常易于安装与管理，可以适应大多数存储场景。但是在一些特定领域也有一定的局限性。
UDB会尽量遵循现有的数据库标准，但是 MySQL 不然，它在这反面有这一些混合的背景。如果你之前是使用的 MySQL 或 Microsoft Access，对于某些更改可能会觉得不习惯（比如不能使用双引号来引用字符串）。
本节描述从 MySQL 迁移到 UDB 数据库需要关注的问题。
MySQL 与 UDB的不同点
MySQL 使用非标准的 # 作为一行注释的开头， UDB使用 – （双破折号），这个是符合 ANSI 标准的。并且在两个数据库中都适用。
MySQL 使用 ‘ 或 “ 来引用数值（即 WHERE name=”John”），这并不符合数据库标准。UDB 只使用单引号 ‘（即 WHEREname=’John’）。UDB中双引号仅用于系统标识符，字段名，表名等（如 WHERE “lastname”=’Smith’）。
MySQL 使用反引号 ` 来引用系统标识符，这是绝对非数据库标准的。
UDB在比较字符串时会 区分大小写 。字段 “Smith” 与 “smith” 是不一样的。这与 MySQL 以及其它小型数据库系统（如 Microsoft Access）有很大差异。在 UDB中，可以有以下操作：
在查询时使用正确的大小写（即，WHERE name=’Smith’）。
使用转换函数，如搜索查询时使用lower()（即，- WHERE lower(Iname)=’smith’）。
使用不区分大小写的运算符，如 LIKE 或 ~*。
UDB中的数据库、表、字段和列的名称都是不区分大小写的，如果需要区分大小写可以用双引号 “ 括起来。MySQL 中，表名可以区分大小写，也可以不区分大小写，这个取决于使用的操作系统。
MySQL 中支持 C 语言逻辑运算符（如 ‘foo’||’bar’ 表示 ‘foo’OR’bar’，’foo’&&’bar’ 表示 ‘foo’AND’bar’）。这虽然能对 C 语言开发者提供些帮助，但是却是以违反数据库标准为代价。UDB遵循数据库标准，使用 || 表示字符串连接（’foo’||’bar’=’foobar’）。
常见适配问题
为了尽量减少从源数据库迁移到udb数据库后，应用端的代码修改工作量和复杂度，下文列出了一些常见问题，同时列出部分可以直接在udb数据库端解决的适配问题(比如缺少函数等)。
大小写敏感
引号引起来的字符串大小写完全匹配，
无引号引起来则全部作为小写
单双引号
单引号只能用在值的位置，
比如：
select * from tb where name='张三'
错误写法:
-- select * from tb where name="张三"
双引号可以用在列名，表名 的位置
比如：
select * from "tb" where "name"='张三'
错误写法:
-- select * from 't1' where "name"="33"
关键字必须用双引号引起来
比如:
select * from “user” where id=1
整型可以不使用引号，也可以使用引号
比如：
select * from “user” where id=1
to_days
CREATE OR REPLACE FUNCTION to_days(TIMESTAMP with time zone) RETURNS int AS $$ SELECT $1::TIMESTAMP::DATE - '0001-01-01bc' $$ LANGUAGE sql;
create or replace function to_days(interval)
returns interval as $$
select extract(epoch from $1) / (24 * 60 * 60) * interval '1day';
$$ language sql;
from_days
CREATE OR REPLACE FUNCTION from_days(int) RETURNS date AS $$ SELECT date '0001-01-01bc' + $1 $$ LANGUAGE sql;
类型隐式转换
CREATE CAST (source_type AS target_type) WITH INOUT [FUNCTION function_name()] [AS IMPLICIT|ASSIGNMENT];
举例:
对time类型做like字符串查询
select * from users where o like '%57%'
更换为
select * from users where o::text like '%57%'
创建time as text 类型隐式转换后 则不需要用::转换类型。
注意：如果用 xx as varchar 则可能不生效。
CREATE CAST (time AS text) WITH INOUT AS IMPLICIT;
select * from users where o like '%57%';
drop cast (time AS text);
对int类型做like查询
select * from users where id like '%3%' -- 报错
select * from users where id::varchar like '%3%' -- 正常
CREATE CAST (int4 AS char) WITH INOUT AS IMPLICIT; --创建隐式转换
select * from users where id like '%3%'； -- 正常
drop cast (int4 AS char); --删除隐式转换
查看所有隐式转换
select
(select typname from pg_type where oid = t.castsource) as "castsource",
(select typname from pg_type where oid = t.casttarget) as "casttarget",
_type
castcontext,
castmethod
from pg_cast as t
参数:
source_type 转换的源数据类型的名称。
target_type 转换的目标数据类型的名称。
function_name[(argument_type [, …])] 用于执行强制转换的函数。函数名称可以是模式限定的。如果不是，将在模式搜索路径中查找该函数。函数的结果数据类型必须与转换的目标类型匹配。如果未指定参数列表，则函数名称在其 中必须是唯一的。
WITHOUT FUNCTION 指示源类型可二进制强制转换为目标类型，因此不需要任何函数来执行转换。
WITH INOUT 指示强制转换是 I/O 转换强制转换，通过调用源数据类型的输出函数并将结果字符串传递给目标数据类型的输入函数来执行。
AS ASSIGNMENT 指示可以在赋值上下文中隐式调用强制转换。
AS IMPLICIT 指示可以在任何上下文中隐式调用转换。*
operator does not exist: character varying = integer
原因：=号两边类型不匹配，比如字符串=整型
解决：
把值用单引号引起来
示例：
select t1.id from t1 where name='33'
2，使用cast转换类型
示例：
select * from t1 where name=cast(33 as varchar)
substring_index()
SELECT SUBSTRING_INDEX('a,b,c,d',',',-1);
在mysql中结果为d
UDB中无该函数，创建自定义函数，实现该功能
CREATE OR REPLACE FUNCTION substring_index(varchar, varchar, integer)
RETURNS varchar AS $$
DECLARE
tokens varchar[];
length integer ;
indexnum integer;
BEGIN
tokens := pg_catalog.string_to_array($1, $2);
length := pg_catalog.array_upper(tokens, 1);
indexnum := length - ($3 * -1) + 1;
IF $3 >= 0 THEN
RETURN pg_catalog.array_to_string(tokens[1:$3], $2);
ELSE
RETURN pg_catalog.array_to_string(tokens[indexnum:length], $2);
END IF;
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
timestampdiff
create or REPLACE FUNCTION timestampdiff(HOUR text,create_time TIMESTAMP,end_time TIMESTAMP)
RETURNS BIGINT
as
$$
BEGIN
if upper($1)='SECOND' then
return trunc(extract(EPOCH FROM ($3 - $2::TIMESTAMP)) )::bigint;
end if;
if UPPER($1)='HOUR' then
return trunc(extract(EPOCH FROM ($3 - $2::TIMESTAMP)/3600) )::bigint;
end if;
if upper($1)='DAY' then
return trunc(extract(EPOCH FROM ($3 - $2::TIMESTAMP)/3600/24) )::bigint;
end if;
if upper($1)='MONTH' then
return trunc(extract(EPOCH FROM ($3 - $2::TIMESTAMP)/3600/24/30) )::bigint;
end if;
if upper($1)='YEAR' then
return trunc(extract(EPOCH FROM ($3 - $2::TIMESTAMP)/3600/24/365) )::bigint;
end if;
end;
$$
LANGUAGE plpgsql
hex
encode(字段名::bytea,'hex')
if
create or replace function if(bln boolean,inValue1 anyelement,inValue2 anyelement)
returns anyelement as
$$
begin
if bln=true then
return inValue1;
else
return inValue2;
end if;
end;
$$
language plpgsql;
DATE_FORMAT
解决：
使用 to_char
DATE_FORMAT(b.update_date,'%Y-%m-%d %H:%i')
to_char(b.update_date,'YYYY-MM-DD HH24:MI')
手动创建DATE_FORMAT函数，更多格式化字符需要都补充上
CREATE OR REPLACE FUNCTION date_format(indate anyelement, intext text)
RETURNS text
LANGUAGE plpgsql
AS $function$
BEGIN
IF upper(inText) = upper('%Y%m%d_%H%i') THEN
return to_char(inDate,'YYYYMMDD_HH24MI');
END IF;
IF upper(inText) = upper('%Y%m%d%H%i%s') THEN
return to_char(inDate,'YYYYMMDDHH24MISS');
END IF;
IF upper(inText) = upper('%Y-%m-%d %H') THEN
return to_char(inDate,'YYYY-MM-DD HH24');
END IF;
IF upper(inText) = upper('%Y-%m-%d') THEN
return to_char(inDate,'YYYY-MM-DD');
END IF;
IF upper(inText) = upper('%Y-%m') THEN
return to_char(inDate,'YYYY-MM');
end if;
IF upper(inText) = upper('%m%d') THEN
return to_char(inDate,'MMDD');
END IF;
raise exception '时间格式:% 未在函数中定义，请添加！',$2;
return '';
END;
$function$
select date_format()
field
解决：
手动创建函数，要注意结果是否符合预期
CREATE FUNCTION field(anyelement, VARIADIC anyarray) RETURNS numeric AS $$
SELECT
COALESCE(
( SELECT i FROM generate_subscripts($2, 1) gs(i)
WHERE $2[i] = $1 ),
0);
$$ LANGUAGE SQL STABLE
select * from t1 order by age, field(name,'dd','a') desc
使用case when 整型间接排序
select * from t1 order by age,
case
when name='dd' then 3
when name='a' then 2
else 1
end
group by 相对mysql,必须写全列名
参考mysql官方文档 https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html group by 只写一列并不是严格的SQL标准,只是简化了查询操作,不建议使用它. 在mysql 5.7以后默认已不允许这种操作,需要修改 sql_mode 关闭 ONLY_FULL_GROUP_BY 才允许. 在udb中可以用 DISTINCT ON 实现类似功能. 例如 mysql8 默认开启full group by,非标准sql会报错.
select * from tb1 group by name;
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.tb1.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
查看已启用ONLY_FULL_GROUP_BY
select @@global.sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
禁用ONLY_FULL_GROUP_BY,再次查询正常
set @@sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
select * from tb1 group by name;
conv函数
暂不支持
获取汉字拼音首字母大写函数
CREATE OR REPLACE FUNCTION py_first(s character varying)
RETURNS character varying AS
$BODY$
declare
retval character varying;
c character varying;
l integer;
b bytea;
w integer;
begin
if s='-' then
RETURN '-';
end if;
l=length(s);
retval='';
while l>0 loop
c=left(s,1);
b=convert_to(c,'GB18030')::bytea;
if get_byte(b,0)<127 then
retval=retval || upper(c);
elsif length(b)=2 then
begin
w=get_byte(b,0)*256+get_byte(b,1);
--汉字GBK编码按拼音排序，按字符数来查找，基于概率来说，效率应该比这个强:)
if w between 45217 and 45252 then --"A";45217;45252;35
retval=retval || 'a';
elsif w between 45253 and 45760 then --"B";45253;45760;507
retval=retval || 'b';
elsif w between 45761 and 46317 then --"C";45761;46317;556
retval=retval || 'c';
elsif w between 46318 and 46825 then --"D";46318;46825;507
retval=retval || 'd';
elsif w between 46826 and 47009 then--"E";46826;47009;183
retval=retval || 'e';
elsif w between 47010 and 47296 then--"F";47010;47296;286
retval=retval || 'f';
elsif w between 47297 and 47613 then --"G";47297;47613;316
retval=retval || 'g';
elsif w between 47614 and 48118 then --"H";47614;48118;504
retval=retval || 'h';
elsif w between 48119 and 49061 then --"J";48119;49061;942
retval=retval || 'j';
elsif w between 49062 and 49323 then--"K";49062;49323;261
retval=retval || 'k';
elsif w between 49324 and 49895 then --"L";49324;49895;571
retval=retval || 'l';
elsif w between 49896 and 50370 then --"M";49896;50370;474
retval=retval || 'm';
elsif w between 50371 and 50613 then --"N";50371;50613;242
retval=retval || 'n';
elsif w between 50614 and 50621 then --"O";50614;50621;7
retval=retval || 'o';
elsif w between 50622 and 50905 then--"P";50622;50905;283
retval=retval || 'p';
elsif w between 50906 and 51386 then --"Q";50906;51386;480
retval=retval || 'q';
elsif w between 51387 and 51445 then--"R";51387;51445;58
retval=retval || 'r';
elsif w between 51446 and 52216 then --"S";51446;52217;
retval=retval || 's';
elsif w between 52218 and 52697 then --"T";52218;52697;479
retval=retval || 't';
elsif w between 52698 and 52979 then--"W";52698;52979;281
retval=retval || 'w';
elsif w between 52980 and 53688 then --"X";52980;53688;
retval=retval || 'x';
elsif w between 53689 and 54480 then --"Y";53689;54480;791
retval=retval || 'y';
elsif w between 54481 and 55289 then --"Z";54481;55289;808
retval=retval || 'z';
end if;
end;
end if;
s=substring(s,2,l-1);
l=l-1;
end loop;
return upper(left(retval,1));
end;
$BODY$
LANGUAGE plpgsql IMMUTABLE;
hex函数
create function hex(text) returns text language sql immutable strict as $$
select encode($1::bytea, 'hex')
$$;
create function hex(bigint) returns text language sql immutable strict as $$
select to_hex($1)
$$;
create function unhex(text) returns text language sql immutable strict as $$
select encode(decode($1, 'hex'), 'escape')
$$;
数据结构差异
| mysql | UDB |
|---|---|
| TINYINT | SMALLINT |
| SMALLINT | SMALLINT |
| MEDIUMINT | INTEGER |
| BIGINT | BIGINT |
| FLOAT | REAL |
| DOUBLE | DOUBLE PRECISION |
| BOOLEAN | BOOLEAN |
| TINYTEXT | TEXT |
| TEXT | TEXT |
| MEDIUMTEXT | TEXT |
| LONGTEXT | TEXT |
| BINARY(n) | BYTEA |
| VARBINARY(n) | BYTEA |
| TINYBLOB | BYTEA |
| BLOB | BYTEA |
| MEDIUMBLOB | BYTEA |
| LONGBLOB | BYTEA |
| DATE | DATE |
| TIME | TIME [WITHOUT TIME ZONE] |
| DATETIME | TIMESTAMP [WITHOUT TIME ZONE] |
| TIMESTAMP | TIMESTAMP [WITHOUT TIME ZONE] |
| AUTO_INCREMENT | SERIAL , BIGSERIAL |
| column ENUM (value1, value2, […] | column VARCHAR(255) NOT NULL, CHECK (column IN (value1, value2, […])) 自定义数据类型实现类似效果： CREATE TYPE mood AS ENUM ('sad','ok','happy'); CREATE TABLE person ( current_mood mood ... ) |
语法差异
| 语法差异 | mysql | UDB | 是否相同 |
|---|---|---|---|
| 分页 | select * from t1 limit 2,2; | select * from tbl limit 2 offset 2; | 否 |
| 插入数据时：如果不存在则insert，存在则update | replace实现 | upsert | 否 |
| 大小写兼容 | 通过配置可兼容 | 表字段或表名为大写时，字段或表名必须添加双引号 | 否 |
| if(), case when | if(), case when 条件1 then 符合值 else 不符合值 end； | case when 条件1 then 符合值 else 不符合值 end； | 否 |
| round(字段，小数位数) | round(字段，小数位数) | round(case(‘字段’ as numeric),小数位数) | 否 |
| null值判断 | 支持 ifnull()，NVL(),COALESCE() | 支持COALESCE() | 否 |
| Update-单表更新 | 相同 | 相同（不可全表更新） | 是 |
| update-更新单表多个字段 | 相同 | 相同 | 是 |
| update-更新并返回 | select tem1,tem2 from update test set tem1 = '',tem2 = '' | UPDATE test SET tem1 = '',tem2 = '' RETURNING tem2,tem2; | 否 |
| Update表关联更新 | 相同 | 相同 | 是 |
| Insert-单行插入 | 相同 | 相同 | 是 |
| Insert-插入指定字段 | 相同 | 相同 | 是 |
| insert-插入多行 | 相同 | 相同 | 是 |
| insert-插入并返回 | 不支持 | INSERT INTO() RETURNING did | 否 |
| Insert-插入，存在则更新 | INSERT INTO () VALUE() ON DUPLICATE KEY UPDATE name = EXCLUDED.name | INSERT INTO distributors ( did , dname ) VALUES ( 9 , ' Antwerp Design' ) ON CONFLICT （did）DO UPDATE SET name = EXCLUDED.name | 否 |
| insert-不存在插入，存在更新 | replace实现 | upsert语句 | 否 |
| SELECT | 相同 | 相同 | 是 |
| DELETE | DELETE FROM table | DELTE FROM table（不可全表删除） | 是 |
| DELETE | DELETE FROM table WHERE | DELETE FROM table WHERE | 是 |
| DELETE-删除并返回 | 不支持 | DELETE FROM table WHERE RETURNING * ; | 否 |
| INDEX-add | 支持alter，create创建 | 支持create | 否 |
| INDEX-delete | 支持alter，drop | 支持drop | 否 |
| 字符串常量 | 支持单双引号 | 支持双引号 | 否 |
| 插入数据时自增主键 | insert into t1(name) values(‘a’); insert into t1(id, name) values(null, ‘b’); | insert into t1(name) values(‘a’); | 否 |
| 库名长度 | 无强制限制 | 表名及字段名总长度不超过63个字符 | 否 |
语法改写示例
| 序号 | 差异关键字 | 差异描述 | mysql示例 | UDB示例 |
|---|---|---|---|---|
| 1 | limit | limit 0,1 改成 limit 1 offset 0 | select * from t_user limit 0,1 | select * from t_user limit 1 offset 0 |
| 2 | 字段大小写 | 查询字段为驼峰命名，加上双引号，select "startTime" form ... | - | - |
| 3 | ifnull | 没有ifnull函数，改用COALESCE()函数， | select ifnull(avatar, 'aa'),"name" from t_user; | select COALESCE(avatar, 'aa'),"name" from t_user; |
| 4 | DATE_SUB() | 没有DATE_SUB()时间计算函数，改用select now() + '1 seconds' 或 select now() + '-1 seconds' | select DATE_SUB(logintime,INTERVAL 1 DAY) from t_user; | select logintime + '1 days' from t_user; |
| 5 | if() | 没有if()函数，改用 case when 条件 then 值 else 值 end | select if(name = 'admin', true, false) as isAdmin from t_user | select case when name = 'admin' then true else false end as isAdmin from t_user |
| 6 | 别名大小写 | 查询字段的别名也需要用双引号包起来 | select start_time as startTime from … | select start_time as "startTime" from … |
| 7 | 正则表达式、REGEXP | 正则表达式匹配，where taget_name ~ '^123$ | asd'...，其中~为匹配正则表达式区分大小写，~*为不区分大小写，前面加叹号则为不匹配正则表达式如：!~ | select * from t_user where name REGEXP '^adm*'; |
| 8 | binary | mysql的where判断加上binary来区分大小写，where binary id = ‘abc’，在udb中是直接区分大小写的，将binary去掉就行 | select * from t_user where binary name = 'ADMIN'; | select * from t_user where name = 'ADMIN'; |
| 9 | date_format() | date_format()函数用不了，换成 to_char，select to_char(create_time, 'yyyy-MM-dd hh24:mi:ss') from ... | select date_format(logintime, '%Y-%m-%d') from t_user; | select to_char(logintime, 'yyyy-MM-dd') from t_user; |
| 10 | group_concat_max_len系统变量 | 在udb中无此变量需要注释此语句 | 无 | 无 |
| 11 | 函数GROUP_CONCAT() | GROUP_CONCAT()函数没有，使用 array_to_string( array_agg( target_name), ',' ) from 代替 |
select GROUP_CONCAT(industry) from t_company group by province; | select array_to_string( array_agg( industry), ',' ) from t_company group by province; |
| 12 | &&，|| | &&和||没有这个符号，用and和or替换 | select * from t_user where ISNULL(avatar) && realname = 'admin'; | select * from t_user where avatar is null and realname = 'admin'; |
| 13 | ISNULL | ISNULL没有,使用is null来进行判断 | select * from t_user where ISNULL(avatar); | select * from t_user where avatar is null; |
| 14 | FIND_IN_SET() | where FIND_IN_ SET('123', user_ids) ... 使用不了，换成 where '123' = ANY( string_to_ array( user_id, ',')) ... |
select * from t_company where find_in_set ('aa', address); |
select * from t_company where 'aa' = ANY(string_to_ array( address, ',')); |
| 15 | 数字字符串比较、连表 | mysql中能直接对数字和字符串进行=相等判断，udb需换成，'123' = cast(123 as VARCHAR) 或者 123 = cast('123' as INTEGER)；或者123 = '123'::INTEGER | select * from t_model_ layout_task record as r left join t_model layout_form as f on r.formId = f.id，其中r.formId是vachar，f.id是int |
select * from t_model_layout_ task_record as r left join t_model_ layout_form as f on r.formId = cast(f.id as VARCHAR) |
| 16 | GROUP BY | GROUP BY 分组查 询不能查不 在分组内 的数据（也 不能对非GROUP BY的字段 进行ORDER BY排序），需要变成 连表查询，链 表查需要注 意是否有 重复数据。 例如：SELECT cname, wmname, MAX( avg) FROM makerar GROUP BY cname ORDER BY wmname;（查询失败） 如果没有重复数据换成： SELECT m.cname, m.wmname, t.mx FROM ( SELECT cname, MAX( avg) AS mx FROM makerar GROUP BY cname ) t JOIN makerar m ON m.cname = t.cname AND t.mx = m.avg ORDER BY m.wmname ; 如果有 重复数据需 要进行去重则换成：（ 利用窗口函数） SELECT cname, DISTINCT ON( wmname)wmname wmname, MAX( avg) OVER ( PARTITION BY wmname ) AS mx FROM makerar 如果需要对 不是去重的字 段进行排序则在最外层 加上排序： SELECT * FROM ( SELECT cname, DISTINCT ON( wmname)wmname wmname, MAX( avg) OVER ( PARTITION BY wmname) AS mx FROM makerar ) bb ORDER BY m.wmname |
select name, count( type) from t_com pany group by type order by type desc; |
select aa.name, aa.count from ( select distinct on( type) type "type",name,count( type) OVER ( PARTITION BY type) from t_company) aa order by aa.type desc; |
| 17 | 表名别名大小写 | 查询结果别名中有大写需要用双引号包含起来如果不包含起来查询结果会自动转为小写，select area_id as "areaId" from ... | ||
| 18 | 表名大小写 | 表名称中含有大写字母需要用双引号将表格名称包含起来 | select PASSWORD from t_user; | select "PASSWORD" from t_user; |
| 19 | ` ` | 查询的字段` 号换成双引号，select "name" from ... | select `name` from t_user; | select "name" from t_user; |
| 20 | instr() | 没有instr()函数，改成like | select * from t_user where INSTR(name,'a')>0 | select * from t_user where name like '%a%' |
| 21 | ON DUPLICATE KEY UPDATE |
mysql的存 在则更新写法，udb 改成： INSERT INTO 表名( 列1, 列2...) VALUES ( '值１', '值２', ...）ON CONFLICT( 唯一或排除 约束字段名) DO UPDATE SET 列１='值', 列２='值', ...; 如果是批量 插入的话改成： INSERT INTO 表名( 列1, 列2...) VALUES ( '值１', '值２', ...）ON CONFLICT( 唯一或排除 约束字段名) DO UPDATE SET 列１=excluded.列1, 列２=excluded.列2, ...; 需要注意的是： 唯一或排除约束字段 名必须是一个 唯一索引或唯 一联合索引，如果填 写多个唯一索 引则不生效，如果要 生效的话，必须把他们建立成一个唯一联合索引。 |
insert into t_user( id, name, logintime, PASSWORD, createuser, updateuser) values( 1, 'test', now( ), 'test', 1, 1) on duplicate key update logintime = values( logintime); |
insert into t_user( id, name, logintime, "PASSWORD", createuser, updateuser) values( 1, 'test', now( ), 'test', 1, 1) ON CONFLICT( id) DO UPDATE SET logintime =excluded. logintime; |
| 22 | unix_timestamp() | 没有unix_timestamp()函数，udb换成date_part( 'epoch', now()):: integer |
select unix_timestamp( createtime) from t_user; |
select date_ part('epoch', createtime):: integer from t_user; |
| 23 | auto_increment | mysql查询information_ schema.tables的auto_increment字段获取主键自增的值，而udb的information_ schema.tables中不存在auto_increment。 |
||
| 24 | SYSDATE() | 没有 SYSDATE() 函数，换成 NOW() | select SYSDATE(); | select now(); |
| 25 | from_unixtime() | 没有from_unixtime()函数，换成to_timestamp() | select from_unixtime( 1673833489); |
select to_timestamp( 1673833489); |
| 26 | uuid() | uuid()函数不存在，安装扩展函数create extension "uuid-ossp"; | select uuid(); | select uuid_generate_ v4(); |
| 27 | 时间模糊查询 | 在进行时间模糊查询的时候需要转换一下时间，例如： SELECT * FROM xxx WHERE to_char(create_time, 'yyyy-MM-dd hh24:mi:ss') LIKE CONCAT(#{mouth},'%') |
select realname ,logintime from t_user where logintime like '%2023%' |
select realname,logintime from t_user where to_char(logintime, 'yyyy-MM-dd hh24:mi:ss') like '%2023%' |
| 28 | replace into | 在业务先删除在插入。 如果可以的话可以用下面的写法，看业务需要 udb 没有这个语法，如果根据以为能改成存在更新不存在就插入的话可以用以下写法： INSERT INTO 表名(列1,列2...) VALUES ('值１', '值２', ...）ON CONFLICT(唯一或排除约束字段名) DO UPDATE SET 列１='值', 列２='值', ...; 如果是批量插入的话改成： INSERT INTO 表名(列1,列2...) VALUES ('值１', '值２', ...）ON CONFLICT(唯一或排除约束字段名) DO UPDATE SET 列１=excluded.列1, 列２=excluded.列2, ...; 需要注意的是： 唯一或排除约束字段名必须是一个唯一索引或唯一联合索引，如果填写多个唯一索引则不生效，如果要生效的话，必须把他们建立成一个唯一联合索引。 |
||
| 29 | like | 如果对int字段进行like判断需要将字段转为VARCHAR类型，例如： where state:: VARCHAR like concat('%', #{state},'%') … |
select id from t_user where id like '%2023%' | select id from t_user where id:: varchar like '%2023%' |
| 30 | 时间/between and | 如果判断between and是时间，情况1、数据库是timestamp，and两边是字符串格式为'yyyy-MM-dd HH:mm:ss'则 where to_char( create_time, 'yyyy-MM-dd hh24:mi:ss') between #{startTime} and #{endTime} |
select * from t_user where logintime between '2023-01-01 00:00:00' and '2023-01-16 23:59:59' |
select * from t_user where to_char( logintime, 'yyyy-MM-dd hh24:mi:ss') between '2023-01-01 00:00:00' and '2023-01-16 23:59:59' |
| 31 | 关键字 | 关键字： name、value、label、moudle 需要双引号包含起来 |
||
| 32 | 运算符号 + | mysql 返回结果可直接通过+拼接字符串，udb改成concat方法 | ||
| 33 | 实体类 | 实体类里面@TableField 里写的字段有大写或者 ` 号的要改成双引号，例如 @TableField( "\"order\"")、@TableField( "\"createTime\"") |
||
| 34 | curdate() | 没有curdate()函数，换成，current_date。注意没有括号例如：select current_date; | select curdate(); | select current_date; |
| 35 | inet_aton()、inet6_aton() | 没有inet_aton()和inet6_aton()函数，使用inet()函数进行判断 | select inet_aton( '1.1.1.1') |
select inet( '1.1.1.1') |
| 36 | locate() | 没有locate()函数，换成position() | 没有locate()函数，换成position() | select position('b' in 'ayyvkhlbm') |
| 37 | ORDER BY | 按照指定字段值排序 | ORDER BY FIELD ( `field`, value1, value2) |
使用CASE WHEN END替代（eg：ORDER BY CASE field WHEN value1 THEN WHEN value2 THEN 2 END） |
| 38 | convert() | 没有convert()函数，用convert_from()替换 | convert( vendor using gbk) |
convert_from( vendor::bytea, 'gbk') |
| 39 | bool字段查询和 插入，不可以使用0，1；修改 udb 数据库内置 转换规则 SQL 语句： update pg_cast set castcontext='i' where castsource = 'integer':: regtype and casttarget= 'boolean':: regtype; |
update pg_cast set castcontext ='i' where castsource ='integer':: regtype and casttarget ='boolean':: regtype; |
type 为 bool 类型 insert into test_ly(id, type) VALUES(2,0); select * from test_ly where type = 1; |
type为 bool类型 insert into test_ly(id, type) VALUES(2,0); select * from test_ly where type = 1; |
| 40 | 关联字段，条件查询字段类型不一致时，产生报错问题；强制转换，如转换为字符串 ::varchar,bigint ::BIGINT |
::BIGINT | select u.id from t_user as u left join t_cascade_work_order w on u.id = w.author_id::BIGINT; |