关于java:程序员你如何写可重复执行的SQL语句

38次阅读

共计 10030 个字符，预计需要花费 26 分钟才能阅读完成。

上图的意思：屡战屡败，屡试不爽。

故事

程序员小张：刚毕业，加入工作 1 年左右，日常工作是 CRUD

架构师老李：多个大型项目教训，精通各种开发架构屠龙宝术；

小张留神到，在理论的我的项目开发场景中，很多开发人员只关注编写 SQL 脚本来满足性能需要，而疏忽了脚本的可反复执行性。

这就意味着，如果脚本中的某个局部执行失败，运维人员就必须从头提供一个新的脚本，这对运维团队和开发人员来说是一个挑战。

因而，小张决定钻研如何编写基于 MySQL 的能够反复执行的 SQL 脚本，以进步开发效率和简化运维流程。

他向公司的架构师老李征询了这个问题。老李是一位经验丰富的架构师，

他在多个大型项目中积攒了许多贵重的教训，精通各种开发架构屠龙宝术。

老李听了小张的问题后，笑了笑并开始给予领导。他向小张解释了如何编写一个具备可反复执行性的 SQL 脚本，并分享了以下几个关键点：

a. 应用事务：事务是一组 SQL 语句的逻辑单元，能够保障这组语句要么全副执行胜利，要么全副回滚。

   通过应用事务，能够确保脚本的所有批改操作要么残缺地执行，要么不执行。

b. 应用条件查看：在每个须要批改数据的语句之前，增加条件查看以确保只有当数据不存在或满足特定条件时才进行批改。

   这样能够防止反复插入雷同的数据，或者执行不必要的更新操作。

c. 错误处理：在编写脚本时，思考到可能呈现的谬误状况，并提供适当的错误处理机制。例如，应用 IF…ELSE 语句来解决特定条件下的执行逻辑。

d. 应用存储过程：如果脚本非常复杂，蕴含多个步骤和业务逻辑，能够思考将它们封装为存储过程。这样能够更好地组织和治理代码，并进步脚本的可读性和维护性。

小张听得津津乐道，他开始将老李的倡议付诸实践。他认真钻研每个 SQL 语句，依据老李的领导进行批改和优化。

他应用了事务来包裹整个脚本，增加了条件查看来防止反复插入数据，并实现了错误处理机制以应答异常情况。

背景

所以开发提供给到运维的 SQL 脚本有肯定根本要求：

1. 能反复执行；

2. 不出错，（不报错，逻辑正确）；

如果脚本不可反复执行，则运维无奈自动化，会反过来要求后端开发人员给出适配以后环境的新的 SQL 脚本，减少了运维和沟通老本。

那么怎么写可反复执行的 SQL 脚本呢？

分成 4 个场景，来介绍举例。

1 创立表 

create table if not exists nginx_config (id varchar(36) not null default ''comment'UUID',
    namespace varchar(255) not null default ''comment' 环境命名空间 ',
    config_content text comment "nginx http 块配置",
    content_md5 varchar(64) not null default ''comment' 配置内容的 MD5 值 ',
    manipulator varchar(64) not null default ''comment' 操作者 ',
    description varchar(512) not null default ''comment' 形容 ',
    gmt_created bigint unsigned not null default 0 comment '创立工夫',
    primary key(id)
)ENGINE=InnoDB comment 'nginx 配置表' ;

删除表在生产环境是禁止的。

备份形式批改表名

批改表名：先创立新表，再 copy 历史数据进去，不容许删除表；

DELIMITER //
drop procedure if exists modify_table_name;
CREATE PROCEDURE modify_table_name(IN table_name VARCHAR(255),
  IN new_name VARCHAR(255)
)
BEGIN
  DECLARE database_name VARCHAR(255);
  DECLARE table_exists INT DEFAULT 0;
  DECLARE new_table_exists INT DEFAULT 0;
  SELECT DATABASE() INTO database_name;
  set @db_table_name=concat(database_name,'/',table_name);
  select count(t1.TABLE_ID) INTO table_exists  from information_schema.INNODB_TABLES t1 where t1.NAME=@db_table_name ;
  set @db_table_name_new=concat(database_name,'/',new_name);
  select count(t1.TABLE_ID) INTO new_table_exists  from information_schema.INNODB_TABLES t1 where t1.NAME=@db_table_name_new ;

  IF table_exists = 1 AND new_table_exists = 0 THEN
    SET @query = CONCAT('create table',new_name,'like',table_name);
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET @query = CONCAT('insert into', new_name, 'select * from',table_name);
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SELECT 'table name modify successfully.' AS result ,@db_table_name,@db_table_name_new,table_exists,new_table_exists;
  ELSE
    SELECT 'table name not exists or new_name already exists.' AS result,@db_table_name,@db_table_name_new,table_exists,new_table_exists;
  END IF;

END //

DELIMITER ;

测试脚本：

create table user(id bigint auto_increment primary key ,name varchar(30),age tinyint)comment 'user 表';

insert into user(id, name, age) VALUES  (1,'a',1),(2,'b',2),(3,'c',3);


call modify_table_name('user','user1');

select * from user1;


call modify_table_name('user','user2');

select * from user2;

测试后果合乎预期。

新增批改删除字段 

drop procedure if exists modify_table_field;
CREATE PROCEDURE modify_table_field(IN tableName VARCHAR(50), IN fieldName VARCHAR(50), IN fieldAction VARCHAR(10), IN fieldType VARCHAR(255))
BEGIN
    IF fieldAction = 'add' THEN
        IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_name = tableName AND column_name = fieldName) THEN
            SET @query = CONCAT('ALTER TABLE', tableName, 'ADD COLUMN', fieldName, ' ', fieldType);
            PREPARE stmt FROM @query;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            SELECT 'Field added successfully.' AS result;
        ELSE
            SELECT 'Field already exists.' AS result;
        END IF;
    ELSEIF fieldAction = 'modify' THEN
        IF EXISTS (SELECT * FROM information_schema.columns WHERE table_name = tableName AND column_name = fieldName) THEN
            SET @query = CONCAT('ALTER TABLE', tableName, 'CHANGE COLUMN', fieldName, '', fieldName,' ', fieldType);
            select @query;
            PREPARE stmt FROM @query;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            SELECT 'Field modified successfully.' AS result;
        ELSE
            SELECT 'Field does not exist or has the same name.' AS result;
        END IF;
    ELSEIF fieldAction = 'delete' THEN
        IF EXISTS (SELECT * FROM information_schema.columns WHERE table_name = tableName AND column_name = fieldName) THEN
            SET @query = CONCAT('ALTER TABLE', tableName, 'DROP COLUMN', fieldName);
            PREPARE stmt FROM @query;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            SELECT 'Field deleted successfully.' AS result;
        ELSE
            SELECT 'Field does not exist.' AS result;
        END IF;
    ELSE
        SELECT 'Invalid field action.' AS result;
    END IF;
END;

测试脚本 

create table if not exists sys_agent
(
    agent_id          bigint                             not null comment '客服惟一 id' primary key,
    agent_name        varchar(64)                        null comment '客服名称',
    agent_type        varchar(30)                        null comment '客服类型 (场地客服、直聘客服)',
    district          varchar(30)                        null comment '地区',
    service_language  varchar(30)                        null comment '服务语种',
    agent_description varchar(500)                       null comment '客户形容',
    status            tinyint(1)                         null comment '状态 (0= 有效,1= 无效)，默认为 1',
    del_flag          tinyint(1)                         null comment '是否删除 (0=false,1=true)',
    user_id           bigint                             null comment '用户 id(关联的用户信息)',
    time_zone         varchar(50)                        null comment '时区',
    create_by         varchar(50)                        null comment '创建者',
    create_time       datetime default CURRENT_TIMESTAMP null comment '创立工夫',
    update_by         varchar(50)                        null comment '批改者',
    update_time       datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP comment '批改工夫'
) comment '客服治理';



CALL modify_table_field('sys_agent', 'sex', 'add', 'tinyint not null comment'' 性别 ''');
CALL modify_table_field('sys_agent', 'sex2', 'add', 'tinyint not null comment'' 性别 ''');


CALL modify_table_field('sys_agent', 'sex', 'modify', 'int not null comment'' 性别 ''');


CALL modify_table_field('sys_agent', 'sex', 'delete', '');
CALL modify_table_field('sys_agent', 'sex2', 'delete', '');

测试后果合乎预期。

新增批改删除索引

个别放在建表语句中，80% 的状况；

如果是我的项目前期减少索引，进行调优，能够参考字段，写一个存储过程反对索引的新增能够反复执行；

DELIMITER //
drop procedure if exists modify_table_index;
CREATE PROCEDURE modify_table_index(IN table_name VARCHAR(255),
  IN index_name VARCHAR(255),
  IN index_action ENUM('add', 'modify', 'delete'),
  IN index_columns VARCHAR(255)
)
BEGIN
  DECLARE database_name VARCHAR(255);
  DECLARE index_exists INT DEFAULT 0;
  DECLARE index_exists_action INT DEFAULT 0;

  -- 获取以后数据库名
  SELECT DATABASE() INTO database_name;

  set @db_table_name=concat(database_name,'/',table_name);
  -- 查看索引是否存在
  select count(t2.INDEX_ID) INTO index_exists  from information_schema.INNODB_TABLES t1 left join information_schema.INNODB_INDEXES t2 on t1.TABLE_ID=T2.TABLE_ID
  where t1.NAME=@db_table_name and t2.NAME=index_name;

  set index_exists_action=index_exists;

  IF index_action = 'add' THEN
    -- 增加索引
    IF index_exists < 1 THEN
      SET @query = CONCAT('ALTER TABLE `', database_name, '`.`', table_name, '` ADD INDEX `', index_name, '` (', index_columns, ')');
      PREPARE stmt FROM @query;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
      select count(t2.INDEX_ID) INTO index_exists_action  from information_schema.INNODB_TABLES t1 left join information_schema.INNODB_INDEXES t2 on t1.TABLE_ID=T2.TABLE_ID where t1.NAME=@db_table_name and t2.NAME=index_name;
      SELECT 'Index added successfully.' AS result ,database_name,index_exists,@db_table_name,index_exists_action;
    ELSE
      SELECT 'Index already exists.' AS result,database_name,index_exists,@db_table_name,index_exists_action;
    END IF;

  ELSEIF index_action = 'modify' THEN
    -- 批改索引（先删除后增加）IF index_exists > 0 THEN
      SET @query = CONCAT('ALTER TABLE `', database_name, '`.`', table_name, '` DROP INDEX `', index_name, '`');
      PREPARE stmt FROM @query;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;

      SET @query = CONCAT('ALTER TABLE `', database_name, '`.`', table_name, '` ADD INDEX `', index_name, '` (', index_columns, ')');
      PREPARE stmt FROM @query;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
      select count(t2.INDEX_ID) INTO index_exists_action  from information_schema.INNODB_TABLES t1 left join information_schema.INNODB_INDEXES t2 on t1.TABLE_ID=T2.TABLE_ID where t1.NAME=@db_table_name and t2.NAME=index_name;
      SELECT 'Index modified successfully.' AS result,database_name,index_exists,@db_table_name,index_exists_action;
    ELSE
      SELECT 'Index does not exist. create' AS result,database_name,index_exists,@db_table_name,index_exists_action;

      SET @query = CONCAT('ALTER TABLE `', database_name, '`.`', table_name, '` ADD INDEX `', index_name, '` (', index_columns, ')');
      PREPARE stmt FROM @query;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
      select count(t2.INDEX_ID) INTO index_exists_action  from information_schema.INNODB_TABLES t1 left join information_schema.INNODB_INDEXES t2 on t1.TABLE_ID=T2.TABLE_ID where t1.NAME=@db_table_name and t2.NAME=index_name;
      SELECT 'Index added successfully.' AS result ,database_name,index_exists,@db_table_name,index_exists_action;
    END IF;

  ELSEIF index_action = 'delete' THEN
    -- 删除索引
    IF index_exists > 0 THEN
      SET @query = CONCAT('ALTER TABLE `', database_name, '`.`', table_name, '` DROP INDEX `', index_name, '`');
      PREPARE stmt FROM @query;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
      select count(t2.INDEX_ID) INTO index_exists_action  from information_schema.INNODB_TABLES t1 left join information_schema.INNODB_INDEXES t2 on t1.TABLE_ID=T2.TABLE_ID where t1.NAME=@db_table_name and t2.NAME=index_name;
      SELECT 'Index deleted successfully.' AS result,database_name,index_exists,@db_table_name,index_exists_action;
    ELSE
      SELECT 'Index does not exist.' AS result,database_name,index_exists,@db_table_name,index_exists_action;
    END IF;

  ELSE
    SELECT 'Invalid index action.' AS result,database_name,index_exists,@db_table_name,index_exists_action;
  END IF;

END //

DELIMITER ;

测试脚本 

create table if not exists sys_agent
(
    agent_id          bigint                             not null comment '客服惟一 id'
        primary key,
    agent_name        varchar(64)                        null comment '客服名称',
    agent_type        varchar(30)                        null comment '客服类型 (场地客服、直聘客服)',
    district          varchar(30)                        null comment '地区',
    service_language  varchar(30)                        null comment '服务语种',
    agent_description varchar(500)                       null comment '客户形容',
    status            tinyint(1)                         null comment '状态 (0= 有效,1= 无效)，默认为 1',
    del_flag          tinyint(1)                         null comment '是否删除 (0=false,1=true)',
    user_id           bigint                             null comment '用户 id(关联的用户信息)',
    time_zone         varchar(50)                        null comment '时区',
    create_by         varchar(50)                        null comment '创建者',
    create_time       datetime default CURRENT_TIMESTAMP null comment '创立工夫',
    update_by         varchar(50)                        null comment '批改者',
    update_time       datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP comment '批改工夫'
)comment '客服治理';


CALL modify_table_index('sys_agent', 'ix_agentName', 'add', 'agent_name,agent_type');
CALL modify_table_index('sys_agent', 'ix_agentName', 'delete', '');
CALL modify_table_index('sys_agent', 'ix_agentName', 'modify', 'agent_name,agent_type');

新增数据

replace into 语句 依照主键或者惟一值，存在则先删除再插入，不存在则直接插入；

留神： 肯定要写字段名称 。

REPLACE INTO route_config (route_id, route_order, route_uri, route_filters, route_predicates, route_metadata, memo, created, updated, deleted) VALUES ('app-metadata-runtime', 1, 'lb://app-metadata-runtime', '[{"name":"StripPrefix","args":{"parts":"2"}}]', '[{"name":"Path","args":{"pattern":"/api/mr/**"}}]', '{}', '云枢服务 app-metadata-runtime', '2020-07-31 21:44:11', '2020-09-07 20:24:13', 0);

小结

依照不同的场景写了对应的存储过程，使得批改字段，批改索引，批改表，插入数据能够反复执行。

如果有应用问题或者优化倡议，欢送提出来。还原跟我交换 ;

原创不易，关注诚可贵，转发价更高！转载请注明出处，让咱们互通有无，共同进步，欢送沟通交流。

正文完
java
发表至： java
2023-09-04
 0
关于java:Java获取本机IP
关于java:如何正确的创建和销毁-Java-对象
关于java:docker专栏8使用IDEA远程管理docker镜像及容器服务
关于java:JVM知识总结2HotSpot虚拟机对象