mysql到Oracle迁移要注意的地方

浏览数：25 / 时间：2015年06月12日

从mysql迁移到oracle

项目开始阶段使用mysql数据库开发，后期改成oracle。数据抽象Db类对sql语句进行了封装，所以我主要工作就是参照mysql驱动的接口写一个oracle驱动。

基本的siud操作两者差别不是特别大，只有个别地方需要做一些工作：

在oracle中建立相应的序列和触发器，实现mysql中的auto_increment字段
使用子查询，实现mysql的limit以及sql_calc_found_rows功能
对sql语句进行替换，避免mysql不出问题的字符或关键字在oracle中出问题

最后有两个问题比较致命：

联表查询的时候会出现“列定义不明确”的错误
分组查询的时候会出现“不是一个group查询”的错误

对于前者，原因就是a表中有字段xx，b表中也有字段xx，然后select a.*, b.*就会出现错误了。

对于后者，就是oracle中对group查询有这样的限制，select的字段、order中使用的字段，要么是出现在group by中，要么是被统计函数作用。而在mysql中却没有这样的限制。

这两个致命问题，我开始时是想要在oracle驱动上对sql语句进行hack，但是测试了一些例子发现没有什么规律而言，而且数据库驱动不维持表的元信息，所以写起来比较困难，就算最后写出来，执行效率也不高。最后只能返回去改程序，同时也给自己长记性，要改改在mysql中养成的那些臭毛病。

此外还有一些需要注意的地方，比如：

字段类型的转换
NULL的问题
单双引号

最后上代码：

  1 <?php
  2 define(‘_TS‘, microtime(true));
  3 error_reporting(E_ALL);
  4 header(‘Content-type: text/plain; charset=utf-8‘);
  5 
  6 $_db = ‘dbname‘;
  7 $_prefix = ‘pre_‘;
  8 $_new_prefix = ‘pre2_‘;
  9 
 10 $_sql_table = "select table_name, auto_increment, table_comment from information_schema.tables where table_schema = ‘".$_db."‘ and table_name like ‘".$_prefix."%‘;";
 11 $_sql_column = "select column_name, column_default, is_nullable, column_type, column_key, extra, column_comment from information_schema.columns where table_schema = ‘".$_db."‘ and table_name = ‘".$_prefix."%s‘ order by ordinal_position;";
 12 $_sql_checknull = "select count(*) from %s where %s = ‘‘ or %s is null";
 13 
 14 $db = mysql_connect(‘localhost‘, ‘root‘, ‘‘);
 15 mysql_select_db($_db);
 16 mysql_query(‘set names utf8;‘);
 17 
 18 function query($sql, $mode = MYSQL_ASSOC)
 19 {
 20     $q = mysql_query($sql);
 21     if (!$q)
 22         exit(‘query error: ‘. mysql_error());
 23 
 24     $ret = array();
 25     while ($tmp = mysql_fetch_array($q, $mode))
 26         $ret[] = $tmp;
 27 
 28     return $ret;
 29 }
 30 
 31 echo ‘SET DEFINE OFF;‘. PHP_EOL;
 32 
 33 foreach (query($_sql_table) as $table)
 34 {
 35     $t = substr($table[‘table_name‘], strlen($_prefix));
 36     if (!$t)
 37         exit("unknown table: {$t}");
 38 
 39     $new_table_name = $_new_prefix. $t;
 40 
 41     echo ‘-- ‘. $t. PHP_EOL;
 42     echo ‘DROP TABLE ‘. $new_table_name. ‘;‘. PHP_EOL;
 43     echo ‘CREATE TABLE ‘. $new_table_name. PHP_EOL;
 44     echo ‘(‘. PHP_EOL;
 45 
 46     $pks = array();
 47     $idx = array();
 48     $aic = ‘‘;
 49     $comments = ‘‘;
 50 
 51     $cArr = query(sprintf($_sql_column, $t));
 52     $i = count($cArr);
 53     foreach ($cArr as $c)
 54     {
 55         if ($c[‘extra‘] == ‘auto_increment‘)
 56             $aic = $c[‘column_name‘];
 57 
 58         echo $c[‘column_name‘]. ‘ ‘;
 59         if (!preg_match(‘/^(\w+)\b[^\d]*(\d+)?[^\d]*(\d+)?/‘, $c[‘column_type‘], $match))
 60             exit("cannot match: {$t}.{$c[‘column_name‘]}");
 61 
 62         array_shift($match);
 63         $type = strtolower(array_shift($match));
 64         switch ($type)
 65         {
 66         case ‘mediumint‘:
 67         case ‘int‘:
 68         case ‘tinyint‘:
 69             echo ‘NUMBER(‘. ($match[0] < 5 ? 5 : $match[0]). ‘)‘;
 70             break;
 71         case ‘decimal‘:
 72             echo ‘NUMBER(‘. $match[0]. ‘,‘. $match[1]. ‘)‘;
 73             break;
 74         case ‘char‘:
 75         case ‘varchar‘:
 76             $match[0] *= 2;
 77             echo ‘VARCHAR2(‘. ($match[0] > 4000 ? 4000 : $match[0]). ‘)‘;
 78             break;
 79         case ‘date‘:
 80         case ‘enum‘:
 81             echo ‘VARCHAR2(10)‘;
 82             break;
 83         case ‘text‘:
 84             echo ‘NCLOB‘;
 85             break;
 86         default:
 87             exit("unknown type: {$c[‘column_name‘]} {$type}");
 88         }
 89 
 90         if ($c[‘column_default‘] !== null)
 91             echo " DEFAULT ‘". $c[‘column_default‘]. "‘";
 92 
 93         switch (strtoupper($c[‘column_key‘]))
 94         {
 95         case ‘PRI‘:
 96             $pks[] = $c[‘column_name‘];
 97             break;
 98         case ‘MUL‘:
 99             $idx[$c[‘column_name‘]] = ‘INDEX‘;
100             break;
101         case ‘UNI‘:
102             $idx[$c[‘column_name‘]] = ‘UNIQUE INDEX‘;
103             break;
104         }
105 
106         if (strtoupper($c[‘is_nullable‘]) == ‘NO‘)
107         {
108             $checknull = query(sprintf($_sql_checknull, $table[‘table_name‘], $c[‘column_name‘], $c[‘column_name‘]), MYSQL_NUM);
109             if (!(int) $checknull[0][0])
110                 echo ‘ NOT NULL‘;
111         }
112 
113         if (--$i)
114             echo ‘,‘;
115 
116         echo PHP_EOL;
117 
118         $comments .= ‘COMMENT ON COLUMN ‘. $new_table_name. ‘.‘. $c[‘column_name‘];
119         $comments .= " IS ‘". $c[‘column_comment‘]. "‘;". PHP_EOL;
120     }
121 
122     if (count($pks))
123         echo ‘, PRIMARY KEY (‘.implode(‘, ‘, $pks).‘)‘. PHP_EOL;
124 
125     echo ‘);‘. PHP_EOL;
126 
127     foreach ($idx as $k => $v)
128         echo ‘CREATE ‘. $v. ‘ ‘. $new_table_name. ‘_‘. $k. ‘ ON ‘. $new_table_name. ‘ (‘. $k. ‘);‘. PHP_EOL;
129 
130     echo ‘COMMENT ON TABLE ‘. $new_table_name. " IS ‘". addslashes($table[‘table_comment‘]). "‘;". PHP_EOL;
131     echo $comments;
132 
133     foreach (query("select * from {$table[‘table_name‘]}") as $row)
134     {
135         echo ‘INSERT INTO ‘. $new_table_name. ‘ VALUES (‘;
136         foreach ($row as & $ri)
137             $ri = "‘".addslashes($ri)."‘";
138 
139         echo implode(‘,‘, $row);
140         echo ‘);‘. PHP_EOL;
141     }
142 
143     if ($aic)
144     {
145         echo ‘DROP SEQUENCE SEQU_‘. $t. ‘;‘. PHP_EOL;
146         echo ‘CREATE SEQUENCE SEQU_‘. $t. ‘ MINVALUE 1 MAXVALUE 999999999999 INCREMENT BY 1‘. PHP_EOL;
147         echo ‘START WITH ‘. $table[‘auto_increment‘]. ‘ NOCACHE ORDER NOCYCLE ;‘. PHP_EOL;
148         echo ‘CREATE OR REPLACE TRIGGER TRIG_‘. $t. ‘‘. PHP_EOL;
149         echo ‘BEFORE INSERT ON ‘. $new_table_name. ‘‘. PHP_EOL;
150         echo ‘FOR EACH ROW‘. PHP_EOL;
151         echo ‘BEGIN‘. PHP_EOL;
152         echo ‘  SELECT SEQU_‘. $t. ‘.NEXTVAL INTO :NEW.‘.$aic.‘ FROM DUAL;‘. PHP_EOL;
153         echo ‘END;‘. PHP_EOL;
154         echo ‘/‘. PHP_EOL;
155     }
156 
157     echo PHP_EOL;
158 }
159 
160 mysql_close($db);
161 echo ‘-- ‘;
162 printf(‘%.3f‘, microtime(true) - _TS);

<?php
// 比较关键的一些操作

// connect
$linkId = oci_new_connect(‘user‘, ‘pass‘, ‘host/sid‘, ‘utf8‘);

// query
$stmt = oci_parse($linkId, $sql);
$mode = false ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS;
if (!oci_execute($stmt, $mode))
    throw new Exception(‘query error: ‘. $sql);

// fetch
$ret = array();
while ($tmp = oci_fetch_array($stmt, OCI_BOTH + OCI_RETURN_NULLS + OCI_RETURN_LOBS))
    $ret[] = array_change_key_case($tmp);

// select
$sql = ‘select %s ‘;
$sql .= ‘from %s ‘;
$sql .= ‘left join %s on %s ‘;
$sql .= ‘where %s ‘;
$sql .= ‘group by %s ‘;
$sql .= ‘having %s ‘;
$sql .= ‘order by %s ‘;

$sqlCalcFoundRows = ‘select max(rownum) from (‘. $sql. ‘)‘;
$sql = ‘select * from (select a.*, rownum r from (‘. $sql. ‘) a where rownum <= 10) b where r > 0‘;

// insert
$sql = ‘insert into %s (%s) values (%s)‘;
$sqlInsertId = ‘select sequ_%s.currval from %s‘;

// update
$sql = ‘update %s set %s ‘;

// delete
$sql = ‘delete from %s %s‘;

// replace
$sql = ‘merge into %s a using (select %s from dual where rownum < 2) b on (a.%s = b.%s) when matched then update set %s when not matched then insert (%s) values (%s)‘;

// startTransaction

// commitTransaction
oci_commit($linkId);

// rollbackTransaction
oci_rollback($linkId);

1．自动增长的数据类型处理

MYSQL有自动增长的数据类型，插入记录时不用操作此字段，会自动获得数据值。ORACLE没有自动增长的数据类型，需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段。

CREATE SEQUENCE 序列号的名称 (最好是表名+序列号标记) INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE;

INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL

2. 单引号的处理

MYSQL里可以用双引号包起字符串，ORACLE里只可以用单引号包起字符串。在插入和修改字符串前必须做单引号的替换：把所有出现的一个单引号替换成两个单引号。当然你如果使用 Convert Mysql to Oracle 工具就不用考虑这个问题

3．长字符串的处理

在ORACLE中，INSERT和UPDATE时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型，方法借用ORACLE里自带的DBMS_LOB程序包。插入修改记录前一定要做进行非空和长度判断，不能为空的字段值和超出长度字段值都应该提出警告,返回上次操作。

4. 翻页的SQL语句的处理

MYSQL处理翻页的SQL语句比较简单，用LIMIT 开始位置, 记录个数。ORACLE处理翻页的SQL语句就比较繁琐了。每个结果集只有一个ROWNUM字段标明它的位置, 并且只能用ROWNUM<100, 不能用ROWNUM>80。

以下是经过分析后较好的两种ORACLE翻页SQL语句( ID是唯一关键字的字段名 )：

语句一：

SELECT ID, [FIELD_NAME,...] FROM TABLE_NAME WHERE ID IN ( SELECT ID FROM (SELECT ROWNUM AS NUMROW, ID FROM TABLE_NAME WHERE 条件1 ORDER BY 条件2) WHERE NUMROW > 80 AND NUMROW < 100 ) ORDER BY 条件3;

语句二：

SELECT * FROM (( SELECT ROWNUM AS NUMROW, c.* from (select [FIELD_NAME,...] FROM TABLE_NAME WHERE 条件1 ORDER BY 条件2) c) WHERE NUMROW > 80 AND NUMROW < 100 ) ORDER BY 条件3;

5. 日期字段的处理

MYSQL日期字段分DATE和TIME两种，ORACLE日期字段只有DATE，包含年月日时分秒信息，用当前数据库的系统时间为SYSDATE, 精确到秒。

日期字段的数学运算公式有很大的不同。MYSQL找到离当前时间7天用 DATE_FIELD_NAME > SUBDATE（NOW（），INTERVAL 7 DAY）ORACLE找到离当前时间7天用 DATE_FIELD_NAME >SYSDATE - 7;

6. 字符串的模糊比较

MYSQL里用字段名 like ‘%字符串%‘,ORACLE里也可以用字段名 like ‘%字符串%‘ 但这种方法不能使用索引, 速度不快，用字符串比较函数 instr(字段名,‘字符串‘)>0 会得到更精确的查找结果。

7. 空字符的处理

MYSQL的非空字段也有空的内容，ORACLE里定义了非空字段就不容许有空的内容。按MYSQL的NOT NULL来定义ORACLE表结构, 导数据的时候会产生错误。因此导数据时要对空字符进行判断，如果为NULL或空字符，需要把它改成一个空格的字符串。

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

mysql到Oracle迁移要注意的地方