项目mysql转换为kingbase人大金仓数据库
项目 mysql 转换为 kingbase 人大金仓数据库
官网
已遇到的需要进行替换的部分
UUID() 部分
-
执行语句：
-- mysql： INSERT INTO login_log (ID, USERID, DLSJ) values (REPLACE(UUID(), '-', ''), '47CD913F8D0F4E3E972A8499E406952C', '2023-12-21 00:00:00') -- kingbase： INSERT INTO login_log (ID, USERID, DLSJ) values (SYS_GUID()), '47CD913F8D0F4E3E972A8499E406952C', '2023-12-21 00:00:00')- mysql 里的话，直接用 UUID 的话 id 是带有短横线的，但 kingbase 可以直接使用，没有短横线的情况
进行分组并加以排序选择
mysql
-
要求：DXID 字段进行分组，根据 PX 字段进行降序排序（即：把最大的放在第一位），排序后只取 PX 最大的那一行数据
select id, dxid, xzb, yzb, zzb, wy, cd, create_time, num from ( SELECT *, @num := IF(@dxid = dxid, @num + 1, 1) AS num, @dxid := dxid AS set_dxid FROM em_dxsz_ex CROSS JOIN (SELECT @num := 0, @dxid := '') AS vars where alid = #{alid} ORDER BY dxid, px DESC ) a group by a.dxid- 说实话这里 mysql 的写法我现在的能力没能看透彻
- 直接写 num 的话会被认为是一个字段，但是数据库表里没有这个字段就会报错，加上 @ 的话就可以理解为临时变量（虚拟字段）
- @num := IF(@dxid = dxid, @num + 1, 1) 就是当当前行的 dxid 等于变量 @dxid 的话，就 @num 自增一，否则就将 @num 重置为 1
- 在 select 中 想要赋值只能用 :=，因为 = 就只是比较操作符，不能把右边的值赋给左边
- 这里有一个问题，就是因为 mysql 宽容性比较大，在这里的 group by 确实是排序了，然而 mysql 自行把 a.dxid 相同，但其他字段不同的部分给优化掉了，只保留了之前 order by 排序后在第一位的那行数据（把那些 dxid 相同的，但是经过排序后不是在第一位的那些数据都给去掉了，不会被查询出来），对于 mysql 来说，这里的 group by 的作用其实就是消除重复的行，只保留最大的那行数据
- 说实话这里 mysql 的写法我现在的能力没能看透彻
-
转换成 kingbase 的话就很简单明了了
select id, dxid, xzb, yzb, zzb, wy, cd, create_time, num from ( SELECT ROW_NUMBER() OVER(PARTITION BY "DXID" ORDER BY "PX" DESC) num,t.* FROM em_dxsz_ex t where alid = #{alid} ORDER BY dxid, px DESC ) a WHERE num = 1
kingbase —— row_number() 排序函数
-
解读语句
SELECT ROW_NUMBER() OVER(PARTITION BY "DXID" ORDER BY "PX" DESC) num,t.* FROM em_dxsz_ex t-
这里的 ROW_NUMBER() 函数将针对 SELECT 语句返回的每一行，从 1 开始编号，赋予其连续的编号
-
over() 里面就是：根据 DXID 编号，DXID 同的按 PX 降序进行 1、2、3 ...... 的编号，不同的就还是 1、1、1 ......
- 即 DXID 相同的就进行排序，排序的方式为 PX 降序，即 PX 最大的开始排 1，之后就是 2、3 ......
-
后面的 num 是排序序号的别名，t.* 就是查询所有了
ORDER BY dxid, px DESC- 组和组之间就是按 dxid 升序、px 降序的形式
WHERE num = 1- 就是取每个分组里 DXID 最大的那一条数据，也就是一开始排序时名 num 为 1 的数据
-
-
在使用 row_number() over() 函数时候，over() 里头的分组以及排序的执行晚于 where、group by、order by 的执行
列为 null 时的排序规则不同
-
mysql 和 kingbase 对列为 null 的默认排序规则是不一样的
- 在 MySQL 中，NULL 值被认为是最小的值，所以在升序排序时，NULL 值会排在最前面；而在降序排序时，NULL 值会排在最后面
- 而在 Kingbase 数据库中，NULL 值被认为是最大的值，所以在升序排序时，NULL 值会排在最后面；而在降序排序时，NULL 值会排在最前面
用到 group by 的话，select 的数据要进行分组
- group by 是把字段相同的放在一行里面分组，所以若是 select 里存在有其他 group by 的相同，但其他需要查询的字段不同的话，该语句就会报错（正常情况如此）
mysql
- 但 mysql 要求没那么严谨，会自行分出组后，把那些冲突的数据给优化去掉（只保留一行数据），即：在 mysql 里不会报错
kingbase
- 而在 kingbase 这种严谨的数据库就会报错，比如
select name, age from 表 group by name，name 相同的是放在一起了，但其中 age 不同的怎么办？这时候 select 里的就几乎都要写在 group by 里- 可是这样的话就会出现一个情况：
- 若是有数据：朱呀朱 —— 22岁，朱呀朱 —— 18 岁。mysql 查询出来的就是 “ 朱呀朱 —— 22岁 ” 的数据，但 kingbase 因为又对 age 分组了，所以最后还是查到了所有的数据
- 这并不符合 mysql 向 kingbase 的转化的要求 —— 不管语句如何，至少查询出来的结果要一致才行
- 这里就是需要用上述 “ 进行分组并加以排序选择 ” 时的做法了，把 mysql 优化出来的写为 kingbase 中排序 num 为 1 的即可
select 不用都写在 group by 里的情况
- 除非对一些字段进行了聚合函数的操作，比如把 age 换成 sum(age) 后就不需要再写进 group by 里了（聚合函数就是：输入多个数据，输出一个数据）
- 但如果 group by 后面跟着主键的话，就不需要把 select 里其余的字段都写在 group by 中，因为既然都按照主键分组了，那就肯定不会出现重复的情况了
group_concat 拼接有些差异
-
在单纯简单的拼接下两个数据库之间没有什么差异，如：
-
根据 score 分组，同 scroe 的 student 字段拼、放一起，如：
select score,group_concat(student) from exam group by score; -
默认 student 字段多个是用逗号隔开的
-
-
但若是使用了 group_concat 并且先后还有别的拼接部分就很可能会出问题，比如给每个数据拼接前都用双引号引起来：
GROUP_CONCAT( '"', x.GBMC, '"' ORDER BY cd ) GBMC-
mysql 中此语句根据 cd 字段顺序排序，每个 x.GBMC 拼接在一起并用引号引起来，执行正常符合预期
-
但是在 kingbase 里，不能直接用上述的 sql 语句，不然会报类似 " 函数 pg_catalog.array_agg(unknown, varchar, unknown) 不存在 Hint: 没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换 " 这种错，在官网里可见两个数据库对于此方法的语法是不同的，并且 mysql 能自行拼接 order 前面的数据，而 kingbase 不行，需要先自行拼接再进行排序：
GROUP_CONCAT( concat('"', x.GBMC, '"') ORDER BY cd ) GBMC- 就不会报错与 mysql 展示的效果一致了
-
truncate 与 trunc 平替
- mysql 中 truncate(x, y) 是截取 x 数据留 y 位小数（不是四舍五入）
- 而在 kingbase 中用的是 TRUNC 方法平替
- 四舍五入参考 ROUND 方法
- 但是有一点差异、不同，mysql 的 TRUNCATE (b.SXMD, 3 ) 截取 3 位的话，如果 b.SXMD 不足以 3 位，mysql 会自行补足几个 0，而 kingbase 的 TRUNC 方法则不行，只会单纯的截取，不会补足 0
- 想要进行小数点后面 0 的补齐的话，可以考虑使用 to_char 函数进行格式化的操作
时间方面的一些函数
-
关于时间方面的函数使用，两个数据库的函数都不太相同
-
例如在获取到开始日期与结束日期时，想要做到时间范围的查询，并且只输入一个时间参数就可以执行查询语句：
-
在 mysql 使用 DATE_FORMAT、DATE_ADD 函数（xml 中的显示）：
<if test="endTime != null and endTime != ''"> AND <![CDATA[ cjsj <= DATE_FORMAT(DATE_ADD(#{endTime,jdbcType=VARCHAR},INTERVAL 1 DAY),'%Y-%m-%d')]]> </if>- 一个用来格式化，一个用来向后推迟一天（这样在结束日期时选择当天也可以查询到当天的数据）
-
kingbase 使用 TO_CHAR、TO_DATE 替代：
<if test="endTime != null and endTime != ''"> AND <![CDATA[ cjsj <= TO_CHAR((TO_DATE(#{endTime,jdbcType=VARCHAR}) + 1 ),'%Y-%m-%d')]]> </if>- 可以做到相同的功能
-
转换成数字类型
- mysql 可以用 xx + 0 的方式转为数字类型
- kingbase 不行，会报错 “ 无效的类型 integer 输入语法 ”，这里就需要使用
to_number()函数来进行替换
返回当前日期的函数也不同
- mysql 可以在 sql 语句中用函数 curdate() 获取当前时间
- kingbase 则是使用 current_date() 方法
一些不同的特性
一个可以忽略的但需要了解下的特性
-
在 mysql 中想要进行字符串的拼接的话是用 concat() 等函数来实现的，但是在 kingbase 中除了那些函数的拼接写法，还有 “ || ” 也可以进行拼接，例如：
SELECT * from xxx where name like concat('%', '朱呀朱', '%') -- 在kingbase或者Oracle中还有下述写法也可行： SELECT * from xxx where name like '%' || '朱呀朱' || '%'
在 mysql 中可以直接拼无关联的字段作为 from 的表，但 kingbase 不行
-
在 mysql 中可以直接在 from 中存放无关联字段自成一表，如：
SELECT gc.gcsl, al.alsl, lt.ltsl, gd.gdsl, jd.jdsl, pump.bsl, valve.fsl FROM ( (SELECT COUNT(GCBH) gcsl FROM em_gcxx) gc, (SELECT COUNT(ALBH) alsl FROM em_alxx) al, (SELECT COUNT(LTID) ltsl FROM em_ltxx) lt, (SELECT count(GDID) gdsl from binfo_gdjc) gd, (SELECT count(ID) jdsl from al_zt_exp_yj where SAVE_TYPE ='xx') jd, (SELECT count(id) bsl from al_zt_exp_yj where SAVE_TYPE ='yy') pump, (SELECT count(id) fsl from al_zt_exp_yj where SAVE_TYPE ='zz') valve )-
执行结果为：
-
-
但是这种写法本来就是不够严谨的，在 kingbase 中就需要更改完善语句：
SELECT gc.gcsl, al.alsl, lt.ltsl, gd.gdsl, jd.jdsl, pump.bsl, valve.fsl FROM (SELECT COUNT(GCBH) gcsl FROM em_gcxx) gc left join (SELECT COUNT(ALBH) alsl FROM em_alxx) al on 1 = 1 left join (SELECT COUNT(LTID) ltsl FROM em_ltxx) lt on 1 = 1 left join (select count(GDID) gdsl from binfo_gdjc) gd on 1 = 1 left join (select count(ID) jdsl from al_zt_exp_yj where SAVE_TYPE ='节点') jd on 1 = 1 left join (select count(id) bsl from al_zt_exp_yj where SAVE_TYPE ='泵') pump on 1 = 1 left join (select count(id) fsl from al_zt_exp_yj where SAVE_TYPE ='阀') valve on 1 = 1- 利用 left join 进行连接成一表就正确了
cancat 中出现 null 的情况下
-
语句一：
concat(ltmc, '==>', IF(mnfs = 'hymx', '黑油', '组分')) ltmc-
mysql 在使用 concat 时是一旦有 null，那就全为 null（即这里的 ltmc 若是为 null 的话，整个拼接操作下来的结果其实就是 null）
-
而 kingbase 不是，为 null 的就跨过不加以拼接，所以这里就需要用到 case when else end 来操作了
-
此处的结果可能就是变成了 " ==>黑油 " 这样的数据
-
这里想要用 kingbase 做到同样的想过就需要更改语句为：
CASE WHEN ltmc IS NULL THEN NULL ELSE CONCAT(ltmc, '==>',CASE WHEN mnfs = 'hymx' THEN '黑油' ELSE '组分' END) END AS ltmc
-
-
-
语句二：
ifnull( CONCAT('[{','"x":',dx.XZB,',','"y":',dx.ZZB,'},{','"x":',dx.XZB+ex.xZB,',','"y":',dx.yZB+ex.yZB,'}]'),'' ) lj,- 还是上面说过的：mysql 是 null + 数字 = null, 导致 lj 字段无数据，而 kingbase 中的数据若是 null 就会出现 {"x":,"y":} 的情况也不对
- 这里可以考虑使用 COALESCE(字段 , 0) 函数，将 null 时替换成 0 显示
考虑库的配置
- 数据库进行更改时，要注意数据库对空字符串和 null 的兼容，否则可能会出现查询时：空字符串和 null 都默认为了 null，即不存在空字符串的数据情况
一些在 kingbase 中遇到的问题
单双引号
- 在 mysql 中不管是用单引号还是双引号都可以包裹字符串，而在 kingbase 中，只能用单引号来包裹
IFNULL 函数和 CASE WHEN 语句在处理空值（NULL）时的行为是不同的
- 在 kingbase 数据库里，
IFNULL(JDSZSJ, '[0]') sj可能会报错 “ 错误: 日期/时间值超出范围: "[0]" ”，而写成CASE WHEN JDSZSJ IS NULL THEN '[0]' ELSE CAST(JDSZSJ AS VARCHAR) END sj就不会报错了（JDSZSJ在数据库中是时间类型的 ）- 当使用
IFNULL(JDSZSJ, '[0]') sj时，如果JDSZSJ为 NULL，它会尝试将字符串 "[0]" 转换为日期/时间类型。由于 "[0]" 不是一个有效的日期/时间值，因此会报错 “日期/时间值超出范围”
- 当使用
- 而使用
CASE WHEN JDSZSJ IS NULL THEN '[0]' ELSE CAST(JDSZSJ AS VARCHAR) END sj时，如果JDSZSJ为 NULL，它会返回 "[0]"（作为一个字符串）。然后，当JDSZSJ不为 NULL 时，它会将JDSZSJ转换为 VARCHAR 类型。这样，无论JDSZSJ是 NULL 还是非 NULL，都不会尝试将其转换为日期 / 时间类型，因此不会出现 “日期/时间值超出范围” 的错误
更改的基本技巧
-
更改 select 查询语句时保留更改前的语句，然后和更改后的语句一起查询出来用于比较
-
在更改语句时，可以自行添加一些 select 的字段，便于直接对比
-
更改语句要一层一层的剥（注释或提取出来），嵌套的语句一个一个执行着看返回样式，理解含义
