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	相同	alter table t_test add c_c int;	关键字column可选：alter table t_test add column c_c int;
删除列	oracle	相同	alter table t_test drop column c_c;
删除列	mysql	相同	alter table t_test drop column c_c;
重命名列	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	相同	alter table t_f drop partition p3;
字段注释	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	相同	drop table t_test;
删除表（判断表存在）	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	相同	insert into t_test values(101,'c101');	"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	相同	insert into t_test values(100,null);
多行数据	oracle	不同		不支持
多行数据	mysql	不同	insert into t_test values(4,'dd'),(5,'ee'),(6,'ff');	采用逗号分隔，直接插入多行赋值
set语句	oracle	不同		不支持
set语句	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	相同	update t_test set c_a=11,c_b='c11' where c_a=10;
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 );
select子句	mysql	相同
case when子句	oracle	相同	update t_test set c_b=case c_a when 1 then 'test1' when 2 then 'test2' else 'testother' end ;
case when子句	mysql	相同
limit子句	oracle	不同
limit子句	mysql	不同	update t_test set c_b='test' limit 1;
select子查询	oracle	不同
select子查询	mysql	不同
order by子句	oracle	不同
order by子句	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	不同
inner join子句	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	相同	select length(c_a)from t_test;
取指定长度	oracle	相同	select substr(c_b,1,2)from t_test;
取指定长度	mysql	相同	select substr(c_b,1,2)from t_test;
字符串转数值	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	varchar	3155
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://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

ITPub博客

ORACLE与MYSQL代码开发差异

1 概述

2 SQL开发

2.1 DDL (数据定义语言)

2.1.1 CREATE

2.1.1.1 CREATE TABLE

2.1.1.2 CREATE INDEX

2.1.2 ALTER

2.1.2.1 ALTER TABLE

2.1.2.2 ALTER INDEX

2.1.3 DROP

2.1.3.1 DROP TABLE

2.1.3.2 DROP INDEX

2.1.4 TRUNCATE

2.1.5 COMMENT

2.1.6 RENAME

2.2 DML (数据操作语言)

2.2.1 SELECT

2.2.2 INSERT

2.2.3 UPDATE

2.2.4 DELETE

2.3 DCL (数据控制语言)

2.4 GRANT

2.5 REVOKE

2.6 TCL (数据定义语言)

2.6.1 COMMIT

2.6.2 ROLLBACK

3 PL/SQL（SQL/PSM）

4 函数（FUNCTION）

5 过程（PROCEDURE）

6 数据类型

6.1

6.2 DATE

7 函数使用

7.1 日期和时间相关

7.2 字符串函数

7.3 类型转换函数

7.4 其它函数

8 Mysql常用工具

8.1 Mysql

8.2 Mysqladmin管理工具

8.3 Mysqlbinlog日志管理

8.4 Mysqlimport数据导入工具(文本格式)

8.5 Mysqldump数据导出工具

8.6 Perror 查看错误

9 其它补充

10 ORACLE向MYSQL迁移方案

10.1 MYSQL创建数据库

10.2 MYSQL关键参数

10.2.1 启动命令

10.3 数据模型转换

10.3.1 字段类型对照

10.3.2 约束属性

10.4 差异要点

11 常见错误（客户端）

11.1 ERROR 1040

11.2 ERROR 1041

11.3 ERROR 1042

11.4 ERROR 1044

11.5 ERROR 1045

11.6 ERROR 1046

11.7 ERROR 1049

11.8 ERROR 1050

11.9 ERROR 1051

11.10 ERROR 1054

11.11 ERROR 1064

11.12 ERROR 1130

11.13 ERROR 1133

11.14 ERROR 1149

11.15 ERROR 1205

11.16 ERROR 1264

12 MYSQL管理工具(针对WINDOWS )

12.1 MySQL Workbench

12.2 SQLYOG

12.3 SQL DEVELOPER

13 MYSQL优化工具

13.1 Explain

13.2 PROFIE

14 安装部署