MySQL/人大金仓与达梦（DM）的SQL语言区别

注：不同版本之间存在差异

1、官方SQL语言文档

达梦-https://download.csdn.net/download/weixin_44167504/73814867
人大金仓-https://download.csdn.net/download/weixin_44167504/87513039

2、时间类型转换的某些函数差异，

如mysql的convert（‘时间’，DATE） 达梦可以使用to_char/date_format('','%Y-%m-%d %H:%i:%s')

//mysql:
convert(Date,DATE)
date_format(Date,'%Y-%m-%d %H:%i:%s')
//达梦：
to_char(Date,'YYYY-MM-DD')
to_char(Date,'YYYY-MM-DD HH24:MI:SS')
to_date(Date,'YYYY-MM-DD HH24:MI:SS')
date_format(Date,'%Y-%m-%d %H:%i:%s')
//人大金仓：
to_date(Date,'YYYY-MM-DD')
to_char(Date,'YYYY-MM-DD HH24:MI:SS')=>转换出的类型是varchar型，不能与时间类型的相比较，可以两个都使用to_char转换的相比较

3、group by

达梦：只能拿到分组 的字段与聚合函数的字段，与postgreSQL相同;只能使用单组分组函数即不支持嵌套使用group by，与postgreSQL、mysql不同；

4、达梦ID自增问题

（1）、已建表
（达梦）仅当指定列列表，且SET IDENTITY_INSERT为ON时，才能对自增列赋值 ；表设置自增后不能对自增列进行赋值，修改表

（2）、建表时设置自增

CREATE TABLE "SCHEMA_NAME"."table_test" (
            "id" INT IDENTITY(1,1) NOT NULL,
            "name" varchar(50),
            NOT CLUSTER PRIMARY KEY("id")) STORAGE(ON "MAIN", CLUSTERBTR) ;
            COMMENT ON COLUMN "SCHEMA_NAME"."table_test"."name" IS '注释';
            );

4、生成随机数

返回一个[0,1]之间的随机浮点数。n 为数值类型，为生成随机数的种子，当 n
省略时，系统自动生成随机数种子

//mysql、达梦
select floor(RAND()*(SELECT MAX(ID) FROM advertising))
//人大金仓
select floor(RANDOM()*(SELECT MAX(ID) FROM advertising))

参考：https://blog.csdn.net/qq_44821411/article/details/113934678

5、达梦不支持使用一个以上的连接 （如：inner join — 新版本已支持）。

6、人大金仓数据库不支持使用！=''、<> ''，需要有明确的不等于某个数值，否则都返回空。

原因：ora_input_emptystr_isnull 参数位于data/kingbase.conf文件中，默认为true，既当输入的为空字符串’’时，是否转换为null。
解决：将ora_input_emptystr_isnull 改为off
（1）法一：在data/kingbase.conf 将ora_input_emptystr_isnull 设置为false，
在Server/bin目录打开终端，然后sys_ctl restart -D 'data所在目录'重启数据库。
（2）法二：在可视化工具中修改（兼容相关 或 其它选项）

7、达梦关键字

达梦数据库返回字段为关键字时一律返回大写（不论字段是否大小写同时存在），除非使用双引号“”，如
value，time，times，sum，max，min，avg，type，count，date，year，month，day，hour，online
等，如select sum(Date) as time from ..或select time from ..=>返回的time字段名为大写。

8、Data_sub函数

DATE_SUB(date,INTERVAL expr type) => DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

expr 在达梦中需要加单引号，建议使用到这类函数时数字加上单引号。

查询近30天的数据

Date_sub 适用于mysql/达梦查询近段时间内的数据

date_sub(curdate(), INTERVAL '30' DAY) <= DATE

PostgreSQL则用

DATE >= NOW() - INTERVAL '30 DAY' 

9、单双引号的差异

达梦数据库对于单引号双引号很敏感，加单引号代表数值，加双引号代表字段；但是在mysql中查询的字段不能直接加双引号，不然会直接将查询字段的值也按字段输出，如select “AlarmDate” from ..=>输出的值为[“AlarmDate”,“AlarmDate”]，mysql中加双引号只能在取别名的时候加，如select AlarmDate as “alarm” from ..。

10、人大金仓id自增问题

（1）人大金仓数据库没有ID自增，可在插入时含ID字段或添加自增函数
参考 ： postgresql主键自增
（2）直接创建表并设置自增函数

#先建表
CREATE TABLE "public"."table_stats"
(
"id" SERIAL NOT NULL,
"datetime" TIMESTAMP,
"succ" INTEGER,
"false" INTEGER
)
#建完表后执行
ALTER SEQUENCE "public"."table_stats_id_seq" INCREMENT BY 1;//起始值
SELECT SETVAL('"public"."table_stats_id_seq"',1); //当前ID值
COMMENT ON COLUMN "public"."table_stats"."id" IS '自增id';
COMMENT ON COLUMN "public"."table_stats"."datetime" IS '上传时间';
COMMENT ON COLUMN "public"."table_stats"."succ" IS '成功数量';
COMMENT ON COLUMN "public"."table_stats"."false" IS '失败数量'

11、空字符

人大金仓中类似于MySQL中IFNULL（d.ID,0）的替代==>COALESCE(d.ID,0) AS “HasBind”；
当某个值为null时赋予某个值给该字段

1、IFNULL(expression,value) ；若 n1 为空，则返回 n2 ; //如：ifnull(hasbind,0) as hasbind
2、ISNULL(n1,n2)=>当 n1 为非空时，返回 n1
3、NVL(expression,value)
4、COALESCE(n1,n2,…nx) 返回第一个非空的值
注：COALESCE(expression,value)=>value类型为varchar， IFNULL(expression,value)=>value类型为integer

*注：新版R6人大金仓中判空函数都能用，需要注意返回的类型需要与被判空的字段类型相近，如expression为int则value不能为varchar*

12、达梦数据库 update

update 自增列不能修改，where字段不能修改（如 where name=''则name不能修改）

13、update联表更新的差异

mysql/达梦：

update tableA,tableB set tableA.name=tableB.name where tableA.id=tableB.id

postgreSQL：

update tableA set tableA.name=tableB.name from tableB  where tableA.id=tableB.id

如官方手册中提到：

14、达梦字段长度一般设置成mysql设置长度的3倍

mysql中存储如varchar类型存一个文字需要占用的长度：

mysql	达梦	编码类型	结论
varchar(1)	varchar(3)	UTF-8	mysql长度是达梦的3倍
varchar(1)	varchar(2)	gb18030	mysql长度是达梦的2倍

数据库时配置LENGTH_IN_CHAR（VARCHAR 类型对象的长度是否以字符
为单位）1：字符，0：字节（默认）。参数修改参考：【达梦数据库】dminit常用参数学习
官方文档定义：

15、人大金仓数据库时间查询优化

注：减少时间格式的转化
对没有索引的字段进行筛选时，如查询当天的数据 select * from table where tday>=to_date(SYSDATE,'YYYY-MM-DD') AND tday<to_date(SYSDATE+1,'YYYY-MM-DD') ，给一个指定的查询范围就不会查询整个表；若如to_date(tday,'YYYY-MM-DD')=to_date(SYSDATE,'YYYY-MM-DD') 则会查询整个表，导致查询速率极低（待完善）

优化后	优化前	注
tday>=to_date(SYSDATE,‘YYYY-MM-DD’) AND tday<to_date(SYSDATE+1,‘YYYY-MM-DD’)	to_date(tday,‘YYYY-MM-DD’)=to_date(SYSDATE,‘YYYY-MM-DD’)	（SYSDATE+1）为当前时间+1天

16、实现跨数据库关联查询

1、mysql/达梦

直接在表名前加上数据库的库名，如select a.name,b.num from db1.table a,db2.table b on a.id=b.id

2、Kingbase（人大金仓）——使用 datebase link

创建新的 database link 对象（需要连接被连接的都要创建），可将dblink建成视图后直接访问视图（相当于访问dblink）

CREATE EXTENSION IF NOT EXISTS dblink;

查看是否成功创建拓展

 select * from pg_extension;

使用方式：
简单查询

select * from dblink('hostaddr=192.168.1.239 port=54321 dbname=db_name user=system password=123456','select name,code from "tablename"') AS tablename("name" VARCHAR,"code" VARCHAR)

or

select * from dblink('hostaddr=192.168.1.239 port=54321 dbname=db_name user=system password=123456','select * from "tablename"') AS tablename

参数名	含义
hostaddr	数据库IP
port	数据库端口
dbname	查询的数据库名称
user	用户名
password	密码

其它请参考：PostgreSql 使用dblink跨库 https://blog.csdn.net/weixin_43865381/article/details/122920855

17、 时间查询 ，获取近几天、一周、一月、季度、一年时间内的数据，两个日期相差多少年、月、日

https://blog.csdn.net/weixin_44167504/article/details/120974653?spm=1001.2014.3001.5501

18、CAST AS类型转换

语法

CAST ( 'string' AS type )

1、KingbaseES 允许它用于所有类型
2、SQL 指定这种语法只用于一些数据类型，mysql中只能使用 BINARY，CHAR，DATE，DATETIME，TIME，DECIMAL，SIGNED，UNSIGNED