1. Toy模板网首页
  2. > Toy博客

程序员：你如何写可重复执行的SQL语句？

9月前 作者：李福春 分类：Toy博客 阅读(31) 违法举报

这篇具有很好参考价值的文章主要介绍了程序员：你如何写可重复执行的SQL语句？。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

程序员：你如何写可重复执行的SQL语句？

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

故事

程序员：你如何写可重复执行的SQL语句？

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

程序员：你如何写可重复执行的SQL语句？

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

小张注意到，在实际的项目开发场景中，很多开发人员只关注编写SQL脚本来满足功能需求，而忽略了脚本的可重复执行性。

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

因此，小张决定研究如何编写基于MySQL的可以重复执行的SQL脚本，以提高开发效率和简化运维流程。

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

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

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

a.使用事务：事务是一组SQL语句的逻辑单元，可以保证这组语句要么全部执行成功，要么全部回滚。

   通过使用事务，可以确保脚本的所有修改操作要么完整地执行，要么不执行。

b.使用条件检查：在每个需要修改数据的语句之前，添加条件检查以确保只有当数据不存在或满足特定条件时才进行修改。

   这样可以避免重复插入相同的数据，或者执行不必要的更新操作。

c.错误处理：在编写脚本时，考虑到可能出现的错误情况，并提供适当的错误处理机制。例如，使用IF...ELSE语句来处理特定条件下的执行逻辑。

d.使用存储过程：如果脚本非常复杂，包含多个步骤和业务逻辑，可以考虑将它们封装为存储过程。这样可以更好地组织和管理代码，并提高脚本的可读性和维护性。

小张听得津津有味，他开始将老李的建议付诸实践。他仔细研究每个SQL语句，根据老李的指导进行修改和优化。

他使用了事务来包裹整个脚本，添加了条件检查来避免重复插入数据，并实现了错误处理机制以应对异常情况。

背景

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

1.能重复执行；

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

如果脚本不可重复执行，则运维无法自动化，会反过来要求后端开发人员给出适配当前环境的新的SQL脚本，增加了运维和沟通成本。

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

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

程序员：你如何写可重复执行的SQL语句？

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

小结

按照不同的场景写了对应的存储过程，使得修改字段，修改索引，修改表，插入数据可以重复执行。

如果有使用问题或者优化建议，欢迎提出来。还原跟我交流 ;

原创不易，关注诚可贵，转发价更高！转载请注明出处，让我们互通有无，共同进步，欢迎沟通交流。文章来源地址https://www.toymoban.com/news/detail-695028.html

到了这里，关于程序员：你如何写可重复执行的SQL语句？的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处： 如若内容造成侵权/违法违规/事实不符，请点击违法举报进行投诉反馈，一经查实，立即删除！

分享到：
领支付宝红包 赞助服务器费用

相关文章

  • 聊聊程序员那些【越早知道越好】的道理或者建议-程序员如何提升自己

    比如好好学习ide的使用和快捷键 ，以及一些常用的命令。 写一个实体类：Alt+Insert，shift+ ↓ ↓ ↓（或者ctrl+a全选） ，回车 把代码封装成方法：Ctrl + Alt + M 代码格式化：Ctrl + Alt + L 删除无用的导包：ctrl+alt+o 构建项目：ctrl+f9 … 比如 mybatis generate lombok 比如引入自动化工具，

    2023年04月24日
    浏览(69)
  • 程序员如何成长

    程序员如何成长

    做技术是打怪兽不是养宠物，为什么要打怪兽？因为难；为什么难很重要？因为难的事情才能带来成长；为什么要成长？承认吧，因为「如何成长」是当代人，包括你我他在内焦虑的源泉。 过去几个月内我在写一系列主题为「NodeJS实战」的文章，内容来源是过去两年独自开发

    2024年02月08日
    浏览(49)
  • 程序员如何制作PPT？

    程序员如何制作PPT？

    有道无术，术尚可求也；有术无道，止于术。大家好，我是程序员雪球，今天让我们一起探讨如何从零开始制作高质量的 PPT。 上周，领导要求我撰写一份关于 4到6月持续集成运营分析的报告，并通过 PPT 的形式进行汇报。作为一名五年经验的老程序员，我深知 PPT 制作并非我

    2024年02月16日
    浏览(38)
  • 程序员如何提高代码能力

    程序员如何提高代码能力

    C++ 是一种功能强大的编程语言，广泛应用于操作系统、数据库、游戏开发等领域。而要成为一名优秀的 C++ 程序员，不仅需要掌握 C++ 的基本语法和特性，还需要不断提高自己的代码能力。 以下是一些具体的方法和建议，帮助你提高 C++ 代码能力： 阅读优秀的代码 阅读优秀代

    2023年04月25日
    浏览(51)
  • 程序员如何提高代码能力？

    程序员如何提高代码能力？

    作为一名程序员，自己的本质工作就是做程序开发，那么程序开发的时候最直接的体现就是代码，检验一个程序员技术水平的一个核心环节就是开发时候的代码能力。众所周知，程序开发的水平提升是一个循序渐进的过程，每一位程序员都是从“菜鸟”变成“大神”的，所以

    2024年02月01日
    浏览(51)
  • 程序员该如何学习技术

    程序员该如何学习技术

    前言   学习是第一生产力，我从来都是这么认为的，人只有只有不断地学习才能意识到自己的缺点和不足，身为程序员，我更认为人们应当抱着终身学习的想法实践下去，这是我所一直践行且相信的。   高处不胜寒，只有站在更高的地方，才能欣赏更多的风景，当年老

    2024年02月01日
    浏览(90)

  • 程序员行业还是高薪职业吗？我来和大家聊聊C++程序员该如何学习

    此外，程序员的劳动大多是脑力活动，不需要东奔西跑。这也就意味着，程序员的工作不会对身体健康造成太大的影响。 我们都知道，我们现在的生活水平越来越高科技，越来越先进。在这样的发展速度下，程序员怎么可能被淘汰呢？所以，别听网上的瞎说，什么互联网红利

    2024年02月05日
    浏览(52)

  • 【话题】程序员如何选择职业赛道？

    程序员的职业赛道确实多样化，每个方向都有其独特的魅力和挑战。以下是一些主流的编程职业赛道，以及它们的特点和所需的技能： 特点：负责网站的外观和用户交互。 技能：HTML, CSS, JavaScript, React, Angular, Vue等。 美丽花园：创造性地设计用户界面，实现流畅的用户体验。

    2024年03月08日
    浏览(68)
  • 程序员如何维护自己的电脑

    程序员如何维护自己的电脑

            作为一名程序员，维护自己的电脑是非常重要的。一个良好维护的电脑能够提高工作效率和编程体验。本篇博客将介绍一些维护电脑的基本技巧，包括软件更新、病毒防护、文件组织和备份等方面。 定期更新操作系统和软件是保持电脑安全性和性能的关键。以下

    2024年02月02日
    浏览(46)
  • 程序员如何高质量重构代码？

    程序员如何高质量重构代码？

    有道无术，术尚可求也，有术无道止于术。你好，我是程序员雪球，今天和你聊聊程序员重构代码那些事。   程序员重构代码的重要性不言而喻，但如何进行有效的重构呢？下面是一些建议和指导。 为什么要重构？ 重构是提高代码质量和可维护性的重要手段，旨在在不改变

    2024年02月15日
    浏览(68)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包，优惠每天领

二维码1

领取红包

二维码2

领红包