徐亮，2011-4-1

導(dǎo)言：最近的數(shù)據(jù)超市項(xiàng)目需要從SQLServer遷移到MySql。在之前并沒(méi)有很多數(shù)據(jù)庫(kù)遷移方面的經(jīng)驗(yàn)，所以也不知道遷移需要花費(fèi)多久，都要做什么工作。通過(guò)幾天的工作，項(xiàng)目已經(jīng)順利遷移到MySql上。該文檔總結(jié)了遷移的經(jīng)驗(yàn)，同時(shí)也方便以后數(shù)據(jù)庫(kù)遷移。

1、數(shù)據(jù)庫(kù)遷移需要做的工作

1.1 建表腳本修改

1.2 數(shù)據(jù)導(dǎo)入（編碼、數(shù)據(jù)類型設(shè)置）

1.3 項(xiàng)目中的SQL修改

1.4 數(shù)據(jù)庫(kù)連接（驅(qū)動(dòng)）

1.5 連接項(xiàng)目中的程序，測(cè)試并修改程序

2、常用數(shù)據(jù)庫(kù)中在開(kāi)發(fā)方面的不同

2.1 數(shù)據(jù)類型

2.2 自增

2.3 分頁(yè)

2.4 內(nèi)置函數(shù)

2.5 模糊查詢

3、ORM工具與遷移

使用hibernate、ibatis，在數(shù)據(jù)庫(kù)遷移中的不同效率

4、各數(shù)據(jù)庫(kù)的不同數(shù)據(jù)類型比較及參考資料

4.1、MySQL中的建表SQL

4.2、Java到SQL數(shù)據(jù)類型影射表

4.3、 MySql與Oracle數(shù)據(jù)類型的相互轉(zhuǎn)化

4.4、ORACLE與SQLSERVER、MYSQL的數(shù)據(jù)類型對(duì)照表

1、數(shù)據(jù)庫(kù)遷移需要做的工作

1、1 數(shù)據(jù)庫(kù)建表腳本的修改

1.1.1 由于各種數(shù)據(jù)庫(kù)的數(shù)據(jù)類型并不相同，需要更改部分?jǐn)?shù)據(jù)類型。

1.1.2 在MySQL腳本里暫不能給日期數(shù)據(jù)設(shè)置當(dāng)前時(shí)間，字段如：registertime（注冊(cè)時(shí)間）；需在程序中設(shè)置當(dāng)前日期，再保存進(jìn)數(shù)據(jù)庫(kù)或?qū)?/span>registertime設(shè)置為時(shí)間戳（timestamp）

1.1.3 在MySQL里的表和字段的注釋，見(jiàn)【5】中的建表語(yǔ)句。

1、2 將數(shù)據(jù)導(dǎo)入到目標(biāo)數(shù)據(jù)庫(kù)的中（其中可以需要修改數(shù)據(jù)類型）

1.2.1設(shè)置數(shù)據(jù)庫(kù)的編碼，防止中文亂碼

1)、最簡(jiǎn)單的修改方法，就是修改mysql的my.ini文件中的字符集鍵值，

如：default-character-set = utf8

（character_set_server = utf8）

修改完后，重啟mysql的服務(wù)，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 ;

如果：沒(méi)有設(shè)置前兩條，可以通過(guò)以下方式實(shí)現(xiàn)編碼：

a 建數(shù)據(jù)庫(kù)時(shí) 設(shè)置數(shù)據(jù)庫(kù)支持的編碼：create database datmart charset=utf8; b 使用數(shù)據(jù)庫(kù)datmart use datmart; c 插入中文數(shù)據(jù)時(shí)，需要先設(shè)置：set names utf8; d 將 數(shù)據(jù)導(dǎo)入 source d:\datmart.sql

(每個(gè)見(jiàn)表語(yǔ)句后加：ENGINE=MyISAM  DEFAULT CHARSET=utf8;)

1.2.2 數(shù)據(jù)類型

即使MySQL中有bit,但SQLServer中的bit類型（取0或1，分別對(duì)應(yīng)了bool的true和false）的變量在MySQL中,不能順利導(dǎo)入。需要將其設(shè)置為tinyint（1）,才能順利導(dǎo)入。

12.3導(dǎo)入數(shù)據(jù)

 采用第三方工具，如Navicat 8，通過(guò)微軟提供的ODBC連接數(shù)據(jù)源，并導(dǎo)入數(shù)據(jù)或者自己寫(xiě)一個(gè)通用程序，將全部數(shù)據(jù)讀入在寫(xiě)進(jìn)新的數(shù)據(jù)庫(kù)中。

1、3 修改項(xiàng)目中的SQL，使得SQL在目標(biāo)數(shù)據(jù)庫(kù)里也可以使用

1.4 數(shù)據(jù)庫(kù)連接（下載驅(qū)動(dòng)）

SQLServer的數(shù)據(jù)庫(kù)連接:

datamart_driver=com.microsoft.sqlserver.jdbc.SQLServerDriver

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

MySql的數(shù)據(jù)庫(kù)連接:

datamart_driver=com.mysql.jdbc.Driver

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

Oracle的數(shù)據(jù)庫(kù)連接：

datamart_driver=oracle.jdbc.driver.OracleDriver

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

用戶名和密碼都是：

datamart_username=root

datamart_password=sd100301

2、常用數(shù)據(jù)庫(kù)中在開(kāi)發(fā)方面的不同

2.1 分頁(yè)、2.2內(nèi)置函數(shù)、2.3自增  2.4存儲(chǔ)過(guò)程 2.5模糊查詢

2.1 分頁(yè)

Oracle中的分頁(yè)：可以采用rownumber實(shí)現(xiàn)；SQLServer中的分頁(yè)，采用內(nèi)容函數(shù)row_number() 實(shí)現(xiàn)；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 desc) as 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 內(nèi)置函數(shù)

數(shù)據(jù)庫(kù)中有許多內(nèi)置函數(shù)，不少是用于處理字符串、日期等的。

SQLServer的len(),相當(dāng)于MySQL的length(),相當(dāng)于Oracle的Len().

2.3 自增

2.3.1 自增關(guān)鍵字 

Oralce: SQLServer: identity MySQL:auto_increment

2.3.2 Oracle中的自增(序列號(hào))：

定義：CREATE SEQUENCE emp_sequence        

INCREMENT BY 1   -- 每次加幾個(gè)        

START WITH 1     -- 從1開(kāi)始計(jì)數(shù)        

NOMAXVALUE       -- 不設(shè)置最大值        

NOCYCLE          -- 一直累加，不循環(huán)        CACHE 10;

使用：emp_sequence.CURRVAL      emp_sequence.NEXTVAL  

2.3.3 自增帶來(lái)的問(wèn)題及其解決

問(wèn)題：（oracle中的自增字段，如果它的值不是連續(xù)的，并且您將其做為主鍵，那么遷移到其它數(shù)據(jù)庫(kù)時(shí)候，那些不連續(xù)的值發(fā)生了改變。而其它表是與該字段關(guān)聯(lián)的，這樣程序就會(huì)出錯(cuò)）

解決方式：在目標(biāo)數(shù)據(jù)庫(kù)中建立統(tǒng)一的表，并有同樣的字段但不自增；導(dǎo)入數(shù)據(jù)后，再修改表的結(jié)構(gòu)，使得該字段自增。

2.3.4 自己實(shí)現(xiàn)id字段的自增的SQL語(yǔ)句

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 存儲(chǔ)過(guò)程

不同的數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程相差的比SQL間的差異到大，所以項(xiàng)目中的存儲(chǔ)過(guò)程需要改不少地方?；蛘?，如果對(duì)性能影響不大，可以不用存儲(chǔ)過(guò)程。

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，是正則表達(dá)式

3、ORM工具與遷移

使用hibernate、ibatis，在數(shù)據(jù)庫(kù)遷移中的不同效率

ibatis:sql需要自己寫(xiě) hibernate:sql自動(dòng)生成;

Hibernate的特點(diǎn)： Hibernate功能強(qiáng)大，數(shù)據(jù)庫(kù)無(wú)關(guān)性好，O/R映射能力強(qiáng)，如果你對(duì)Hibernate相當(dāng)精通，而且對(duì)Hibernate進(jìn)行了適當(dāng)?shù)姆庋b，那么你的項(xiàng)目整個(gè)持久層代碼會(huì)相當(dāng)簡(jiǎn)單，需要寫(xiě)的代碼很少，開(kāi)發(fā)速度很快。

iBATIS的特點(diǎn)： iBATIS入門(mén)簡(jiǎn)單，即學(xué)即用，提供了數(shù)據(jù)庫(kù)查詢的自動(dòng)對(duì)象綁定功能，而且延續(xù)了很好的SQL使用經(jīng)驗(yàn)，對(duì)于沒(méi)有那么高的對(duì)象模型要求的項(xiàng)目來(lái)說(shuō)，相當(dāng)完美。iBATIS的缺點(diǎn)就是框架還是比較簡(jiǎn)陋，功能尚有缺失，雖然簡(jiǎn)化了數(shù)據(jù)綁定代碼，但是整個(gè)底層數(shù)據(jù)庫(kù)查詢實(shí)際還是要自己寫(xiě)的，工作量也比較大，而且不太容易適應(yīng)快速數(shù)據(jù)庫(kù)修改。

易遷移行比較：對(duì)于數(shù)據(jù)庫(kù)遷移來(lái)說(shuō)，常用的數(shù)據(jù)庫(kù)操作，如增刪改查等，在hibernate中基本不需要改動(dòng)；而ibatis中是自己寫(xiě)的針對(duì)特定數(shù)據(jù)庫(kù)類型的SQL,所以需要改不少內(nèi)容。

4、各數(shù)據(jù)庫(kù)的不同數(shù)據(jù)類型比較及參考資料

4.1 MySQL中的建表SQL:

CREATE TABLE `apiindicator` (

  `id` int(11) NOT NULL,

  `apiid` int(11) DEFAULT NULL COMMENT '關(guān)聯(lián)api信息表（apiinfo）id',

  `cnname` varchar(100) DEFAULT NULL COMMENT '指標(biāo)中文名',

  `enname` varchar(60) DEFAULT NULL COMMENT '指標(biāo)英文名 ',

  `description` varchar(1000) DEFAULT NULL,

  `datatype` varchar(15) DEFAULT NULL, `isout` bit DEFAULT '1' COMMENT  '是否必須輸出 ，默認(rèn)為輸出；0：不輸出 1：輸出',

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

  `isdelete` bit DEFAULT '0' COMMENT '刪除標(biāo)記：0未刪除；1已刪除',

  PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Api指標(biāo)表 ';

4.2：Java到SQL數(shù)據(jù)類型影射表

4.3 MySql與Oracle數(shù)據(jù)類型的相互轉(zhuǎn)化

4.4 ORACLE與SQLSERVER、MYSQL的數(shù)據(jù)類型對(duì)照表