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 '__ _寅 甲_ __';