mysql 批量改字段

1.通过存储过程改

DROP PROCEDURE
IF
	EXISTS addColumn;

DELIMITER $$
CREATE PROCEDURE addColumn () BEGIN-- 定义表名变量
	DECLARE
		s_tablename VARCHAR ( 100 );
/*显示表的数据库中的所有表
SELECT table_name FROM information_schema.tables WHERE table_schema='databasename' Order by table_name ;
*/#显示所有
	DECLARE
		cur_table_structure CURSOR FOR SELECT
		table_name 
	FROM
		INFORMATION_SCHEMA.TABLES 
	WHERE
		table_schema = 'pdm' 
		AND table_name NOT IN (
		SELECT
			t.table_name 
		FROM
			( SELECT table_name, column_name FROM information_schema.COLUMNS WHERE table_name IN ( SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'pmd' and table_name != 'tmp_doc_info' and table_name like "t%") ) t 
		WHERE
			t.column_name = 'object_name' 
		);
	DECLARE
		CONTINUE HANDLER FOR SQLSTATE '02000' 
		SET s_tablename = NULL;
	OPEN cur_table_structure;
	FETCH cur_table_structure INTO s_tablename;
	WHILE
			( s_tablename IS NOT NULL ) DO
			
			SET @MyQuery = CONCAT(
      'ALTER table ',
      s_tablename,
      " ADD (
   `creator` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '创建者',
	 `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
   `updater` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '更新者',
   `update_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
   `update_ip` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新ip',
  `version` int(16) NOT NULL DEFAULT 0 COMMENT '乐观锁'
 );"
    ) ;
			
		PREPARE MSQL 
		FROM
			@MyQuery;
		EXECUTE MSQL;#USING @c;
		FETCH cur_table_structure INTO s_tablename;
		
	END WHILE;
	CLOSE cur_table_structure;
	
END;
$$ #执行存储过程
CALL addColumn ();

2、通过事务改

begin;                                                  
alter table tools  add creator varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '创建者';
alter table tools  add create_time datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间';
alter table tools  add updater varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '更新者';
alter table tools  add update_time datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间';
alter table tools  add update_ip varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新ip';
alter table tools  add version int(16) NOT NULL DEFAULT 0 COMMENT '乐观锁';
commit;

begin;                                                
alter table tree_link add creator varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '创建者';
alter table tree_link add create_time datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间';
alter table tree_link add updater varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '更新者';
alter table tree_link add update_time datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间';
alter table tree_link add update_ip varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新ip';
alter table tree_link add version int(16) NOT NULL DEFAULT 0 COMMENT '乐观锁';
commit;


begin;                                                 
alter table tree_root  add creator varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '创建者';
alter table tree_root  add create_time datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间';
alter table tree_root  add updater varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '更新者';
alter table tree_root  add update_time datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间';
alter table tree_root  add update_ip varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新ip';
alter table tree_root  add version int(16) NOT NULL DEFAULT 0 COMMENT '乐观锁';
commit;


begin;                                                  
alter table type_definition add creator varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '创建者';
alter table type_definition add create_time datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间';
alter table type_definition add updater varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '更新者';
alter table type_definition add update_time datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间';
alter table type_definition add update_ip varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新ip';
alter table type_definition add version int(16) NOT NULL DEFAULT 0 COMMENT '乐观锁';
commit;


begin;                                                  
alter table type_definition_master  add creator varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '创建者';
alter table type_definition_master  add create_time datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间';
alter table type_definition_master  add updater varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '更新者';
alter table type_definition_master  add update_time datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间';
alter table type_definition_master  add update_ip varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新ip';
alter table type_definition_master  add version int(16) NOT NULL DEFAULT 0 COMMENT '乐观锁';
commit;


begin;                                                 
alter table type_prop_field_link  add creator varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '创建者';
alter table type_prop_field_link  add create_time datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间';
alter table type_prop_field_link  add updater varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '更新者';
alter table type_prop_field_link  add update_time datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间';
alter table type_prop_field_link  add update_ip varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新ip';
alter table type_prop_field_link  add version int(16) NOT NULL DEFAULT 0 COMMENT '乐观锁';
commit;


begin;                                                 
alter table type_property_link  add creator varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '创建者';
alter table type_property_link  add create_time datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间';
alter table type_property_link  add updater varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '更新者';
alter table type_property_link  add update_time datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间';
alter table type_property_link  add update_ip varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新ip';
alter table type_property_link  add version int(16) NOT NULL DEFAULT 0 COMMENT '乐观锁';
commit;


begin;                                                  
alter table user_doc_link add creator varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '创建者';
alter table user_doc_link add create_time datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间';
alter table user_doc_link add updater varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '更新者';
alter table user_doc_link add update_time datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间';
alter table user_doc_link add update_ip varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新ip';
alter table user_doc_link add version int(16) NOT NULL DEFAULT 0 COMMENT '乐观锁';
commit;


begin;                                                 
alter table value_type_config add creator varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '创建者';                     
alter table value_type_config add create_time datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间';                                     
alter table value_type_config add updater varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '更新者';                     
alter table value_type_config add update_time datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间';      
alter table value_type_config add update_ip varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新ip';                
alter table value_type_config add version int(16) NOT NULL DEFAULT 0 COMMENT '乐观锁';                                                                         
commit;