自定义MySQL实用的函数和存储过程（持续更新）

灰太狼 2022-12-22 09:47 169阅读 0赞

**目录**

目标

功能列表

一键生成SQL

一键生成SQL（带驼峰式别名）

一键生成SQL（带驼峰式别名和字段注释）

根据表注释查询相关表集合

根据字段注释查询相关表集合

随机返回子字符串

随机返回范围日期

时间戳+随机数

随机生成两个数范围内的数

生成Mybatis插入格式

生成Mybatis插入格式（含字段注释）

根据table生成实体类

截取自定位置的字符串内容

--------------------

# 目标 #

简化MySQL日常开发，包括：自动生成测试数据、一键生成SQL、查询相关表集合等。

--------------------

# 功能列表 #

## 一键生成SQL ##

**功能说明：**输入表名称，获取表的所有字段。

/*单表查询工具*/
    DELIMITER $$
     
    USE `数据库名字`$$
     
    DROP FUNCTION IF EXISTS `one_table`$$
     
    CREATE DEFINER=`数据库账号`@`%` FUNCTION `one_table`(one_table_name VARCHAR(1024)) RETURNS TEXT CHARSET utf8
    BEGIN
        DECLARE result_sql VARCHAR(2048);
        SELECT  CONCAT('select ','\n', GROUP_CONCAT(COLUMN_NAME),'\n','from ','\n',one_table_name) INTO result_sql FROM information_schema.COLUMNS 
        WHERE  table_schema = DATABASE() AND table_name =one_table_name;
        
        RETURN result_sql;
        END$$
     
    DELIMITER ;

/*两表查询工具*/
    DELIMITER $$
     
    USE `数据库名字`$$
     
    DROP FUNCTION IF EXISTS `two_table`$$
     
    CREATE DEFINER=`数据库账号`@`%` FUNCTION `two_table`(one_table_name VARCHAR(1024),two_table_name VARCHAR(1024)) RETURNS TEXT CHARSET utf8
    BEGIN
    
        DECLARE result_sql TEXT;
    SELECT CONCAT('select ','\n',GROUP_CONCAT(a.table_info,'\n'),'from ','\n',GROUP_CONCAT(a.table_name SEPARATOR ' inner join '),'\non \n') INTO result_sql FROM (
    SELECT  CONCAT(one_table_name ,' a') table_name,CONCAT(GROUP_CONCAT('a.',COLUMN_NAME) ) table_info FROM information_schema.COLUMNS 
    WHERE  table_schema = DATABASE() AND table_name = one_table_name
    UNION ALL
    SELECT  CONCAT(two_table_name, ' b') table_name,CONCAT(GROUP_CONCAT('b.',COLUMN_NAME) ) table_info FROM information_schema.COLUMNS 
    WHERE  table_schema = DATABASE() AND table_name = two_table_name
    )a ;
    
    RETURN result_sql;
        END$$

**调用&返回结果：**

/*查询学生表的字段：*/
    SELECT `one_table`('student')
    /*返回结果：*/
    SELECT 
    id,student_name,sex,age
    FROM 
    student
    
    /*关联学生表和家长表，查询所有字段：*/
    SELECT `two_table`('student','parent_info')
    /*返回结果：*/
    SELECT 
    a.id,a.student_name,a.sex,a.age
    ,b.id,b.parent_name,b.student_id
    FROM 
    student a INNER JOIN parent_info b
    ON

--------------------

## 一键生成SQL（带驼峰式别名） ##

**功能说明：**输入表名称，获取表的所有字段，并给所有字段定义驼峰式别名。

DELIMITER $$
    
    USE `数据库名称`$$
    
    DROP FUNCTION IF EXISTS `create_bean`$$
    
    CREATE DEFINER=`数据库账号`@`localhost` FUNCTION `create_bean`(var_string TEXT) RETURNS TEXT CHARSET utf8mb4
        DETERMINISTIC
    BEGIN
    DECLARE var_mysql_string TEXT;
    DECLARE var_mybatis_string TEXT;
    SELECT  
    CONCAT(GROUP_CONCAT(
    IF(
    LOCATE('_',var_string) > 0
    ,
    CONCAT(
    /*头部*/
    LEFT(var_string,INSTR(var_string,'_')-1),
    /*中部（要修改）*/
    UPPER(
    SUBSTRING(var_string,INSTR(var_string,'_')+1,1)
    ),
    /*尾部*/
    SUBSTRING(var_string,INSTR(var_string,'_')+2)),
    var_string
    ))) 
    INTO @a;
        
    WHILE LOCATE('_',@a) > 0 DO 
    SELECT 
    CONCAT(
    /*头部*/
    LEFT(@a,INSTR(@a,'_')-1),
    /*中部（要修改）*/
    UPPER(
    SUBSTRING(@a,INSTR(@a,'_')+1,1)
    ),
    /*尾部*/
    SUBSTRING(@a,INSTR(@a,'_')+2)
    ) INTO @a; 
    END WHILE;
    
    RETURN @a;
        
        END$$
    
    DELIMITER ;

DELIMITER $$
    
    USE `数据库名称`$$
    
    DROP FUNCTION IF EXISTS `other_table_name`$$
    
    CREATE DEFINER=`数据库账号`@`localhost` FUNCTION `other_table_name`(var_table_name TEXT) RETURNS TEXT CHARSET utf8mb4
        DETERMINISTIC
    BEGIN
        
    SELECT 
    CONCAT('select ','\n', GROUP_CONCAT(a.COLUMN_NAME,' ' ,create_bean(b.COLUMN_NAME) SEPARATOR ','),'\n','from ','\n',var_table_name)
    INTO @a
    FROM 
    (
    SELECT COLUMN_NAME
    FROM information_schema.COLUMNS 
    WHERE  table_schema = DATABASE() AND table_name =var_table_name
    )a , 
    (
    SELECT COLUMN_NAME
    FROM information_schema.COLUMNS 
    WHERE  table_schema = DATABASE() AND table_name =var_table_name
    )b 
    WHERE a.COLUMN_NAME=b.COLUMN_NAME;
    RETURN @a;
        END$$
    
    DELIMITER ;

**调用&返回结果：**

#调用
    SELECT other_table_name('student');
    #返回结果
    select 
    id id,stu_name stuName,stu_age stuAge,stu_sex stuSex
    from 
    student

--------------------

## 一键生成SQL（带驼峰式别名和字段注释） ##

**功能说明：**输入表名称，获取表的所有字段，并给所有字段定义驼峰式别名和字段注释（如果字段有注释）。

DELIMITER $$
    
    USE `数据库名称`$$
    
    DROP FUNCTION IF EXISTS `other_table_name_details`$$
    
    CREATE DEFINER=`账号`@`localhost` FUNCTION `other_table_name_details`(var_table_name TEXT) RETURNS TEXT CHARSET utf8mb4
        DETERMINISTIC
    BEGIN
        
    SELECT 
    CONCAT('select ', GROUP_CONCAT(
    IF(column_comment IS NULL OR column_comment ='' , '', CONCAT('\n','')),
    '\n',a.COLUMN_NAME,' ' ,create_bean(b.COLUMN_NAME) SEPARATOR ','),'\n','from ','\n',var_table_name)
     INTO @a
    FROM 
    (
    SELECT COLUMN_NAME,column_comment
    FROM information_schema.COLUMNS 
    WHERE  table_schema = DATABASE() AND table_name =var_table_name
    )a , 
    (
    SELECT COLUMN_NAME
    FROM information_schema.COLUMNS 
    WHERE  table_schema = DATABASE() AND table_name =var_table_name
    )b 
    WHERE a.COLUMN_NAME=b.COLUMN_NAME;    
        
    RETURN @a;
        END$$
    
    DELIMITER ;

**调用&返回结果：**

/*调用*/
    SELECT other_table_name_details('b_pay_steplog')
    /*返回结果*/
    select 
    loguid loguid,
    entvesion entvesion,
    entcode entcode,
    extappid extappid,
    buid buid,
    storeuid storeuid,
    
    paytype paytype,
    
    state state,
    dt dt,
    
    paysource paysource,
    
    attach attach,
    
    srefno srefno,
    
    outtradeno outtradeno,
    
    businessType businessType
    from 
    b_pay_steplog

--------------------

## 根据表注释查询相关表集合 ##

**功能说明：**输入表的注释信息，获取相关表的集合。

DELIMITER $$
     
    USE `数据库名称`$$
     
    DROP PROCEDURE IF EXISTS `table_comment`$$
     
    CREATE DEFINER=`数据库账号`@`%` PROCEDURE `table_comment`(param_table_name VARCHAR(1024) CHARSET 'utf8mb4')
    BEGIN
    IF param_table_name IS NOT NULL  AND LENGTH(REPLACE(param_table_name,' ',''))>0
    THEN
    SELECT
        TABLE_NAME 表名称,
        TABLE_COMMENT 表备注
    FROM
        information_schema.`TABLES`
    WHERE
        TABLE_SCHEMA = DATABASE() AND TABLE_COMMENT LIKE CONCAT('%',param_table_name,'%') ORDER BY TABLE_NAME;
    ELSE
    SELECT
        TABLE_NAME 表名称,
        TABLE_COMMENT 表备注
    FROM
        information_schema.`TABLES`
    WHERE
        TABLE_SCHEMA = DATABASE()  ORDER BY TABLE_NAME;
    END IF;
        END$$
     
    DELIMITER ;

/*给表加上注释：*/
    ALTER TABLE `parent_info`  COMMENT='家长信息表';
    ALTER TABLE `student` COMMENT='学生信息表';
    ALTER TABLE `student_grade` COMMENT='学生成绩表';

**调用&返回结果：**

/*查询所有表信息：*/
    CALL `table_comment`(NULL)

![20201119181031887.png][]

/*查询和学生有关的表信息：*/
    CALL `table_comment`('学生')

![20201119181108352.png][]

--------------------

## 根据字段注释查询相关表集合 ##

**功能说明：**输入字段的注释信息，获取相关表的集合。

DELIMITER $$
     
    USE `数据库名称`$$
     
    DROP PROCEDURE IF EXISTS `t_col`$$
     
    CREATE DEFINER=`数据库账号`@`%` PROCEDURE `t_col`(param_column_name VARCHAR(1024) CHARSET 'utf8mb4')
    BEGIN
    SELECT DISTINCT a.table_name 表名称,b.TABLE_COMMENT 表备注
    FROM information_schema.COLUMNS a ,information_schema.`TABLES` b 
    WHERE  a.table_schema = DATABASE() AND a.table_name=b.table_name AND a.column_comment LIKE CONCAT('%',param_column_name,'%');
        END$$
     
    DELIMITER ;

**调用&返回结果：**

/*查询字段注释信息中包含'性别'的表集合：*/
    CALL t_col('性别');

![20201119192308611.png][]

--------------------

## 随机返回子字符串 ##

**功能说明：**输入以英文逗号分隔的数据，随机返回一个子字符串。

DELIMITER $$
     
    USE `数据库名字`$$
     
    DROP FUNCTION IF EXISTS `rand_string`$$
     
    CREATE DEFINER=`数据库账号`@`%` FUNCTION `rand_string`(var_string TEXT) RETURNS TEXT CHARSET utf8
    BEGIN
    /*根据逗号分隔字符串，统计有多少个字符串。*/
    SET @num= LENGTH(var_string)-LENGTH(REPLACE(var_string,',',''))+1;
    /*随机生成位置*/
    SET @index= FLOOR(RAND()*@num)+1;
    /*开始截取*/
    SET @rand_start =SUBSTRING_INDEX(var_string,',',@index);
    SET  @rand_end =SUBSTRING_INDEX(@rand_start,',',-1);
    RETURN @rand_end ;
        END$$
     
    DELIMITER ;

**调用&返回结果：**

/*随机返回姓名，以英文逗号分隔。*/
    SELECT `rand_string`('Tom,Mark,Tony,Green')

--------------------

## 随机返回范围日期 ##

**功能说明：**输入两个日期，随机生成这两个日期之间的一个日期。

DELIMITER $$
     
    USE `数据库名称`$$
     
    DROP FUNCTION IF EXISTS `create_time`$$
     
    CREATE DEFINER=`数据库账号`@`%` FUNCTION `create_time`(start_time VARCHAR(10),end_time VARCHAR(10) ) RETURNS VARCHAR(36) CHARSET utf8
    BEGIN
      /*两个日期之差*/
       DECLARE num INT(4) DEFAULT ABS(DATEDIFF(start_time, end_time));
       DECLARE rand_date DATE DEFAULT DATE_ADD(start_time,INTERVAL FLOOR(RAND()*num) DAY);
        RETURN CONCAT(
    rand_date,' ',
    /*随机时*/
     LPAD(FLOOR(RAND()*24),2, 0),':',
    /*随机分*/
     LPAD(FLOOR(RAND()*60),2, 0),':',
    /*随机秒*/
     LPAD(FLOOR(RAND()*60),2, 0)
    );
        END$$
     
    DELIMITER ;

**调用&返回结果：**

/*随机生成一个范围日期：*/
    SELECT create_time('2020-01-20','2020-01-09') rand_time

--------------------

## 时间戳+随机数 ##

/*毫秒级别时间戳+5位随机数*/
    SELECT 
    CONCAT(REPLACE(UNIX_TIMESTAMP(CURRENT_TIMESTAMP(3)),'.',''),LPAD(FLOOR(RAND()*(100000)),5, 0))  order_number ;

--------------------

## 随机生成两个数范围内的数 ##

**功能说明：**输入两个整数，随机生成范围数据。

**调用&返回结果：**

/*[a,b]的范围整数*/
    SELECT FLOOR(b + (RAND() * a-b+1));

--------------------

## 生成Mybatis插入格式 ##

**功能说明**：输入表名，生成dao层的插入映射。

DELIMITER $$
    
    USE `数据库`$$
    
    DROP FUNCTION IF EXISTS `insert_dao`$$
    
    CREATE DEFINER=`数据库账号`@`%` FUNCTION `insert_dao`(param_table_name TEXT) RETURNS TEXT CHARSET utf8mb4
    BEGIN
    DECLARE var_mysql_string TEXT;
    DECLARE var_mybatis_string TEXT;
    SELECT  CONCAT('insert into  ',param_table_name,'\n', '(',GROUP_CONCAT(COLUMN_NAME),'\n',')values( ','\n') INTO var_mysql_string FROM information_schema.COLUMNS WHERE  table_schema = DATABASE() AND table_name =param_table_name;
    SELECT  
    CONCAT(GROUP_CONCAT(
    IF(
    LOCATE('_',COLUMN_NAME) > 0
    ,
    CONCAT(
    /*头部*/
    LEFT(COLUMN_NAME,INSTR(COLUMN_NAME,'_')-1),
    /*中部（要修改）*/
    UPPER(
    SUBSTRING(COLUMN_NAME,INSTR(COLUMN_NAME,'_')+1,1)
    ),
    /*尾部*/
    SUBSTRING(COLUMN_NAME,INSTR(COLUMN_NAME,'_')+2)),
    COLUMN_NAME
    ))) 
    INTO @a
      FROM information_schema.COLUMNS 
    WHERE  table_schema = DATABASE() AND table_name =param_table_name;
        
    WHILE LOCATE('_',@a) > 0 DO 
    SELECT 
    CONCAT(
    /*头部*/
    LEFT(@a,INSTR(@a,'_')-1),
    /*中部（要修改）*/
    UPPER(
    SUBSTRING(@a,INSTR(@a,'_')+1,1)
    ),
    /*尾部*/
    SUBSTRING(@a,INSTR(@a,'_')+2)
    ) INTO @a; 
    END WHILE;
    SET var_mybatis_string= CONCAT('#{',REPLACE(@a,',','},#{'),'}','\n',');') ;
    RETURN CONCAT(var_mysql_string,var_mybatis_string);
        END$$
    
    DELIMITER ;

**调用&返回结果：**

/*生成member_up_level表的映射。*/
    SELECT insert_dao('member_up_level');
    
    /*返回结果。*/
    insert into  member_up_level
    (id,create_time,member_id,before_up_level,after_up_level,up_level_type
    )values( 
    #{id},#{createTime},#{memberId},#{beforeUpLevel},#{afterUpLevel},#{upLevelType}
    );

--------------------

## 生成Mybatis插入格式（含字段注释） ##

**功能说明**：输入表名，生成dao层的插入映射，如果数据库字段有注释，则展示字段注释。

DELIMITER $$
    
    USE `数据库名称`$$
    
    DROP FUNCTION IF EXISTS `insert_dao_details`$$
    
    CREATE DEFINER=`账号`@`%` FUNCTION `insert_dao_details`(param_table_name TEXT) RETURNS TEXT CHARSET utf8mb4
    BEGIN
    DECLARE var_mysql_string TEXT;
    DECLARE var_mybatis_string TEXT;
    
    SELECT  CONCAT('insert into  ',
    param_table_name, '(',GROUP_CONCAT(
    IF(column_comment IS NULL OR column_comment ='','',
    CONCAT('\n','')
    ),
    '\n',COLUMN_NAME),'\n',')values( ','\n')  INTO var_mysql_string  FROM information_schema.COLUMNS 
    WHERE  table_schema = DATABASE() AND table_name =param_table_name;
    
    SELECT  
    CONCAT(GROUP_CONCAT(
    IF(
    LOCATE('_',COLUMN_NAME) > 0
    ,
    CONCAT(
    /*头部*/
    LEFT(COLUMN_NAME,INSTR(COLUMN_NAME,'_')-1),
    /*中部（要修改）*/
    UPPER(
    SUBSTRING(COLUMN_NAME,INSTR(COLUMN_NAME,'_')+1,1)
    ),
    /*尾部*/
    SUBSTRING(COLUMN_NAME,INSTR(COLUMN_NAME,'_')+2)),
    COLUMN_NAME
    ))) 
    INTO @a
      FROM information_schema.COLUMNS 
    WHERE  table_schema = DATABASE() AND table_name =param_table_name;
        
    WHILE LOCATE('_',@a) > 0 DO 
    SELECT 
    CONCAT(
    /*头部*/
    LEFT(@a,INSTR(@a,'_')-1),
    /*中部（要修改）*/
    UPPER(
    SUBSTRING(@a,INSTR(@a,'_')+1,1)
    ),
    /*尾部*/
    SUBSTRING(@a,INSTR(@a,'_')+2)
    ) INTO @a; 
    END WHILE;
    SET var_mybatis_string= CONCAT('#{',REPLACE(@a,',','},#{'),'}','\n',');') ;
    RETURN CONCAT(var_mysql_string,var_mybatis_string);
        END$$
    
    DELIMITER ;

**调用&返回结果：**

/*生成member_up_level表的映射。*/
    SELECT insert_dao_details('b_pay_steplog')
    
    /*返回结果。*/
    insert into  b_pay_steplog(
    loguid,
    entvesion,
    entcode,
    extappid,
    buid,
    storeuid,
    
    paytype,
    
    state,
    dt,
    
    paysource,
    
    attach,
    
    srefno,
    
    outtradeno,
    
    businessType
    )values( 
    #{loguid},#{entvesion},#{entcode},#{extappid},#{buid},#{storeuid},#{paytype},#{state},#{dt},#{paysource},#{attach},#{srefno},#{outtradeno},#{businessType}
    );

--------------------

## 根据table生成实体类 ##

**功能说明**：输入表名，生成JAVA Bean对象。

DELIMITER $$
    
    USE `数据库名`$$
    
    DROP FUNCTION IF EXISTS `create_bean`$$
    
    CREATE DEFINER=`数据库登录名`@`%` FUNCTION `create_bean`(param_table_name VARCHAR(1024)) RETURNS TEXT CHARSET utf8
    BEGIN
    SELECT 
    GROUP_CONCAT('//',COLUMN_COMMENT,'\n','private ',
    CASE DATA_TYPE
    		WHEN 'bigint' THEN
    			"Long"
    		WHEN 'decimal' THEN
    			'BigDecimal'
    		WHEN 'date' THEN
    		'Date'
    		WHEN 'datetime' THEN
    		'Date'
    		WHEN 'varchar' THEN
    		'String'
    		WHEN 'int' THEN
    		'Integer/Long'
    		ELSE
    		'null'
    		END 
    ,' ',
     COLUMN_NAME  
    SEPARATOR ';\n') INTO @a
    FROM information_schema.COLUMNS WHERE  table_schema = DATABASE() AND table_name =param_table_name;
    WHILE LOCATE('_',@a) > 0 DO 
    SELECT 
    CONCAT(
    #头部
    LEFT(@a,INSTR(@a,'_')-1),
    #中部（要修改）
    UPPER(
    SUBSTRING(@a,INSTR(@a,'_')+1,1)
    ),
    #尾部
    SUBSTRING(@a,INSTR(@a,'_')+2)
    ) INTO @a; 
    END WHILE;
    RETURN CONCAT(@a,';');
        END$$
    
    DELIMITER ;

**调用&返回结果：**

/*生成investment_order表的映射。*/
    SELECT create_bean('investment_order');
    
    /*返回结果。*/
    
    //主键
    private Integer/Long id;
    //订单号
    private String orderNo;
    //投资金额
    private BigDecimal investmentAmount;
    //静态收益
    private BigDecimal staticIncome;
    //最近一次的静态收益
    private BigDecimal staticIncomeLately;
    //会员ID
    private Integer/Long memberId;
    //0、正常；1、销毁
    private Integer/Long orderStatus;

--------------------

## 截取自定位置的字符串内容 ##

**功能说明**：输入分隔符的位置，截取指定内容。

/*n表示第n个和第n-1个分隔符前面的内容。*/
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('字符串内容', ',', n), ',', -1) msg

**调用&返回结果：**

/*案例如下，将输出777。*/
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('111,222,333,444,555,666,777,888,999,000', ',', 7), ',', -1) msg

[20201119181031887.png]: /images/20221120/4db127a745e64603970881c5da7514e0.png
[20201119181108352.png]: /images/20221120/98a51b389eb34201a9af6ca190c26819.png
[20201119192308611.png]: /images/20221120/2820b73d05c44baea99b37bb937d2157.png