Mysql的基本用法

ゞ浴缸里的玫瑰 2023-07-07 03:42 1阅读 0赞

**1、修改自定义函数访问权限的方式**

//security_type有个类型：DEFINER和INVOKER
    //DEFINER：在执行存储过程前验证definer对应的用户如：root@192.168.2.%是否存在，以及是否具有执行存储过程的权限，若没有则报错 
    //INVOKER：在执行存储过程时判断inovker即调用该存储过程的用户是否有相应权限，若没有则报错
    update mysql.proc set security_type='INVOKER' where db='数据库名' and name='自定义函数名';

**2、存储过程和游标的使用**  
场景：将所有产品的单价都提高%10

-- 定义SQL输入的结束符为//
    delimiter // 
    -- 定义存储过程
    CREATE PROCEDURE change_price()
    BEGIN
    	DECLARE var_id INT;
    	DECLARE var_price DOUBLE;
    	DECLARE var_new_price DOUBLE;
    	-- 定义游标结束标志
    	DECLARE done INT DEFAULT FALSE;
    	-- 定义游标
    	DECLARE cur CURSOR FOR SELECT id, price from commodity;
    	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    	
    	-- 打开游标
    	OPEN cur;
    	-- 循环游标值
    	cur_loop: LOOP
    	    -- 获取游标值
    		FETCH FROM cur INTO var_id, var_price;
    		-- 判断游标是否结束
    		IF done THEN LEAVE cur_loop; END IF;
    		-- 设置新的价格
    		set var_new_price := var_price * 1.1;
    		-- 更新价格
    		UPDATE commodity SET price = var_new_price WHERE id=var_id;
    	END LOOP;
    	-- 关闭游标
    	CLOSE cur;
    END;
    //
    -- 还原SQL输入的结束符
    DELIMITER ;
    
    -- 调用存储过程
    CALL change_price();
    -- 删除存储过程
    DROP PROCEDURE change_price;

**3、游标定义时表名可变的解决方案**  
游标定义中表名是不能为变量，但可以使用视图，间接实现游标中表名可变。

-----------------------------
    -- 场景 修改id_path
    -- 人员表中有org_id和id_path 例如：org_id='f9da5d54fe3248bf8282e9977b5f8a0b', id_path=''/413b2b183fc44b26a456224082f2190b/c32e0aa83ebe4ae8ba5b395006ab0308/f9da5d54fe3248bf8282e9977b5f8a0b
    -- 角色表中有org_id和id_path 例如：org_id='4b07a404f59347118c0a69ef997cd82b',id_path='/413b2b183fc44b26a456224082f2190b/c32e0aa83ebe4ae8ba5b395006ab0308/f9da5d54fe3248bf8282e9977b5f8a0b/4b07a404f59347118c0a69ef997cd82b'
    -- 需要将系统的所有org_id为c32e0aa83ebe4ae8ba5b395006ab0308修改成2993814831e34c7b8e00de24c25d0e4c，因此需要修改org_id和org_path
    -----------------------------
    -- 定义SQL输入的结束符为//
    delimiter // 
    -- 定义存储过程 
    -- var_old_orgid 需要修改的机构ID
    -- var_new_orgid 新机构ID
    -- var_tablename 需要修改的表
    CREATE PROCEDURE change_org(var_old_orgid VARCHAR(50), var_new_orgid VARCHAR(50), var_tablename VARCHAR(50))
    BEGIN
    	-- 定义表的主键名
    	DECLARE var_objuid VARCHAR(50);
    	-- 定义idpath
    	DECLARE var_idpath VARCHAR(500);
    	-- 定义游标结束标志
    	DECLARE done INT DEFAULT FALSE;
    	-- 定义游标其中view_org_info为视图
    	DECLARE cur CURSOR FOR SELECT id, id_path FROM view_org_info;
    	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    	
    	-- 定义视图
    	DROP VIEW IF EXISTS view_org_info;
    	SET @view_sql = CONCAT("CREATE VIEW view_org_info AS SELECT objuid, org_id, id_path FROM ", var_tablename, " WHERE id_path LIKE '%", var_old_orgid, "%'");
    	-- 执行视图
    	PREPARE stmt FROM @view_sql;
    	EXECUTE stmt;
    	DEALLOCATE PREPARE stmt;
    	
    	-- 打开游标
    	OPEN cur;
    	cur_loop: LOOP
    		FETCH FROM cur INTO var_objuid, var_idpath;
    		IF done THEN LEAVE cur_loop; END IF;
    		-- 替换id_path中的id
    		set @newIdPath := REPLACE(var_idpath, var_old_orgid, var_new_orgid);
    		-- 更新数据库
    		set @update_sql = CONCAT("UPDATE ", var_tablename, " SET id_path = '", @newIdPath, "' WHERE objuid = '", var_objuid, "'");
    		-- 执行sql
    		PREPARE stmt FROM @update_sql;
    		EXECUTE stmt;
    		DEALLOCATE PREPARE stmt;
    	END LOOP;
    	CLOSE cur;
    	DROP VIEW IF EXISTS view_org_info;
    END;
    //
    -- 还原SQL输入的结束符
    DELIMITER ;
    
    -- 调用存储过程
    CALL change_org();
    -- 删除存储过程
    DROP PROCEDURE change_org;