=与:=区别
- = 只有在set和update时才是和:=一样，赋值的作用，其它都是等于的作用。鉴于此，用变量实现行号时，必须用:=
- := 不只在set和update时时赋值的作用，在select也是赋值的作用。
序列号：
两种创建变量并赋值的方式
SET @变量名 = 值;
SELECT 值 INTO @变量名;
select (@rowNO := @rowNo+1) AS rowno from blog,(select @rowNO :=0) b
必须要有select @rowNo:=0,不然查询出来的全是null
或者这样
set @rowNo=0;
select (@rowNO := @rowNo+1) AS rowno from blog;
分组：
一个user表，有id,name字段，name有重复，求id最小的不同name的记录表。
复杂的：
select * from user a where a.id=
(select id from user b where a.name=b.name limit 1)
group by：
select * from user where id in (select min(id) from user group by name having count(1)>0)
子查询分组：
select * from (select * from `test` order by `date` desc) `temp` group by category_id order by `date` desc
虽然没有理解，但是测下来是对的
上面三种，测试下来，还是groupby与子查询分组效率差不多，最上面的效率最低，不管是否有索引的情况，都最慢。
同数据库表数据迁移
-- 迁移统一账户数据
update hs_issue_info a ,hs_draw_info b set
a.add_person_p2p_account_draw=1,
a.add_person_p2p_account_draw_archive_id=b.unify_loan_account,
a.add_person_p2p_account_draw_p2p_account_id=b.p2p_account_id,
a.add_person_p2p_account_draw_p2p_account_name=b.p2p_account_name,
a.add_person_p2p_account_draw_p2p_account_tel=b.phone_number
where a.draw_id=b.draw_id and b.unify_loan_account is not null;
存储过程
delimiter $$
drop procedure if exists pro;
create procedure pro()
begin
declare num int default 0;
select count(1) from hs_draw_info where unify_loan_account is not null into num;
select num;
end
$$
delimiter ;
call pro();
delimiter 是分隔符，其实就是告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。
默认情况下，delimiter是分号;，在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。
最后一个$$就是告诉命令行，语句可以执行了。
案例，迁移数据
创建临时表，遍历插入数据
-- 创建临时表
CREATE TEMPORARY TABLE IF NOT EXISTS temp
(
id INT NOT NULL DEFAULT 0,
role_full_name VARCHAR(100) NOT NULL,
right_full_name VARCHAR(100) NOT NULL
)
-- 初始化数据
set @rowNo=0;
insert into temp (id,role_full_name,right_full_name)
select (@rowNO := @rowNo+1) AS id,
a.full_name as role_full_name,
c.full_name as right_full_name
from role a
join role_right b
on a.role_id=b.role_id
join `right` c
on b.right_id=c.right_id
where a.application_id=39
and c.application_id=39;
-- 修改数据-- 修改数据
update temp set role_full_name=replace(role_full_name,'huishi','huishi-pub');
update temp set right_full_name=replace(right_full_name,'huishi','huishi-pub');
update temp set role_full_name=replace(role_full_name,'huishi','huishi-pub');
update temp set right_full_name=replace(right_full_name,'huishi','huishi-pub');
-- 查看信息
select * from temp;
select distinct role_full_name,right_full_name from temp;
select @rowNo;
-- 创建存储过程，循环插入数据
delimiter $$
drop procedure if exists pro $$
create procedure pro()
begin
declare num int default 1;
declare totalNum int default 0;
declare role_id2 int default 0;
declare right_id2 int default 0;
declare role_full_name2 varchar(100) default '';
declare right_full_name2 varchar(100) default '';
set num=1;
while num<=@rowNo do
select role_full_name,right_full_name into role_full_name2,right_full_name2 from temp where id=num;
select role_id into role_id2 from role where full_name=role_full_name2;
select right_id into right_id2 from `right` where full_name=right_full_name2;
select count(1) into totalNum from role_right where role_id=role_id2 and right_id=right_id2;
if totalNum=0 then
INSERT INTO `role_right`(`role_id`, `right_id`, `creator`, `is_active`, `insert_time`, `update_time`) VALUES (role_id2, right_id2, NULL, 1, now(), now());
end if;
set num=num+1;
end while;
end $$
delimiter ;
-- 执行存储过程
call pro();
-- 删除临时表
DROP TABLE IF EXISTS temp;
迁移部分征信数据附件：
delimiter $$
drop procedure if exists pro $$
create procedure pro()
begin
declare num,dataNum int default 0;
declare archiveId,searchArchiveId,creditDataId bigint default 0;
set num=1;
set @rowNo=0;
create table temp as
select (@rowNO := @rowNo+1) AS id, a.*
from hs_credit_order a
join hs_loan_order b
on a.loan_order_id=b.loan_order_id
join hs_workflow_log c
on a.loan_order_id=c.business_no
where b.loan_status=7
and c.workflow_key like 'HS-FKSP%'
and c.current_node_name='通过'
and c.create_time>'2020-02-18 00:00:00'
and c.create_time<'2020-05-19 00:00:00';
while num<=@rowNo do
select dataNum=count(1) from hs_cdc_credit_data a join temp b on a.credit_order_id=b.credit_order_id;
-- 只有不存在对应征信才新增数据
if dataNum=0 then
INSERT INTO `hs_cdc_credit_data`( `loan_order_id`, `credit_order_id`, `credit_category`, `business_category`, `search_name`, `search_archive_id`, `report_time`, `complete_time`, `credit_data_status`, `create_time`, `create_name`, `modify_time`, `modify_name`)
select loan_order_id,credit_order_id,2,2,search_name,search_archive_id,NULL,NULL,1,now(),'admin',now(),'admin' from temp where id=num;
select search_archive_id into searchArchiveId from temp where id=num;
select LAST_INSERT_ID() into creditDataId;
-- 批量新增查询附件
INSERT INTO `hs_cdc_credit_data_file`( `credit_data_id`, `catalog_key`, `catalog_name`, `image_id`, `file_sort`, `is_image`, `is_system`, `file_status`, `create_time`, `create_name`, `modify_time`, `modify_name`)
select creditDataId,NULL,NULL,bb.img_id,bb.detail_sort,true,true,1,now(),'admin',now(),'admin' from hs_archive_attachment aa join
hs_archive_attachment_detail bb
on aa.attachment_id=bb.attachment_id
where aa.catalog_key='rhzxbgtp' and aa.attachment_status=1 and bb.detail_status=1 and aa.archive_id=searchArchiveId;
end if;
set num=num+1;
end while;
drop table temp;
end $$
delimiter ;
-- 执行存储过程
call pro();
注意上面，创建临时表使用了
create table temp as copytable