ORACLE与MYSQL代码开发差异

时间:2021-09-06 01:31:39
1         概述

参照的版本:oracle 11g,mysql 5.6.10

去oracle化依照如下原则:

·         兼容 oraclemysql

·          

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 joinwhere条件代替

获取固定值

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/SQLSQL/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)typesecond,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) s1x个位置开始,共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<out_test.txt< span="">

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    ORACLEMYSQL迁移方案

针对迁移方案,从创建数据库,数据模型转换(主要在字段类型,约束属性等)来说明,差异要点。

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位以下 整数,需要转换成相应的整型(tinyintsmallintmediumintintbigint)来节约存储空间。

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 迁移(最新版本不支持oraclemysql)。

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:查询诊断分析工具,可以知道CPUIOSWAP等信息。

    操作如下:

    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://mirror.cogentco.com/pub/mysql/MySQL-5.6/  

http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.6/

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