MySQL存储过程，判空创建索引，字段

1 索引创建

DROP PROCEDURE IF EXISTS add_Index;  
DELIMITER //
CREATE PROCEDURE add_Index (IN p_dbname VARCHAR (200),IN p_tablename VARCHAR (200),IN p_idxname VARCHAR (200),IN p_index VARCHAR (200))  
BEGIN  
DECLARE addstr VARCHAR (250);
SET @addstr = CONCAT(' CREATE INDEX ', p_idxname,' ON ',p_tablename,p_index);    

SELECT COUNT(*) INTO @cnt  
FROM information_schema.statistics  
WHERE TABLE_SCHEMA = p_dbname  
      AND table_name = p_tablename  
      AND index_name = p_idxname;
IF @cnt <=0 THEN  
    PREPARE stmt FROM   @addstr;  
  EXECUTE stmt;  
END IF;   
END;    
//
DELIMITER;

/*   
 * 传递参数并且执行,参数必须包括在''中   
 * */  
CALL add_Index ( 
    'bigdata-web',  
    'dsc_personnel_account',  
    'dsc_personnel_account_account_id_IDX',  
    '(account_id)'  
); 

2 新建字段

-- 存储过程--添加dsc_assets_info字段
DROP PROCEDURE IF EXISTS dsc_assets_info_add_col;
DELIMITER //
create procedure dsc_assets_info_add_col()
BEGIN
declare _count int;
-- department_zzd_id
set _count=(select count(*) from information_schema.COLUMNS
where TABLE_NAME = 'dsc_assets_info' and
table_schema= (select database()) and
column_name='department_zzd_id');
if _count=0 then
ALTER TABLE `bigdata-web`.dsc_assets_info ADD department_zzd_id varchar(255) NULL COMMENT '所属部门浙政钉ID';
end if;
-- app_code
set _count=(select count(*) from information_schema.COLUMNS
where TABLE_NAME = 'dsc_assets_info' and
table_schema= (select database()) and
column_name='app_code');
if _count=0 then
ALTER TABLE `bigdata-web`.dsc_assets_info ADD app_code varchar(255) NULL COMMENT '应用编码';
end if;
-- sub_app_system
set _count=(select count(*) from information_schema.COLUMNS
where TABLE_NAME = 'dsc_assets_info' and
table_schema= (select database()) and
column_name='sub_app_system');
if _count=0 then
ALTER TABLE `bigdata-web`.dsc_assets_info ADD sub_app_system varchar(255) NULL COMMENT '子应用系统';
end if;
-- app_manager
set _count=(select count(*) from information_schema.COLUMNS
where TABLE_NAME = 'dsc_assets_info' and
table_schema= (select database()) and
column_name='app_manager');
if _count=0 then
ALTER TABLE `bigdata-web`.dsc_assets_info ADD app_manager varchar(255) NULL COMMENT '应用管理员';
end if;
-- app_manager_contact
set _count=(select count(*) from information_schema.COLUMNS
where TABLE_NAME = 'dsc_assets_info' and
table_schema= (select database()) and
column_name='app_manager_contact');
if _count=0 then
ALTER TABLE `bigdata-web`.dsc_assets_info ADD app_manager_contact varchar(255) NULL COMMENT '应用联系方式';
end if;
-- resource_manager_zzd_id
set _count=(select count(*) from information_schema.COLUMNS
where TABLE_NAME = 'dsc_assets_info' and
table_schema= (select database()) and
column_name='resource_manager_zzd_id');
if _count=0 then
ALTER TABLE `bigdata-web`.dsc_assets_info ADD resource_manager_zzd_id varchar(255) NULL COMMENT '资源管理员浙政钉ID';
end if;
-- app_manager_zzd_Id
set _count=(select count(*) from information_schema.COLUMNS
where TABLE_NAME = 'dsc_assets_info' and
table_schema= (select database()) and
column_name='app_manager_zzd_Id');
if _count=0 then
ALTER TABLE `bigdata-web`.dsc_assets_info ADD app_manager_zzd_Id varchar(255) NULL COMMENT '应用管理员浙政钉ID';
end if;
end;
//
DELIMITER ;
CALL dsc_assets_info_add_col();
DROP PROCEDURE IF EXISTS dsc_assets_info_add_col;