MySql通过存储过程新增表字段、普通索引

1. 新增表字段（已存在该字段会先删除）

DELIMITER $$

DROP PROCEDURE IF EXISTS `AddColumn`$$

CREATE DEFINER=`root`@`%` PROCEDURE `AddColumn`(
  IN tableName TINYTEXT,
  IN fieldName TINYTEXT,
  IN fieldDef TEXT
)
BEGIN
  IF EXISTS
  (SELECT
    *
  FROM
    information_schema.COLUMNS
  WHERE column_name = fieldName
    AND table_name = tableName
    AND table_schema = DATABASE())
  THEN SET @ddd = CONCAT(
    'ALTER TABLE ',
    DATABASE(),
    '.',
    tableName,
    ' DROP COLUMN ',
    fieldName
  );
  PREPARE stmt FROM @ddd;
  EXECUTE stmt;
  END IF;
  SET @dda = CONCAT(
    'ALTER TABLE ',
    DATABASE(),
    '.',
    tableName,
    ' ADD COLUMN ',
    fieldName,
    ' ',
    fieldDef
  );
  PREPARE stmt FROM @dda;
  EXECUTE stmt;
END$$

DELIMITER ;

 使用：

CALL AddColumn('shop_address','is_del','TINYINT(1) DEFAULT 0 NULL COMMENT "删除标记，1删除；0未删除默认"');

CALL AddColumn('t_user_qnr_topic_option','is_enable','TINYINT(1) DEFAULT 0 NULL COMMENT "启用，1-启用，0-禁用"');
CALL AddColumn('t_user_qnr_topic_option','create_time','timestamp DEFAULT CURRENT_TIMESTAMP COMMENT "创建时间"');
CALL AddColumn('t_user_qnr_topic_option','update_time','timestamp DEFAULT CURRENT_TIMESTAMP COMMENT "更新时间"');
CALL AddColumn('t_user_qnr_topic_option','creator','varchar(50) COMMENT "创建人"');
CALL AddColumn('t_user_qnr_topic_option','modifier','varchar(50) COMMENT "修改人"');

1. 新增普通索引（已存在该索引名称会忽略）

DELIMITER $$

DROP PROCEDURE IF EXISTS `AddIndex`$$

CREATE DEFINER=`root`@`%` PROCEDURE `AddIndex`(
  IN p_tablename VARCHAR (200),
  IN p_indexname VARCHAR (200),
  IN p_index VARCHAR (200)
)
BEGIN
  DECLARE str VARCHAR (250);
  SET @str = CONCAT(
    ' ALTER TABLE ',
    p_tablename,
    ' ADD INDEX ',
    p_indexname,
    ' (',
    p_index,
    ') '
  );
  SELECT
    COUNT(*) INTO @cnt
  FROM
    information_schema.statistics
  WHERE TABLE_SCHEMA = DATABASE()
    AND table_name = p_tablename
    AND index_name = p_indexname;
  IF @cnt <= 0
  THEN PREPARE stmt FROM @str;
  EXECUTE stmt;
  END IF;
END$$

DELIMITER ;

使用：

CALL AddIndex('shop_address','tenant_id','tenant_id');