一、 `ALTER PROCEDURE` 的语法与用途
1. 基本语法
ALTER PROCEDURE procedure_name [characteristic ...]
2. 可修改的特征(characteristic)
这些特征主要用于提供元数据信息,帮助 MySQL 和开发者理解存储过程的行为,某些特征(如 `SQL SECURITY`)也具有实际的安全影响。
特征 | 说明 |
---|---|
COMMENT 'string' | 添加或修改注释。这是非常有用的,用于说明过程的功能。 |
LANGUAGE SQL | 说明过程体语言,目前总是 SQL。 |
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | 数据访问特性。向服务器声明过程的行为,主要用于优化和文档。 |
SQL SECURITY { DEFINER | INVOKER } | 非常重要的安全特性。DEFINER (默认)以定义者的权限执行;INVOKER 以调用者的权限执行。 |
3. 实例演示(基于您的例子)
修改存储过程 `showstuscore` 的特性:
将数据访问特性改为“修改数据”,并将安全特性改为“调用者权限”
ALTER PROCEDURE showstuscore MODIFIES SQL DATA SQL SECURITY INVOKER;
修改后,使用 `SHOW CREATE PROCEDURE` 查看,定义头部会反映出这些变化:
CREATE DEFINER=`root`@`localhost` PROCEDURE `showstuscore`() MODIFIES SQL DATA SQL SECURITY INVOKER BEGIN END
二、 如何修改存储过程的“内容”(主体逻辑)
这是核心要点。由于 `ALTER PROCEDURE` 不能修改过程体,标准且通用的方法是:
1. 完全重建法(删除后重新创建)
这是最常用、最直接的方法。
第一步:删除已有的存储过程
DROP PROCEDURE IF EXISTS showstuscore;
第二步:使用新的逻辑重新创建同名的存储过程
DELIMITER //
CREATE PROCEDURE showstuscore()
BEGIN
这里是新的、修改后的SQL逻辑
例如,我们不只是查询,还要更新一个日志表
SELECT id, name, score FROM studentinfo;
新增的逻辑
INSERT INTO access_log (action, time) VALUES ('called showstuscore', NOW());
END //
DELIMITER ;
优点:简单明了,绝对有效。
缺点:需要拥有删除(`DROP`)和创建(`CREATE`)的权限。在删除和重建的极短间隙内,该过程不可用。
2. 创建新过程法(重命名)
如果不想影响现有过程,或者要保留旧版本。
首先,将旧过程重命名(通过创建新名,删除旧名实现)
1. 创建一份副本(重命名)
DELIMITER //
CREATE PROCEDURE showstuscore_old() 新名字
BEGIN
SELECT id,name,score FROM studentinfo;
END //
DELIMITER ;
2. 删除旧过程
DROP PROCEDURE showstuscore;
3. 创建新的、逻辑已修改的过程,使用原始名称
DELIMITER //
CREATE PROCEDURE showstuscore() -- 原始名字
BEGIN
SELECT id, name, score FROM studentinfo;
INSERT INTO access_log (action, time) VALUES ('called showstuscore', NOW());
END //
DELIMITER ;
优点:可以实现“热更新”,避免服务中断(先创建新的,再切换)。
缺点:步骤更繁琐。
三、 重要特性详解
1. `SQL SECURITY` 特性
`DEFINER`(定义者):过程以创建它的用户的权限来执行。调用者只需要有执行(`EXECUTE`)该过程的权限即可,不需要有操作底层表的权限。这增强了安全性,是推荐的方式。
`INVOKER`(调用者):过程以当前调用它的用户的权限来执行。调用者必须既有执行过程的权限,又有操作底层所有表的权限。更灵活但安全性较低。
2. 数据访问特性
这些主要是声明性的,用于优化和文档化,数据库可能会根据这些提示进行优化。
`CONTAINS SQL`:包含SQL,但不读也不写(如 `SET @x = 1`)。
`NO SQL`:不包含SQL(在MySQL中基本无用)。
`READS SQL DATA`:包含`SELECT`等读操作。
`MODIFIES SQL DATA`:包含`INSERT`, `UPDATE`, `DELETE`等写操作。
C语言网提供由在职研发工程师或ACM蓝桥杯竞赛优秀选手录制的视频教程,并配有习题和答疑,点击了解:
一点编程也不会写的:零基础C语言学练课程
解决困扰你多年的C语言疑难杂症特性的C语言进阶课程
从零到写出一个爬虫的Python编程课程
只会语法写不出代码?手把手带你写100个编程真题的编程百练课程
信息学奥赛或C++选手的 必学C++课程
蓝桥杯ACM、信息学奥赛的必学课程:算法竞赛课入门课程
手把手讲解近五年真题的蓝桥杯辅导课程