MySQL使用小记

时间：2019-05-10 18:03:11 收藏：0 阅读：138

1.MySQL Server has gone away

问题出现场景：导入50M的数据库SQL文件

问题分析：

　　情况1：SQL语句太长，超过了max_allowed_packet的大小；

　　情况2：连接超时，获取数据连接时采用singleton，多次连接数据库使用的是同一个链接，且某两次操作数据库的间隔超过wait_timeout

解决方案： my.ini中修改

wait_timeout=2880000
interactive_timeout = 2880000max_allowed_packet = 10M(也可以设置自己需要的大小) 
max_allowed_packet 参数的作用是，用来控制其通信缓冲区的最大长度。

2、Max_connections

set GLOBAL max_connections=2000;

3、MYSQL时间统计函数（30分钟一统计）

CREATE  FUNCTION `date_half_hour_format`(in_date TIMESTAMP) RETURNS TIMESTAMP
BEGIN
	DECLARE out_date TIMESTAMP;
	DECLARE s_date VARCHAR(255);
	DECLARE s_minute VARCHAR(2);
	DECLARE int_minute INT;
	
	SET s_minute = SUBSTRING(in_date, 15, 2);
	SET int_minute = CAST(s_minute AS SIGNED);
	
	IF int_minute <= 29 THEN
	  SET int_minute = 0;
	  SET s_date = CONCAT(LEFT(in_date, 14),‘0‘,int_minute);
	ELSE
	  SET int_minute = 30;
	  SET s_date = CONCAT(LEFT(in_date, 14),int_minute);
	END IF;
	  
	SET out_date = STR_TO_DATE(s_date,‘%Y-%m-%d %H:%i‘);
	
	RETURN out_date;
	END

　　摘自：https://blog.csdn.net/Hendiaome/article/details/72457506

延伸实现：10分钟一统计

DROP FUNCTION date_half_hour_format;
CREATE  FUNCTION `date_half_hour_format`(in_date TIMESTAMP) RETURNS TIMESTAMP
BEGIN
    DECLARE out_date TIMESTAMP;
    DECLARE s_date VARCHAR(255);
    DECLARE s_minute VARCHAR(2);
    DECLARE int_minute INT;
    
    SET s_minute = SUBSTRING(in_date, 15, 2);
    SET int_minute = CAST(s_minute AS SIGNED);
    
    IF int_minute <= 9 THEN
      SET int_minute = 0;
      SET s_date = CONCAT(LEFT(in_date, 14),‘0‘,int_minute);
    ELSEIF int_minute > 9 and int_minute<=19 THEN
      SET int_minute = 10;
      SET s_date = CONCAT(LEFT(in_date, 14),int_minute);
    ELSEIF int_minute > 19 and int_minute<=29 THEN
      SET int_minute = 20;
      SET s_date = CONCAT(LEFT(in_date, 14),int_minute);
    ELSEIF int_minute > 29 and int_minute<=39 THEN
      SET int_minute = 30;
      SET s_date = CONCAT(LEFT(in_date, 14),int_minute);
    ELSEIF int_minute > 39 and int_minute<=49 THEN
      SET int_minute = 40;
      SET s_date = CONCAT(LEFT(in_date, 14),int_minute);
    ELSE 
      SET int_minute = 50;
      SET s_date = CONCAT(LEFT(in_date, 14),int_minute);
    END IF;
      
    SET out_date = STR_TO_DATE(s_date,‘%Y-%m-%d %H:%i‘);
    
    RETURN out_date;
    END

原文：https://www.cnblogs.com/ylhssn/p/10740956.html