mysql脚本集

16 用一个表去更新另外一个表
数据直接复制过来，很容易

insert into acc_aa_customer(as_id,aa_id,cust_type,credit_code,province,city,area,address)
select 26889,aa_id,cust_type,credit_code,province,city,area,address
from acc_aa_customer 
where as_id=26386;

但是如果通过一个外表复制，可以采用

update acc_aa_customer aac
join acc_assisting_accounting aaa on aac.as_id=aaa.as_id and aac.aa_id=aaa.id 
set aac.code=aaa.code,aac.name=aaa.name
where aaa.as_id=7

15 记录去重
下面的语句虽然可以，但是看看执行实践就知道，这种方案是不可取的。

select t1.*
FROM acc_assisting_accounting t1
WHERE t1.as_id=26386 and t1.aa_type=10001
and EXISTS (
    SELECT *
    FROM acc_assisting_accounting t2
    WHERE t2.as_id=26386 and t2.aa_type=10001 and t1.code = t2.code 
    AND t1.id < t2.id
)

改用临时表方案,同样比较慢


借助excel,先筛选出重复项


然后删除重复项，excel秒删,接着再数据库中做相反操作即可。

14 检查全表扫描

select t.*
from (
SELECT
 digest_text AS sql_text,
 count_star AS exec_count, IF (
 sum_no_good_index_used > 0
 OR sum_no_index_used > 0,
 '*',
 ''
) AS full_scan,
 sum_created_tmp_tables AS tmp_tables,
 sum_created_tmp_disk_tables AS tmp_disk_tables,
 sum_sort_rows AS rows_sorted
FROM
 events_statements_summary_by_digest
WHERE
 schema_name = 'bskj'
AND digest_text LIKE 'select%'
and DATE_FORMAT(first_seen,'%Y-%m-%d')>='2023-01-01'
ORDER BY
 count_star DESC
LIMIT 100 ) t
where t.full_scan='*'

13 定时备份
参考了MySQL数据库备份脚本(mysqldump)

#!/bin/bash
#删除15天以前备份

source /etc/profile           #加载系统环境变量
source ~/.bash_profile    #加载用户环境变量
set -o nounset             #引用未初始化变量时退出
#set -o errexit             #执行shell命令遇到错误时退出

user="bak"
password="bak用户的密码"
host="localhost"
port="3306"
#需备份的数据库，数组
db=("test")
#备份时加锁方式，
#MyISAM为锁表--lock-all-tables，
#InnoDB为锁行--single-transaction
lock="--single-transaction"
mysql_path="/appdata/mysql"
backup_path="/appdata/nfsv/mysql/full/test"
date=$(date +%Y-%m-%d_%H-%M-%S)
day=15
backup_log="${backup_path}/backup.log"

#建立备份目录
if [ ! -e $backup_path ];then
    mkdir -p $backup_path
fi

#删除以前备份
find $backup_path -type f -mtime +$day -exec rm -rf {} \; > /dev/null 2>&1

echo "开始备份数据库：${db[*]}"

#备份并压缩
backup_sql(){
    dbname=$1
    backup_name="${dbname}_${date}.sql"
    #-R备份存储过程，函数，触发器
    mysqldump -h $host -P $port -u $user -p$password $lock --default-character-set=utf8mb4 --flush-logs -R $dbname > $backup_path/$backup_name    
    if [[ $? == 0 ]];then
        cd $backup_path
        tar zcpvf $backup_name.tar.gz $backup_name
        size=$(du $backup_name.tar.gz -sh | awk '{print $1}')
        rm -rf $backup_name
        echo "$date 备份 $dbname($size) 成功 "
    else
        cd $backup_path
        rm -rf $backup_name
        echo "$date 备份 $dbname 失败 "
    fi
}

#循环备份
length=${#db[@]}
for (( i = 0; i < $length; i++ )); do
        backup_sql ${db[$i]} >> $backup_log 2>&1
done

echo "备份结束,结果查看 $backup_log"
du $backup_path/*$date* -sh | awk '{print "文件:" $2 ",大小:" $1}'

设置定时任务

vim /etc/crontab

0 0 * * * root /usr/bin/sh /tools/scripts/bak_test.sh &

# 让定时任务生效
service crond restart

给bak用户的权限selct Reload Process Lock Tables Show View
下面的错误说的是test.week_stats这个视图有问题

mysqldump: Couldn't execute 'SHOW FIELDS FROM `week_stats`': View 'test.week_stats' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)
2022-11-29_13-27-35 备份 acc 失败 

12 备份与还原
若有备份的sql，执行source xxdb.sql，很快，但是有个问题，如果xx.sql中有指定的use xxdb，就麻烦了，这个时候，它还是会还原到xxdb中，这个时候如何处理呢？如果文件小，直接改xxdb.sql中的文件，如果文件很大怎么办？

mysqldump -uroot -p eayc > eayc0803.sql单个指定备份的数据，就不会出现create database xxx；use xxx;
如果使用了mysqldump -uroot -p --all-databases或者mysqldump -uroot -p --databases db1 db2,那么就只能用指定的数据库还原了

11 拼接字符串
CONCAT_WS中第一个参数是分割符

update bazi_info set full_bazi = CONCAT_WS(' ',year,month,day,hour);

10 批量新增字段

alter table bazi_info 
add `yg`  char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '年干' ,
add `yz`  char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '年支' ,
add `mg`  char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '月干' ,
add `mz`  char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '月支' ,
add `dz`  char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '日' ,
add `hg`  char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '时干' ,
add `hz`  char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '时支' ,
add `ctg`  tinyint(4) NULL DEFAULT 0 COMMENT '财透干' ,
add `gtg`  tinyint(4) NULL DEFAULT 0 COMMENT 'gtg' ,
add `stg`  tinyint(4) NULL DEFAULT 0 COMMENT '伤透干' ,
add `tghhybyp`  tinyint(4) NULL DEFAULT 0 COMMENT '天干合化印比一片' ,
add `tgybyp`  tinyint(4) NULL DEFAULT 0 COMMENT '天干印比一片' ,
add `ymghh`  char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '年月干合化' ,
add `mdghh`  char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '月日干合化' ,
add `dhghh`  char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '日时干合化' ,
add `qiangruo`  varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '强弱' ;

9 json类型
mysql5.7就已经支持了json类型，mariadb也支持了，我还是相对迟钝了，现在才知道。之前一直困惑一些规则，如果用数据来配置，即时搞一些配置表，还是很麻烦。于是今天尝试使用json类型来解决这个问题。
比如下面是甲生亥运的金不换和调候用神，这样表达很清晰了。在程序中转换为对象不就可以了。命理虽然有些规则，但是我曾经使用drools来做，特别复杂，那条路很难走下去.

{
	"jbhx": {
		"sn": "顺",
		"wx": [],
		"dz": ["寅", "卯"]
	},
	"jbhj": {
		"dz": ["午", "未"]
	},
	"thx": {
		"tg": ["丁"],
		"dz": ["壬", "癸"]
	}
}

添加json类型字段

alter table lxr_jbh_ysth add rule json default null comment '规则';
alter table lxr_jbh_ysth modify rule json default null;

旧版本的navicat并不知道，而dbeaver也需要更新驱动，它会显示为longtext类型

8 字符串截取

update bazi_example set content = left(content,char_length(content)-13)
where content regexp '星尘算命：([甲乙丙丁庚戊己庚辛壬癸子丑寅卯辰巳午未申酉戌亥]{2} ?){4}$'

update bazi_example set category=right(category,char_length(category)-char_length(SUBSTRING_INDEX(category,'文章分类',1)))
where char_length(category)>=15
and category regexp '文章分类'

-- char(10) 换行符 char(13) 回车符
update bazi_example set source=replace(replace(source,char(10),''),char(13),'')

7 多表关联update

update bazi_info bi,bazi_jiazi bj
set bi.hour_xun=bj.xun,bi.hour_shu=bj.id
where bi.hour=bj.jiazi;

update baidu_url bu, 
(
	select bu.id buid
from baidu_url bu
left join baidu_person bp on bu.id = bp.id
where bp.id is null and bu.status='1'
) but
set bu.status = '0' 
where bu.id = but.buid

6 存储过程
技术点：动态sql，需要先创建临时表，先执行sql将数据写入临时表，然后通过游标从临时表中获取数据，将数据写入到一个表中，同时计数存储的数量。
使用uuidreplace(uuid(),'-','')作为id，可能会重复

CREATE PROCEDURE `set_assert`(in i_sql varchar(1000), in i_assert varchar(200),in i_source varchar(200), in i_author varchar(50), out cn int)
begin
  -- i_sql等入参变量最好定义长度，否则会报错
	-- 定义变量
	declare v_bazi char(11);
	-- 创建临时标的sql变量
	declare temp_sql varchar(1000);
		-- 游标结束的标志，必须定义在游标之前
	declare done int default 0;
	-- 定义游标
	declare c_bazi cursor for select * from tmp_bazi;
	-- 捕获系统抛出的 not found 错误，如果捕获到，将 done 设置为 1  相当于try异常
	declare continue handler for not found set done =1;
	-- 创建临时表
	set temp_sql=concat('create temporary table tmp_bazi(',i_sql, ')');
	-- 执行动态sql
	set @sql=temp_sql;
	prepare stmt from @sql;
	execute stmt;
	deallocate prepare stmt;
	-- 统计计算结果
	set cn = 0;
	-- 打开游标
	open c_bazi;
		ddd:loop
		fetch c_bazi into v_bazi;
			if done=1 then 
				leave ddd;
			end if;
			insert into bazi_full_assertion(id,bazi,assertion,source,author)
				values(md5(concat(v_bazi,i_assert)),v_bazi,i_assert,i_source,i_author);
			set cn=cn+1;
		end loop;
	-- 关闭游标
	close c_bazi;
	-- 删除临时表
	DROP TEMPORARY TABLE tmp_bazi;
end

存储过程调用示例,这里的入参和出参，均带上了@,不带上会报错，存储过程中declare的是局部变量，这里定义的@i_sql是用户变量，不用事先申明，随着连接的关闭而消失。MySQL变量的使用

set @i_sql = "select full_bazi from bazi_info where full_bazi like '__ _寅 甲_ __' and full_bazi REGEXP '(?=.*申)(?=.*子)(?=.*辰)(?=.*庚)'";
set @i_assert = '甲生寅月，申子辰备，得庚为贵';
set @i_source = '余氏用神辞渊';
set @i_author = '梁湘润';
set @cn = 0;
call set_assert(@i_sql, @i_assert, @i_source, @i_author,@cn);
select @cn;

5 统计数量及中文字节长度
这里使用的是CHAR_LENGTH，而不是length，因为中文字节长度。

-- 甲生寅月，柱多癸水火神灭，李义府藏笑中之刀
select count(*) from bazi_info where full_bazi like '__ _寅 甲_ __' 
and CHAR_LENGTH(full_bazi)-CHAR_LENGTH(replace(full_bazi,'子',''))+CHAR_LENGTH(full_bazi)-CHAR_LENGTH(replace(full_bazi,'癸',''))>=3;

4 包含其中一个即可
REGEXP '[庚辛]'庚或辛是可选的

-- 甲生寅月，四柱庚辛会局，不免鼓盆之凄，丧明之痛
select count(*) from bazi_info where full_bazi like '__ _寅 甲_ __' and full_bazi REGEXP '[庚辛]'
and full_bazi REGEXP '((?=.*巳)(?=.*酉)(?=.*丑))|((?=.*申)(?=.*酉)(?=.*戌))';

3 正则取反

-- 甲生寅月，申子辰备，无戊则贫
select count(*) from bazi_info where full_bazi like '__ _寅 甲_ __' 
and full_bazi REGEXP '(?=.*申)(?=.*子)(?=.*辰)' 
and full_bazi not regexp '(?=.*戊)';

2 同时包含两个及多个字符

-- 甲生寅月，申子辰备
select count(*) from bazi_info where full_bazi like '__ _寅 甲_ __' and full_bazi REGEXP '(?=.*申)(?=.*子)(?=.*辰)';

1 占位符
使用_作为占位符

-- --------------------- 甲生寅月 -----------------------
select count(*) from bazi_info where full_bazi like '__ _寅 甲_ __';