MySQL SP 模板，一键套用

drop PROCEDURE sp_name;
DELIMITER $$
CREATE PROCEDURE sp_name(IN input_col1 VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,IN input_col2 datetime)
BEGIN
    SELECT * FROM table WHERE col1= 'XXX';
    
    ##搞个联合查询更新
    create table tmp_staging_table as 
		select col1,col2,a.create_time
		from table1 a 
		left join table2 b on a.id=b.id
		left join table3 e on e.id = b.id
		left join table4 c on b.id = c.id
		left join new_db.table5 d on c.code = d.no 
		and e.name=d.no 
		and case when e.name='XXX' then 'AA' else e.name end = d.`wind farm`
		where a.num is not null and b.deleted = 0
		AND b.id is not null 
		and e.name = input_col1 and a.create_time>= input_col2;

		##update in the result table
	update table1 r
	inner join tmp_staging_table s
		 on r.id = s.id
		 and r.code= s.code
	set r.col1=s.col1,r.col2=s.col2;
    
END $$
DELIMITER ;


##
call sp_name('ur-input','2024-06-18 16:00:00')

新建一个查询然后代码贴进去修改即可，不要在函数的地方建，正确的语法总报错，有啥错，好得很！建好一样会在函数里出现。

之所以会设置输入参数的编码格式是因为我在调用中传参，中文的，说我编码格式不一致，出不来，所以varchar给设了一把。至于日期，date/datetime都不要设，设了才报错。