oarcle mysql 字段的差別和互換另外：

mysql與oracle 表字段定義比較

 有很多應用項目, 剛起步的時候用MYSQL資料庫基本上能實作各種功能需求，随着應用使用者的增多，資料量的增加，MYSQL漸漸地出現不堪重負的情況：連接配接很慢甚至當機，于是就有把資料從MYSQL遷到ORACLE的需求，應用程式也要相應做一些修改。總結出以下幾點注意事項。

說明

mysql

oracle

VARCHAR

變長字元串

VARCHAR[0-65535]

定義長度預設按字元長度計算，如果是GBK編碼的漢字将占用2個位元組

VARCHAR2[1-4000]

VARCHAR是VARCHAR2的同義詞

定義預設按位元組長度計算

TINYINT

SMALLINT

MEDIUMINT

INT

BIGINT

整數

TINYINT(-128-127)

SMALLINT(-32768-32767)

MEDIUMINT(-8388608-8388607)

INT(-2147483648-2147483647)

BIGINT(-9223372036854775808-9223372036854775807) 

無專用類型，

TINYINT可以用NUMBER(3,0)代替

SMALLINT可以用NUMBER(5,0)代替

MEDUIMINT可以用NUMBER(7,0)代替

INT可以用NUMBER(10,0)代替

BIGINT可以用NUMBER(20,0)代替

ORACLE中有SMALLINT,INT,INTEGER類型，不過這是NUMBER(38,0)的同義詞

DECIMAL

NUMERIC

數值類型

DECIMAL[1-65[,0-30]]

NUMERIC是DECIMAL的同義詞

NUMBER 可表示數範圍：1*10^-130至1*10^126

NUMBER([1-38][,-84-127])

DECIMAL、NUMERIC、DEC是NUMBER的同義詞

FLOAT

浮點型

FLOAT(D,M)

oracle10g開始增加BINARY_FLOAT類型

10g以前無專用類型，可以用NUMBER代替

ORACLE中有FLOAT和REAL類型，不過這是NUMBER的同義詞

DOUBLE

雙精度浮點型

DOUBLE(D,M)

oracle10g開始增加BINARY_DOUBLE類型

ORACLE中有DOUBLE PRECISION類型，不過這是NUMBER的同義詞

BIT

位類型

BIT(1-64)

無

DATETIME

日期類型

DATE，3位元組存儲，隻存儲日期，沒有時間，支援範圍是[1000-01-01]至[9999-12-31]

TIME，3位元組存儲，隻存儲時間，沒有日期，支援範圍是[-838:59:59]至[838:59:59]

DATETIME，占8位元組存儲，可表示日期和時間，支援範圍是[1000-01-01 00:00:00]至[9999-12-31 23:59:59]

TIMESTAMP，占4位元組存儲，可表示日期和時間，範圍是[1970-01-01 00:00:00]至[2038-01-19 03:14:07]

DATE類型

7位元組存儲，可表示日期和時間，支援範圍是[-4712-01-01 00:00:00]至[9999-12-31 23:59:59]

TIMESTAMP

高精度日期

5.6.4以前不支援小數秒精度

5.6.4開始TIME,DATETIME,TIMESTAMP支援，最多可以6位小數秒，也就是微秒級别

TIMESTAMP[0-9]

占用空間7-11個位元組，當小數秒精度為0時與DATE類型相同，小數秒最高精度可達9位，也就是納精度

YEAR

年份

YEAR，1位元組存儲，隻存儲年份，支援範圍是[1901]至[2155]

無對應類型，可以用NUMBER(3,0)代替

CHAR

定長字元串

CHAR[0-255]，定義長度預設按字元長度計算，最大儲存255字元

CHAR[1-2000]

UNSIGNED

無符号說明

支援，用于數值類型

不支援

CLOB

大字元串，一般用于存儲文本檔案或超大描述及備注類資訊

TINYTEXT 最大支援255個位元組

TEXT最大支援65535個位元組

MEDIUMTEXT最大支援16MB個位元組

LONGTEXT最大支援4GB位元組

字段不支援預設值

支援(CLOB)

oracle10g以前最大支援4GB個位元組

oracle10g開始最大支援4GB個資料塊，資料塊大小為2KB-32KB

oracle還有一個LONG類型，是早期的存儲大字元串類型，最大支援2GB位元組,現已不推薦使用

BLOB

大二進制對象，一般用于存儲檔案或圖檔資料

TINYBLOB 最大支援255個位元組

BLOB最大支援65535個位元組

MEDIUMBLOB最大支援16MB個位元組

LONGBLOB最大支援4GB位元組

支援(BLOB)

oracle10g開始最大支援4G個資料塊，資料塊大小為2KB-32KB

oracle還有一個LONG RAW類型，是早期的存儲二進制類型，最大支援2GB位元組,現已不推薦使用

BINARY

二進制資訊

BINARY(0-255)，定長

VARBINARY(0-65535)，變長

RAW(1-2000)

ENUM

枚舉類型

ENUM(v1,v2,v3,...),最多65535個元素

SET

集合類型

SET(v1,v2,v3,...)，最多64個元素

NATIONAL CHAR

國際化字元集類型，較少使用

無，MYSQL可以對每個字段指定字元編碼

支援

NCHAR(1-2000)

NVARCHAR(1-4000)

NCLOB

BFILE

外部檔案指針類型

檔案大小最大4GB

檔案名稱最長255字元

自定義資料類型

XML類型

自增類型

自動增長類型

使用簡單

一般使用SEQUENCE解決，用法與自增類型差别較大，使用較複雜，但能實作非常靈活的應用，包括字元自增主鍵、全局主鍵等等

字段預設值表達式

不支援函數和表達式

TEXT和BLOB字段類型不支援預設值

支援函數和表達式

字段順序修改

支援，例如，把emp表的id字段順序放在name字段後面：

alter table emp modify column id varchar(20) after name;

不支援，隻能重建表或字段

虛拟字段

虛拟字段是一個邏輯字段定義，其結果值通常是一個表達式，并在表中存儲實體值，不占用空間，主要用于簡化查詢邏輯。比如有一個商品銷售表有單價和數量兩個字段，那可以建一個虛拟字段金額，其表達式=單價*數量

11g支援，例：

create table sales

(

  id       number,

  quantity number,

  price    number,

  amount   GENERATED always as (quantity*price) virtual

);

表字段數限制

INNODB 最大1000個字段

所有字段總定義長度不能超過65535位元組

所有固定長度字段的總長度不超過半個資料塊大小(資料塊大小一般為16K)

最大1000個字段

1． 自動增長的資料類型處理

          MYSQL有自動增長的資料類型，插入記錄時不用操作此字段，會自動獲得資料值。ORACLE沒有自動增長的資料類型，需要建立一個自動增長的序列号，插入記錄時要把序列号的下一個值賦于此字段。

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

    其中最大的值按字段的長度來定, 如果定義的自動增長的序列号 NUMBER(6) , 最大值為999999

          INSERT 語句插入這個字段值為: 序列号的名稱.NEXTVAL

2. 單引号的處理

          MYSQL裡可以用雙引号包起字元串，ORACLE裡隻可以用單引号包起字元串。在插入和修改字元串前必須做單引号的替換：把所有出現的一個單引号替換成兩個單引号。

3.  翻頁的SQL語句的處理

          MYSQL處理翻頁的SQL語句比較簡單，用LIMIT 開始位置, 記錄個數；PHP裡還可以用SEEK定位到結果集的位置。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;

4． 長字元串的處理

    長字元串的處理ORACLE也有它特殊的地方。INSERT和UPDATE時最大可操作的字元串長度小于等于4000個單位元組, 如果要插入更長的字元串, 請考慮字段用CLOB類型，方法借用ORACLE裡自帶的DBMS_LOB程式包。插入修改記錄前一定要做進行非空和長度判斷，不能為空的字段值和超出長度字段值都應該提出。

5.         日期字段的處理

           MYSQL日期字段分DATE和TIME兩種，ORACLE日期字段隻有DATE，包含年月日時分秒資訊，用目前資料庫的系統時間為SYSDATE, 精确到秒，或者用字元串轉換成日期型函數TO_DATE(‘<st1:chsdate isrocdate="False" islunardate="False" day="1" month="8" year="2001">2001-08-01</st1:chsdate>’,’YYYY-MM-DD’)年-月-日 24小時:分鐘:秒 的格式YYYY-MM-DD

HH24:MI:SS TO_DATE()還有很多種日期格式, 可以參看ORACLE DOC.日期型字段轉換成字元串函數TO_CHAR(‘<st1:chsdate isrocdate="False" islunardate="False" day="1" month="8" year="2001">2001-08-01</st1:chsdate>’,’YYYY-MM-DD HH24:MI:SS’)

    日期字段的數學運算公式有很大的不同。MYSQL找到離目前時間7天用 DATE_FIELD_NAME > SUBDATE（NOW（），INTERVAL 7 DAY）ORACLE找到離目前時間7天用 DATE_FIELD_NAME >SYSDATE - 7;

6.  空字元的處理

           MYSQL的非空字段也有空的内容，ORACLE裡定義了非空字段就不容許有空的内容。按MYSQL的NOT NULL來定義ORACLE表結構, 導資料的時候會産生錯誤。是以導資料時要對空字元進行判斷，如果為NULL或空字元，需要把它改成一個空格的字元串。

7. 字元串的模糊比較

          MYSQL裡用 字段名 like '%字元串%',ORACLE裡也可以用 字段名 like '%字元串%' 但這種方法不能使用索引, 速度不快，用字元串比較函數 instr(字段名,'字元串')>0 會得到更精确的查找結果。

8. 程式和函數裡，操作資料庫的工作完成後請注意結果集和指針的釋放。