使用ALTER TABLE向MySQL数据库的表中添加字段
-- 向buildBaseInfo中添加字段 ALTER TABLE table_name ADD COLUMN column_name VARCHAR(100) DEFAULT NULL COMMENT "新加字段" AFTER old_column;
语句内容:
table_name
:表明;column_name
:需要添加的字段名;VARCHAR(100)
:字段类型为varchar,长度100;DEFAULT NULL
:默认值NULL;AFTER old_column
:新增字段添加在old_column字段后面。CREATE DEFINER=`portal`@`%` PROCEDURE `pro_AddColumn`( IN tab VARCHAR(100) ) BEGIN IF NOT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name=tab AND COLUMN_NAME="CREATE_DATE") THEN SET @exe = CONCAT("ALTER TABLE ",tab," ADD CREATE_DATE DATE COMMENT "创建时间""); PREPARE sql1 FROM @exe; EXECUTE sql1; END IF; IF NOT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name=tab AND COLUMN_NAME="CREATE_USER") THEN SET @exe = CONCAT("ALTER TABLE ",tab," ADD CREATE_USER VARCHAR(50) COMMENT "创建人""); PREPARE sql1 FROM @exe; EXECUTE sql1; END IF; IF NOT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name=tab AND COLUMN_NAME="MODIFIED_DATE") THEN SET @exe = CONCAT("ALTER TABLE ",tab," ADD MODIFIED_DATE DATE COMMENT "修改时间""); PREPARE sql1 FROM @exe; EXECUTE sql1; END IF; IF NOT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name=tab AND COLUMN_NAME="MODIFIED_USER") THEN SET @exe = CONCAT("ALTER TABLE ",tab," ADD MODIFIED_USER VARCHAR(50) COMMENT "修改人""); PREPARE sql1 FROM @exe; EXECUTE sql1; END IF; IF NOT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name=tab AND COLUMN_NAME="IP") THEN SET @exe = CONCAT("ALTER TABLE ",tab," ADD IP VARCHAR(40) COMMENT "ip""); PREPARE sql1 FROM @exe; EXECUTE sql1; END IF; END
把代码复制进去,保存
CALL pro_AddColumn("tableA"); CALL pro_AddColumn("tableB"); CALL pro_AddColumn("tableC"); CALL pro_AddColumn("tableD");
代码复制进去执行即可,括号里面是表名
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。
相关文章: