达梦数据库使用常见错误及解决方案（MySQL）

[-4080]: 不是 group by 表达式

【例子】：

select ri.*,count(bd.id) bindDeviceCount
        from room_ip ri left join bids_device bd on ri.name = bd.room_name

【问题原因】：

• GROUP BY 和 ORDER BY 一起使用时，ORDER BY 要在 GROUP BY 的后面。
• 在 select 需要查询的语句中选中的字段，必须出现在 GROUP BY 子句中。

【解决方法】：

若不想修改 SQL 语句，可以通过以下方法解决： 方法 1：修改 dm.ini 的 compatible_mode 参数为 4，来兼容 MySQL 语法，修改参数后需要重启数据库服务。 方法 2: 非 mysql 兼容模式下（即 COMPATIBLE_MODE 不等于 4），修改 GROUP_OPT_FLAG（动态会话级）参数包含 1 取值，即支持查询项不是 GROUP BY 表达式。

alter  system set 'GROUP_OPT_FLAG'=1 both;

 使用Map接受数据列名会变成大写

 【解决方法】：加上columnNameUpperCase=false配置，如下

        jdbc:dm://192.168.0.96:5236?columnNameUpperCase=false

 【注意事项】：有些关键词还是会出现大写的情况，比如【count，enable】，可以加上双引号的方式来解决，如下：

在DM8上对大字段类型列进行排序、分组等操作时，会报错-2685:试图在blob或者clob列上排序或比较 

【例子】：C1为大字段

• SELECT ID,C1 FROM T ORDER BY C1;
• SELECT ID,COUNT(*) FROM T GROUP BY ID;（虽然没有使用到大字段C1，但是因为表中含有大字段，分组的时候依然会报错）

【解决方法】：

将数据库参数ENABLE_BLOB_CMP_FLAG设置为1后，数据库支持DISTINCT、ORDER BY、分析函数和集函数支持对大字段进行处理。

【注意事项】：

该参数并不能支持GROUP BY 对大字段进行处理。即不能 GROUP BY C1，正常来说也不会对TEXT字段进行分组

 ON DUPLICATE KEY UPDATE语法的改写

【mysql】

    insert into "user"(third_id,third_status,mk_time,flag,card_type,valid_start_time,valid_end_time,user_name,
    organization,department,face_photo,access_card,type,can_access,enable_app,status,register_code,create_time,update_time,tel_extension,card_id)
    values
    <foreach collection="userList" index="index" item="item" separator=",">
      (#{item.thirdId}, #{item.thirdStatus},#{item.mkTime},#{item.flag},#{item.cardType},#{item.validStartTime},
      #{item.validEndTime},#{item.userName},#{item.organization},
      #{item.department}, #{item.facePhoto},#{item.accessCard},0,#{item.canAccess},0,0,#{item.registerCode},#{item.createTime},#{item.updateTime},#{item.telExtension},#{item.cardId})
    </foreach>
    ON DUPLICATE KEY UPDATE
    third_status = values(third_status),
    mk_time = values(mk_time),
    flag = values(flag),
    card_type = values(card_type),
    valid_start_time = values(valid_start_time),
    valid_end_time = values(valid_end_time),
    user_name = values(user_name),
    organization = values(organization),
    department = values(department),
    face_photo = values(face_photo),
    access_card = values(access_card),
    update_time = values(update_time),
    tel_extension = values(tel_extension),
    card_id=values(card_id);

【dm】

      MERGE INTO "user" T1
      USING (
      <foreach collection="userList" item="item" index="index" separator="UNION ALL">
        SELECT
        #{item.thirdId} thirdId, #{item.thirdStatus} thirdStatus,#{item.mkTime} mkTim,#{item.flag} flag,#{item.cardType} cardType,#{item.validStartTime} validStartTime,
        #{item.validEndTime} validEndTime,#{item.userName} userName,#{item.organization} organization,
        #{item.department} department, #{item.facePhoto} facePhoto,#{item.accessCard} accessCard,0 type,#{item.canAccess} canAccess,0 enableApp,
        0 status,#{item.registerCode} registerCode,#{item.createTime} createTime,#{item.updateTime} updateTime,#{item.telExtension} telExtension,#{item.cardId} cardId
        FROM dual
      </foreach>
      ) T2 ON (T1.third_id = T2.thirdId )
      WHEN NOT MATCHED THEN INSERT(third_id,third_status,mk_time,flag,card_type,valid_start_time,valid_end_time,user_name,
      organization,department,face_photo,access_card,type,can_access,enable_app,status,register_code,create_time,update_time,tel_extension,card_id) VALUES
      (T2.thirdId, T2.thirdStatus, T2.mkTim, T2.flag, T2.cardType, T2.validStartTime, T2.validEndTime, T2.userName, T2.organization, T2.department, T2.facePhoto, T2.accessCard,
      T2.type, T2.canAccess, T2.enableApp, T2.status, T2.registerCode, T2.createTime, T2.updateTime, T2.telExtension, T2.cardId
      )
      WHEN MATCHED THEN UPDATE
      SET
      T1.third_status = T2.thirdStatus,
      T1.mk_time = T2.mkTim,
      T1.flag = T2.flag,
      T1.card_type = T2.cardType,
      T1.valid_start_time = T2.validStartTime,
      T1.valid_end_time = T2.validEndTime,
      T1.user_name = T2.userName,
      T1.organization = T2.organization,
      T1.department = T2.department,
      T1.face_photo = T2.facePhoto,
      T1.access_card = T2.accessCard,
      T1.update_time = T2.updateTime,
      T1.tel_extension = T2.telExtension,
      T1.card_id=T2.cardId;

  GROUP_CONCAT改写为LISTAGG

【mysql】

        SELECT
            location_id,
            GROUP_CONCAT( personnel_id ORDER BY snap_time DESC ) ids,
            COUNT(personnel_id) count
        FROM
            user_access_record
        WHERE
            door_no = 1
        GROUP BY
            location_id

【dm】

        SELECT
            location_id,
            LISTAGG( personnel_id,',')WITHIN GROUP(ORDER BY snap_time DESC) ids,
            COUNT(personnel_id) count
        FROM
            user_access_record
        WHERE
            door_no = 1
        GROUP BY
            location_id

SELECT
        pr.id,
        pr.project_no,
        LISTAGG(DISTINCT eir.expert_name,',') stockName,
        LISTAGG(DISTINCT eir2.expert_name,',') expertName
        FROM
        project_sync_record pr
        left join expert_sync_record eir
        on eir.expert_type = '采购人' and pr.project_no = eir.project_no and TO_CHAR (pr.actual_start_time, 'yyyy-mm-dd') = eir.create_date
        left join expert_sync_record eir2
        on eir2.expert_type = '专家' and pr.project_no = eir2.project_no and eir.create_date = eir2.create_date
group by pr.project_no,pr.meeting_type

分组后按组里某个字段排序取最大一条记录的其他字段

【mysql】注意mysql8.0之前是不支持该语法的，顾这里不列例子

【dm】按user_id分组，并取check_time最大的一条记录的result

        SELECT
            "user".id,
            "user".user_name name,
            "user".phone tel,
            "user".photo_url photoUrl,
            ah.check_time checkTime,
            ah.result "result"
        FROM
            ( SELECT id, row_number() over ( PARTITION BY user_id ORDER BY check_time DESC ) AS f_part FROM alarm_handler WHERE record_id = #{id} ) t
                INNER JOIN alarm_handler ah ON t.id = ah.id
                LEFT JOIN "user" ON ah.user_id = "user".id
        WHERE
            t.f_part = 1

达梦建表时主键默认是非聚集索引

达梦建表时主键默认是非聚集索引，默认聚集索引键是 ROWID，即记录默认以 ROWID 在页面中排序。ROWID 是 B 树为记录生成的逻辑递增序号，表上不同记录的 ROWID 是不一样的，并且最新插入的记录 ROWID 最大。很多情况下，以 ROWID 建的默认聚集索引并不能提高查询速度，因为实际情况下很少人根据 ROWID 来查找数据。

如果想和mysql一样，建表时主键即是聚集索引，可以参考如下：

在 dm.ini 配置文件中，可以通过指定 PK_WITH_CLUSTER 使表中的主键为聚集主键。默认情况下，PK_WITH_CLUSTER 为 0，即建表时指定的主键是非聚集主键；若为 1，则主键为聚集主键。

但是有两个注意点：

1.聚集索引只能有一个

2.聚集索引不能和大字段（比如TEXT）同时存在

所以要不要聚集索引还是看实际情况，这一点是和mysql不同的。

关联查询排序索引失效

不知道为什么关联之后达梦索引就变得很不智能，需要用hint提示它该怎么用索引，mysql这方面会好很多

   select /*+ TOP_ORDER_OPT_FLAG(1) */pm.*, 
          "user".organization guikou_organization, 
          "user".department guikou_department, 
          "user".phone guikou_phone 
     from park_manage pm 
LEFT JOIN "user" 
       ON pm.guikou_id = "user".id
 order by pm.create_time desc limit 20;

/*+ TOP_ORDER_OPT_FLAG(1) */的意思参考

查询所有表所占的大小及所在空间

SELECT 
A.OWNER AS "模式",
A.SEGMENT_NAME AS "表名",
A.BYTES/1024 AS "大小(KB)",
A.TABLESPACE_NAME AS "所属表空间",
B.COMMENTS AS "表注释" 
FROM DBA_SEGMENTS A,DBA_TAB_COMMENTS B 
WHERE A.OWNER=B.OWNER 
AND A.SEGMENT_NAME = B.TABLE_NAME 
--AND A.OWNER='SYSDBA' 
ORDER BY SEGMENT_NAME ASC;