MySql 使用总结

1、[Warning] IP address 'xxxx' could not be resolved: Name or service not known错误解决

# 进入/etc 找到mysql的配置文件my.cnf（linux环境下）或者my.ini(windows环境下)进行编辑加入如下一行即可
# mysql默认会反向解析DNS，对于访问者Mysql不会判断是hosts还是ip都会进行dns反向解析，频繁地查询数据库和权限检查，这大大增加了数据库的压力，导致数据库连接缓慢，严重的时候甚至死机，出现“连接数据库时出错”等字样。
# 不再进行反解析（ip不反解成域名），这样可以加快数据库的反应时间
[mysqld]
skip-name-resolve

2、mysqlbinlog liblog转SQL

mysqlbinlog --no-defaults /var/lib/mysql/binlog.000254>/var/lib/mysql/000254.sql

3、日志恢复数据

mysqlbinlog --no-defaults /var/lib/mysql/binlog.000254 | mysql -uroot -p123456

3、1行1列字段转1列多行

-- 测试示例
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1),',',-1) AS num 
FROM mysql.help_topic 
WHERE help_topic_id < LENGTH('7654,7698,7782,7788')-LENGTH(REPLACE('7654,7698,7782,7788',',',''))+1

-- 生产环境示例
select GROUP_CONCAT(displayName SEPARATOR '_') 目录
from fine_authority_object
where id in
(
	SELECT DISTINCT id,SUBSTRING_INDEX(SUBSTRING_INDEX(fullPath,'-_-',help_topic_id+1),'-_-',-1) AS num 
	FROM fine_authority_object
	left join mysql.help_topic on 1=1
 	WHERE id='738cc283-fed6-4f50-aa6e-f37967732da4'
	and help_topic_id < LENGTH(fullPath)-LENGTH(REPLACE(fullPath,'-_-',''))+1
)