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;