一文把握如何编写可重复执行的SQL

一文把握如何编写可重复执行的SQL

文章已同步个人博客：一文把握如何编写可重复执行的SQL
配景

先提出问题，这里的可重复执行是指什么？我们为什么要编写可重复执行的sql？
可重复执行是指一条sql重复多次执行都不会报错，不会因为报错而中断同sql脚本的其它sql语句。
好比如下的建表sql只能执行一次，再次执行就会报错，提示我们example_table 表已存在。

1. CREATE TABLE `example_table` (
2.   `id` INT NOT NULL AUTO_INCREMENT,
3.   `name` VARCHAR(100) NOT NULL,
4.   `age` INT DEFAULT 0,
5.   `email` VARCHAR(200),
6.   PRIMARY KEY (`id`)
7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

复制代码

通常来说项目发版的执行SQL脚本语句是由DDL 和 DML 组成的，如果SQL脚本文件中某个SQL执行非常就会中断整个SQL脚本文件的执行。我们还要根据报错SQL的位置，重新将SQL脚本文件中未执行的SQL摘出来重新整理执行。一旦报错，整个SQL脚本执行过程就变得繁琐起来。
编写可重复执行的SQL，酿成相识决这一痛点的利器。当SQL脚本中的SQL语句都是可重复执行的，脚本中某个SQL有问题，直接在当前SQL脚本中就可以修改，我们若需要重新执行，只需要重新执行该SQL脚本就可以。其他已经执行成功的SQL，依然会成功执行，也不会对库表数据造成影响。接下来我们梳理一下各类SQL可重复执行的写法和注意点，本文中的SQL均基于MySql语法。
如何实现

表格罗列出编写SQL中常见的sql需求：
SQLSQL类型创建表DDL对表新增字段DDL对表修改字段DDL对表新增索引DDL插入一条新记载DML对表中记载进行更新DML对表中记载进行删除DML 编写SQL涉及到的Mysql语法和系统表

• IF NOT EXISTS
  
• MySql 预处理语句原生语法
  
• 字段表 INFORMATION_SCHEMA.COLUMNS
  
• 索引表 INFORMATION_SCHEMA.STATISTICS
  
• 查询当前数据库名称 SCHEMA()
  

预处理语句语法介绍

预处理语句（Prepared Statements）是一种将 SQL 查询与其参数分离的机制。与传统的查询方式不同，预处理语句首先会将 SQL 查询进行编译、优化，并将其缓存，随后可以多次执行该查询，而不必每次都重新编译和解析 SQL 语句。每次执行时，预处理语句只需要提供不同的参数即可，这使得它在需要执行多次相同 SQL 查询的场景中具有明显的性能上风。

1. -- 准备预处理语句
2. PREPARE stmt FROM 'SELECT name, age FROM users WHERE id = ?';
4. -- 设置参数并执行语句
5. SET @userId = 1;
6. EXECUTE stmt USING @userId;
8. -- 释放预处理语句
9. DEALLOCATE PREPARE stmt;

复制代码

• PREPARE 将带有占位符 ? 的 SQL 语句预处理并编译。
  
• SET 用于设置查询参数。
  
• EXECUTE 执行预处理语句并传递参数。
  
• DEALLOCATE PREPARE 释放预处理语句，避免占用资源。
  

---

接下来详细分析一下各种sql如何改写为可重复执行的写法。
创建表

原写法：

1. CREATE TABLE `example_table` (
2.   `id` INT NOT NULL AUTO_INCREMENT,
3.   `name` VARCHAR(100) NOT NULL,
4.   `age` INT DEFAULT 0,
5.   `email` VARCHAR(200),
6.   PRIMARY KEY (`id`)
7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

复制代码

创建表可以使用IF NOT EXISTS 语法进行判定，仅当表不存在的时候才会创建表。
可重复执行的写法：

1. CREATE TABLE IF NOT EXISTS `example_table` (
2.   `id` INT NOT NULL AUTO_INCREMENT,
3.   `name` VARCHAR(100) NOT NULL,
4.   `age` INT DEFAULT 0,
5.   `email` VARCHAR(200),
6.   PRIMARY KEY (`id`)
7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

复制代码

对表新增字段

原写法：

1. ALTER TABLE example_table ADD COLUMN create_time datetime null comment '创建时间';

复制代码

我们能不能也判定如果这个表中没有这个字段才执行新增字段，答案是可以的。在这里就要用到 MySql 预处理语句的语法了。
判定，如果在当前数据库中，存在当前这个表，表中没有这个字段，那么才会执行新增该字段。

• INFORMATION_SCHEMA.COLUMNS 系统级的字段表，记载了全部的字段信息
  
• SCHEMA() 当前数据库名称
  

可重复执行的写法：

1. set @sql = 'select 1 from dual;';
2. select ' ALTER TABLE example_table add COLUMN create_time datetime NULL comment ''创建时间'' ;' into @sql
3. from dual where (select count(1) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='example_table' AND COLUMN_NAME='sort')=0;
4. PREPARE stmt FROM @sql;
5. EXECUTE stmt;
6. DEALLOCATE PREPARE stmt;

复制代码

对表修改字段

原写法：

1. ALTER TABLE example_table MODIFY COLUMN email VARCHAR(500) DEFAULT '' comment '邮箱';

复制代码

同样使用 MySql 预处理语句的语法，判定在当前数据库中，存在这个表，且有这个字段，那么才会执行修改字段的语句。
可重复执行的写法：

1. set @sql = 'select 1 from dual;';
2. select ' ALTER TABLE example_table MODIFY COLUMN email VARCHAR(500) DEFAULT '''' comment ''邮箱'' ;' into @sql
3. from dual where (select count(1) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='example_table' AND COLUMN_NAME='email')=1;
4. PREPARE stmt FROM @sql;
5. EXECUTE stmt;
6. DEALLOCATE PREPARE stmt;

复制代码

对表新增索引

原写法：

1. ALTER TABLE example_table add index idx_name (name) COMMENT '名称索引';

复制代码

要判定当前数据库中，这个表中，根据索引名查询，未查到该索引就进行添加索引。

• INFORMATION_SCHEMA.STATISTICS 系统级的索引表，记载了全部的索引信息
  

可重复执行的写法：

1. set @sql = 'select 1 from dual;';
2. select 'ALTER TABLE example_table add index idx_name (name) COMMENT ''名称索引'';' into @sql from dual
3. where (select count(1) FROM INFORMATION_SCHEMA.STATISTICS where TABLE_SCHEMA=SCHEMA() and TABLE_NAME='example_table' and index_name='idx_name')=0;
4. PREPARE stmt FROM @sql;
5. EXECUTE stmt;
6. DEALLOCATE PREPARE stmt;

复制代码

插入一条新记载

原写法：

1. insert into example_table(name, age, email, create_time) VALUE ('jack', 18, 'jackaaa@gmail.com', now());

复制代码

在这里我们判定如果这个表中没有jack这个name，才进行插入记载。使用DUAL虚拟表帮助我们添加判定条件。
可重复执行的写法：

1. INSERT INTO example_table
2. (name, age, email, create_time)
3. SELECT 'jack', 18, 'jackaaa@gmail.com', now()
4. FROM DUAL
5. WHERE NOT EXISTS (select id from example_table where name = 'jack');

复制代码

对表中记载进行更新

update 语句因为其天生的幂等特质，不需要改写，就支持可重复执行。

1. update example_table set age = 20 where name = 'jack';

复制代码

对表中记载进行删除

delete 语句因为其天生的幂等特质，不需要改写，就支持可重复执行。

1. delete from example_table where name = 'tom';

复制代码

总结

至此，SQL脚本中常见的7类SQL写法如何改写为可重复执行的SQL，就整理完成了。可重复执行的SQL脚本不仅在执行时提供了便利，也为项目迁徙，项目本地化部署带来了便利。
参考

• mysql性能优化-预处理语句（Prepared Statements）
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。