sql&db

数据库迁移及及区别(Oracle,MySQL,SQLServer)

字号+ 作者:徐亮 来源:转载 2016-01-08 14:58 我要评论( )

公司的一个项目要作数据迁移改工作,所以到网上找了一篇文章

导言:最近的数据超市项目需要从SQLServer迁移到MySql。在之前并没有很多数据库迁移方面的经验,所以也不知道迁移需要花费多久,都要做什么工作。通过天的工作,项目已经顺利迁移到MySql上。该文档总结了迁移的经验,同时也方便以后数据库迁移。

 

1、数据库迁移需要做的工作

1.1 建表脚本修改

1.2 数据导入(编码、数据类型设置)

1.3 项目中的SQL修改

1.4 数据库连接(驱动)

1.5 连接项目中的程序,测试并修改程序

2、常用数据库中在开发方面的不同

2.1 数据类型

2.2 自增

2.3 分页

2.4 内置函数

2.5 模糊查询

3、ORM工具与迁移

使用hibernate、ibatis,在数据库迁移中的不同效率

4、各数据库的不同数据类型比较及参考资料

4.1MySQL中的建表SQL

4.2JavaSQL数据类型影射表

4.3、 MySqlOracle数据类型的相互转化

4.4ORACLESQLSERVERMYSQL的数据类型对照表

 

1、数据库迁移需要做的工作

1、1 数据库建表脚本的修改

1.1.1 由于各种数据库的数据类型并不相同,需要更改部分数据类型。

1.1.2 在MySQL脚本里暂不能给日期数据设置当前时间,字段如:registertime(注册时间);需在程序中设置当前日期,再保存进数据库或将registertime设置为时间戳(timestamp

1.1.3 MySQL里的表和字段的注释,见【5】中的建表语句。

1、2 将数据导入到目标数据库的中(其中可以需要修改数据类型)

1.2.1设置数据库的编码,防止中文乱码

1)最简单的修改方法,就是修改mysqlmy.ini文件中的字符集键值,

如:default-character-set utf8

character_set_server utf8

修改完后,重启mysql的服务,service mysql restart

2)还有一种修改字符集的方法,就是使用mysql的命令,如:

mysql> SET character_set_client utf8 ;mysql> SET character_set_connection utf8 ;

mysql> SET character_set_database utf8 ;mysql> SET character_set_results utf8 ;mysql> SET character_set_server utf8 ;mysql> SET collation_connection utf8 ;mysql> SET collation_database utf8 ; mysql> SET collation_server utf8 ;

如果:没有设置前两条,可以通过以下方式实现编码

建数据库时 设置数据库支持的编码:create database datmart charset=utf8; 使用数据库datmart use datmart; 插入中文数据时,需要先设置:set names utf8; 将 数据导入 source d:\datmart.sql

(每个见表语句后加:ENGINE=MyISAM  DEFAULT CHARSET=utf8;)

1.2.2 数据类型

即使MySQL中有bit,但SQLServer中的bit类型(取0或1,分别对应了booltruefalse)的变量在MySQL中,不能顺利导入。需要将其设置为tinyint(1),才能顺利导入。

12.3导入数据

 采用第三方工具,如Navicat 8,通过微软提供的ODBC连接数据源,并导入数据或者自己写一个通用程序,将全部数据读入在写进新的数据库中。

1、3 修改项目中的SQL,使得SQL在目标数据库里也可以使用

1.4 数据库连接(下载驱动)

SQLServer的数据库连接:

datamart_driver=com.microsoft.sqlserver.jdbc.SQLServerDriver

datamart_url=jdbc:sqlserver://172.16.6.23:1433;DatabaseName=datamart

MySql的数据库连接:

datamart_driver=com.mysql.jdbc.Driver

datamart_url=jdbc:mysql://172.16.6.23:3306/datamart

Oracle的数据库连接:

datamart_driver=oracle.jdbc.driver.OracleDriver

datamart_url=jdbc:oracle:thin:@172.16.6.26:1521:datamart

用户名和密码都是:

datamart_username=root

datamart_password=sd100301

2、常用数据库中在开发方面的不同

2.1 分页、2.2内置函数、2.3自增  2.4存储过程 2.5模糊查询

2.1 分页

Oracle中的分页:可以采用rownumber实现;SQLServer中的分页,采用内容函数row_number() 实现;MySQL中采用limit。

Oracle中的SQL:

 select rn,first_name,salary

 from(select rownum as rn,frist_name,salary

      from (select first_name,salary from s_emp order by salary)) 

 where rn between 11 and 20

SQLServer中的SQL:

select from( select row_number() over(order by salary descas rownumber,*      

             from s_emp  where salary>3000

   as tb 

where  rownumber between 11 and 20

MySQL中基本的SQL:

select from apiinfo where id<41

order by enname desc

limit 5,200

2.2 内置函数

数据库中有许多内置函数,不少是用于处理字符串、日期等的。

SQLServer的len(),相当于MySQL的length(),相当于Oracle的Len().

2.3 自增

2.3.1 自增关键字 

Oralce: SQLServer: identity MySQL:auto_increment

2.3.2 Oracle中的自增(序列号):

定义:CREATE SEQUENCE emp_sequence        

INCREMENT BY   -- 每次加几个        

START WITH     -- 1开始计数        

NOMAXVALUE       -- 不设置最大值        

NOCYCLE          -- 一直累加,不循环        CACHE 10;

使用:emp_sequence.CURRVAL      emp_sequence.NEXTVAL  

2.3.3 自增带来的问题及其解决

问题:(oracle中的自增字段,如果它的值不是连续的,并且您将其做为主键,那么迁移到其它数据库时候,那些不连续的值发生了改变。而其它表是与该字段关联的,这样程序就会出错)

解决方式:在目标数据库中建立统一的表,并有同样的字段但不自增;导入数据后,再修改表的结构,使得该字段自增。

2.3.4 自己实现id字段的自增的SQL语句

insert into orderApi (id,ordernumber,apiid)

select distinct IFNULL((select max(id)+1 from orderApi),1),#ordernumber#,#apiid# 

from orderApi  group by id

2.4 存储过程

不同的数据库存储过程相差的比SQL间的差异到大,所以项目中的存储过程需要改不少地方。或者,如果对性能影响不大,可以不用存储过程。

2.5 模糊查询

SQLServer中,模糊查询可以使用 

select from apiinfo where cnname like #key#+'%';

但在MySql中,则需改为:

select from apiinfo where cnname like '%$key$%'

or

select from apiinfo where cnname  REGEXP '^['+#key#+']'

注:#key#,是方法中传入的值;MySQL中的SQL使用了REGEXP,是正则表达式

 

3、ORM工具与迁移

使用hibernate、ibatis,在数据库迁移中的不同效率

ibatis:sql需要自己写 hibernate:sql自动生成;

Hibernate的特点: Hibernate功能强大,数据库无关性好,O/R映射能力强,如果你对Hibernate相当精通,而且对Hibernate进行了适当的封装,那么你的项目整个持久层代码会相当简单,需要写的代码很少开发速度很快

iBATIS的特点: iBATIS入门简单,即学即用,提供了数据库查询的自动对象绑定功能,而且延续了很好的SQL使用经验,对于没有那么高的对象模型要求的项目来说,相当完美。iBATIS的缺点就是框架还是比较简陋,功能尚有缺失,虽然简化了数据绑定代码,但是整个底层数据库查询实际还是要自己写的,工作量也比较大,而且不太容易适应快速数据库修改。

易迁移行比较:对于数据库迁移来说,常用的数据库操作,如增删改查等,在hibernate中基本不需要改动;而ibatis中是自己写的针对特定数据库类型的SQL,所以需要改不少内容。

 

4、各数据库的不同数据类型比较及参考资料

4.1 MySQL中的建表SQL:

CREATE TABLE `apiindicator` (

  `id` int(11) NOT NULL,

  `apiid` int(11) DEFAULT NULL COMMENT '关联api信息表(apiinfoid',

  `cnname` varchar(100) DEFAULT NULL COMMENT '指标中文名',

  `enname` varchar(60) DEFAULT NULL COMMENT '指标英文名 ',

  `description` varchar(1000) DEFAULT NULL,

  `datatype` varchar(15) DEFAULT NULL, `isout` bit DEFAULT '1' COMMENT  '是否必须输出 ,默认为输出;0:不输出 1:输出',

  `state` int(11) DEFAULT '0',

  `isdelete` bit DEFAULT '0' COMMENT '删除标记:0未删除;1已删除',

  PRIMARY KEY (`id`)

ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Api指标表 ';

 

4.2JavaSQL数据类型影射表

String

VARCHAR or LONGVARCHAR

java.math.BigDecimal

NUMERIC

Boolean

BIT

Byte

TINYINT

Short

SMALLINT

Int

INTEGER

Long

BIGINT

Float

REAL

Double

DOUBLE

byte[]

VARBINARY or LONGVARBINARY

java.sql.Date

DATE

java.sql.Time

TIME

java.sql.Timestamp

TIMESTAMP

 

4.3 MySqlOracle数据类型的相互转化

BIGINT

NUMBER(19, 0)

BIT

RAW

BLOB

BLOB, RAW

CHAR

CHAR

DATE

DATE

DATETIME

DATE

DECIMAL

FLOAT (24)

DOUBLE

FLOAT (24)

DOUBLE PRECISION

FLOAT (24)

ENUM

VARCHAR2

FLOAT

FLOAT

INT

NUMBER(10, 0)

INTEGER

NUMBER(10, 0)

LONGBLOB

BLOB, RAW

LONGTEXT

CLOB, RAW

MEDIUMBLOB

BLOB, RAW

MEDIUMINT

NUMBER(7, 0)

MEDIUMTEXT

CLOB, RAW

NUMERIC

NUMBER

REAL

FLOAT (24)

SET

VARCHAR2

SMALLINT

NUMBER(5, 0)

TEXT

VARCHAR2, CLOB

TIME

DATE

TIMESTAMP

DATE

TINYBLOB

RAW

TINYINT

NUMBER(3, 0)

TINYTEXT

VARCHAR2

VARCHAR

VARCHAR2, CLOB

YEAR

NUMBER

 

4.4 ORACLESQLSERVERMYSQL的数据类型对照表

 

Oracle 数据类型

SQL Server 数据类型

Mysql数据类型

BFILE

VARBINARY(MAX)

 

BLOB

VARBINARY(MAX)

BLOB, LONGBLOB, MEDIUMBLOB

CHAR([1-2000])

CHAR([1-2000])

CHAR

CLOB

VARCHAR(MAX)

TEXT, LONGTEXT, MEDIUMTEXT

DATE

DATETIME

DATE,DATETIME, TIME, TIMESTAMP

FLOAT

FLOAT

REAL,

DECIMAL, DOUBLE, DOUBLE PRECISION

FLOAT([1-53])

FLOAT([1-53])

FLOAT([54-126])

FLOAT

INT

NUMERIC(38)

 

INTERVAL

DATETIME

 

LONG

VARCHAR(MAX)

 

LONG RAW

IMAGE

 

NCHAR([1-1000])

NCHAR([1-1000])

 

NCLOB

NVARCHAR(MAX)

 

NUMBER

FLOAT

INT, INTEGER, NUMERIC, YEAR, MEDIUMINT

NUMBER([1-38])

NUMERIC([1-38])

SMALLINT, TINYINT, BIGINT

NUMBER([0-38],[1-38])

NUMERIC([0-38],[1-38])

 

NVARCHAR2([1-2000])

NVARCHAR([1-2000])

SET, VARCHAR, ENUM

RAW([1-2000])

VARBINARY([1-2000])

BLOB,BIT, TINYBLOB, LONGTEXT, LONGBLOB, MEDIUMBLOB, MEDIUMTEXT

REAL

FLOAT

 

ROWID

CHAR(18)

 

TIMESTAMP

DATETIME

 

UROWID

CHAR(18)

 

VARCHAR2([1-4000])

VARCHAR([1-4000])

TEXT, TINYTEXT

转载请注明出处。

1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。

相关文章
网友点评
评论列表(网友评论仅供网友表达个人看法,并不表明本站同意其观点或证实其描述)