mysql到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 <?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或空字符，需要把它改成一个空格的字符串。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
