我们在进行新的功能开发时,由于历史项目的存在,需要在程序起动时对数据进行判断,如果发现是历史版本的数据,则需要对数据进行一些特定的处理;如果是最新的数据,则跳过处理过程。这在系统的版本迭代中尤为重要!本文将阐述如何自动化的实现该过程。
何为自动化
- 每次系统启动,均执行一次我们设定好的SQL语句。
- 当数据表存在且未升级时,进行数据表的升级操作。
- 当数据表不存在或数据已经升过级时,不对数据表进行操作。
功能描述
在数据库measurement
中,当存在数据表web_app_menu
,且该表中不存在db_type
字段时,为其添加db_type
字段。同时,进行部分数据的更新及删除操作。
知识点
普通的MYSQL语句并不支持IF ELSE
控制流
所以无法使用普通的SQL语句来实现如下的伪代码:
if (存在('web_app_menu'))
if (存在('db_type'))
// 执行语句
解决方法:使用存储过程来实现在MYSQL中的逻辑判断,进行实现伪代码中的逻辑功能。
遇到;
则会立即执行;
前面的语句。
存储过程在使用前,需要先进行定义,在定义的过程中,我们需要加入SQL语句,这就避免不了,我们在SQL语句中出现;
。而一旦;
出现,mysql会立即执行;
以前语句,此时,由于我们的存储过程未定义完毕,所以会得到一个“语法错误”的提示。
解决方法: 在定义存储过程前,重写;
。以使得在定义存储时,即使出现了;
,也不被mysql执行。在定义结束后,再将重写的;
复原。
存储定义后并不会自动执行
开始使用存储时,可能会遇到一个误区:储存定义后,为什么没有自动执行。其实MYSQL中的存储过程,就像我们在普通的语言中定义一个函数是一样的道理。函数定义后,并不会自动执行,只有当函数被调用时,才会被自动执行。所以,解决的方法也很简单:调用存储过程。
示例代码
-- 将 ; 转义为 // (即遇到//才相当于;) 防止定义 存储过程 时中,遇到 ; 而中断的问题。
DELIMITER //
-- 如果存在存储过程(sql函数)FUN20180516则将其删除
DROP PROCEDURE IF EXISTS `FUN20180516` //
-- 新建存储过程(sql函数),起名为:FUN20180518。相当于C中的(void FUN20180516())
CREATE PROCEDURE `FUN20180516` ()
-- 定义函数开始(相当于C中的 { )
BEGIN
-- 声明变量 int hasDb
DECLARE hasDb INT;
DECLARE hasDbType INT;
-- 执行语句(查询数据表是否存在),并将返回值给hasDb
SELECT count(*) INTO hasDb FROM information_schema.tables WHERE (table_schema = 'measurement') AND (table_name= 'web_app_menu');
-- 如果数据表存在(注意:用=来判断,而不是==)
IF hasDb = 1 THEN
-- 判断字段是否存在
SELECT count(*) INTO hasDbType FROM information_schema.columns WHERE (table_schema = 'measurement') and (table_name= 'web_app_menu') and (column_name = 'db_type');
-- 字段不存在,则执行以下代码
IF hasDbType = 0 THEN
ALTER TABLE `web_app_menu` ADD `db_type` VARCHAR(31) NOT NULL;
UPDATE `web_app_menu` SET `db_type` = 'web_app_menu' WHERE db_type = '';
DELETE wr.* FROM web_app_menu_role wr WHERE wr.web_app_menu_id IN (SELECT w.id FROM web_app_menu w WHERE w.id IN (select w1.p_id FROM web_app_menu w1 WHERE w.route_name = 'measurementStandardAssessmentApply'));
DELETE wr.* FROM web_app_menu_role wr WHERE wr.web_app_menu_id IN (SELECT w.id FROM web_app_menu w WHERE w.route_name = 'measurementStandardAssessmentApply');
DELETE w.* FROM web_app_menu w WHERE w.p_id IN (SELECT id FROM (SELECT id FROM web_app_menu w2 WHERE w2.route_name = 'measurementStandardAssessmentApply') x);
DELETE w.* FROM web_app_menu w WHERE w.route_name = 'measurementStandardAssessmentApply';
-- 控制台打印日志
SELECT '20180516 success';
end if;
END IF;
-- 下面我们用 // 相当于 END ;也就是说,我们的函数定义完了,你可以一并进行加载了。
END //
-- 再将 ; 恢复为 ;。防止后面的语句中,使用; 而不生效的问题
DELIMITER ;
-- 调用函数
call FUN20180516();
结合spring
首先: spring
支持两种方式在启动时执行SQL语句。其中是在resources
中建立data.sql
,另一种是建立schema.sql
。两个文件的区别在于:data.sql
后于JPA执行,而schema.sql
先于JPA执行。
由于我们需要在JPA起作用前先改变数据表的结构,删除冗余数据,所以需要建立schema.sql
。
其次:spring并不支持DELIMITER
语法,所以在schema.sql
中,删除DELIMITER //
及DELIMITER
;。但同样的,如果不重写;
,则还会出现遇到;
则执行的问题。所以我们还需要在配置文件中加入:spring.datasource.separator=//
来起到重写;
的作用。
此时,schema.sql
的形式如下:注意几处//
-- spring.datasource.separator=//已经过;进行了改写
-- 如果存在存储过程(sql函数)FUN20180516则将其删除
DROP PROCEDURE IF EXISTS `FUN20180516` //
-- 新建存储过程(sql函数),起名为:FUN20180518。相当于C中的(void FUN20180516())
CREATE PROCEDURE `FUN20180516` ()
-- 定义函数开始(相当于C中的 { )
BEGIN
-- 声明变量 int hasDb
DECLARE hasDb INT;
DECLARE hasDbType INT;
-- 执行语句(查询数据表是否存在),并将返回值给hasDb
SELECT count(*) INTO hasDb FROM information_schema.tables WHERE (table_schema = 'measurement') and (table_schema = 'measurement') AND (table_name= 'web_app_menu');
-- 如果数据表存在(注意:用=来判断,而不是==)
IF hasDb = 1 THEN
-- 判断字段是否存在
SELECT count(*) INTO hasDbType FROM information_schema.columns WHERE (table_name= 'web_app_menu') and (column_name = 'db_type');
-- 字段不存在,则执行以下代码
IF hasDbType = 0 THEN
ALTER TABLE `web_app_menu` ADD `db_type` VARCHAR(31) NOT NULL;
UPDATE `web_app_menu` SET `db_type` = 'web_app_menu' WHERE db_type = '';
DELETE wr.* FROM web_app_menu_role wr WHERE wr.web_app_menu_id IN (SELECT w.id FROM web_app_menu w WHERE w.id IN (select w1.p_id FROM web_app_menu w1 WHERE w.route_name = 'measurementStandardAssessmentApply'));
DELETE wr.* FROM web_app_menu_role wr WHERE wr.web_app_menu_id IN (SELECT w.id FROM web_app_menu w WHERE w.route_name = 'measurementStandardAssessmentApply');
DELETE w.* FROM web_app_menu w WHERE w.p_id IN (SELECT id FROM (SELECT id FROM web_app_menu w2 WHERE w2.route_name = 'measurementStandardAssessmentApply') x);
DELETE w.* FROM web_app_menu w WHERE w.route_name = 'measurementStandardAssessmentApply';
-- 控制台打印日志
SELECT '20180516 success';
end if;
END IF;
END //
-- 调用函数
call FUN20180516() //
application.properties
spring.datasource.separator=//
参考
http://www.cnblogs.com/geaozh...
http://blog.sina.com.cn/s/blo...
https://www.techonthenet.com/...
https://dba.stackexchange.com...
https://docs.spring.io/spring...
https://stackoverflow.com/que...
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。