MySQL脚本，支持重复执行

最新推荐文章于 2024-06-16 21:21:11 发布

LvQiFen

最新推荐文章于 2024-06-16 21:21:11 发布

阅读量1.7k

点赞数 6

分类专栏： # MySQL 文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/LvQiFen/article/details/120703222

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

`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;

LvQiFen

关注

6
点赞
踩
14

收藏

觉得还不错? 一键收藏
0
评论
MySQL脚本，支持重复执行

MySQL脚本一、建表删表前言：以下脚本语句，使用前建议先进行自测一遍，如有错误及时提醒更正（一）新增表**新增表的脚本可以从Navicat中转储导出，但是要更改下建表语句为：CREATE TABLE IF NOT EXISTS **1、模板create table if not exists 数据库名.表名 (具体的建表语句) engine = 存储引擎 default charset = 字符集 collate = 校对集 comment = '表注释'; 2、参考
复制链接

扫一扫