ORACLE與MYSQL程式碼開發差異

Michael_DD發表於2014-12-02
OracleMySql

ORACLE與MYSQL程式碼開發差異

1         概述

參照的版本：oracle 11g，mysql 5.6.10

去oracle化依照如下原則：

·         相容 oracle和mysql

·           

2         SQL開發

2.1       DDL (資料定義語言)

主要包括CREATE，ALTER，DROP，TRUNCATE，COMMENT，RENAME語句。

2.1.1       CREATE

建立表，索引比較。

2.1.1.1     CREATE TABLE

Create table 語句，兩者在約束等條件下基本一致；其它方面略有差異。對照關係如下表：

內容

資料庫

對比

例句

標註

基本建表

oracle

相同

create table t_test (c_a int,c_b varchar(255))

基本相同，資料型別：
mysql的INT相當於oracle中的NUMBER(10,0)（詳見後面資料型別章節）

mysql

預設值約束

oracle

相同

create table t_test (c_a int default 0,c_b varchar(255) );

 

mysql

非空約束

oracle

相同

create table t_test (c_a int not null,c_b varchar(255))

 

mysql

唯一性約束

oracle

相同

create table t_test (c_a int unique,c_b varchar(255))或者 create table t_test (c_a int ,c_b varchar(255), unique (c_a))

自動建立索引，oracle不能在該約束欄位上新建索引，而mysql沒有此限制。

mysql

主鍵約束

oracle

相同

create table t_test (c_a int primary key,c_b varchar(255)) 或者create table t_test (c_a int ,c_b varchar(255), constraint p_a primary key (c_a))

自動建立索引，oracle不能在該約束欄位上再新建索引，而mysql沒有此限制。

mysql

外來鍵約束

oracle

相同

create table t_f(c_a int primary key,c_b varchar(255), foreign key(c_a)  references t_test(c_a));

 

mysql

檢查條件約束

oracle

相同

create table t_test (c_a int check (c_a>0) ,c_b varchar(255))或者
create table t_test (c_a int ,c_b varchar(255),check (c_a>0) )

 

mysql

表空間指定

oracle

不同

create table t_test (c_a int,c_b varchar(255) ) tablespace t_data;

 

mysql

create table t_test (c_a int,c_b varchar(255)  ) 

mysql無指定tablespace選項，預設innodb儲存引擎

CTAS建表

oracle

不同

create table t_test_b as select c_a c_b,c_b c_d from t_test;

oracle不允許增加新的欄位的定義，只能透過別名方式，但是欄位屬性無法修改

mysql

create table t_test_b (n_a int,n_b varchar(300)) as select c_a c_b,c_b c_d from t_test;

可以選擇新的欄位和欄位屬性

表結構複製

oracle

不同

 

 

mysql

create table t_test_b like t_test;

表結構（包括約束等）全部複製。但不復製表中資料。

建立索引

oracle

不同

 

不支援，但在primary key時支援using index

mysql

create table t_test (c_a int,c_b varchar(255) ,index i_test_c_a(c_a) );

支援可選建表同時建立索引

建立按月分割槽

oracle

不同

create table  t_test (c_a int,c_b varchar(255),sts_date date)
 partition by range (sts_date)
 (
   partition P1301 values less than (TO_DATE('2013-01-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS')),
   partition P1302 values less than (TO_DATE('2013-02-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS')),
    PARTITION PMAX VALUES LESS THAN (MAXVALUE)
  )

 

mysql

CREATE TABLE  t_test (c_a INT,c_b VARCHAR(255),sts_date DATETIME)
 PARTITION BY RANGE (TO_DAYS(sts_date))
 (
   PARTITION P1301 VALUES LESS THAN (TO_DAYS('2013-01-01')),
   PARTITION P1302 VALUES LESS THAN (TO_DAYS('2013-02-01')),
    PARTITION PMAX VALUES LESS THAN (MAXVALUE)
  )

分割槽欄位是日期時，需要使用year(),to_days函式輔助分割槽建表。

表註釋

oracle

不同

 

comment語法

mysql

create table t_test(c_a int primary key,c_b varchar(255)) comment 'test'

建立表時可新增註釋。

 

2.1.1.2     CREATE INDEX

create index 相對簡單，語句基本一致。

內容

資料庫

對比

例句

標註

基本建索引

oracle

相同

create index i_test_c_a on t_test (c_a);

 

mysql

唯一性索引

oracle

相同

create unique index i_test_c_a on t_test (c_a);

 

mysql

分割槽索引

oracle

不同

create index i_p_test on t_test(c_a) global partition by range(c_a)
(partition p1 values less than(1000),
partition p2 values less than(2000),
partition p3 values less than(MAXVALUE));

建立分割槽索引

mysql

 

無此語句

 

2.1.2       ALTER

2.1.2.1     ALTER TABLE

ALTER TABLE兩者有較多的不一樣，如下表

內容

資料庫

對比

例句

標註

增加列

oracle

相同

alter table t_test add c_c int;

 

mysql

關鍵字column可選：alter table t_test add column c_c int;

刪除列

oracle

相同

alter table t_test drop column c_c;

 

mysql

重新命名列

oracle

不同

alter table t_test rename column c_c to c_d;

使用rename column更改 列名

mysql

alter table t_test change c_c c_d int;

使用change更改列名

修改列型別

oracle

不同

alter table t_test modify c_d date;

使用modify更改列型別

mysql

alter table t_test change c_d c_d date

使用change更改列型別

增加增加主鍵

oracle

相同

alter table t_test add constraint pk_t_c_a primary key(c_a);

 

mysql

mysql還可以使用如下語句：alter table t_test change c_a c_a primary key

增加預設值

oracle

不同

alter table t_test modify c_a default 0;

使用modify更改約束

mysql

alter table t_test change c_a c_a int default 0

使用change更改約束

增加非空

oracle

不同

alter table t_test modify c_a not null;

使用modify更改約束

mysql

alter table t_test change c_a c_a int not null;

使用change更改約束

重新命名錶

oracle

不同

rename t_test to t_n;

使用rename語句

mysql

alter table t_test rename t_n;

 

新建索引

oracle

不同

 

無此語句

mysql

alter table t_test add index idx_t_b (c_a);

 

新建唯一索引

oracle

不同

 

無此語句

mysql

alter table t_test add unique index idx_t_b (c_a);

 

新建主鍵

oracle

不同

 

無此語句

mysql

alter table t_test add constraint p_t_b primary key (c_a);

 

新建外來鍵

oracle

不同

 

無此語句

mysql

alter table t_f add constraint p_t_f foreign key (c_a) references t_test(c_a);

 

刪除索引

oracle

不同

 

無此語句

mysql

alter table t_f drop index;

 

刪除主鍵

oracle

不同

 

無此語句

mysql

alter table t_f drop primary key;

 

刪除外來鍵

oracle

不同

 

無此語句

mysql

alter table t_f drop foreign key p_t_f

 

轉換為分割槽表

oracle

不同

 

需要透過建立中間表的方式

mysql

alter table t_f partition by range (c_a)
(partition P1 values less than(10),
partition P2 values less than(20));

 

增加新的分割槽

oracle

不同

alter table t_f add partition P3 values less than(30)

注意增加分割槽的值必須大於已有分割槽的值。

mysql

alter table t_f add partition (partition p3 values less than(30));

注意增加分割槽的值必須大於已有分割槽的值。

拆分的分割槽

oracle

不同

alter table t_f split partition PM at (40) into(partition p4,partition pm)

使用 split partiton拆分

mysql

alter table t_f reorganize partition PM into(partition p4 values less than(30),partition pm values less than(MAXVALUE));

使用reorganize partiton拆分

刪除分割槽

oracle

相同

alter table t_f drop partition p3;

 

mysql

 

欄位註釋

oracle

不同

 

無此語句

mysql

alter table t_test modify column c_a int comment 'number';

需要寫出欄位名和型別（可以參照建表語句）(1024位元組)

 

2.1.2.2     ALTER INDEX

ORACLE 透過ALTER INDEX語句改變索引的定義。而MYSQL沒有此語句，相同的功能使用ALTER TABLE語句操作（詳見ALTER TABLE專題）。

內容

資料庫

對比

例句

標註

重新命名索引

oracle

不同

alter index i_test_c_a rename to i_test_n

 

mysql

 

只能透過刪除舊索引和重建新索引

重建索引

oracle

不同

alter index i_test_c_a rebuild (online)

 

mysql

 

沒有此功能

索引失效

oracle

不同

alter index idx_t_a unusable;

 

mysql

 

INNODB儲存引擎沒有此操作。

 

2.1.3       DROP

2.1.3.1     DROP TABLE

內容

資料庫

對比

例句

標註

刪除表

oracle

相同

drop table t_test;

 

mysql

刪除表（判斷表存在）

oracle

不同

 

 

mysql

drop table if exits t_test;

判斷表是否存在，如果不存在，不會報錯。

刪除被約束的表

oracle

不同

drop table t_test cascade constraints ;

 

mysql

 

沒有此操作，需要先刪除約束，再刪除表。

 

2.1.3.2     DROP INDEX

內容

資料庫

對比

例句

標註

刪除索引

oracle

不同

drop index i_t_f;

 

mysql

drop index i_t_f on t_test;

刪除索引必須指明表名，即支援不同的表名可以擁有相同的索引名

 

2.1.4       TRUNCATE

 

內容

資料庫

對比

例句

標註

清空表

oracle

相同

truncate table t_test;

 

mysql

 

2.1.5       COMMENT

內容

資料庫

對比

例句

標註

註釋表

oracle

不同

comment on table t_test is 'test'

註釋為空表示去掉註釋

mysql

 

見create table模組，註釋為空表示去掉註釋

註釋欄位

oracle

不同

comment on column t_test.c_a is 'number';

註釋為空表示去掉註釋

mysql

 

見alter table模組，註釋為空表示去掉註釋

 

2.1.6       RENAME

內容

資料庫

對比

例句

標註

重新命名錶

oracle

不同

rename t_test to t_n

 

mysql

rename table t_test to t_n;

需要關鍵字table

重新命名檢視

oracle

不同

rename t_view to t_n

 

mysql

rename view t_view to t_n;

需要關鍵字table

 

2.2       DML (資料操作語言)

主要包括SELECT，INSERT，UPDATE，DELETE語句。

2.2.1       SELECT

Select 包括了查詢，子查詢，連線，聯合等方式

內容

資料庫

對比

例句

標註

基本語句

oracle

相同

select * from t_test;

 

mysql

查詢加鎖

oracle

相同

select * from t_test  for update;

建議最好不在併發的正式環境中使用此方式

mysql

表別名引用

oracle

不同

select * from t_test  a where a.c_a=1;

 

mysql

select * from t_test  a where a.c_a=1 或者
select * from t_test as a where a.c_a=1

支援可選擇增加關鍵字as

欄位別名

oracle

不同

select c_a a,c_b b from t_test;
select c_a as "a",c_b as "b" from t_test;

 

mysql

select c_a a,c_b b from t_test;
select c_a as "a",c_b as "b" from t_test;
select c_a 'a',c_b 'b' from t_test;

支援對別名加單引號

連線:內連線

oracle

相同

select a.* from t_test a,t_test1 b where a.c_a=b.c_a;
select a.* from t_test a inner join t_test1 b on a.c_a=b.c_a;

一般使用第一種寫法。

mysql

連線：外左連線

oracle

不同

select a.c_a,a.c_b,b.c_a,b.c_b from t_test a,t_test1 b where a.c_a=b.c_a(+);
select a.c_a,a.c_b,b.c_a,b.c_b from t_test a left join t_test1 b on a.c_a=b.c_a;

支援可選(+)

mysql

select a.c_a,a.c_b,b.c_a,b.c_b from t_test a left join t_test1 b on a.c_a=b.c_a;
select a.c_a,a.c_b,b.c_a,b.c_b from t_test a left join t_test1 b using(c_a);

支援可選 using語句

連線：外左連線

oracle

不同

select a.c_a,a.c_b,b.c_a,b.c_b from t_test a,t_test1 b where a.c_a(+)=b.c_a;
select a.c_a,a.c_b,b.c_a,b.c_b from t_test a right join t_test1 b on a.c_a=b.c_a

支援可選(+)

mysql

select a.c_a,a.c_b,b.c_a,b.c_b from t_test a right  join t_test1 b on a.c_a=b.c_a;
select a.c_a,a.c_b,b.c_a,b.c_b from t_test a left join t_test1 b using(c_a);

支援可選 using語句

並集（聯合）

oracle

相同

select * from t_test union select * from t_test1
select * from t_test union all select * from t_test1;

 

mysql

交集

oracle

不同

select a.c_a,a.c_b from t_test a intersect select b.c_a,b.c_b from t_test1 b

使intersect語法

mysql

select a.c_a,a.c_b from t_test a inner join t_test1 b using(c_a,c_b)

使用inner join代替

差集

oracle

不同

select a.c_a,a.c_b from t_test a minus select b.c_a,b.c_b from t_test1 b;

使用minus語法

mysql

SELECT a.c_a,a.c_b from t_test a left join t_test1 b using(c_a,c_b) where b.c_a is null;

使用left join和where條件代替

獲取固定值

oracle

不同

select 1 number,'string' string from dual;

需要dual表構成完整的pl/sql語法

mysql

select 1 number,'string' string;

沒有dual表。

分組查詢

oracle

不同

select c_a,c_b from t_test group by c_a,c_b;

嚴格按照查詢欄位列出分組條件。

mysql

select c_a,c_b from t_test group by c_b;

可以支援預設查詢欄位。

限制輸出

oracle

不同

select * from t_test where rownum<3;

 

mysql

select * from t_test limit 2;

使用limit關鍵字

限制輸出（從第M行）

oracle

不同

 

 

mysql

select * from t_test limit 2,3;

從第2行開始輸出3行

字串擷取

oracle

不同

select substr(c_b,1,1) from t_test;

 

mysql

select substring(c_b,1,1) from t_test;

使用substring

合併行

oracle

不同

select wm_concat(c_a) from t_test;

以逗號分隔；

mysql

select group_concat(c_a) from t_test;

以逗號分隔；

all子查詢

oracle

相同

select * from t_test where c_a> all(select c_a from t_test1);

 

mysql

any子查詢

oracle

相同

select * from t_test where c_a>= any(select c_a from t_test1);

 

mysql

exist子查詢

oracle

相同

select * from t_test where exists (select * from t_test1 where c_a=t_test.c_a);

 

mysql

not exist子查詢

oracle

相同

select * from t_test where not exists (select * from t_test1 where c_a=t_test.c_a);

 

mysql

正規表示式查詢

oracle

不同

select * from t_test where regexp_like (c_a,'[14]');

 

mysql

select * from t_test where c_a regexp '[14]';

 

按字母大小查詢

oracle

不同

select * from t_test where c_a like 'a%';

對字母的大小寫完全匹配

mysql

select * from t_test where c_a like binary 'a%';

增加關鍵字binary，完全 匹配大小寫

 

2.2.2       INSERT

內容

資料庫

對比

例句

標註

基本語句

oracle

相同

insert into t_test values(101,'c101');

 

mysql

"into"可選

子查詢方式

oracle

相同

insert into t_test select 4,'dd' from dual union select 5,'ee' from dual union select 6,'ff' from dual;

 

mysql

"into"可選

空值

oracle

相同

insert into t_test values(100,null);

 

mysql

 

多行資料

oracle

不同

 

不支援

mysql

insert into t_test values(4,'dd'),(5,'ee'),(6,'ff');

採用逗號分隔，直接插入多行賦值

set語句

oracle

不同

 

不支援

mysql

insert into t_test set c_a=1;

其餘欄位按預設值處理

引用已經賦值的欄位

oracle

不同

 

不支援

mysql

insert into t_test(c_a,c_b) values(1,concat('c',c_a));

所引用的欄位必須已經賦值過

忽略錯誤

oracle

不同

 

不支援

mysql

insert ignore into t_test values(4,'ignore');

不會插入資料，不會返回錯誤資訊。

 

2.2.3       UPDATE

內容

資料庫

對比

例句

標註

基本語句

oracle

相同

update t_test set c_a=11,c_b='c11' where c_a=10;

 

mysql

 

select子句

oracle

相同

update t_test a set a.c_b='test' where a.c_a=(select c_a from t_test1 b where a.c_a=b.c_a );

 

mysql

 

case when子句

oracle

相同

update t_test set c_b=case c_a when 1 then 'test1' when 2 then 'test2' else 'testother' end ;

 

mysql

 

limit子句

oracle

不同

 

 

mysql

update t_test set c_b='test' limit 1;

 

select子查詢

oracle

不同

 

 

mysql

 

 

order by子句

oracle

不同

 

 

mysql

update t_test set c_b='test' order by c_a;

按排序後

多表同時更新

oracle

不同

 

 

mysql

update t_test a,t_test1 b set a.c_b = '1',b.c_b='2' where a.c_a =b.c_a;

不能新增order by 和limit子句

inner join子句

oracle

不同

 

 

mysql

update t_test a inner join t_test1  b  on a.c_a=b.c_a set a.c_b=b.c_b;

 

忽略錯誤

oracle

不同

 

不支援

mysql

update ignore t_test a set a.c_a =4 where a.c_a =1;

不會更新資料，不會返回錯誤資訊。

 

2.2.4       DELETE

 

內容

資料庫

對比

例句

標註

基本語句

oracle

相同

delete from t_test where c_a=1;

 

mysql

 

2.3       DCL (資料控制語言)

主要包括GRANT，REVOKE語句。

2.4       GRANT

內容

資料庫

對比

例句

標註

基本語句

oracle

相同

grant select on tpcc.item to test;

 

mysql

指定主機

oracle

不同

 

 

mysql

grant select on tpcc.item to test@'localhost';

 

 

2.5       REVOKE

內容

資料庫

對比

例句

標註

基本語句

oracle

相同

revoke select on tpcc.item from test;

 

mysql

指定主機

oracle

不同

 

 

mysql

revoke select on tpcc.item from test@'localhost';

 

 

2.6       TCL (資料定義語言)

主要包括SAVEPOINT，COMMIT，ROLLBACK，SETTRANSACTION語句。

2.6.1       COMMIT

內容

資料庫

對比

例句

標註

提交

oracle

相同

commit

 

mysql

 

2.6.2       ROLLBACK

內容

資料庫

對比

例句

標註

提交

oracle

相同

rollback

 

mysql

 

3         PL/SQL（SQL/PSM）

 

4         函式（FUNCTION）

 

內容

資料庫

對比

例句

標註

當前時間查詢

oracle

不同

select sysdate from dual;

 

mysql

select sysdate() from dual;

mysql需要加雙括號

長度

oracle

相同

select length(c_a)from t_test;

 

mysql

取指定長度

oracle

相同

select substr(c_b,1,2)from t_test;

 

mysql

字串轉數值

oracle

不同

select to_number('123') from dual;

 

mysql

select cast('123' as signed integer) from dual;

dual表可選

數值轉字串

oracle

不同

select to_char(123) from dual;

 

mysql

select cast(123 as char) from dual;

dual表可選

字串轉日期

oracle

不同

select to_date('2013-01-01','yyyy-mm-dd') from dual;

 

mysql

select str_to_date('2013-01-01','%Y/%m/%d') from dual;

dual表可選

字串轉日期

oracle

不同

select to_date('2013-01-01','yyyy-mm-dd') from dual;

 

mysql

select str_to_date('2013-01-01','%Y-%m-%d')from dual;

dual表可選

 

 

 

 

 

 

 

 

條件判斷

oracle

不同

select decode(c_a,1,1,0) from t_test; 或者
select case when c_a=1 then 1 else 0 end from t_test;

使用decode語法較簡單，也支援case語法

mysql

select case when c_a=1 then 1 else 0 end from t_test;

只支援case語法

空值判斷

oracle

不同

select nvl(c_a,0) from t_test;

使用nvl

mysql

select ifnull(c_a,0) from t_test;

使用ifnull

字串連線

oracle

不同

select c_a||c_b from t_test;或者
select concat(c_a,c_b) from t_test;

可以使用'|'代替concat語法

mysql

select concat(c_a,c_b,…) from t_test;

只支援concat語法

 

5         過程（PROCEDURE）

 

 

6         資料型別

6.1        

6.2       DATE

型別

有效時間

預設值

date

1000-01-01 至 9999-12-31

無

datetime 

1000-01-01 00:00:00 至 9999-12-31 23:59:59

now()

timestamp   

1970-01-01 00:00:01 至 2038-01-19 03:14:07

CURRENT_TIMESTAMP()

 

 

 

7         函式使用

7.1       日期和時間相關

獲取當前系統日期和時間：

now(): 在語句執行開始得到值

sysdate()：在函式執行開始時得到最新值

localtime()：在語句執行開始得到值

獲取當前日期（年月日）：

curdate(),current_date()

獲取當前時間（時分秒）：

curtime(),current_time()

獲取當前時間（按格式）：

   小時：Hour(time)

   分鐘：minute(time)

   月份：month(date)

   年份：year(date)

   格式化時間 date_format(date,format); %Y %m %d %H %i %s %f

日期計算：

   日期加減：date_add(date,INTERVAL expr type)，type指second,minute,hour,day,week,month,year等；

   例如：select date_add(sysdate(),interval -1 day);

7.2       字串函式

返回大寫字串：ucase(‘aaa’) 或者upper(‘aaa’);

返回小寫字串：lcase(‘AAA’) 或者lower(‘AAA’);

返回字串長度：length(‘AAA’)；

截斷空格： ltrim(‘ asss ‘) ,rtrim(‘ aaa ‘), trim(‘ aaa  ‘);

字串連線：concat(s1,s2,…);

字串替換：insert(s1,x,y,s2) 將s1第x個位置開始，共y個字元的子串替換成s2;

返回字串指定值：left(s1,x)返回s1最左邊的x個字元；right(s1,x)返回s1最右邊的x個字元。

字元擷取：substring(s1,m[,n])取s1從第m位置開始的餘下所有或長度為n的字串；

7.3       型別轉換函式

cast(s1 as type): 指定的型別如：binary,char,date,time,datetime,signed,unsigned。

convert(s1,type): 指定的型別如：binary,char,date,time,datetime,signed,unsigned。

 

7.4       其它函式

database():   返回當前連線的資料庫名

user():   返回當前連線串資訊。

 

 

 

 

8         Mysql常用工具

8.1       Mysql

Mysql可以連線MYSQL資料庫，下面是常用操作。

1    新建一個連線： mysql -hlocalhost -uroot -p -P 3306 ;其中test表示開啟的資料庫。

2         直接執行語句：-e  ‘sql’; 

3         預設是自動提交，透過如下方式：

mysql>select @@autocommit;

mysql>set autocommit=0;  #關閉自動提交

 

8.2       Mysqladmin管理工具

1         停止資料庫；mysqladmin –uroot –p shutdown;

2         檢視程式資訊：mysqladmin –uroot –p processlist;

8.3       Mysqlbinlog日誌管理

1         檢視test資料庫的操作日誌：mysqlbinlog -d test -s master-bin.000001.

2         結果輸出到檔案：-r out_test.txt

3         使用mysql匯入資料: mysql –uroot –p  test

8.4       Mysqlimport資料匯入工具(文字格式)

1         匯入資料:mysqlimport -uroot  -p test --fields-enclosed-by=\' --fields-terminated-by=, t_test

--fields-enclosed-by 資料的引用符

--fields-terminated-by表示分隔符，預設的分隔符是跳格符（Tab）。

t_test的記錄如：

1,'1'

2,'2'

 

8.5       Mysqldump資料匯出工具

1         匯出指定表: mysqldump  -n -t -uroot -p -B test --tables t_test   (-n 不生成建庫語句 -t不生成建表語句 –B指定資料庫名 –tables指定表)

8.6       Perror 檢視錯誤

perror  1045:檢視1045的錯誤資訊。

9         其它補充

 

10    ORACLE向MYSQL遷移方案

針對遷移方案，從建立資料庫，資料模型轉換（主要在欄位型別，約束屬性等）來說明，差異要點。

10.1  MYSQL建立資料庫

10.2  MYSQL關鍵引數

MYSQL服務端啟動時初始化的靜態配置引數，下表列出針對業務開發受影響的引數：

靜態引數

推薦值

說明

範圍

lower_case_table_names

1

1   資料字典以小寫字母存放表名稱；檔案系統也寫小寫字母存放檔名。
2  SQL語句中不區分表名的大小寫，DB始終以小寫方式解析表名。
3   保證資料庫的資料一致性：SQL語句中相同表名（只是大小寫不一樣）的寫法都是指定同一張表。

服務端

transaction_isolation

read-committed

同一事務中允許讀取已提交的資料。事務隔離級別MYSQL預設是REPEATABLE-READ ，(ORACLE預設級別READ-COMMITTED)。

服務端

innodb_file_per_table

on

開啟獨立表空間

服務端

sync_binlog

1

日誌檔案每寫1個事務，同步資料到磁碟上

 

innodb_flush_log_at_trx_commit

1

提交事務時，資料實時寫日誌檔案和磁碟上

服務端

innodb_additional_mem_pool_size

64M

儲存資料字典和資料結構

 

innodb_buffer_pool_size

5120M

快取後設資料和索引資料。實體記憶體的60%～80%之間。

服務端

innodb_max_dirty_pages_pct

80

髒資料最多在快取中佔的百分比。如果過多，會導致停止服務變慢（寫日誌和磁碟）

 

max_allowed_packet

64M

服務端對訊息包的限制，暫定64M，如果客戶端返回 錯誤有如下：“ Packet is larger than max_allowed_packet from server configuration of…"，需要再次調整該值。

服務端和客戶端

query_cache_type

0

關閉查詢快取標識。

服務端

max_connections

5000

開發資料庫暫定，如果超過最大連線，可實時動態調整。

服務端

max_connect_errors

10000

最大連線失敗的次數，預設100次。（暫未再現ERROR 1129，測試如果出現 ，增加此引數值）

服務端

connect_timeout

10

在獲取連線請求時的超時。預設值10秒。注意測試時的超時問題。

服務端

interactive_timeout

1800

伺服器關閉互動連線之前等待活動的時間（秒）。為避免空連線長時間佔用服務暫定30分鐘關閉（預設是8小時）。

服務端

wait_timeout

1800

伺服器關閉非互動連線之前等待活動的時間（秒）。為避免空連線長時間佔用服務暫定30分鐘關閉（預設是8小時）。

服務端

thread_cache_size

64

快取空閒執行緒，如果是短連線很多，需要增加。

服務端

long_query_time

5

慢查詢的執行時間上限（秒），預設會生成*-slow.log檔案

服務端

read_buffer_size

1M

執行順序讀分配快取區，預設128K

服務端

sort_buffer_size

1M

執行排序是使用分配快取區

服務端

join_buffer_size

2M

執行聯合查詢分配快取區

服務端

read_rnd_buffer_size

1M

針對order by查詢分配的快取區

服務端

 

10.2.1  啟動命令

mysqld_safe  --defaults-file= my.cnf；

10.3  資料模型轉換

10.3.1  欄位型別對照

POWERDESIGN自動轉換PDM後，ORACLE版本和MYSQL版本的所有資料型別對照關係如下：

ORACLE模型

總數

mysql模型

總數

CHAR

5

char

5

NUMBER

6493

numeric

6493

DATE

1574

date

1574

NVARCHAR2

1

varchar

3155

VARCHAR2

3154

BLOB

8

longblob

8

MYSQL版本從節約儲存空間的角度，numeric型別可以分成tinyint（佔1個位元組），smallint（佔2個位元組），mediumint（佔3個位元組），int（佔4個位元組），bigint（佔8個位元組），而numeric是每個資料佔1個位元組；日期欄位由於業務大多數都會精確到時分秒，選擇datetime型別；字串型別選擇varchar；由於BLOB 在ORACLE版本中最大能支援4GB，選擇longblob型別。

  轉換後的MYSQL版本的資料型別明細（與ORACLE版本對應關係 ）如下：

ORACLE模型

總數

mysql模型

總數

BLOB

8

longblob

8

CHAR(1)

3

char(1)

3

CHAR(8)

2

char(8)

2

DATE

1574

datetime

1574

NUMBER(1)

370

tinyint

370

NUMBER(2)

503

tinyint

503

NUMBER(3)

23

smallint

23

NUMBER(3,2)

1

numeric(3,2)

1

NUMBER(4)

1977

smallint

1977

NUMBER(5)

26

mediumint

26

NUMBER(6)

67

mediumint

67

NUMBER(6,2)

1

numeric(6,2)

1

NUMBER(6,4)

4

numeric(6,4)

4

NUMBER(7)

6

int

6

NUMBER(7,2)

4

numeric(7,2)

4

NUMBER(8)

315

int

315

NUMBER(8,4)

1

numeric(8,4)

1

NUMBER(9)

1524

int

1524

NUMBER(9,5)

1

numeric(9,5)

1

NUMBER(10)

95

bigint

95

NUMBER(10,5)

6

numeric(10,5)

6

NUMBER(11)

2

bigint

2

NUMBER(11,5)

10

numeric(11,5)

10

NUMBER(12)

326

bigint

326

NUMBER(12,2)

2

numeric(12,2)

2

NUMBER(12,5)

17

numeric(12,5)

17

NUMBER(14)

92

bigint

92

NUMBER(15)

1083

bigint

1083

NUMBER(16)

2

bigint

2

NUMBER(17)

4

bigint

4

NUMBER(19)

22

numeric(19,0)

22

NUMBER(20)

2

numeric(20,0)

2

NUMBER(24)

1

numeric(24,0)

1

NUMBER(25)

2

numeric(25,0)

2

NUMBER(32)

4

numeric(32,0)

4

NVARCHAR2(256)

1

varchar(256)

1

VARCHAR2(1)

23

varchar(1)

23

VARCHAR2(2)

12

varchar(2)

12

VARCHAR2(3)

16

varchar(3)

16

VARCHAR2(4)

31

varchar(4)

31

VARCHAR2(5)

20

varchar(5)

20

VARCHAR2(6)

3

varchar(6)

3

VARCHAR2(7)

107

varchar(7)

107

VARCHAR2(8)

64

varchar(8)

64

VARCHAR2(9)

10

varchar(9)

10

VARCHAR2(10)

65

varchar(10)

65

VARCHAR2(11)

2

varchar(11)

2

VARCHAR2(12)

9

varchar(12)

9

VARCHAR2(14)

26

varchar(14)

26

VARCHAR2(15)

45

varchar(15)

45

VARCHAR2(16)

50

varchar(16)

50

VARCHAR2(17)

1

varchar(17)

1

VARCHAR2(18)

3

varchar(18)

3

VARCHAR2(20)

118

varchar(20)

118

VARCHAR2(22)

1

varchar(22)

1

VARCHAR2(24)

13

varchar(24)

13

VARCHAR2(25)

1

varchar(25)

1

VARCHAR2(30)

23

varchar(30)

23

VARCHAR2(32)

301

varchar(32)

301

VARCHAR2(40)

12

varchar(40)

12

VARCHAR2(48)

4

varchar(48)

4

VARCHAR2(50)

27

varchar(50)

27

VARCHAR2(64)

349

varchar(64)

349

VARCHAR2(80)

2

varchar(80)

2

VARCHAR2(100)

43

varchar(100)

43

VARCHAR2(124)

2

varchar(124)

2

VARCHAR2(128)

161

varchar(128)

161

VARCHAR2(200)

41

varchar(200)

41

VARCHAR2(255)

25

varchar(255)

25

VARCHAR2(256)

399

varchar(256)

399

VARCHAR2(400)

3

varchar(400)

3

VARCHAR2(500)

8

varchar(500)

8

VARCHAR2(512)

181

varchar(512)

181

VARCHAR2(1024)

431

varchar(1024)

431

VARCHAR2(2000)

443

varchar(2000)

443

VARCHAR2(2048)

21

varchar(2048)

21

VARCHAR2(3072)

2

varchar(3072)

2

VARCHAR2(4000)

56

varchar(4000)

56

 

10.3.2  約束屬性

Powerdesigner工具轉換成MYSQL版本時，約束屬性會自動按MYSQL方式建立，有少數差異，見下面差異要點。

10.4  差異要點

MYSQL版本資料模型建立需要注意以下要點：

1         在19位以下 整數，需要轉換成相應的整型（tinyint，smallint，mediumint，int，bigint）來節約儲存空間。

2         日期型別（DATE）需要指定為datetime。

3         註釋語句，POWERDESIGNER工具會自動轉換成MYSQL版本語句。

4         PDM中的一些欄位（填入序列號值），POWERDESIGNER工具會自動新增auto_increment（類序列號屬性）。需要手工去除這種屬性，業務採用框架推出的序列號使用方案。

5         datetime的預設值是now()，不能使用sysdate()。

6         建立主鍵所指定的資料型別所佔儲存不能太大，會報錯。比如使用varchar(512)來建立組合主鍵。mysql5.6版本不能超過767位元組（UTF8字符集――3倍指定欄位型別）。

7         建立索引同主鍵一樣。mysql5.6版本不能超過767位元組（UTF8字符集――3倍指定欄位型別）。

8         欄位的註釋不要超過1024個字元（mysql5.6版本）。如果需要超長的註釋，建議新建一張描述註釋的表

9         UD使用者下單表的較大的varchar欄位（如：varchar(2000)）需要轉換為text或者blob（每行包括含帶有varchar，最大長度不能大於65535位元組）。

10     特別注意合理設計表索引和主鍵，這會影響DML語句執行是行鎖還是表鎖？MYSQL是基於索引條件檢索資料時加行鎖，否則加表鎖（這一點與ORACLE不同）。

 

 

 

 

11    常見錯誤（客戶端）

11.1  ERROR 1040

超過最大連線數。需要增加max_connections引數的值。

11.2  ERROR 1041

記憶體不足。需要檢查配置中的記憶體設定（特別注意：innodb_buffer_pool_size）。一般不 要超過實體記憶體的80%

11.3  ERROR 1042

無效的主機名。常見的連線方式為：mysql -u test -p  -h IP。注意mysql.user表中是否有對應原主機的連線。

11.4  ERROR 1044

資料庫使用者許可權不足。需要管理員增加許可權。

11.5  ERROR 1045

資料庫伺服器/資料庫使用者名稱/資料庫名/資料庫密碼錯誤。分別仔細檢查使用者名稱和密碼和資料庫名是否正確？

11.6  ERROR 1046

沒有選擇資料庫。任何SQL操作前需要先選擇資料庫。客戶端連線時使用（use database_name）。

11.7  ERROR 1049

資料庫不存在。

11.8  ERROR 1050

表已經存在。建立的表已經存在。

11.9  ERROR 1051

表不存在。

11.10      ERROR 1054

欄位不存在。

11.11      ERROR 1064

不支援的SQL語法。出現此種錯誤，需要仔細檢查語句，錯誤提示一般會顯示錯誤的位置

11.12      ERROR 1130

沒有連線資料庫的許可權。需要增加許可權。

11.13      ERROR 1133

資料庫使用者不存在，需要先建立使用者。

11.14      ERROR 1149

SQL語句錯誤。

11.15      ERROR 1205

加鎖超時。需要檢視系統中鎖。一般使用show processlist 可以看到執行中的鎖表語句。

11.16      ERROR 1264

欄位溢位報錯。

12    MYSQL管理工具(針對WINDOWS )

12.1  MySQL Workbench

它是MYSQL推出，可以操作MYSQL，同時可以設計的ER/資料庫建模工具。使用它設計和建立新的資料庫圖示，建立資料庫文件，以及進行復雜的MySQL 遷移（最新版本不支援oracle到mysql）。

12.2  SQLYOG

第三方工具，易於使用的、快速而簡潔的圖形化管理MYSQL資料庫的工具。

 

12.3  SQL DEVELOPER

它是ORACLE推出，支援同時操作 oracle,mysql資料庫。

 

13    MYSQL最佳化工具

13.1  Explain

語法：explain select …..

    Select_type:查詢型別，主要以下幾種值

       SIMPLE:除子查詢或者 union之外的其他查詢。

       UNION： 第二個查詢開始的所有select，第一個是PRIMARY；

       PRIMARY:子查詢中的最外層查詢，不是主鍵查詢。

    TYPE:查詢方式，

       ALL：全表

       CONST:讀常量，實際會只匹配一條記錄

       Index:全索引掃描。

       Rang:索引範圍掃描。

       Ref:索引引用查詢。

       Unique_subquery:子查詢返回結果欄位組合 是主鍵或唯一 約束。

  Index_merge:查詢中同時使用兩個（或更多）的索引，對索引合併後再讀取資料。

Eq_ref:最多隻匹配一條，一般指主鍵或唯一約束。

    Possible_keys:可以使用的索引，如果是NULL，表示沒有使用到索引。

    KEY:從Possible_keys中選擇的索引，如果沒有是NULL。

13.2       PROFIE

       Profiling:查詢診斷分析工具，可以知道CPU，IO，SWAP等資訊。

    操作如下：

    mysql>set profiling=1;

    mysql>select count(*) from test;

    mysql>show profile cpu,block io for query 1;

14    安裝部署

14.1  單資料庫安裝

14.1.1  安裝程式包

服務端： rpm -ivh MySQL-server-5.6.13-1.rhel5.x86_64.rpm

客戶端： rpm -ivh MySQL-client-5.6.13-1.rhel5.x86_64.rpm

14.1.2  驗證

       mysql --help

      

14.1.3  啟動資料庫

·         建立使用者

groupadd mysql

useradd -g mysql mysql

·         建立my.cnf檔案（注意mysql預設查詢的my.cnf路徑，以避免讀取錯誤配置檔案。

[mysqld]

datadir = /data01/mysql/data

port = 3306

server_id = 1

log-bin = /data01/mysql/log/master-bin

log-bin-index=/data01/mysql/log/master-bin.index

log-error=/data01/mysql/log/mysql_error.log

·         安裝資料庫：

mysql_install_db --basedir=/usr --datadir=/data01/mysql/data --user=mysql --defaults-file=/home/mysql/my.cnf

·         啟動資料庫

mysqld_safe --defaults-file=/home/mysql/my.cnf &

·         停止資料庫

mysqladmin -h127.0.0.1 -P 3306 -uroot -p shutdown   (安裝後的初始密碼為空)

下載服務：  

http://dev.mysql.com/downloads/mysql/5.6.html

 

 

14.2  主從部署

14.2.1  安裝程式包

同單資料庫安裝

14.2.2  主服務安裝

·         建立my.cnf配置檔案 （注意替換其中的路徑）

[mysqld]

datadir = /data01/mysql/data

pid-file=/home/mysql/my_etc/master.pid

socket=/home/mysql/my_etc/master.sock

log-error=/data01/mysql/log/mysql_error.log

log-bin=/data01/mysql/log/master-bin

log-bin-index=/data01/mysql/log/master-bin.index

tmpdir=/data01/mysql/tmpdir

innodb_data_home_dir=/data01/mysql/data

innodb_log_group_home_dir=/data01/mysql/log

 

character-set-server=utf8

 

user=mysql

server_id=1

port=3400                             ##### master db port

binlog-format=ROW

binlog-rows-query-log-events=1

sync_binlog=1

log-slave-updates=true

max_binlog_size=1G

max_relay_log_size=1G

expire_logs_days=3

binlog_cache_size=1M

 

gtid-mode=on

enforce-gtid-consistency=true

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

master-verify-checksum=1

slave-sql-verify-checksum=1

 

lower_case_table_names=1

transaction_isolation =read-committed

innodb_file_per_table=on

innodb_buffer_pool_size =256M   ###### buffer pool

innodb_buffer_pool_instances=8

innodb_flush_log_at_trx_commit=1

innodb_data_file_path=ibdata1:100M;ibdata2:100M:autoextend

innodb_file_io_threads=4

innodb_thread_concurrency=12

innodb_additional_mem_pool_size=64M

innodb_log_buffer_size = 8M

innodb_log_file_size=256M

innodb_log_files_in_group=3

innodb_max_dirty_pages_pct=80

innodb_flush_method=O_DIRECT

innodb_autoextend_increment = 128M

innodb_read_io_threads=16

innodb_write_io_threads=16

innodb_io_capacity=1000

innodb_io_capacity_max=4000

 

query_cache_type=0

max_connect_errors=10000

max_connections=2000

character-set-server=utf8

 

 

sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

 

 

·         安裝資料庫：

mysql_install_db --basedir=/usr --datadir=/data01/mysql/data --user=mysql --defaults-file=/home/mysql/my.cnf

 

·         啟動資料庫

mysqld_safe --defaults-file=/home/mysql/my.cnf &

·         複製資料庫檔案

打包datadir目錄下的檔案，複製到從資料庫的datadir目錄下面。

 

·         登入資料庫，建立複製使用者

mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'repl_user';

mysql>flush privileges;

 

 

14.2.3  從服務安裝

·         建立my.cnf配置檔案 （注意替換其中的路徑）

[mysqld]

datadir=/data01/mysql/data

pid-file=/home/mysql/my_etc/slave.pid

socket=/home/mysql/my_etc/slave.sock

log-error=/data01/mysql/log/slave_error.log

tmpdir=/data01/mysql/tmpdir

log-bin=/data01/mysql/log/slave-bin

log-bin-index=/data01/mysql/log/slave-bin.index

innodb_data_home_dir=/data01/mysql/data

innodb_log_group_home_dir=/data01/mysql/log

 

user=mysql

server_id=1001

port=4400

report-port=4400

binlog-format=ROW

binlog-rows-query-log-events=1

sync_binlog=1

log-slave-updates=true

max_binlog_size=1G

max_relay_log_size=1G

expire_logs_days=3

binlog_cache_size=1M

gtid-mode=on

enforce-gtid-consistency=true

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

master-verify-checksum=1

slave-sql-verify-checksum=1

read-only=1

 

lower_case_table_names=1

transaction_isolation =read-committed

innodb_file_per_table=on

innodb_buffer_pool_size =1024M   ################

innodb_buffer_pool_instances=8

innodb_flush_log_at_trx_commit=1

innodb_data_file_path=ibdata1:100M;ibdata2:100M:autoextend

innodb_file_io_threads=4

innodb_thread_concurrency=12

innodb_additional_mem_pool_size=64M

innodb_log_buffer_size = 8M

innodb_log_file_size=256M

innodb_log_files_in_group=3

innodb_max_dirty_pages_pct=80

innodb_flush_method=O_DIRECT

innodb_autoextend_increment = 128M

innodb_read_io_threads=16

innodb_write_io_threads=16

innodb_io_capacity=1000

innodb_io_capacity_max=4000

 

query_cache_type=0

max_connect_errors=10000

max_connections=2000

character-set-server=utf8

 

 

sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

 

·         安裝資料庫：

mysql_install_db --basedir=/usr --datadir=/data01/mysql/data --user=mysql --defaults-file=/home/mysql/my.cnf

 

·         啟動資料庫

mysqld_safe --defaults-file=/home/mysql/my.cnf &

 

·         登入資料庫，啟動複製功能

mysql>CHANGE MASTER TO MASTER_HOST='182.168.8.143', MASTER_PORT=3400, MASTER_USER='repl_user',MASTER_PASSWORD='repl_user', master_auto_position=1;

mysql>start slave;

mysql>show slave status\G

     出現如下資訊表示主從複製成功：

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29500582/viewspace-1354190/，如需轉載，請註明出處，否則將追究法律責任。

相關文章