文章目录
原文地址:
mysql数据库转移到oracle的经历
1、 字段类型的对应。
MySQL | oracle |
---|---|
longblob / blob | BLOB |
longtext / mediumtext | 没有text类型,是不是应该用LONG?但是oracle的一个表中不能出现两个LONG的字段。所以暂用VARCHAR2(2000)对应 |
timestamp | 暂用TIMESTAMP对应,但是oracle认识的timestamp是这样的:“insert into JJJ values (14, ‘天ss天’,‘18-8月-2016 10.42.12’)”;你写2016-8-18 10:42:12是无法写入oracle的。 |
datetime,类似于timestamp的格式 | oracle的DATE类型,只认识’14-6月 -16’,不认识’14-6月 -16 10:9:8’,不认识2016-06-09。,所以暂用TIMESTAMP对应 |
date,mysql的date类型如:2016-06-09 | oracle的DATE类型,只认识’14-6月 -16’,不认识’14-6月 -16 10:9:8’,不认识2016-06-09。 |
enum(‘3’,‘2’,‘1’,‘0’) | oracle没有枚举型,暂用VARCHAR2(10)对应。其中10这个长度看情况调节。 |
zerofill,比如int(11) unsigned zerofill,zerofill,用0补充左边不足的位数。int(11)就要补足11位。比如00000000001。 | 暂用NUMBER(11)对应。 |
float、decimal、double | 比如"double(x,y)" => “NUMBER(x,y)”,但需要验证可行性。 |
decimal(10,0) | decimal后面0位小数?暂用NUMBER(10,2) |
varchar(0) | 0代表啥?没有长度。oracle不充许0长度的字段。暂用VARCHAR2(1)对应。 |
varchar(5000) | oracle最大VARCHAR2(4000),4001都不行。暂用VARCHAR2(4000)对应。 |
2、表名和字段名的长度,oracle不能超过30,mysql无这个限制。
建表时,若超出,oracle会报错 : ORA-00972 identifier is too long
3、字段默认值问题。
-
MySQL创建表时,如果某字段定义为NOT
NULL,并且没有明确定义default值,则insert时可以不填该字段,mysql会自动给它填一个0或者空串或者其他符合该字段类型的空值。当然如果该字段有明确defaut值,则会填入这个default值。而oracle,创建表时,如果某字段定义为NOT
NULL,并且没有明确定义default值,则insert时必需在sql语句中写值,否则报错。当然如果该字段有明确的default值,则可以在insert中不填该字段的值。
-
Mysql的字段默认值有四种:
未定义、自定义的值(包括空串’‘和’0’)、CURRENT_TIMESTAMP、0000-00-00 00:00:00
select COLUMN_DEFAULT from
information_schema.columns的值,对于未定义的字段,得到NULL;就算这个字段是not null的,也是NULL。
Mysql的默认值(COLUMN_DEFAULT) | oracle默认值 |
---|---|
NULL | 不写default语句 |
自定义的值(包括空串’‘和’0’) | 写相应的default语句 |
CURRENT_TIMESTAMP | 暂时不写default语句 |
0000-00-00 00:00:00 | 暂时不写default语句 |
-
insert语句,对于mysql,values后面可以不写全所有字段,插入时mysql会自动插入默认值;但是oracle不行,报
ORA-00947: not enough values
,除非你在values前面加上明确的字段名。
比如beibei_trade3表有四个字段:
ttid
,shop_id
,name
,lastchanged
,insert into beibei_trade3 values ('', '', 'user')
//oracle报错not enough values
,mysql正确。insert into beibei_trade3 (ttid,shop_id,name) values ('', '', 'user')
//oracle正确,mysql正确。
4、索引问题。
-
oracle中的索引名不能重复,而Mysql可以。
如果重复,报错:
ORA-00955: name is already used by an existing object
。 -
索引名不同,字段也不可以一样。
5、自增字段问题。
(1)sequence + trigger 组合使用。这样每个表都要有一个 sequence + trigger。有人会认为过多使用触发器来实现Oracle字段自增,容易造成死锁或者阻塞。
(2)只用sequence,需要修改系统框架的底层insert的实现,要显示地调用序列。
6、表空间问题。
oracle最多支持32G的数据库。而我们系统经常会超过这个数。
7、语法问题
结尾分号:
- oracle的sql中不能用分号结尾,而mysql可以;oracle的sql中不能出现`(按键1的左边),但mysql中经常用它来括住表名。
双引号:
- oracle支持的sql语法中,表名和字段名,可以加双引号,也可以不加双引号,但不能加单引号;
- 字段的值如果字符串型,必需加单引号,但不能加双引号;
- 字段的值如果是数字,可以不加单引号,也可以加单引号,但一定不能加双引号。
比如:
$sql = 'select * from zzJkb where "type" = "23"';
//其中type是NUMBER
型的。
执行它会报错:General error: 904 OCIStmtExecute: ORA-00904: "23": 标识符无效 (ext\pdo_oci\oci_statement.c:148)
$sql = 'select * from zzJkb where "type" = 23';
或者$sql = 'select * from zzJkb where "type" = \'23\'';
就正确。
另外:
对于表名和字段名,加与不加双引号,oracle理解是有区别的。
$sql = "select * from zzJkb where code = 'abc'";
//这个表名zzJkb,如果不加双引号,oracle统统识别成大写,也就是ZZJKB。字段code识别为CODE。
$sql = "select name from zzJkb";
//name 和 zzJkb,统统识别成大写。如果oracle中只有小写的zzJkb表名,或者只有小写的name字段,则匹配不成功。
$sql = 'select "name" from "zzjkb"' ;
//name和zzjkb,都识别成双引号中一模一样的大小写形式,不会统一转成大写。如果你oracle表中name字段是大写的或者NaMe,就匹配不成功。
大小写:
- oracle中的表名和字段名,都是大小写敏感的。你可以在里面创建ZZJKB,同时创建ZZjkb或者zzJKB。
mysql的sql语句,一般不区分大小写。
比如:
mysql库里有一张zzjkb全小写的表名,$sql = "select * from zzjKB";
//mysql中可以执行成功。
如果一个mysql表中的字段名,有大小写混合的情况,那么select * 出来的结果数组的key是照搬过来,也是大小写混合。
但是oracle表select *的结果数组中,其key全都是小写,不管实际库中的字段是不是大写,得到的key全都是小写。
这样,就不允许一个表的字段名中,出现大小写混合,要统一成全小写。否则就会出现程序中得到的key不一致的情况,在mysql和oracle下表现不一致。
可能$onecolumn['Id']
在使用mysql时可用,那么在使用oracle时就不可用。
而我们程序中拼sql时,对于字段或表名,一般是不加双引号的(因为一开始支持的是mysql),这样,在使用oracle时,会被识别成大写。(所以oracle创建的表名字段名,统统要大写;mysql的表名和字段名,统统要小写)
关键字:
- user
拼接oracle的建表sql时,不能出现user这个关键字,会报错,比如
CREATE TABLE beibei_trade (
ttid NUMBER(38) DEFAULT '0' NOT NULL ,
user VARCHAR2(255) DEFAULT '' NULL ,
)
报错:ORA-00904: : invalid identifier
只能将这种oracle认定的关键字,改为双引号括起,比如 “user” 或者 "USER"
主键自增字段
- region表在mysql下,region_id是主键自增字段。要把该字段搞成不要自增。
因为在oracle下,如果某字段自增,会导致插入数据时,该字段不接受传入的值,而始终使用自增值。