mysql常用自用自定义封装 最后更新时间：2023年10月12日 ### 函数 #### 脱敏 ##### tuoming (col_name,start_save,end_save,xnum) 用于字段脱敏的函数。 ###### 形参解释 - col_name - varchar - 需要脱敏的字段名 - start_save - int - 脱敏字段开头保留多少个字符 - end_s - int - 脱敏字段末尾保留多少个字符 ###### 函数创建 ```sql CREATE FUNCTION tuoming (col_name VARCHAR(255),start_save INT(12),end_save INT(12)) RETURNS VARCHAR(255) comment "脱敏函数" BEGIN DECLARE val VARCHAR(255); DECLARE xnum INT(12); DECLARE char_len int(12); set char_len = CHAR_LENGTH(col_name); set xnum = char_len - start_save - end_save; SET val = concat( substr(col_name, 1, start_save), REPEAT ("*", xnum), substr(col_name, char_len - end_save + 1) ); RETURN val; END ``` ##### phoneTM(col_name) 手机号码脱敏 ###### 注意 需要结合上面的脱敏函数使用 ###### 形参解释 - col_name - int - 需要脱敏的字段名 ##### 函数创建 ```sql create function phoneTM(col_name varchar(255)) returns varchar(255) comment "手机号码脱敏" BEGIN DECLARE val VARCHAR(255); DECLARE start_save int(12) DEFAULT 3; DECLARE end_save int(12) DEFAULT 3; SET val = tuoming(col_name,start_save,end_save); RETURN val; END ``` ##### carnumTM(col_name) 车牌号脱敏 ###### 注意 需要结合上面的脱敏函数使用 ###### 形参解释 - col_name - int - 需要脱敏的字段名 ##### 函数创建 ```sql create function carnumTM(col_name varchar(255)) returns varchar(255) comment "车牌号脱敏" BEGIN DECLARE val VARCHAR(255); DECLARE start_save int(12) DEFAULT 2; DECLARE end_save int(12) DEFAULT 2; SET val = tuoming(col_name,start_save,end_save); RETURN val; END ``` ##### dzTM(col_name) 地址脱敏 ###### 注意 需要结合上面的脱敏函数使用 ###### 形参解释 - col_name - int - 需要脱敏的字段名 ##### 函数创建 ```sql create function dzTM(col_name varchar(255)) returns varchar(255) comment "地址脱敏" BEGIN DECLARE val VARCHAR(255); DECLARE start_save int(12) DEFAULT 3; DECLARE end_save int(12) DEFAULT 4; SET val = tuoming(col_name,start_save,end_save); RETURN val; END ``` ##### emailTM(col_name) 电子邮箱脱敏 ###### 注意 需要结合上面的脱敏函数使用 ###### 形参解释 - col_name - int - 需要脱敏的字段名 ##### 函数创建 ```sql create function emailTM(col_name varchar(255)) returns varchar(255) comment "电子邮箱脱敏" BEGIN DECLARE val VARCHAR(255); DECLARE start_save int(12) DEFAULT 3; DECLARE end_save int(12) DEFAULT 4; SET val = tuoming(col_name,start_save,end_save); RETURN val; END ``` ##### nameTM(col_name) 姓名脱敏 ###### 注意 需要结合上面的脱敏函数使用 ###### 形参解释 - col_name - int - 需要脱敏的字段名 ##### 函数创建 ```sql create function nameTM(col_name varchar(255)) returns varchar(255) comment "姓名脱敏" BEGIN DECLARE val VARCHAR(255); DECLARE start_save int(12) DEFAULT 1; DECLARE end_save int(12) DEFAULT 0; SET val = tuoming(col_name,start_save,end_save); RETURN val; END ``` ##### sfzhTM(col_name) 身份证号脱敏 ###### 注意 需要结合上面的脱敏函数使用 ###### 形参解释 - col_name - int - 需要脱敏的字段名 ##### 函数创建 ```sql create function sfzhTM(col_name varchar(255)) returns varchar(255) comment "身份证号脱敏" BEGIN DECLARE val VARCHAR(255); DECLARE start_save int(12) DEFAULT 5; DECLARE end_save int(12) DEFAULT 4; SET val = tuoming(col_name,start_save,end_save); RETURN val; END ``` ##### universalNumTM(col_name) 通用的编号脱敏 ###### 注意 需要结合上面的脱敏函数使用 ###### 形参解释 - col_name - int - 需要脱敏的字段名 ##### 函数创建 ```sql create function universalNumTM(col_name varchar(255)) returns varchar(255) comment "通用的编号脱敏" BEGIN DECLARE val VARCHAR(255); DECLARE start_save int(12) DEFAULT 3; DECLARE end_save int(12) DEFAULT 4; SET val = tuoming(col_name,start_save,end_save); RETURN val; END ``` ### 过程存储 #### 动态sql ##### dynamicOperation(sqls) 用于字段脱敏的函数。 ###### 形参解释 - sqls - in - varchar - 需要运行sql代码字符串 ###### 过程存储创建 ```sql CREATE PROCEDURE dynamicOperation(in sqls VARCHAR(255)) -- 改过程用于动态运行sql BEGIN DECLARE tmp_drop_sql VARCHAR(255); set tmp_drop_sql = sqls; set @tmp_tab_sql = tmp_drop_sql; PREPARE tmp_PREPARE from @tmp_tab_sql; EXECUTE tmp_PREPARE ; end ``` #### 自动获取全库所有数据表的数据前三条并拼接 ##### allTabColSelect(table_schema_str,need_table_names,not_need_table_names) 用于字段脱敏的函数。 #### 依赖 - 过程存储 - dynamicOperation() ###### 形参解释 - table_schema_str - in - varchar - 需要处理的数据库 - need_table_names - in - varchar - 需要处理的数据表 - not_need_table_names - in - varchar - 不处理的数据表 ###### 过程存储创建 ```sql -- 所用过程存储依赖 -- // dynamicOperation() 动态执行sql drop PROCEDURE if EXISTS allTabColSelect; create PROCEDURE allTabColSelect(in table_schema_str VARCHAR(255),in need_table_names VARCHAR(255),in not_need_table_names VARCHAR(255)) BEGIN DECLARE tmp_len int(12); DECLARE num int(12) DEFAULT 0; DECLARE col_num int(12); DECLARE tmp_sql LONGTEXT; declare tmp_tab_sql LONGTEXT; DECLARE tmp_table_name varchar(50); DECLARE tmp_table_comment varchar(50); DECLARE tmp_col_name varchar(50); DECLARE tmp_col_comment varchar(50); Declare tmp_create_sql LONGTEXT; Declare tmp_inset_sql LONGTEXT; -- 初始化两张临时表 -- drop table if EXISTS tmpYxxTbale; drop table if EXISTS tmp; CREATE table tmp AS ( SELECT tabs.table_name AS table_name, tabs.table_comment AS table_comment, cols.COLUMN_NAME AS col_name, cols.COLUMN_COMMENT AS col_comment FROM information_schema.`COLUMNS` cols,( SELECT table_name, table_comment FROM information_schema.`TABLES` WHERE table_SCHEMA = table_schema_str -- 需要处理的数据库 AND table_name LIKE need_table_names -- 需要处理的数据表 and table_name not REGEXP not_need_table_names -- 不处理的数据表 ) tabs WHERE cols.table_name = tabs.table_name ); SELECT count(1) into tmp_len from tmp; while num < tmp_len do set col_num = num+1; -- 取出最新表元信息 SELECT table_name,table_comment,col_name,col_comment into tmp_table_name,tmp_table_comment,tmp_col_name,tmp_col_comment from tmp LIMIT col_num,1; -- 创建动态查询语句 set tmp_tab_sql = concat("(SELECT '",tmp_table_name,"' as ","table_name",", '",tmp_table_comment,"' as ","table_comment",", '",tmp_col_name,"' as ","filed",", ",tmp_col_name," as ","field_value",", '",tmp_col_comment,"' as ","filed_comment"," from ",tmp_table_name," limit 1,3);"); if num = 0 then -- 创建临时表，用于存放查询的数据信息 -- 删除表 drop table if EXISTS tmpYxxTbale; -- 创建表 CREATE TABLE tmpYxxTbale ( `table_name` varchar(255) NULL, `table_comment` varchar(255) NULL, `filed` longtext NULL, `field_value` longtext NULL, `filed_comment` longtext NULL ); end if; set tmp_inset_sql = concat("INSERT into tmpYxxTbale ", tmp_tab_sql); call dynamicOperation(tmp_inset_sql); set num = num+1; end while; drop table if EXISTS tmp; select * from tmpYxxTbale; end ```
Comments | NOTHING