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

已于 2023-04-24 16:07:09 修改
阅读量1.2k 收藏 3
点赞数 2
分类专栏： MySQL 文章标签： MySQL实用函数 MySQL实用存储过程 两个日期之间的所有日期 两个日期时间的所有小时 MySQL两个日期之间所有日期
于 2020-11-19 19:25:39 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_39706570/article/details/109816862
版权

目录

目标

功能列表

一键生成SQL

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

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

根据表名称查询所有字段的备注

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

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

随机返回子字符串

随机返回范围日期

时间戳+随机数

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

生成Mybatis插入格式

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

生成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','<!--',column_comment,'-->')),
'\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,
<!--支付类型，0微信支付（默认）1储值卡支付-->
paytype paytype,
<!--交易状态-1失败，0支付中，1支付成功-->
state state,
dt dt,
<!--支付通道（0：原生微信，1：小智微信，2：先锋代扣）-->
paysource paysource,
<!--附加数据-->
attach attach,
<!--第三方商户订单号-->
srefno srefno,
<!--商户订单号-->
outtradeno outtradeno,
<!--业务状态（表示支付对应的业务是否已发生业务中心处理完毕） 0 未处理 1已处理-->
businessType businessType
from 
b_pay_steplog

根据表名称查询所有字段的备注

功能说明：输入表的名称，获取相关表字段的备注。

CREATE DEFINER=`账号`@`localhost` PROCEDURE `select_t_comment`(var_table_name TEXT)
BEGIN
    SELECT COLUMN_NAME,COLUMN_TYPE,COLUMN_COMMENT,COLUMN_KEY
    FROM information_schema.COLUMNS 
    WHERE  table_schema = DATABASE() AND table_name =var_table_name
    ORDER BY COLUMN_KEY DESC,COLUMN_NAME;
END

调用&返回结果：

CALL `select_t_comment`('soil_land_agriculture')

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

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

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)

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

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

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

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('性别');

随机返回子字符串

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

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','<!--',column_comment,'-->')
),
'\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,
<!--支付类型，0微信支付（默认）1储值卡支付-->
paytype,
<!--交易状态-1失败，0支付中，1支付成功-->
state,
dt,
<!--支付通道（0：原生微信，1：小智微信，2：先锋代扣）-->
paysource,
<!--附加数据-->
attach,
<!--第三方商户订单号-->
srefno,
<!--商户订单号-->
outtradeno,
<!--业务状态（表示支付对应的业务是否已发生业务中心处理完毕） 0 未处理 1已处理-->
businessType
)values( 
#{loguid},#{entvesion},#{entcode},#{extappid},#{buid},#{storeuid},#{paytype},#{state},#{dt},#{paysource},#{attach},#{srefno},#{outtradeno},#{businessType}
);

生成Mybatis修改格式

功能说明：输入表名和库名，生成Mybatis修改SQL。

SELECT CONCAT('UPDATE \n',table_name,'\nSET ', column_list, '\nWHERE condition;') AS update_statement
FROM (
  SELECT GROUP_CONCAT(CONCAT( '\n' ,column_name, ' = ', '#{',`create_bean`(column_name),'}') SEPARATOR ', ') 
  AS column_list,table_name
  FROM information_schema.columns 
  WHERE table_schema = DATABASE() AND table_name = '表名称'
) AS COLUMNS;

调用&返回结果：

UPDATE 
traffic_source_site_his
SET 
id = #{id}, 
site_code = #{siteCode}, 
monitor_time = #{monitorTime}, 
create_time = #{createTime}, 
update_time = #{updateTime}, 
only_key = #{onlyKey}, 
a25020 = #{a25020}, 
a25506 = #{a25506}, 
a25503 = #{a25503}, 
a24517 = #{a24517}, 
a24516 = #{a24516}
WHERE condition;

生成Mybatis修改格式（带判断标签）

功能说明：输入表名和库名，生成Mybatis修改SQL。

SELECT CONCAT('UPDATE \n',table_name,'\n<set> ', column_list, '\n</set>\nWHERE condition;') AS update_statement
FROM (
  SELECT GROUP_CONCAT(
  "\n	<if test='",`create_bean`(column_name)," != null "," and ",`create_bean`(column_name)," !=\"\"'>",
  CONCAT( '\n	' ,column_name, ' = ', '#{',`create_bean`(column_name),'},'  ) ,
  "\n	</if>" SEPARATOR ''
  ) 
  AS column_list,table_name
  FROM information_schema.columns 
  WHERE table_schema = DATABASE() AND table_name = '表名称'
) AS COLUMNS;

调用&返回结果：

UPDATE 
traffic_source_site_his
<set> 
	<if test='id != null  and id !=""'>
	id = #{id},
	</if>
	<if test='siteCode != null  and siteCode !=""'>
	site_code = #{siteCode},
	</if>
	<if test='monitorTime != null  and monitorTime !=""'>
	monitor_time = #{monitorTime},
	</if>
	<if test='createTime != null  and createTime !=""'>
	create_time = #{createTime},
	</if>
</set>
WHERE condition;

根据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

增加字段&给表和字段加注释

功能说明：给表和字段加注释，给表增加字段。

/*给列加注释*/
ALTER TABLE 表名称 MODIFY 表字段 VARCHAR(255) NULL COMMENT '字段注释' ;
/*给表加注释*/
ALTER TABLE 表名称 COMMENT='表注释'; 
/*给表增加一列并给它加上注释*/
ALTER TABLE 表名称 ADD COLUMN 表字段 VARCHAR(255) DEFAULT NULL COMMENT '字段注释';
ALTER TABLE 表名称 ADD COLUMN 表字段 VARCHAR(255) DEFAULT '' NOT NULL COMMENT '字段注释';

枚举两个日期之间的天或小时

功能说明：给定开始时间和结束时间，枚举这两个日期之间的所有天或小时。

DELIMITER $$

USE `gll`$$

DROP PROCEDURE IF EXISTS `date_list`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `date_list`(start_time VARCHAR(10),end_time VARCHAR(10),date_type VARCHAR(1))
BEGIN
DECLARE var_i INT(10);
DECLARE var_date_num INT(10);
DECLARE var_date TEXT;
SET var_i=0;
SET var_date="";

IF 
date_type='d'
THEN
SET var_date_num= DATEDIFF(end_time, start_time) ;
ELSE
SET var_date_num= (DATEDIFF(end_time, start_time)+1) *24-1 ;
END IF;
	
WHILE var_i<=var_date_num  DO 
IF 
date_type='d'
THEN
SET var_date=CONCAT(
	var_date,
	DATE_ADD(start_time,INTERVAL var_i DAY),
	'\n'
	);
ELSE
SET var_date=CONCAT(
	var_date,
	DATE_ADD(start_time,INTERVAL var_i HOUR),
	'\n'
	);
END IF;
SET var_i=var_i+1;
END WHILE;
	
SELECT var_date;
	END$$

DELIMITER ;

枚举小时列表

CALL date_list('2022-08-10','2022-08-10','');

结果

2022-08-10 00:00:00
2022-08-10 01:00:00
2022-08-10 02:00:00
2022-08-10 03:00:00
2022-08-10 04:00:00
2022-08-10 05:00:00
2022-08-10 06:00:00
2022-08-10 07:00:00
2022-08-10 08:00:00
2022-08-10 09:00:00
2022-08-10 10:00:00
2022-08-10 11:00:00
2022-08-10 12:00:00
2022-08-10 13:00:00
2022-08-10 14:00:00
2022-08-10 15:00:00
2022-08-10 16:00:00
2022-08-10 17:00:00
2022-08-10 18:00:00
2022-08-10 19:00:00
2022-08-10 20:00:00
2022-08-10 21:00:00
2022-08-10 22:00:00
2022-08-10 23:00:00

枚举天列表

CALL date_list('2022-08-01','2022-08-10','d');

结果

2022-08-01
2022-08-02
2022-08-03
2022-08-04
2022-08-05
2022-08-06
2022-08-07
2022-08-08
2022-08-09
2022-08-10

解锁被锁的表

SELECT * FROM information_schema.`PROCESSLIST` 
#持续时间（选填）
WHERE `time` > 1000 
#sql执行的账号（选填）
AND `user` = 'root' 
#数据库名称（选填）
AND db='epdp_lyg' 
#表名称（选填）
AND info LIKE '%sop_pam_water_hour%'
#根据时间排序
ORDER BY `time` DESC;

#终止目标sql的进程
KILL id;

批量产生分钟数据

/*创建表*/
CREATE TABLE `minute_list` (
  `min_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`min_time`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

/*创建存储过程*/
DELIMITER $$

USE `数据库名称`$$

DROP PROCEDURE IF EXISTS `insert_min_list`$$

CREATE DEFINER=`登录账号`@`localhost` PROCEDURE `insert_min_list`(start_time VARCHAR(10),end_time VARCHAR(10),date_type VARCHAR(10))
BEGIN
DECLARE var_i INT(10) DEFAULT 0;
DECLARE var_min_time VARCHAR(20) DEFAULT "";
DECLARE var_date_num INT(10);
 

SET var_date_num= TIMESTAMPDIFF(MINUTE,start_time,ADDDATE(end_time,INTERVAL 1 DAY)) ;

WHILE var_i<var_date_num  DO 

SELECT min_time INTO var_min_time FROM minute_list WHERE min_time=ADDDATE(start_time,INTERVAL var_i MINUTE);
IF var_min_time =""
THEN 
INSERT INTO minute_list (min_time)VALUES(ADDDATE(start_time,INTERVAL var_i MINUTE));
END IF;

SET var_min_time ="";
SET var_i=var_i+1;
END WHILE;
	END$$

DELIMITER ;

/*调用（录入2023-02-20这一整天的1440个分钟周期）*/
CALL `insert_min_list`("2023-02-20","2023-02-20",NULL);

结果

批量产生小时数据

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `数据库名称`.`insert_hour_list`(start_time VARCHAR(10),end_time VARCHAR(10))
BEGIN
DECLARE var_i INT(10) DEFAULT 0;
DECLARE var_interval INT(10) DEFAULT 0;
DECLARE var_hour_time VARCHAR(20) DEFAULT "";
DECLARE var_date_num INT(10);
 

SET var_date_num= TIMESTAMPDIFF(HOUR,start_time,ADDDATE(end_time,INTERVAL 1 DAY));

WHILE var_i<var_date_num  DO 

SELECT hour_time INTO var_hour_time FROM hour_list WHERE hour_time=ADDDATE(start_time,INTERVAL var_interval HOUR);
IF var_hour_time =""
THEN 
INSERT INTO hour_list (hour_time)VALUES(ADDDATE(start_time,INTERVAL var_interval HOUR));
END IF;

SET var_hour_time ="";
SET var_i=var_i+1;
SET var_interval=var_interval+1;
END WHILE;
	END$$

DELIMITER ;

/*向hour_list表插入2023-02-28这一天24小时的数据*/
CALL insert_hour_list("2023-02-28","2023-02-28");

我的身前一尺是我的世界
关注
  • 2
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
博客
FastDFS安装与测试
01-06 1181
在Linux服务器上搭建单机版FastDFS系统。（考虑到Linux服务器访问GitHub受限，这里我将依赖包下载到本地，再将本地的依赖包上传到Linux服务器上。所以我的操作与Wiki上的操作略有不同，但本质一样）。在Linux服务器上，使用命令行实现文件上传、下载、删除等功能。熟悉FastDFS系统架构。
博客
Python字典类型
11-28 1190
掌握字典类型的使用方法，包括：创建、循环、常用方法等操作。
博客
Python集合类型
11-26 1035
掌握set和frozenset两种集合的使用方法，包括：创建、交集、并集、差集等操作。
博客
Python序列类型
11-24 868
掌握Python序列类型（list、tuple、range）的常用方法（创建、循环、查询、删除、切片等）。
博客
Python字符串类型
11-17 252
掌握字符串类型的使用方法。本文参考官方文档列举了各种字符串方法的使用方法，如有错误烦请指正。
博客
Python数字类型
10-22 303
掌握Python两种数据类型的使用方法。数字类型有三种，分别是：整数（int）浮点数（float）复数（complex）另外，布尔值类型（bool）是整数类型的子类型。
博客
用Netty搭建文件上传系统
05-23 1647
客户端向服务端发送一个文件（可以是视频、音频、文本、表格等格式的文件），服务端接收文件并保存到指定的目录下。服务端保存好文件之后向客户端发送回应：xxx文件收到了。
博客
适配器模式知多少
05-15 404
熟悉适配器设计模式，了解适配器设计模式的使用场景、具体实现。
博客
装饰者设计模式知多少
05-13 519
熟悉装饰者设计模式，了解装饰者设计模式的使用场景、具体实现。
博客
策略设计模式知多少
04-26 739
熟悉策略设计模式，了解策略设计模式的使用场景、具体实现。
博客
观察者设计模式知多少
04-26 811
熟悉观察者设计模式，了解观察者设计模式的使用场景、具体实现（包括：推设计模式、拉设计模式、被动观察者设计模式）。
博客
责任链设计模式知多少
04-25 871
熟悉责任链设计模式，了解责任链设计模式的使用场景、具体实现，单链责任链设计模式和双链责任链设计模式的区别。
博客
用Netty做一个简单的聊天室程序
04-16 1073
一个服务端支持多个客户端同时连接，服务端关注客户端的在线，离线情况，客户端关注其他客户端的离线情况、在线情况，发送的消息。
博客
BIO和NIO的区别与实现
04-14 1009
了解BIO和NIO的区别和应用场景。分析BIO和NIO的线程模型，利用Socket实现BIO和NIO的核心功能。
博客
JAVA反射机制知多少
03-31 1033
了解反射机制的概念，通过反射的方式动态地获取类的名称、成员变量、构造方法、成员变量，注解信息。
博客
Netty黏包半包解决方案
03-24 1536
了解黏包半包发生的原因，了解各个解决方案适用的场景。
博客
Netty之ByteBuf应用详解
03-16 2428
掌握ByteBuf的常用方法。了解池化的ByteBuf和非池化的ByteBuf的区别。了解直接内存的ByteBuf和堆内存的ByteBuf的区别。掌握对ByteBuf的内存释放方法。
博客
Netty之ChannelHandler初解
03-07 856
掌握ChannelHandler基本使用方法。熟悉入栈ChannelHandler和出栈ChannelHandler的执行顺序。分析ChannelHandlerContext和NioSocketChannel写入数据时有什么不同。了解Pipeline添加多个ChannelHandler有什么意义。
博客
Netty之io.netty.util.concurrent.Promise与io.netty.util.concurrent.Future初解
03-01 1354
了解io.netty.util.concurrent.Promise与io.netty.util.concurrent.Future的基本使用方法。了解java.util.concurrent.Future、io.netty.util.concurrent.Promise，io.netty.util.concurrent.Future之间的关系。
博客
Netty之ChannelFuture详解
03-01 2649
了解Netty如何处理客户端与服务器之间的连接与关闭问题。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值