MySQL使用存储过程实现判断表中字段或者索引是否存在，如果不存在则创建

最新推荐文章于 2024-07-18 13:37:57 发布

weixin_30474613

最新推荐文章于 2024-07-18 13:37:57 发布

阅读量933

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/xxoome/p/11237657.html

版权

使用存储过程可以保证数据库升级脚本可以重复执行而不会报错。

CREATE TABLE `tb_user` (
  `id` bigint(20) NOT NULL COMMENT '非自增id',
  `account` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `account_pinyin` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'account的拼音，用于account排序。',
  `password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `salt` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '盐值',
  `mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '手机号,不可为null,默认值为'''',可用于登录',
  `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '邮箱,不可为null,默认值为'''',可用于登录',
  `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '用户是否删除。0：未删除；1：删除',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_account` (`account`),
  UNIQUE KEY `unique_mobile` (`mobile`),
  UNIQUE KEY `unique_email` (`email`),
  KEY `index_updated_at` (`updated_at`),
  KEY `index_account_pinyin` (`account_pinyin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1、存储过程判断索引是否存在

-- 判断索引是否存在，不存在则创建索引
DROP PROCEDURE IF EXISTS add_index;  
DELIMITER //
CREATE PROCEDURE add_index() BEGIN 
DECLARE  target_database VARCHAR(100);
DECLARE  target_table_name VARCHAR(100);
DECLARE  target_column_name VARCHAR(100);
DECLARE  target_index_name VARCHAR(100);
set target_table_name = 'tb_user';
set target_column_name = 'account_pinyin';
set target_index_name = 'index_account_pinyin';
SELECT DATABASE() INTO target_database;
IF NOT EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema = target_database AND table_name = target_table_name AND index_name = target_index_name) THEN
    set @statement = CONCAT("ALTER TABLE ", target_table_name, " ADD INDEX ", target_index_name, "(", target_column_name, " ASC )");
    PREPARE STMT FROM @statement;
    EXECUTE STMT;
END IF;
END
//
DELIMITER ;   -- 注意 DELIMITER 与分号之间有空格
CALL add_index();

2、存储过程判断字段是否存在

-- 判断字段是否存在，不存在则增加
DROP PROCEDURE IF EXISTS add_column;
DELIMITER //
CREATE PROCEDURE add_column() BEGIN
DECLARE  target_database VARCHAR(100);
DECLARE  target_table_name VARCHAR(100);
DECLARE  target_column_name VARCHAR(100);
DECLARE  target_index_name VARCHAR(100);
set target_table_name = 'tb_user';
set target_column_name = 'new_column';
SELECT DATABASE() INTO target_database;
IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema = target_database AND table_name = target_table_name AND column_name = target_column_name) THEN
    set @statement = CONCAT("ALTER TABLE ", target_table_name, " ADD COLUMN ", target_column_name, " VARCHAR(45) NULL");
    PREPARE STMT FROM @statement;
    EXECUTE STMT;
END IF;
END
//
DELIMITER ; -- 注意 DELIMITER 与分号之间有空格
CALL add_column();