1.MySQL Server has gone away
问题出现场景:导入50M的数据库SQL文件
问题分析:
情况1:SQL语句太长,超过了max_allowed_packet的大小;
情况2:连接超时,获取数据连接时采用singleton,多次连接数据库使用的是同一个链接,且某两次操作数据库的间隔超过wait_timeout
解决方案: my.ini中修改
wait_timeout=2880000interactive_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
延伸实现:10分钟一统计
DROP FUNCTIONdate_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 ASSIGNED);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');RETURNout_date;END
注意:
1、my.ini文件编码应为”ANSI“,否则cmd->”mysqld --initialize --console“时会报错:Found option without preceding group in config file
2、mysqld --initialize-insecure --console创建基于密码空白的管理员账户的初始化data目录数据,需要通过--secure-file-priv指定具有文件操作权限的目录,否则会导致无法生成初始化数据,报--secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled.
secure_file_priv 参数是只读参数,不能使用set global命令修改,需直接修改my.ini文件在[mysqld]内加入secure_file_priv =,修改后重启MySQL服务,使用“showglobal variableslike '%secure_file_priv%';”命令查看修改结果。
7、mysql对json数据的使用