编写可重复执⾏的mysql数据库升级脚本
项⽬开发过程中经常需要编写数据库升级脚本来做⼀些基本的表操作：如添加表、添加字段，修改字段等。由于软件部署会经常执⾏数据库升级脚本，这就要求我们编写的升级脚本是可重复执⾏的。
基本思路是对表做操作前先判断表是否存在，对字段做操作前同样需要判断字段是否已经存在，避免脚本执⾏过程报错退出。下⾯基于脚本可重复执⾏的要求，给了⼀些常⽤的MYSQL数据库升级脚本编写样例：
⼀、创建表
DROP TABLE IF EXISTS tbl_user;
CREATE TABLE tbl_user (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL DEFAULT'',
age int(4) NOT NULL DEFAULT'0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10DEFAULT CHARSET=utf8;
⼆、删除表
DROP TABLE IF EXISTS tbl_test3;
三、添加字段
CREATE PROCEDURE add_tbl_user_name_en()
BEGIN
IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS
WHERE TABLE_NAME='tbl_user'
AND TABLE_SCHEMA='testdb3'
AND COLUMN_NAME='name_en')
THEN
ALTER TABLE tbl_user ADD COLUMN name_en varchar(255) NOT NULL DEFAULT '' COMMENT '英⽂名';
END IF;
END;
CALL add_tbl_user_name_en;
DROP PROCEDURE add_tbl_user_name_en;
如何判断表中的字段是否已经存在？在mysql中没有什么简便的⽅法，笔者通过⼀个存储过程来做判断，处理完后再删除存储过程。
四、修改字段名称
CREATE PROCEDURE change_tbl_user_name_en_to_name_cn()
BEGIN
IF EXISTS (SELECT 1 FROM information_schema.COLUMNS
WHERE TABLE_NAME='tbl_user'
AND TABLE_SCHEMA='testdb3'
AND COLUMN_NAME='name_en')
THEN
ALTER TABLE tbl_user CHANGE name_en name_cn varchar(255) NOT NULL DEFAULT '' COMMENT '中⽂名';
END IF;
END;
CALL change_tbl_user_name_en_to_name_cn;
DROP PROCEDURE change_tbl_user_name_en_to_name_cn;
五、删除字段
CREATE PROCEDURE delete_tbl_user_name()
BEGIN
IF EXISTS (SELECT 1 FROM information_schema.COLUMNS
WHERE TABLE_NAME='tbl_user'
AND TABLE_SCHEMA='testdb3'
AND COLUMN_NAME='name')
THEN
ALTER TABLE tbl_user DROP name;
END IF;
END;
CALL delete_tbl_user_name;
DROP PROCEDURE delete_tbl_user_name;
同样，建库，建索引也需要提前判断库或者索引是否已经存在，避免重复创建。
六、初始化静态数据
数据库升级时也会有⼀些静态的配置数据要预置到数据库表中。在插⼊这些数据前需要执⾏⼀下delete或者TRUNCATE TABLE清除数据，避免多次执⾏脚本后插⼊重复数据。
七、在升级脚本中加上 “use 数据库名” 语句
⼀般来说，⼀台mysql服务器上会有多个db，每个db都会有⾃⼰的升级脚本。为了⽅便升级脚本⾃动化执⾏，在升级脚本前⾯需要加
上”use testdb3”, ⽤于指定是对testdb3数据库操作。
七、编写mysql命令⾃动执⾏脚本中的存储过程需要定义分割符
软件部署后，数据库升级脚本⼀般都会通过下⾯这样的mysql命令⾃动执⾏：
E:\temp>mysql -h127.0.0.1 -P3306 -uroot < upgrade.sql
这样执⾏之前的存储过程会有语法问题(在navicat中是可以正常执⾏的)，跟sql的load机制有关系。需要在编写脚本时通过⾃定义分割符来告诉存储过程的sql语句从哪⾥开始到哪⾥结束。下⾯以####作为分割符给⼀个样例：
use testdb3;
delimiter ####
CREATE PROCEDURE add_tbl_user_name_en()
BEGIN
mysql删除重复的数据保留一条IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS
WHERE TABLE_NAME='tbl_user'
AND TABLE_SCHEMA='testdb3'
AND COLUMN_NAME='name_en')
THEN
ALTER TABLE tbl_user ADD COLUMN name_en varchar(255) NOT NULL DEFAULT '' COMMENT '英⽂名';
END IF;
END
####
delimiter ;
CALL add_tbl_user_name_en;
DROP PROCEDURE add_tbl_user_name_en;
SQL语法默认是以分号分割的，在存储过程前重定义为####，存储过程后需要恢复为分号。注意：分割符已经重定义为####，原来在存储过程最后⼀个END后⾯的那个分号就要去掉了。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。