mysql常用自用自定义封装

最后更新时间：2023年10月12日

函数

脱敏

tuoming (col_name,start_save,end_save,xnum)

用于字段脱敏的函数。

形参解释

  • col_name

    • varchar
    • 需要脱敏的字段名

  • start_save

    • int
    • 脱敏字段开头保留多少个字符

  • end_s

    • int
    • 脱敏字段末尾保留多少个字符
函数创建
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
    • 需要脱敏的字段名
函数创建
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
    • 需要脱敏的字段名
函数创建
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
    • 需要脱敏的字段名
函数创建
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
    • 需要脱敏的字段名
函数创建
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
    • 需要脱敏的字段名
函数创建
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
    • 需要脱敏的字段名
函数创建
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
    • 需要脱敏的字段名
函数创建
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代码字符串
过程存储创建
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
    • 不处理的数据表
过程存储创建
-- 所用过程存储依赖
-- // 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

声明：一代明君的小屋|版权所有，违者必究|如未注明，均为原创|本网站采用BY-NC-SA协议进行授权

转载：转载请注明原文链接 - mysql常用自用自定义封装

欢迎来到我的小屋