mysql建表脚本转换为pg mysql创建表脚本

转载

mob64ca13f2b62d 2024-04-08 00:01:35

文章标签 mysql sql 数据库表名字段 文章分类 架构后端开发

`MySQL`脚本

一、建表删表

前言：以下脚本语句，使用前建议先进行自测一遍，如有错误及时提醒更正

（一）新增表

**新增表的脚本可以从Navicat中转储导出，但是要更改下建表语句为：CREATE TABLE IF NOT EXISTS **

1、模板

create table if not exists 数据库名.表名 (具体的建表语句
) engine = 存储引擎 default charset = 字符集 collate = 校对集 comment = '表注释';

2、参考样例

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE  IF NOT EXISTS `crr_policy_index`  (
    `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
    `index_code` varchar(32)   NOT NULL  DEFAULT '' COMMENT '指标唯一编码',
    `policy_id` int(10)   COMMENT '策略ID',
    `index_group` varchar(32)   NOT NULL  DEFAULT ''  COMMENT '分类',
    `version` int(4) NOT NULL DEFAULT 0 COMMENT '版本',
    `index_desc` varchar(128)    DEFAULT ''  COMMENT '描述',
    `index_name` varchar(32)   NOT NULL  DEFAULT ''  COMMENT '指标名称',
    `type` varchar(32)   NOT NULL  DEFAULT '' COMMENT '类型',
    `subject_labels` varchar(32)    DEFAULT '' COMMENT '主体名称',
    `expressions` varchar(128)  DEFAULT '' COMMENT '表达式',
    `options` longtext COMMENT '下拉框内容',
    `threshold_sign` varchar(32)    DEFAULT '' COMMENT '阈值符号',
    `threshold` varchar(32) NOT NULL  DEFAULT '' COMMENT '阈值',
    `modify_time` datetime(0)   DEFAULT now() COMMENT '修改时间',
    `operator_code` varchar(32)  DEFAULT '' COMMENT '操作人',
    PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8 COLLATE = utf8_general_ci comment = '指标策略表' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

（二）删除表

1、模板

drop table if exists 数据库名.表名;

2、参考样例

drop table if exists crr_policy_index;

二、表字段更改

（一）表字段新增

1、模板

set @v_count = 0;
select count(*) into @v_count from information_schema.columns where TABLE_SCHEMA='数据库名' and TABLE_NAME='表名' and column_name = '字段名';
set @sql = if(@v_count = 0,"增加字段语句","select '表名.字段名 is OK.'");
prepare stmt from @sql;
execute stmt;

2、参考样例

set @v_count = 0;
select count(*) into @v_count from information_schema.columns where TABLE_SCHEMA='toolsforcreatetable' and TABLE_NAME='crr_policy_index' and column_name = 'process_instance';
set @sql = if(@v_count = 0,"alter table crr_policy_index add column process_instance varchar(255) NOT NULL COMMENT '流程实例id';","select '表名.字段名 is OK.'");
prepare stmt from @sql;
execute stmt;

（二）表字段删除或修改

1、模板

set @v_count = 0;
select count(*) into @v_count from information_schema.columns where TABLE_SCHEMA='数据库名' and TABLE_NAME='表名' and column_name = '字段名';
set @sql = if(@v_count = 1,"删除或修改字段语句","select '表名.字段名 is deleted.'");
prepare stmt from @sql;

2、修改字段样例

set @v_count = 0;
select count(*) into @v_count from information_schema.columns where TABLE_SCHEMA='toolsforcreatetable' and TABLE_NAME='crr_policy_index' and column_name = 'process_instance';
set @sql = if(@v_count = 1,"alter table crr_policy_index modify column process_instance varchar(255) NOT NULL COMMENT '流程实例id1';","select 'crr_policy_index.process_instance is deleted.'");
prepare stmt from @sql;
execute stmt;

3、删除字段样例

set @v_count = 0;
select count(*) into @v_count from information_schema.columns where TABLE_SCHEMA='toolsforcreatetable' and TABLE_NAME='crr_policy_index' and column_name = 'process_instance';
set @sql = if(@v_count = 1,"alter table crr_policy_index drop column process_instance ;","select 'crr_policy_index.process_instance is deleted.'");
prepare stmt from @sql;
execute stmt;
-- DEALLOCATE PREPARE stmt; 释放

三、主键新增与删除

（一）新增主键

1、模板

set @v_count = 0;
select count(*) into @v_count from information_schema.table_constraints where TABLE_SCHEMA='数据库名' and TABLE_NAME='表名' and CONSTRAINT_NAME = 'PRIMARY';
set @sql = if(@v_count = 0,"alter table 表名 add primary key (字段列表)","select '表名 PRIMARY KEY is OK.'");
prepare stmt from @sql;
execute stmt;

2、参考样例

set @v_count = 0;
select count(*) into @v_count from information_schema.table_constraints where TABLE_SCHEMA='toolsforcreatetable' and TABLE_NAME='crr_policy_index' and CONSTRAINT_NAME = 'PRIMARY';
set @sql = if(@v_count = 0,"alter table crr_policy_index add primary key (id,index_code)","select 'crr_policy_index PRIMARY KEY is OK.'");
prepare stmt from @sql;
execute stmt;

（二）删除主键

1、模板

set @v_count = 0;
select count(*) into @v_count from information_schema.table_constraints where TABLE_SCHEMA='数据库名' and TABLE_NAME='表名' and CONSTRAINT_NAME = 'PRIMARY';
set @sql = if(@v_count = 1,"alter table 表名 drop primary key ","select '表名 PRIMARY KEY is deleted.'");
prepare stmt from @sql;
execute stmt;

2、参考样例

set @v_count = 0;
select count(*) into @v_count from information_schema.table_constraints where TABLE_SCHEMA='toolsforcreatetable' and TABLE_NAME='crr_policy_index' and CONSTRAINT_NAME = 'PRIMARY';
set @sql = if(@v_count = 1,"alter table crr_policy_index drop primary key ","select '表名 PRIMARY KEY is deleted.'");
prepare stmt from @sql;
execute stmt;

四、索引

（一）新增索引

推荐普通索引名的格式：idx_每列首字母（如果索引字段只有一个，建议索引名为 idx_表名_列名）,如：idx_i_t, idx_index_group

推荐索引名的格式：uk_每列首字母（如果索引字段只有一个，建议索引名为 uk_表名_列名）,如：uk_i_t, uk_index_group

1、普通索引

（1）模板

set @v_count = 0;
select count(*) into @v_count from information_schema.statistics where TABLE_SCHEMA='数据库名' and TABLE_NAME='表名' and INDEX_NAME = '索引名';
set @sql = if(@v_count = 0,"create index 索引名 on 表名(字段列表)","select '表名 索引名 is OK.'");
prepare stmt from @sql;
execute stmt;

（2）参考样例

set @v_count = 0;
select count(*) into @v_count from information_schema.statistics where TABLE_SCHEMA='toolsforcreatetable' and TABLE_NAME='crr_policy_index' and INDEX_NAME = 'idx_index_group';
set @sql = if(@v_count = 0,"create index idx_index_group1 on crr_policy_index(index_group)","select 'crr_policy_index idx_index_group is OK.'");
prepare stmt from @sql;
execute stmt;

2、唯一索引加unique

（1）模板

set @v_count = 0;
select count(*) into @v_count from information_schema.statistics where TABLE_SCHEMA='数据库名' and TABLE_NAME='表名' and INDEX_NAME = '索引名';
set @sql = if(@v_count = 0,"create unique index 索引名 on 表名(字段列表)","select '表名 索引名 is OK.'");
prepare stmt from @sql;
execute stmt;

（2）参考样例

set @v_count = 0;
select count(*) into @v_count from information_schema.statistics where TABLE_SCHEMA='toolsforcreatetable' and TABLE_NAME='crr_policy_index' and INDEX_NAME = 'uk_i_t';
set @sql = if(@v_count = 0,"create unique index uk_i_t on crr_policy_index(index_group,type)","select 'crr_policy_index uk_i_t is OK.'");
prepare stmt from @sql;
execute stmt;

（二）修改索引

注意: 在MySQL中并没有提供修改索引的直接指令，一般情况下，我们需要先删除掉原索引，再根据需要创建一个同名的索引，从而变相地实现修改索引操作

（三）删除索引

注意这里是@v_count >= 1（多字段索引时）

（1）模板

set @v_count = 0;
select count(*) into @v_count from information_schema.statistics where TABLE_SCHEMA='数据库名' and TABLE_NAME='表名' and INDEX_NAME = '索引名';
set @sql = if(@v_count >= 1,"alter table 表名 drop index 索引名","select '表名 索引名 is deleted.'");
prepare stmt from @sql;
execute stmt;

（2）参考样例

set @v_count = 0;
select count(*) into @v_count from information_schema.statistics where TABLE_SCHEMA='toolsforcreatetable' and TABLE_NAME='crr_policy_index' and INDEX_NAME = 'uk_i_t';
set @sql = if(@v_count >= 1,"alter table crr_policy_index drop index uk_i_t","select 'crr_policy_index uk_i_t is deleted.'");
prepare stmt from @sql;
execute stmt;

五、表数据增删改

（一）表添加数据

方式一: 先查后加

（1）模板

set @v_count = 0;
select count(*) into @v_count from 表名 where 主键条件;
set @sql = if(@v_count = 0, "insert into 表名(列名) values(列名);", "select '数据已存在.'");
prepare stmt from @sql;
execute stmt;
commit;

（2）参考样例

set @v_count = 0;
select count(*) into @v_count from 表名 where 主键条件;
set @sql = if(@v_count = 0, "INSERT INTO crr_policy_index(id, index_code, policy_id, index_group, version, index_desc, index_name, type, subject_labels, expressions, options, threshold_sign, threshold, modify_time, operator_code) VALUES (1, NULL, NULL, '1', 0, NULL, NULL, '2', NULL, NULL, NULL, NULL, NULL, 'current_timestamp()', NULL);", "select '数据已存在.'");
prepare stmt from @sql;
execute stmt;
commit;

方式二：先删后增

（1）模板

delete from 表名 where 主键条件;
insert into 表名(列名) values (列名);

（2）参考样例

delete from crr_policy_index where id = 1;

INSERT INTO crr_policy_index(id, index_code, policy_id, index_group, version, index_desc, index_name, type, subject_labels, expressions, options, threshold_sign, threshold, modify_time, operator_code) VALUES (1, NULL, NULL, '1', 0, NULL, NULL, '2', NULL, NULL, NULL, NULL, NULL, 'current_timestamp()', NULL);

（二）表删除数据

（1）模板

set @v_count = 0;
select count(*) into @v_count from 表名 where 主键条件;
set @sql = if(@v_count = 1, "delete 语句", "select '数据已删除.'");
prepare stmt from @sql;
execute stmt;
commit;

（2）参考样例

set @v_count = 0;
select count(*) into @v_count from crr_policy_index where id = 1;
set @sql = if(@v_count = 1, "delete from crr_policy_index where id = 1;", "select '数据已删除.'");
prepare stmt from @sql;
execute stmt;
commit;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：mysql如何查询所有部门层级关系 mysql查询每个部门的人数

下一篇：mysql归档方案 mysql归档工具

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

mysql建表脚本转换为pg mysql创建表脚本

mysql建表脚本转换为pg mysql创建表脚本

MySQL脚本

一、建表删表

（一） 新增表

（二）删除表

二、表字段更改

（一）表字段新增

（二）表字段删除或修改

三、主键新增与删除

（一）新增主键

（二）删除主键

四、索引

（一）新增索引

1、普通索引

2、唯一索引 加unique

（二）修改索引

（三）删除索引

五、表数据增删改

（一）表添加数据

方式一: 先查后加

方式二：先删后增

（二）表删除数据

51CTO博客

`MySQL`脚本

（一）新增表

2、唯一索引加unique