MySQL

最新推荐文章于 2024-10-02 12:42:27 发布

程序NM的猿

最新推荐文章于 2024-10-02 12:42:27 发布

阅读量121

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/Vincent_ZP/article/details/125424371

版权

本文详细介绍了MySQL服务端配置，包括开启定时任务、设置密码强度、调整SQL_MODE以解决ONLY_FULL_GROUP_BY问题，以及处理死锁和事务级别问题。此外，还讲解了数据库的乱码配置、备份策略，以及一些实用的SQL查询技巧，如获取当前周数、分组查询第一条记录等。

摘要由CSDN通过智能技术生成

MySQL资料整理

Mysql服务端配置

定时任务配置

-- 查看mysql是否开启了定时任务 off或者0代表没开
SHOW VARIABLES LIKE '%event_scheduler%'
-- 若没开 执行下面这条sql 重启后不生效！
SET GLOBAL event_scheduler = 1;
-- 若想重启后生效，则需要修改mysql配置
-- 在[mysqld]下添加一行 event_scheduler=ON

密码强度

-- 查询密码强度
SHOW VARIABLES LIKE 'validate_password%'; 
-- 设置密码强度LOW|MEDIUM|STRONG
set global validate_password_policy=LOW;

sql_mode

配置文件需以具体docker运行配置为准，常规配置在/etc/mysql/mysql.conf.d/mysqld.cnf

#默认配置
sql_mode =ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#去掉ONLY_FULL_GROUP_BY修改为
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#需重启Mysql服务

死锁问题

-- 在执行结果中可以看到是否有表锁等待或者死锁，如果有死锁发生，可以通过下面的命令来杀掉当前运行的事务
show full PROCESSLIST;
-- 查看当前运行的所有事务：
select * from information_schema.INNODB_TRX;
-- KILL 后面的数字指的是上一步查询结果中的 trx_mysql_thread_id 值。
KILL trx_mysql_thread_id ;

事物级别问题

重复读问题导致的报错error:param autocommit: Autocommit mode. None means use server default. (default: False)

SELECT @@tx_isolation

REPEATABLE-READ 改为 READ-COMMITTED

乱码配置

vi /etc/mysql/conf.d/mysql.cnf
#设置如下内容
[client]
default-character-set=utf8
[mysqld]
character_set_server=utf8

数据库备份

#mysqldump 库名 -u用户名 -p密码 > 文件名
#导出指定数据库表结构和数据
mysqldump device_detection -uroot -pPASSWORD > device_detection.sql

#导出指定表结构和数据
#mysqldump -u用户 -p密码 -B 数据库名 --tables 表名 >　xxx.sql
mysqldump -uroot -pPASSWORD -B device_detection --tables sd_device_info > sd_device_info.sql

以下为docker部署容器内备份拷贝到宿主机

# mysql_container_name 为安装mysql的docker容器名
docker exec -i mysql_container_name bash<<'EOF'
# 判断目录是不是已经存在，如果不存在则创建
if [ ! -d "/home/backups/mysql" ]; then
  mkdir -p /home/backups/mysql
fi
# dbname为数据库的名称，备份到容器内目录
mysqldump dbname -uroot -pPASSWORD > /home/backups/mysql/backups_filename$(date +%Y%m%d).sql
exit
EOF
# 宿主机中判断目录是不是已经存在，如果不存在则创建
if [ ! -d "/home/backups/mysql" ]; then
  mkdir -p /home/backups/mysql
fi
# 将docker中的备份的数据拷贝到宿主机上。
docker cp mysql_container_name:/home/backups/mysql/backups_filename$(date +%Y%m%d).sql /home/backups/mysql/backups_filename$(date +%Y%m%d).sql
#删除容器内的备份文件,否则文件堆积
docker exec -i zeiet-mysql bash<<'EOF'
# 判断文件是否存在，存在则删除
if [ ! -d "/home/backups/mysql/backups_filename$(date +%Y%m%d).sql" ]; then
  rm -f /home/backups/mysql/backups_filename$(date +%Y%m%d).sql
fi
exit
EOF
#若当天tar包已存在先删除
if [ ! -d "/home/backups/mysql/backups_filename$(date +%Y%m%d).sql.tar.gz" ]; then
  rm -f /home/backups/mysql/backups_filename$(date +%Y%m%d).sql.tar.gz
fi
#压缩
tar zczf /home/backups/mysql/backups_filename$(date +%Y%m%d).sql.tar.gz /home/backups/mysql/backups_filename$(date +%Y%m%d).sql
#删除原文件
rm -f /home/backups/mysql/backups_filename$(date +%Y%m%d).sql
#删除超过N天的数据，这里指定了30天
rm -f /home/backups/mysql/backups_filename$(date -d -30day +%Y%m%d).sql.tar.gz

Mysql5.7常用复杂SQL

常用函数

-- 获取当前时间第几周
select weekofyear(curdate());
-- 获取本周周一的日期
select subdate(curdate(),date_format(curdate(),'%w')-1);

分组排序取第一条明细

5.7版本不支持窗口函数，场景：分组排序取第一条明细，无法用max来关联匹配，max(日期)有多条

SELECT
	* 
FROM
	(
SELECT
	hm_unit_code,
	consumption,
	energy_date,
	pe_unit_code,
	STATUS,
	hm_unit_name 
FROM
	biz_ds_hm_unit_day 
WHERE
	id IN (
SELECT
        -- 排序group_concat拼接ID后又截取到第一个，外层用in查询，数据量大情况下效率未知
	SUBSTRING_INDEX( group_concat( id ORDER BY energy_date DESC ), ',', 1 )
FROM
	biz_ds_hm_unit_day 
WHERE
	energy_date BETWEEN '2022-06-01' 
	AND '2022-06-30' 
GROUP BY
	hm_unit_code 
	) 
	) aa 
WHERE
	hm_unit_code = '91330109255704296H'

查询指定日期范围内的每一天日期

依赖mysql系统表help_topic，共计659条数据，日期范围不能超过659天

SELECT
	date_add( date_sub( '2022-07-30', INTERVAL 1 DAY ), INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 1 ) DAY ) days 
FROM
	mysql.help_topic 
WHERE
	help_topic_id < DATEDIFF( '2022-08-04', date_sub( '2022-07-30', INTERVAL 1 DAY ) ) 
ORDER BY
	help_topic_id
-- 查询结果如下
2022-07-30
2022-07-31
2022-08-01
2022-08-02
2022-08-03
2022-08-04

json字段查询更新删除

-- 查询
SELECT * from testforjson where json_extract(info,'$.company') like CONCAT("%",'d',"%");
-- 更新
update feed set content=json_set(content,'$.district','平房区') where id=19;
-- 删除
update feed set content=json_remove(content,'$.district') where son_tag in (4,6);