代码拉取完成,页面将自动刷新
DELIMITER //
CREATE PROCEDURE `TBL`(
IN TBLNAME varchar(16),
IN isDuplicate int,
IN FLDNAME varchar(16))
BEGIN
SET GLOBAL group_concat_max_len=102400;
select
CASE isDuplicate
WHEN 1 THEN FLDNAME
WHEN 0 THEN '0'
END AS isDuplicate,
/*
CASE isDuplicate
WHEN 1 THEN
TRIM(TRAILING concat(',',FLDNAME) FROM TRIM(LEADING concat(FLDNAME,',') FROM replace(tb_flds,concat(',',FLDNAME,','),',')))
WHEN 0 THEN '0'
END AS tb_kv_flds,
CASE isDuplicate
WHEN 1 THEN
TRIM(TRAILING concat(',\'{$',FLDNAME,'}\'') FROM TRIM(LEADING concat('\'{$',FLDNAME,'}\',') FROM replace(tb_values,concat(',\'{$',FLDNAME,'}\','),',')))
WHEN 0 THEN '0'
END as tb_kv_values,
*/
CASE isDuplicate
WHEN 1 THEN insert_kv_str
/* replace( replace( replace( replace( replace( replace(insert_kv_str,concat(',',FLDNAME,','),','),concat(',B.',FLDNAME,','),',') ,concat('(',FLDNAME,','),'(') , concat(',',FLDNAME,')'),')') , concat('B.',FLDNAME,','),''), concat(',B.',FLDNAME),'') */
WHEN 0 THEN '0'
END AS insert_kv_sql
,/*replace(replace(replace(tb_kv_tmp,'(,','('),',)',')'),',,',',') */ tb_kv_tmp as tb_kv_tmptbl,
CASE isDuplicate
WHEN 1 THEN insert_tmp_str
/* replace( replace( replace( replace( replace( replace(insert_str,concat(',\'{$',FLDNAME,'}\','),','),concat(',\'{$',FLDNAME,'}\''),'') ,concat('\'{$',FLDNAME,'}\','),'') , concat('(',FLDNAME,','),'(') , concat(',',FLDNAME,')'),')'), concat(',',FLDNAME,','),',') */
WHEN 0 THEN '0'
END as insert_tmptbl_sql,
tb_flds,tb_types,tb_vars,tb_values,select_str as select_sql,insert_str as insert_sql from
( SELECT tb_flds,tb_types,tb_values,tb_vars
, concat('insert into ',TBLNAME,'(',tb_flds,') select ',tb_tmp_values,' from tmp_',TBLNAME,' B where not exists (select * from ',TBLNAME,' where ',FLDNAME,'=B.',FLDNAME,')') as insert_kv_str
, concat('select ', tb_flds, ' from ', TBLNAME) AS select_str
, concat('insert into ', TBLNAME, '(', tb_flds, ') values(', tb_values, ')') AS insert_str
, concat('insert into tmp_', TBLNAME, '(', tb_flds, ') values(', tb_values, ')') AS insert_tmp_str
, CASE isDuplicate
WHEN 1 THEN
concat('DROP TEMPORARY TABLE IF EXISTS `tmp_',TBLNAME,'`; CREATE TEMPORARY TABLE `tmp_',TBLNAME,'` (',tb_kv_tmp,')')
WHEN 0 THEN '0' END as tb_kv_tmp
FROM (
SELECT GROUP_CONCAT(fld_name) AS tb_flds, GROUP_CONCAT(fld_type) AS tb_types
, GROUP_CONCAT(concat('\'', '{$', fld_name, '}\'')) AS tb_values,
GROUP_CONCAT(concat('$', fld_name)) AS tb_vars,
GROUP_CONCAT(concat('B.',fld_name)) as tb_tmp_values
, GROUP_CONCAT(fld_kv) AS tb_kv_tmp
FROM (
SELECT column_name AS fld_name, column_type AS fld_type,
/*case when column_name=FLDNAME then '' else */
concat(column_name,' ',column_type,' DEFAULT NULL')
/*end */
as fld_kv
FROM information_schema.columns
WHERE (table_schema = 'hms_jsy'
AND table_name = TBLNAME
AND column_key <> 'PRI')
) tmp_tb1
) tmp_tb2
)tmp_tb3;
END //
DELIMITER
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。