参照的版本: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 |
||||
缺省值约束 |
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))或者 |
|
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) |
|
mysql |
CREATE TABLE t_test (c_a INT,c_b VARCHAR(255),sts_date DATETIME) |
分区字段是日期时,需要使用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) |
创建分区索引 |
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) |
|
||
增加新的分区 |
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 或者 |
支持可选择增加关键字as |
||
字段别名 |
oracle |
不同 |
select c_a a,c_b b from t_test; |
|
mysql |
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; |
一般使用第一种写法。 |
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(+); |
支持可选(+) |
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; |
支持可选 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; |
支持可选(+) |
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; |
支持可选 using语句 |
||
并集(联合) |
oracle |
相同 |
select * from t_test union 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; 或者 |
使用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;或者 |
可以使用'|'代替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<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 ORACLE向MYSQL迁移方案
针对迁移方案,从创建数据库,数据模型转换(主要在字段类型,约束属性等)来说明,差异要点。
10.1 MYSQL创建数据库
10.2 MYSQL关键参数
MYSQL服务端启动时初始化的静态配置参数,下表列出针对业务开发受影响的参数:
静态参数 |
推荐值 |
说明 |
范围 |
lower_case_table_names |
1 |
1 数据字典以小写字母存放表名称;文件系统也写小写字母存放文件名。 |
服务端 |
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文件 |
服务端 |
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://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