分享一个批量生成给数据库添加基础字段的脚本

设计数据库或者改造数据库的时候添加基础字段很麻烦，这里分享一个自己写的简单脚本。

1.新建函数（我用的navcat，所以不是直接控制台命令，需要的话自己改也不麻烦）：

BEGIN
 DECLARE target_table_name VARCHAR(100) DEFAULT '';
 DECLARE sql_script text DEFAULT '';
 DECLARE done INT DEFAULT 0;
 DECLARE curl CURSOR FOR
 SELECT table_name
 FROM information_schema.TABLES
 WHERE table_schema = schema_name;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
 OPEN curl;
 REPEAT
 FETCH curl INTO target_table_name;
 IF NOT done
 AND target_table_name <> 'COM_PUNISH'
 AND target_table_name <> 'AUTH_EMPLOYEE'
 AND target_table_name <> 'AUTH_LOG' THEN
 SET @before_filed = (
 SELECT COLUMN_NAME
 FROM information_schema.COLUMNS
 WHERE TABLE_NAME = target_table_name
  AND TABLE_SCHEMA = schema_name
 ORDER BY ORDINAL_POSITION DESC
 LIMIT 9, 1
 );
 SET @SQL ='###\n';
 IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema = schema_name AND table_name = target_table_name AND column_name = 'tenant_id') THEN
 SET @SQL = concat( @SQL,'alter table ',schema_name,'.`',target_table_name , '` add `tenant_id` VARCHAR(255) NULL DEFAULT \'000000\' COMMENT \'租户ID\'; \n');
 End IF;
 IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema = schema_name AND table_name = target_table_name AND column_name = 'create_user') THEN
 SET @SQL = concat( @SQL, 'alter table ',schema_name,'.`',target_table_name , '` add `create_user` bigint(64) NULL DEFAULT NULL COMMENT \'创建人\'; \n');
 End IF;
 IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema = schema_name AND table_name = target_table_name AND column_name = 'create_time') THEN
 SET @SQL = concat( @SQL, 'alter table ',schema_name,'.`',target_table_name, '` add `create_time` datetime(0) NULL DEFAULT NULL COMMENT \'创建时间\'; \n');
 End IF;
 IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema = schema_name AND table_name = target_table_name AND column_name = 'update_user') THEN
 SET @SQL = concat( @SQL, 'alter table ',schema_name,'.`',target_table_name, '` add `update_user` bigint(64) NULL DEFAULT NULL COMMENT \'修改人\'; \n');
 End IF;
 IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema = schema_name AND table_name = target_table_name AND column_name = 'update_time') THEN
 SET @SQL = concat( @SQL, 'alter table ',schema_name,'.`',target_table_name,'` add `update_time` datetime(0) NULL DEFAULT NULL COMMENT \'修改时间\';  \n');
 End IF;
 IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema = schema_name AND table_name = target_table_name AND column_name = 'status') THEN
 SET @SQL = concat( @SQL, 'alter table ',schema_name,'.`',target_table_name ,'` add `status` int(2) NULL DEFAULT NULL COMMENT \'状态\'; \n');
 End IF;
 IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema =schema_name AND table_name = target_table_name AND column_name = 'is_deleted') THEN
 SET @SQL = concat( @SQL, 'alter table ',schema_name,'.`',target_table_name ,'` add `is_deleted` int(2) NULL DEFAULT 0 COMMENT \'是否已删除\';  \n');
 End IF;
--  IF @before_filed IS NOT NULL THEN  ## 此处是有时会出现已有等情况的时候符号不同，但由于上面做的判断，所以一般用不上。
--  SET @SQL = concat(@SQL, ' AFTER `', @before_filed, '`;');
--  ELSE
--  SET @SQL = concat(@SQL, ';');
--  END IF;
--   PREPARE stmt FROM @SQL;##直接运行可能会有问题，所以直接注掉，在最后打出 sql 脚本。
--   EXECUTE stmt;
  SET sql_script = CONCAT(sql_script, @SQL, ' ');
 END IF;
 UNTIL done
 END REPEAT;
 CLOSE curl;
    SELECT sql_script;
END