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 '' 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;

-- 判断索引是否存在，不存在则创建索引

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();

-- 判断字段是否存在，不存在则增加

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();