mysql数据库表升级方案_一个基于mysql数据库的项目的升级解决方案小结(节选)

最新推荐文章于 2024-07-09 08:30:00 发布

AIS_Canada

最新推荐文章于 2024-07-09 08:30:00 发布

阅读量555

点赞数

文章标签： mysql数据库表升级方案

本文链接：https://blog.csdn.net/weixin_30916627/article/details/113651450

版权

1引言

数据库使用MYSQL4.0版本。随用用户数的逐步增多，因为数据库版本过低而引起的抱怨也越来越多，不足也越来越明显。

首先，对产品化部署产生了制约。由于MYSQL4.0数据库的配置文件my.ini是存放在Windows的系统目录下的，同时MYSQL4.0的安装程序无法对端口号进行设置。所以如果一个服务器上已经安装了任意一个版本MYSQL数据库，再安装一个MYSQL4.0，以前的MYSQL数据库将无法使用。但是随着用户的日益增多，客户提供的服务器的软件环境也是各式各样。不可避免会出现客户提供的服务器上已经安装了MYSQL的情况，而目前系统只在MYSQL4.0上可以使用，所以只能和客户商量更换服务器来解决这个问题(目前已经有多家客户存在这种情况)。因此，如果要使得系统实现产品化部署，MYSQL4.0并不符合要求。另外，在MYSQL的官方网站上，已经停止对MYSQL4.0提供下载。一方面说明，MYSQL4.0已渐渐被淘汰；另一方面，MYSQL在未经开发商授权是不能被商用的[2]，因此在实际的部署中，原则上数据库需要用户自行安装的，但安装包的官方下载地址已经不存在了，也会造成商业上的一些问题。所以MYSQL4.0是该系统产品化的一个瓶颈。

其次，对多国语言支持功能弱。由于MYSQL4.0发布时间较早，只支持latin1编码方式存储，而不支持如UTF-8,GBK的编码存储。因此对于如VB，VFP等对Unicode支持不够的编程语言而言，它们从MYSQL4.0数据库获取的中文或日文字符将成乱码显示。而目前一些客户会使用VB制作一些第三方工具来读取该系统的数据库，这个时候就会出现麻烦。

再次，缺少数据库的必要功能。MYSQL4.0是不支持子查询，视图，存储过程等数据库高级特性的。随着该系统功能的逐步增强，代码量的不断增加，不可避免会出现性能问题，而对以上这些高级特性的使用，正是解决性能问题的有效对策之一。所以这部分功能的缺失，是该系统性能改善的一个瓶颈。

还有，长字符存入数据库后有时会出现乱码。MYSQL4.0对各种类型的数据的定义不是很严格。特别是对字符型，由于是用latin1编码进行存储的，一个中文字符是按3个编码单位进行存储的，对于定义为varchar(10)的字段，如果存入4个中文字符的话，第4个中文字符将自动被截去三分之二。因此在数据库中，最后一个中文字符将丢失，同时在界面上显示乱码。更重要的是，这一个过程，数据库没有任何的警示。所以该系统现在的系统中，用户的数据会在不知情的情况下丢失。这个也是该系统客户使用时候的一个瓶颈。

综合这四方面考虑，数据库的版本问题确实是今后该系统发展的一个瓶颈。将该系统的数据库进行升级是一个刻不容缓的事情。

2前期调查和迁移实验

由于MYSQL4.0的诸多限制会影响产品的发展，因此本人受项目组委托，对MYSQL的升级方案进行了具体的调研和整理。(从2009年2月开始，到2009年4月中旬完成)

整个调研工作将分成一下四个阶段进行：

(1)前期调查准备

对MYSQL4.0以上各个版本进行考察，选择合适的MYSQL版本，和对应的JDBC版本

(2)迁移实验

按照先数据修改，再代码修改的顺序进行。并对实验的经验进行归纳，整理出迁移方案，并按照该方案部署Demo系统。

(3)测试验证工作

①对Demo系统进行IT测试，确保迁移完成后的该系统应用能够在新的数据库上正常运行，从而验证迁移工作是否成功。

②使用相同的Demo系统在MYSQL4.0上进行兼容性测试，确保修改后的该系统系统能够在MYSQL4.0上也能正常运行。

(4)正式部署

为了使该方案尽早投入实际的应用中，目前已经和一个需要进行数据库升级的国内客户达成协议，一旦前面4个阶段完成，将为该客户进行数据库升级。

本章中将对第1阶段和第2阶段展开详细介绍：

2.1前期调查的成果

2.1.1数据库版本的选择

经过对MYSQL官方网站相关资料的查询，确认在MYSQL4.0以上的主流版本包括4.1，5.0，5.1三个版本(截止2009年2月)[3]。

表1的数据显示说明，MYSQL5.0以上版本已经能够解决上文中提到的四大瓶颈。另一方面，在升级方案提出后的较长一段时间内，会存在基于MYSQL4.0的该系统和基于升级版本的该系统两者共存的情况。因此，经过升级改造后的程序需要在这两个版本上都能运行，所以程序需要对数据库版本有一定的兼容性。其中比较重要的JDBC的选择上，MYSQL5.1并不能满足要求，同时5.1的新特性也不是升级系统所必须的。因此，综合考虑后，MYSQL5.0是数据库升级最合适的目标版本。而5.0版本当时最新的小版本号为5.0.18，因此接下来的迁移工作将围绕MYSQL5.0.18进行。

表1MYSQL三版本特性对比

版本特性

MYSQL4.1

MYSQL 5.0

MYSQL 5.1

官方网站是否

提供下载

是

是否支持多个

my.ini共存

是

是否支持多种

编码存储

是

长字符存入是否出现乱码

否

引入新功能

子查询；默认支持事务类型表

视图，触发器，存储过程，分布式事务

事件调度器，增强的日志功能

是否能使用和

4.0一样的JDBC

是

否

2.1.2JDBC的选择

由于目前该系统使用的JDBC为2.0.4版本的，该版本只能支持MYSQL4.0，所以JDBC必须进行升级。但是，升级后的JDBC必须能够保证对MYSQL4.0和MYSQL5.0的同时支持，因此JDBC5.0以上的版本可以不做考虑。剩下可以考虑的是3.0和3.1两个版本。其中3.0版本主要是针对4.0，4.1版本设计的，但对5.0版本的基本功能能够支持，但所有的新特性并不支持；3.1版本主要是针对4.1，5.0版本设计的，支持除分布式事务外的所有5.0的特性[4]。因此，选择3.1版本的JDBC更为理想。

2.1.3数据迁移方式的选择

对于已经存在于MYSQL4.0中的老数据如何更新到MYSQL5.0中，这是一个比较关键的问题。可以采用的方法有三种：

(1)直接复制数据文件

(2)使用Dump命令

(3)使用专业工具

经过调查，方法2比较可行。按照方法1操作，发现数据库表中所有字符型的字段的长度都缩小为原来的三分之一，而且这些字段的长度无法进行再修改。这样的话，系统将无法正常使用。

按照方法3，这类开源的工具暂时没有，即使存在非开源的这类工具，那数据迁移的成本也将大大提高，所以方法3也不可行。因此唯一可以行的是方法2。

2.2迁移实验的成果

2.2.1迁移的准备

在前期的调查工作完成后，就开始进入迁移的实际工作。

首先安装好MYSQL4.0和MYSQL5.0两个版本的数据库，其中MYSQL5.0在配置的时候需要将编码方式选择为UTF-8。并且准备好建库的原始脚本，和一份MYSQL4.0数据库下的真实数据拷贝。

然后在Tomcat下部署好合适的系统。由于v5.7.1版本是目前为止品质最稳定，功能上也比较完整的版本，因此我们尝试使用v5.7.1版作为迁移的程序版本。

最后，将准备好JDBC(mysql-connector-java-3.1.10-bin.jar)替换原来的JDBC。

2.2.2迁移的具体方法

整个实验主要分成两个阶段进行展开：

①该系统全新部署的迁移实验；

②使用中的该系统的迁移实验。

两个阶段的迁移思路是一致的

2.2.3迁移的难点分析和解决

按照2.2.2的迁移方法逐步展开工作，问题一个个被发现，然后又一个个被妥善解决。但也遇到了一些困难，经过深入的分析和实践，这些问题最终获得了解决。解决这些问题的思路还是值得借鉴的，因此进行详细的介绍。

难点一：

MYSQL4.0中已被部分截断的字符型数据无法使用Dump命令导出。

难点分析：

该类数据在真实的数据拷贝版本中有2000多条，而且存在于多个表的多个字段中。理论上，只要是varchar和text类型的字段都有发生该情况的可能性。而手工一条条的修改不是很现实的，所以必须找到规律和特征，才能解决该问题。对于任何客户，如果因为数据库的升级，而需要丢失数千条未知的数据，这种情况是无法接受的。

通过对被截断的字符型数据的统计，发现并不是所有被截断数据都无法导出，只有截断后最后一个字符变成乱码的时候，才会无法导出。被截断是因为存入的数据是按照latin1方式存储的，一个中文字占三个字长，当总字长大于子段定义长度的时候，多余的字符将被截断。因此对于中文字，被截断的情况可以分成三类：

l中文字的三分之一编码被截去；

l中文字的三分之二编码被截去；

l中文字的全部编码被截去。

第三类就是不出现乱码的情况，其他两类就会出现乱码。因此，理论上只要把出现乱码的字符串的最后一个或者最后两个编码去除，就可以将该字符串变成合法的字符串，然后就可以进行导出操作了。经过对大量乱码数据的分类归纳，发现这些乱码数据的最后第一个或者最后第二个字符是有规律的，它的ASCII码都大于191。

解决对策：

制作相应的数据库数据乱码修正工具，对所有的varchar和text类型的字段进行检测，如果发现数据的最后一个或者最后第二个字符的编码值大于191,就进行对应的截取，具体代码如下：

update TableName set ColumnName=substring(ColumnName,1, DefineLength-2) where char_length(workName)=DefineLength and ASCII(RIGHT(ColumnName,2)) >191;

update TableName set ColumnName=substring(ColumnName,1, DefineLength-1) where char_length(workName)=DefineLength and ASCII(RIGHT(ColumnName,1)) >191;

难点二：

JDBC3.1版本使用的问题。

难点分析：

按照2.1.3的思路，将原来的JDBC版本进行了替换。在迁移的实验中发现该系统频繁出现“内存溢出”，“JSP无法编译”，“Servlet异常”等问题，导致系统无法正常运行。经过排查，发现问题出现在JDBC上，经过对3.1下所有小版本的验证，发现问题依旧存在。2.1.2节中曾提到，称3.0版的JDBC可以支持5.0版MYSQL的基本功能，因此更换了当时的最新版本3.0.16版本进行尝试。经过验证，3.1下的所有问题都消失了，系统能够稳定运行了。

但是，在迁移工作继续开展中发现了另一个问题：带参的SQL语句无法成功，但对应的SQL在命令行中是能够运行成功的。这表明了程序和数据库都是没有问题的，唯一的可能是问题出在JDBC上，有可能这个版本的JDBC不支持PreStatement的SQL操作。但3.0.16是当时的最新版本了，3.1版本虽然是支持PreStatement的SQL操作，但因为前面提到的问题而无法使用，这是否意味着，迁移工作无法完成呢？这时候，突然发现MYSQL官网上两天前发布了3.0的更新版本3.0.17[5]。替换后验证，3.0.16上的问题解决了，同时3.1上的问题也没有再现。

解决对策：

使用3.0.17版本的JDBC作为该系统连接MYSQL5.0的JDBC。经过验证，该JDBC也同时支持MYSQL4.0的连接。当然，如果不使用3.0.17版本的JDBC的话, SQL操作就需要修改为非PreStatement方式来执行了，这样的修改代价是比较大的。

2.2.4迁移的成果总结

按照迁移的基本方法开展工作，同时对一些难点进行了成功的攻克，整个迁移实验取得了理想的成果。

首先，证明了该系统由MYSQL4.0向5.0进行迁移是可行的。既包括系统全新部署，也包括原有系统的升级。

其次，在实验过程中，获取了MYSQL4.0和MYSQL5.0在使用上差异性的汇总表，根据该汇总表可以对现有的数据库结构和程序进行修改；同时也可以指导开发人员在今后的开发中如何设计出同时符合MYSQL4.0和MYSQL5.0要求的数据表结构，也能编写出满足这2种数据库要求的代码。具体的汇总表见表2。

表2 MYSQL4.0和5.0功能差异对比

MYSQL4.0

MYSQL5.0

int类型字段

允许插入''

不允许插入'' ,只能插入null值