mysql to oracle 迁移数据乱码_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 <?php2 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);

// 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或空字符，需要把它改成一个空格的字符串。