mysql 判断表结构变动_Mysql 判断表或字段是否存在新增/修改表结构sql

DROP PROCEDURE

IF EXISTS proc_tempPro;

CREATE PROCEDURE proc_tempPro ()

BEGIN

START TRANSACTION;

-- 学生表

DROP TABLE IF EXISTS student;

CREATE TABLE student (

id bigint(20) NOT NULL AUTO_INCREMENT ,

name varchar(40) DEFAULT NULL ,

xb varchar(40) DEFAULT NULL ,

age int(3) DEFAULT NULL ,

birthday datetime DEFAULT NULL ,

className varchar(80) DEFAULT NULL ,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 学生表新增班级字段

SELECT

count(*) INTO @count

FROM

information_schema. COLUMNS

WHERE

table_schema = DATABASE ()

AND column_name = ‘className‘

AND table_name = ‘student‘;

IF (@count < 1) THEN

ALTER TABLE student ADD className VARCHAR(40);

ELSE

ALTER TABLE student MODIFY COLUMN className VARCHAR(20);

END IF;

END;

CALL proc_tempPro;

DROP PROCEDURE

IF EXISTS proc_tempPro;