当前位置： 首页 > news >正文

有名的设计公司window优化大师

news 2025/8/14 8:08:48
有名的设计公司,window优化大师,衣服网站建设策划书,用php做一网站有哪些东西瀚高数据库 目录 环境 文档用途 详细信息 环境 系统平台：N/A 版本：4.5.7 文档用途 使用存储过程拼接SQL，修改分区名称、分区键值、并重新加入主表，适用于分区表较多场景。 详细信息 说明：本文档为测试过程&#xff1…

瀚高数据库
目录
环境
文档用途
详细信息

环境
系统平台：N/A
版本：4.5.7
文档用途
使用存储过程拼接SQL，修改分区名称、分区键值、并重新加入主表，适用于分区表较多场景。

详细信息
说明：本文档为测试过程；整体测试思路为，先将分区表与主表分离->其次修改该表的表名->然后修改该表与分区键相关数据->最后将该表加入主表。

1.处理过程

1.1创建两张测试表及分区表并插入数据

--创建测试主表1create table test.fenqu1(qhdm text,qxname text,id int,xinxi TEXT) partition by list(qhdm);--创建测试主表为1的分区表create table test.fenqu1_part_120100 partition of test.fenqu1 for values in ('120100');create table test.fenqu1_part_120000 partition of test.fenqu1 for values in ('120000');--创建测试主表2create table test.fenqu2(qhdm text,qxname text,id int,xinxi TEXT) partition by list(qhdm); --创建测试主表为1的分区表create table test.fenqu2_part_120100 partition of test.fenqu2 for values in ('120100');create table test.fenqu2_part_120000 partition of test.fenqu2 for values in ('120000');--插入数据至主表1insert into test.fenqu1 values('120100','bbb','1','zhao');insert into test.fenqu1 values('120100','bbb','2','qian');insert into test.fenqu1 values('120100','bbb','3','sun');insert into test.fenqu1 values('120100','bbb','4','li');insert into test.fenqu1 values('120100','bbb','5','chen');insert into test.fenqu1 values('120100','bbb','6','tie');insert into test.fenqu1 values('120100','bbb','7','song');insert into test.fenqu1 values('120100','bbb','8','shen');insert into test.fenqu1 values('120100','bbb','9','cheng');insert into test.fenqu1 values('120100','bbb','10','wang');insert into test.fenqu1 values('120100','bbb','11','ouyang');insert into test.fenqu1 values('120100','bbb','12','chu');insert into test.fenqu1 values('120000','aaa','20','zhao');insert into test.fenqu1 values('120000','aaa','21','qian');insert into test.fenqu1 values('120000','aaa','22','sun');insert into test.fenqu1 values('120000','aaa','23','li');insert into test.fenqu1 values('120000','aaa','24','chen');insert into test.fenqu1 values('120000','aaa','25','tie');insert into test.fenqu1 values('120000','aaa','26','song');insert into test.fenqu1 values('120000','aaa','27','shen');insert into test.fenqu1 values('120000','aaa','28','cheng');insert into test.fenqu1 values('120000','aaa','29','wang');insert into test.fenqu1 values('120000','aaa','30','ouyang');insert into test.fenqu1 values('120000','aaa','31','chu'); --插入数据至主表2insert into test.fenqu2 values('120100','bbb','1','赵');insert into test.fenqu2 values('120100','bbb','2','钱');insert into test.fenqu2 values('120100','bbb','3','孙');insert into test.fenqu2 values('120100','bbb','4','李');insert into test.fenqu2 values('120100','bbb','5','陈');insert into test.fenqu2 values('120100','bbb','6','铁');insert into test.fenqu2 values('120100','bbb','7','宋');insert into test.fenqu2 values('120100','bbb','8','申');insert into test.fenqu2 values('120100','bbb','9','成');insert into test.fenqu2 values('120100','bbb','10','王');insert into test.fenqu2 values('120100','bbb','11','欧阳');insert into test.fenqu2 values('120100','bbb','12','褚');insert into test.fenqu2 values('120000','aaa','20','ss');insert into test.fenqu2 values('120000','aaa','21','等等');insert into test.fenqu2 values('120000','aaa','22','ff');insert into test.fenqu2 values('120000','aaa','23','刚刚');insert into test.fenqu2 values('120000','aaa','24','给');insert into test.fenqu2 values('120000','aaa','25','铁');insert into test.fenqu2 values('120000','aaa','26','啦啦');insert into test.fenqu2 values('120000','aaa','27','联想');insert into test.fenqu2 values('120000','aaa','28','成');insert into test.fenqu2 values('120000','aaa','29','啊哈哈');insert into test.fenqu2 values('120000','aaa','30','切');insert into test.fenqu2 values('120000','aaa','31','来来来');

1.2通过主表查看两张表所插入的数据

test_biaofenqu=# select * from test.fenqu1;qhdm  | qxname | id | xinxi--------+--------+----+--------120000 | aaa    | 20 | zhao120000 | aaa    | 21 | qian120000 | aaa    | 22 | sun120000 | aaa    | 23 | li120000 | aaa    | 24 | chen120000 | aaa    | 25 | tie120000 | aaa    | 26 | song120000 | aaa    | 27 | shen120000 | aaa    | 28 | cheng120000 | aaa    | 29 | wang120000 | aaa    | 30 | ouyang120000 | aaa    | 31 | chu120100 | bbb    | 1 | zhao120100 | bbb    | 2 | qian120100 | bbb    | 3 | sun120100 | bbb    | 4 | li120100 | bbb    | 5 | chen120100 | bbb    | 6 | tie120100 | bbb    | 7 | song120100 | bbb    | 8 | shen120100 | bbb    | 9 | cheng120100 | bbb    | 10 | wang120100 | bbb    | 11 | ouyang120100 | bbb    | 12 | chu(24 行记录)test_biaofenqu=# select * from test.fenqu2;qhdm  | qxname | id | xinxi--------+--------+----+--------120000 | aaa    | 20 | ss120000 | aaa    | 21 | 等等120000 | aaa    | 22 | ff120000 | aaa    | 23 | 刚刚120000 | aaa    | 24 | 给120000 | aaa    | 25 | 铁120000 | aaa    | 26 | 啦啦120000 | aaa    | 27 | 联想120000 | aaa    | 28 | 成120000 | aaa    | 29 | 啊哈哈120000 | aaa    | 30 | 切120000 | aaa    | 31 | 来来来120100 | bbb    | 1 | 赵120100 | bbb    | 2 | 钱120100 | bbb    | 3 | 孙120100 | bbb    | 4 | 李120100 | bbb    | 5 | 陈120100 | bbb    | 6 | 铁120100 | bbb    | 7 | 宋120100 | bbb    | 8 | 申120100 | bbb    | 9 | 成120100 | bbb    | 10 | 王120100 | bbb    | 11 | 欧阳120100 | bbb    | 12 | 褚

1.3查看主表与分区表的关系

图片1.png
在这里插入图片描述

1.4创建存储过程及存储过程使用的相关表格

（1）创建用于存放新老区划编码对应表格table_qhdm，并插入数据；--创建表格table_qhdm,用于存所有的区划代码create table table_qxdm (qhdm text,qxdmnew TEXT) ;--插入区划编码数据insert into table_qxdm values('120100','130100');insert into table_qxdm values('120000','130000');（2）   创建表格table_tablename，用于存放所有主表的名称，并插入数据create table table_tablename (tabname text) ;--插入主表名称数据insert into table_tablename values('fenqu1');insert into table_tablename values('fenqu2');（3）创建存放拼接后的sql语句存放表格ql_pinjie--创建用于存储拼接后的语句表sql_pinjiecreate table sql_pinjie (sql_pinjie text) ;（4）创建拼接过程中存放报错表格tab_errorcreate table tab_error (tab_error_sql text) ;（5）创建存储过程update_fenqu_tableCREATE OR REPLACE PROCEDURE test.update_fenqu_table()LANGUAGE plpgsqlAS $procedure$DECLAREv_qhdm record ;v_tablename record ;v_count int8 ;BEGIN--获取所有区划代码，区划代码包括，旧区划代码，新区划代码FOR v_qhdm in ( select qhdm , qxdmnew from table_qxdm )loop BEGINFOR v_tablename in ( select tabname from table_tablename )loop BEGINinsert into sql_pinjie VALUES ('alter table test.'||v_tablename.tabname ||' detach PARTITION test.'||v_tablename.tabname||'_part_'|| v_qhdm.qhdm || ';' ) ;    insert into sql_pinjie VALUES ('alter table test.'||v_tablename.tabname||'_part_'||v_qhdm.qhdm||' RENAME to '||v_tablename.tabname||'_part_'|| v_qhdm.qxdmnew||';') ;insert into sql_pinjie VALUES ( 'update test.'||v_tablename.tabname||'_part_'||v_qhdm.qxdmnew||' set qhdm='''||v_qhdm.qxdmnew||''';') ;insert into sql_pinjie VALUES ( 'alter table test.'||v_tablename.tabname||' ATTACH PARTITION test.'||v_tablename.tabname||'_part_'||v_qhdm.qxdmnew||' for values in ('''||v_qhdm.qxdmnew||''');');EXCEPTION WHEN OTHERS THEN insertinto tab_error values ( 'EXCEPT TABLE' || v_tablename.tabname || '_part_' || v_qhdm.qhdm || ';' ) ;END ;END loop ;COMMIT ;END ;END loop ;END $procedure$;（6）执行存储过程update_fenqu_tablecall update_fenqu_table（7）查看所拼接的sql语句test_biaofenqu=# select * from sql_pinjie;sql_pinjie--------------------------------------------------------------------------------------------alter table test.fenqu1 detach PARTITION test.fenqu1_part_120100;alter table test.fenqu1_part_120100 RENAME to fenqu1_part_130100;update test.fenqu1_part_130100 set qhdm='130100';alter table test.fenqu1 ATTACH PARTITION test.fenqu1_part_130100 for values in ('130100');alter table test.fenqu2 detach PARTITION test.fenqu2_part_120100;alter table test.fenqu2_part_120100 RENAME to fenqu2_part_130100;update test.fenqu2_part_130100 set qhdm='130100';alter table test.fenqu2 ATTACH PARTITION test.fenqu2_part_130100 for values in ('130100');alter table test.fenqu1 detach PARTITION test.fenqu1_part_120000;alter table test.fenqu1_part_120000 RENAME to fenqu1_part_130000;update test.fenqu1_part_130000 set qhdm='130000';alter table test.fenqu1 ATTACH PARTITION test.fenqu1_part_130000 for values in ('130000');alter table test.fenqu2 detach PARTITION test.fenqu2_part_120000;alter table test.fenqu2_part_120000 RENAME to fenqu2_part_130000;update test.fenqu2_part_130000 set qhdm='130000';alter table test.fenqu2 ATTACH PARTITION test.fenqu2_part_130000 for values in ('130000');(16 行记录)（8）执行对应的sql，对分区表完成操作alter table test.fenqu1 detach PARTITION test.fenqu1_part_120100;alter table test.fenqu1_part_120100 RENAME to fenqu1_part_130100;update test.fenqu1_part_130100 set qhdm='130100';alter table test.fenqu1 ATTACH PARTITION test.fenqu1_part_130100 for values in ('130100');alter table test.fenqu2 detach PARTITION test.fenqu2_part_120100;alter table test.fenqu2_part_120100 RENAME to fenqu2_part_130100;update test.fenqu2_part_130100 set qhdm='130100';alter table test.fenqu2 ATTACH PARTITION test.fenqu2_part_130100 for values in ('130100');alter table test.fenqu1 detach PARTITION test.fenqu1_part_120000;alter table test.fenqu1_part_120000 RENAME to fenqu1_part_130000;update test.fenqu1_part_130000 set qhdm='130000';alter table test.fenqu1 ATTACH PARTITION test.fenqu1_part_130000 for values in ('130000');alter table test.fenqu2 detach PARTITION test.fenqu2_part_120000;alter table test.fenqu2_part_120000 RENAME to fenqu2_part_130000;update test.fenqu2_part_130000 set qhdm='130000';alter table test.fenqu2 ATTACH PARTITION test.fenqu2_part_130000 for values in ('130000');

1.5查看修改后主表与分区表的关系

33.png
在这里插入图片描述

http://www.tj-hxxt.cn/news/110316.html

相关文章：

  • 门户网站底部网站优化培训学校
  • 做写真素材网站今日新闻快讯
  • 网站备案和icp备案网站排名优化快速
  • 湖北省建设工程招标网站seo产品优化推广
  • 道滘镇做网站榆林百度seo
  • 淘宝网站后台怎么做百度广告推广费用年费
  • 杭州模板建站定制中关村在线app
  • 建设部官方网站全网推广成功再收费
  • 备案新增网站材料市场调研报告范文大全
  • 5 网站建设的基本步骤是专业网站优化排名
  • 微型购物网站建设模板微信搜一搜怎么做推广
  • 腾讯微博做网站外链步骤seo性能优化
  • 手机网站导航栏如何做注册百度推广账号
  • 海外培训视频网站建设搜索引擎查询
  • 东营建网站公司百度seo公司整站优化
  • 如今做那个网站致富百度竞价系统
  • 做某健身房网站的设计与实现四川seo多少钱
  • wordpress 登录不了seo好学吗
  • 网站运营seo招聘新闻式软文经典案例
  • 网站产品页面什么时候做网站百度收录查询
  • 十堰网站制作公司广东省疫情最新
  • 网站文章内容的选取上海谷歌seo公司
  • 政府门户网站的重要性网络营销环境分析
  • 免费书画网站怎么做的百度手机app
  • 哪些网站是单页应用b2c有哪些电商平台
  • 注册广告公司名字快抖霸屏乐云seo
  • 建e网室内设计网 模型长春关键词优化排名
  • 2016年网站设计风格推广网站文案
  • 互动科技网站建设谷歌的推广是怎么样的推广
  • 秀洲区住房和城乡建设局网站百度极速版app下载安装挣钱