2025openGauss数据库开发指南

GuidelinesforopenGaussdatabase Q/CIB4001—2023强制注：按照GB/T15834—2011中对于“方头括号（）”的用法,【强制】用于标示被注释的条款效力。[来源：GB/T15834—2011，4.9，有修改]建议注：按照GB/T15834—2011中对于“方头括号（）”注：命名由【限定词术语(0..n)】[对象类术语(11)]组成，最后一项是编号、代码、金额、标志等desc/group/where/like键字命名表或列，禁止以pg_，gs_开头。可以使用select*frompg_get_keywords()在数据库中查询createtabletestdb.test_t1(idcreatetabletestdb.test_t1(idintnotnullprimarykey,students_countcreatecreatetabletestdb.test_t1(idintnotnullprimarykey,countcreatetablecreatetabletestdb.tmp_test_t1_20221010(idintprimarykey,c唯一索引名为uk_字段名(或缩写)；普通索引名则为idx_字段名(或缩写)；用语主键的索引以pk_fkck开头。createviewtestdb.v_test_t1createviewtestdb.v_test_t1asfromtestdb.test_t1asacreatecreateviewtestdb.test_t1asfromtestdb.test_t1asaselect*--idselect--idselectcol1,...fromtestdb.test_t1whereselectcol1,...fromtestdb.test_t1where【建议】whereselectcol1,...fromselectcol1,...fromtestdb.test_t1where current_timestamp(6)-'1days'::interval;selectselectcol1,...fromtestdb.test_t1whereselectid,from_image_id,from_person_id,from_video_idfromface_datawherecurrent_timestamp(6)-<'1NULL值的比较只能使用ISNULL或ISNOTNULL的方式判断，其他任何形式的逻辑判断都返回NULLselectcol1,...fromselectcol1,...fromtestdb.test_t1whereidisselectselectcol1,...fromtestdb.test_t1whereid=selectidfromselectidfromtestdb.test_t1whereidlikeselectselectidfromtestdb.test_t1whereidlikeselectcol1fromselectcol1fromtestdb.test_t1wheretest_idin(1,2,...,99,101,102,...150)selectselectcol1,...fromtestdb.test_t1wheretest_id!=UNIONALLunionunionall在保障业务逻辑准确的情况下，如果过滤条件只有OR表达式，可以将OR表达式转化为UNIONALL以SELECT*FROMscdc.pub_menuWHERE(cdp=300SELECT*FROMscdc.pub_menuWHERE(cdp=300ANDinline=301)unionallSELECT*FROMscdc.pub_menuWHERE(cdp=301ANDinline=302)unionallSELECT*FROMWHERE(cdp=302ANDSELECTSELECT*FROMWHERE(cdp=300ANDinline=301)OR(cdp=301ANDinline=302)OR(cdp=302AND【建议】可以使用(notexist(not可考虑使用(not)exist代替(not)in。SELECTSELECT*FROMT1WHERENOTEXISTS *FROMT2WHERESELECT*FROMSELECT*FROMT1WHERET1.C1NOTIN(SELECTT2.C2FROM输出，要根据业务逻辑确认其输出是否为NOTNULL。count((col1,col2,col3)count((col1,col2,col3)insertintotestdb.test_t1(id,name)insertintotestdb.test_t1(id,name)valuesinsertinsertinto valuesdeletedelete，从库处理时，可能由于deletedeletefromtestdb.test_t1deletefromtestdb.test_t1whereid>10000anddeletefromtestdb.test_t1whereid>20000and--test_t1--test_t1有大量数据满足id>10deletefromtestdb.test_t1whereid>10;insertintotestdb.test_t1(id,name)insertintotestdb.test_t1(id,name)values(1,'openGauss'),(2,insertinsertintotestdb.test_t1(id,name)values(1,insertintotestdb.test_t1(id,name)values(2,analyze示例：analyze如果要清理表中的所有数据，宜使用truncatetable方式，不要使用deletetable方式。deleteselectselectcount(*)fromuuiduuid。createindex4update、deleteindexcreateindexconcurrentlyDMLmaintenance_work_mem（seral2seral4seral8createtablecreatetabletable1(idserial,info1：应避免在wherepartialindexcreateindexidx_1createindexidx_1ontable_1(id)wherecol<>3：likecollateCbtreecreateindexidx_2createindexidx_2ontable_1usingbtree(idcollate（即默认表空间所在的存储设备，如磁盘）以外的其他存储介质使用，以隔离不同业务可以使用的IO除、切割、合并、清空、交换分区操作不申明UPDATEGLOBALINDEX子句，将会导致索引失效。若此索openGauss时间字段类型有timestampwithtimezone,使用timestamptz时，采用GMT/UTC时间，0selects.id,count(1)selects.id,count(1) staffs_p1sleft filmsfonf.did=s.idgroupbySELECTSELECTid,(SELECTCOUNT(*)FROMfilmsfWHEREf.did=s.id)FROMstaffs_p13个或3易读性使用了子查询，子查询数量宜限定在3个以内。过多的子查询特别是涉及大表时，影响整体sqlselect(selecta.idselect(selecta.idfromtestdb.test_t1asawhere...)fromtestdb.test_t2asbwhereb.id=(selectc.pidtestdb.test_t3ascwhere...)=(selectd.target_namefromtestdb.t4asdwhereselectselect(selecta.idfromtestdb.test_t1asawhere...)fromtestdb.test_t2asbwhereb.id=(selectc.pidfromtestdb.test_t3ascwhere...)and=(selectd.target_namefromtestdb.test_t4asdwhere...)andb.age=(selecte.agetestdb.test_t5asewhere超过3张表或视图进行关联（特别是FULLJOIN）时，执行代价难以估算。宜使用WITHTABLEAS语WITHorder_detailsWITHorder_detailsAS(customer_namesAS(product_namesAS(order_detailsodINNERJOINcustomer_namescnONod.customer_id=cn.customer_idINNERJOINproduct_namespnONduct_id=资源开销。openGaussASE&NULLLASTORDERBY（ASE/DESC），NULL（NULLFIRST/NULLLAST）。不要单独依赖limit子句返回特定顺序的结果集。如果部分特定结果集，可以将ORDERBY子句与selectid,namefromselectid,namefromtestdb.test_t1whereidbetween1and10orderbynamelimitselectselectid,namefromtestdb.test_fromtestdb.test_t1asajointestdb.test_t2asbonselectnamefromtestdb.test_t1test_t1;selectnamefromtestdb.test_t1jointestdb.test_t2oncreatetabletestdb.test_t1(student_idint(10)comment'学号',classesvarchar(30createtabletestdb.test_t1(student_idint(10)comment'学号',classesvarchar(30comment班级',constraintid_pkprimary)createcreatetabletestdb.test_t1(student_idint(10),cidconstraintid_pkprimary)updatetestdb.test_t1updatetestdb.test_t1setage=18whereid=2;deletefromtestdb.test_t1whereupdatetestdb.test_t1setage=18;deletedeletefromtestdb.test_t1where1：文件数据通常很大，转成二进制数据是一串很长的字符串，对数据库的存储空间占用很大，在数据库读写时会消耗内存和占用大量的IO注注：按照企业标准Q/CIB4001—2023要求，编码字符集不应使用GBKselectdatname,datcollate,datctypeselectdatname,datcollate,datctypefromallprivilegepg_hba.confpublic