Mysql和oracle数据库分析.docx

1、Mysql和oracle数据库分析 数据库分析1.MySQLMySQL是最受欢迎的开源SQL数据库管理系统，它由 MySQL AB开发、发布和支持。MySQL AB是一家基于MySQL开发人员的商业公司，它是一家使用了一种成功的商业模式来结合开源价值和方法论的第二代开源公司。MySQL是MySQL AB的注册商标。MySQL是一个快速的、多线程、多用户和健壮的SQL数据库服务器。MySQL服务器支持关键任务、重负载生产系统的使用，也可以将它嵌入到一个大配置(mass- deployed)的软件中去。与其他数据库管理系统相比，MySQL具有以下优势：(1)MySQL是一个关系数据库管理系统。(2

2、)MySQL是开源的。(3)MySQL服务器是一个快速的、可靠的和易于使用的数据库服务器。(4)MySQL服务器工作在客户/服务器或嵌入系统中。(5)有大量的MySQL软件可以使用。2.Oracle提起数据库，第一个想到的公司，一般都会是Oracle(甲骨文)。该公司成立于1977年，最初是一家专门开发数据库的公司。Oracle在数据库领域一直处于领先地位。 1984年，首先将关系数据库转到了桌面计算机上。然后，Oracle5率先推出了分布式数据库、客户/服务器结构等崭新的概念。Oracle 6首创行锁定模式以及对称多处理计算机的支持最新的Oracle 8主要增加了对象技术，成为关系对象数据库

3、系统。目前，Oracle产品覆盖了大、中、小型机等几十种机型，Oracle数据库成为世界上使用最广泛的关系数据系统之一。Oracle数据库产品具有以下优良特性。(1)兼容性Oracle产品采用标准SQL，并经过美国国家标准技术所(NIST)测试。与IBM SQL/DS、DB2、INGRES、IDMS/R等兼容。(2)可移植性Oracle的产品可运行于很宽范围的硬件与操作系统平台上。可以安装在70种以上不同的大、中、小型机上；可在VMS、DOS、UNIX、Windows等多种操作系统下工作。(3)可联结性Oracle能与多种通讯网络相连，支持各种协议(TCP/IP、DECnet、LU6.2等)。

4、(4)高生产率Oracle产品提供了多种开发工具，能极大地方便用户进行进一步的开发。(5)开放性Oracle良好的兼容性、可移植性、可连接性和高生产率使Oracle RDBMS具有良好的开放性。相同点1.都是关系型数据库管理系统2.目前最流行的数据库不同点优点a. mysql的优点是开源免费、简单易用、稳定可靠、易于扩展都是非常不错的。实际上，用MySQL支撑的google的数据库、Facebook的数据库、中移动飞信的数据库够大了吧，MySQL还是完成能够支撑一些大规模的应用的，尤其在互联网行业，MySQL的复制功能可以很方便的实现读写分离后的水平扩展b. oracle的优点是有甲骨文公司强

5、大的技术后盾，功能丰富和完善、安全、性能优异、人性化缺点a. mysql的缺点就是相对功能少，面向的对象是一般的小型网站或者系统，大型数据库很少用mysql;b. oracle的缺点是对硬件的要求十分高，对管理人员的技术要求高。基本上是这样了。其他a. MySQL是用C+;Oracle系统核心应该是用C写,从Oracle9i开始，Oracle正式采用java做为主要程序开发语言，很多Oracle客户端应用程序都将采用java来编写.1. Oracle是大型数据库而Mysql是中小型数据库，Oracle市场占有率达40%，Mysql只有20%左右，同时Mysql是开源的而Oracle价格非常高。

6、2. Oracle支持大并发，大访问量，是OLTP最好的工具。3. 安装所用的空间差别也是很大的，Mysql安装完后才152M而Oracle有3G左右，且使用的时候Oracle占用特别大的内存空间和其他机器性能。1.组函数用法规则mysql中组函数在select语句中可以随意使用，但在oracle中如果查询语句中有组函数，那其他列名必须是组函数处理过的，或者是group by子句中的列否则报错eg：select name,count(money) from user；这个放在mysql中没有问题在oracle中就有问题了。2.自动增长的数据类型处理MYSQL有自动增长的数据类型，插入记录时不用

7、操作此字段，会自动获得数据值。ORACLE没有自动增长的数据类型，需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段。CREATE SEQUENCE序列号的名称(最好是表名+序列号标记)INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE;其中最大的值按字段的长度来定，如果定义的自动增长的序列号NUMBER(6)，最大值为999999INSERT语句插入这个字段值为：序列号的名称.NEXTVAL3.单引号的处理MYSQL里可以用双引号包起字符串，ORACLE里只可以用单引号包起字符串。在插入和修改字符串前必须做单引

8、号的替换：把所有出现的一个单引号替换成两个单引号。4.翻页的SQL语句的处理MYSQL处理翻页的SQL语句比较简单，用LIMIT开始位置，记录个数；PHP里还可以用SEEK定位到结果集的位置。ORACLE处理翻页的 SQL语句就比较繁琐了。每个结果集只有一个ROWNUM字段标明它的位置，并且只能用ROWNUM80。以下是经过分析后较好的两种ORACLE翻页SQL语句(ID是唯一关键字的字段名)：语句一：SELECT ID, FIELD_NAME,. FROM TABLE_NAME WHERE ID IN ( SELECT ID FROM (SELECT ROWNUM AS NUMROW, ID

9、 FROM TABLE_NAME WHERE 条件1 ORDER BY 条件2) WHERE NUMROW 80 AND NUMROW 80 AND NUMROW SUBDATE(NOW()，INTERVAL 7 DAY)ORACLE找到离当前时间7天用 DATE_FIELD_NAME SYSDATE - 7;MYSQL中插入当前时间的几个函数是：NOW()函数以YYYY-MM-DD HH:MM:SS返回当前的日期时间，可以直接存到DATETIME字段中。CURDATE()以YYYY-MM-DD的格式返回今天的日期，可以直接存到DATE字段中。CURTIME()以HH:MM:SS的格式返回当前

10、的时间，可以直接存到TIME字段中。例：insert into tablename (fieldname) values (now()而oracle中当前时间是sysdate7.空字符的处理MYSQL的非空字段也有空的内容，ORACLE里定义了非空字段就不容许有空的内容。按MYSQL的NOT NULL来定义ORACLE表结构，导数据的时候会产生错误。因此导数据时要对空字符进行判断，如果为NULL或空字符，需要把它改成一个空格的字符串。8.字符串的模糊比较MYSQL里用字段名like%字符串%，ORACLE里也可以用字段名like%字符串%但这种方法不能使用索引，速度不快，用字符串比较函数ins

11、tr(字段名，字符串)0会得到更精确的查找结果。9.程序和函数里，操作数据库的工作完成后请注意结果集和指针的释放。mysql与oracle 表字段定义比较说明mysqloracleVARCHAR变长字符串VARCHAR0-65535定义长度默认按字符长度计算，如果是GBK编码的汉字将占用2个字节VARCHAR21-4000VARCHAR是VARCHAR2的同义词定义默认按字节长度计算TINYINTSMALLINTMEDIUMINTINTBIGINT整数TINYINT(-128-127)SMALLINT(-32768-32767)MEDIUMINT(-8388608-8388607)INT(-2

12、147483648-2147483647)BIGINT(-9223372036854775808-9223372036854775807)无专用类型，TINYINT可以用NUMBER(3,0)代替SMALLINT可以用NUMBER(5,0)代替MEDUIMINT可以用NUMBER(7,0)代替INT可以用NUMBER(10,0)代替BIGINT可以用NUMBER(20,0)代替ORACLE中有SMALLINT,INT,INTEGER类型，不过这是NUMBER(38,0)的同义词DECIMALNUMERIC数值类型DECIMAL1-65,0-30NUMERIC是DECIMAL的同义词NUMBER

13、可表示数范围：1*10-130至1*10126NUMBER(1-38,-84-127)DECIMAL、NUMERIC、DEC是NUMBER的同义词FLOAT浮点型FLOAT(D,M)oracle10g开始增加BINARY_FLOAT类型10g以前无专用类型，可以用NUMBER代替ORACLE中有FLOAT和REAL类型，不过这是NUMBER的同义词DOUBLE双精度浮点型DOUBLE(D,M)oracle10g开始增加BINARY_DOUBLE类型10g以前无专用类型，可以用NUMBER代替ORACLE中有DOUBLE PRECISION类型，不过这是NUMBER的同义词BIT位类型BIT(1

14、-64)无DATETIME日期类型DATE，3字节存储，只存储日期，没有时间，支持范围是1000-01-01至9999-12-31TIME，3字节存储，只存储时间，没有日期，支持范围是-838:59:59至838:59:59DATETIME，占8字节存储，可表示日期和时间，支持范围是1000-01-01 00:00:00至9999-12-31 23:59:59TIMESTAMP，占4字节存储，可表示日期和时间，范围是1970-01-01 00:00:00至2038-01-19 03:14:07DATE类型7字节存储，可表示日期和时间，支持范围是-4712-01-01 00:00:00至9999

15、-12-31 23:59:59TIMESTAMP高精度日期5.6.4以前不支持小数秒精度5.6.4开始TIME,DATETIME,TIMESTAMP支持，最多可以6位小数秒，也就是微秒级别TIMESTAMP0-9占用空间7-11个字节，当小数秒精度为0时与DATE类型相同，小数秒最高精度可达9位，也就是纳精度YEAR年份YEAR，1字节存储，只存储年份，支持范围是1901至2155无对应类型，可以用NUMBER(3,0)代替CHAR定长字符串CHAR0-255，定义长度默认按字符长度计算，最大保存255字符CHAR1-2000定义默认按字节长度计算UNSIGNED无符号说明支持，用于数值类型不

16、支持CLOB大字符串，一般用于存储文本文件或超大描述及备注类信息TINYTEXT最大支持255个字节TEXT最大支持65535个字节MEDIUMTEXT最大支持16MB个字节LONGTEXT最大支持4GB字节字段不支持默认值支持(CLOB)oracle10g以前最大支持4GB个字节oracle10g开始最大支持4GB个数据块，数据块大小为2KB-32KBoracle还有一个LONG类型，是早期的存储大字符串类型，最大支持2GB字节,现已不推荐使用BLOB大二进制对象，一般用于存储文件或图片数据TINYBLOB最大支持255个字节BLOB最大支持65535个字节MEDIUMBLOB最大支持16M

17、B个字节LONGBLOB最大支持4GB字节字段不支持默认值支持(BLOB)oracle10g以前最大支持4GB个字节oracle10g开始最大支持4G个数据块，数据块大小为2KB-32KBoracle还有一个LONG RAW类型，是早期的存储二进制类型，最大支持2GB字节,现已不推荐使用BINARY二进制信息BINARY(0-255)，定长VARBINARY(0-65535)，变长RAW(1-2000)ENUM枚举类型ENUM(v1,v2,v3,.),最多65535个元素不支持SET集合类型SET(v1,v2,v3,.)，最多64个元素不支持NATIONAL CHAR国际化字符集类型，较少使用

18、无，MYSQL可以对每个字段指定字符编码支持NCHAR(1-2000)NVARCHAR(1-4000)NCLOBBFILE外部文件指针类型不支持支持文件大小最大4GB文件名称最长255字符自定义数据类型不支持支持XML类型不支持支持自增类型自动增长类型支持使用简单不支持一般使用SEQUENCE解决，用法与自增类型差别较大，使用较复杂，但能实现非常灵活的应用，包括字符自增主键、全局主键等等字段默认值表达式不支持函数和表达式TEXT和BLOB字段类型不支持默认值支持函数和表达式字段顺序修改支持，例如，把emp表的id字段顺序放在name字段后面：alter table emp modify col

19、umn id varchar(20) after name;不支持，只能重建表或字段虚拟字段虚拟字段是一个逻辑字段定义，其结果值通常是一个表达式，并在表中存储物理值，不占用空间，主要用于简化查询逻辑。比如有一个商品销售表有单价和数量两个字段，那可以建一个虚拟字段金额，其表达式=单价*数量不支持11g支持，例：create table sales( id number, quantity number, price number, amount GENERATED always as (quantity*price) virtual);表字段数限制INNODB最大1000个字段所有字段总定义长度

20、不能超过65535字节所有固定长度字段的总长度不超过半个数据块大小(数据块大小一般为16K)最大1000个字段 mysql与oracle 函数比较编号类别ORACLEMYSQL注释1数字函数round(1.23456,4)round(1.23456,4)一样：ORACLE：select round(1.23456,4) value from dualMYSQL：select round(1.23456,4) value2abs(-1)abs(-1)功能: 将当前数据取绝对值用法: oracle和mysql用法一样mysql: select abs(-1) valueoracle: select

21、 abs(-1) value from dual3ceil(-1.001)ceiling(-1.001)功能: 返回不小于 X 的最小整数用法:mysqls: select ceiling(-1.001) valueoracle: select ceil(-1.001) value from dual4floor(-1.001)floor(-1.001)功能: 返回不大于 X 的最大整数值用法:mysql: select floor(-1.001) valueoracle: select floor(-1.001) value from dual5Max(expr)/Min(expr)Max(

22、expr)/Min(expr)功能:返回 expr 的最小或最大值。MIN() 和 MAX() 可以接受一个字符串参数；在这种情况下，它们将返回最小或最大的字符串传下。用法:ROACLE: select max(user_int_key) from sd_usr;MYSQL: select max(user_int_key) from sd_usr;6字符串函数ascii(str)ascii(str)功能:返回字符串 str 最左边的那个字符的 ASCII 码值。如果 str 是一个空字符串，那么返回值为 0。如果 str 是一个 NULL，返回值也是 NULL.用法:mysql:select

23、 ascii(a) valueoracle:select ascii(a) value from dual7CHAR(N,.)CHAR(N,.)功能:CHAR() 以整数类型解释参数，返回这个整数所代表的 ASCII 码值给出的字符组成的字符串。NULL 值将被忽略.用法:mysql:select char(97) valueoracle:select chr(97) value from dual8REPLACE(str,from_str,to_str)REPLACE(str,from_str,to_str)功能: 在字符串 str 中所有出现的字符串 from_str 均被 to_str

24、替换，然后返回这个字符串.用法:mysql: SELECT REPLACE(abcdef, bcd, ijklmn) valueoracle: SELECT Replace(abcdef, bcd, ijklmn) value from dual9INSTR(sdsq,s,2)INSTR(sdsq,s)参数个数不同ORACLE: select INSTR(sdsq,s,2) value from dual（要求从位置2开始）MYSQL: select INSTR(sdsq,s) value（从默认的位置1开始）10SUBSTR(abcd,2,2)substring(abcd,2,2)函数名称不

25、同：ORACLE: select substr(abcd,2,2) value from dualMYSQL: select substring(abcd,2,2) value11instr(abcdefg,ab)locate(ab,abcdefg)函数名称不同：instr - locate（注意：locate的子串和总串的位置要互换）ORACLE: SELECT instr(abcdefg, ab) VALUE FROM DUALMYSQL: SELECT locate(ab, abcdefg) VALUE12length（str）char_length()函数名称不同：ORACEL: SE

26、LECT length(AAAASDF) VALUE FROM DUALMYSQL: SELECT char_length(AAAASDF) VALUE13REPLACE(abcdef, bcd, ijklmn)REPLACE(abcdef, bcd, ijklmn)一样：ORACLE: SELECT REPLACE(abcdef, bcd, ijklmn) value from dualMYSQL: SELECT REPLACE(abcdef, bcd, ijklmn) value14LPAD(abcd,14, 0)LPAD(abcd,14, 0)一样：ORACLE: select LPAD

27、(abcd,14, 0) value from dualMYSQL: select LPAD(abcd,14, 0) value from dual15UPPER(iv_user_id)UPPER(iv_user_id)一样：ORACLE: select UPPER(user_id) from sd_usr;MYSQL: select UPPER(user_id) from sd_usr;16LOWER(iv_user_id)LOWER(iv_user_id)一样：ORACLE: select LOWER(user_id) from sd_usr;MYSQL: select LOWER(user_id) from sd_usr;17控制流函数nvl(u.email_address, 10)IFNULL(u.email_address, 10)或ISNULL(u.email_address)函数名称不同（根据不同的作用进行选择）：ORACLE: select u.email_address, nvl(u.email_address, 10) value from sd_usr u (如果u.email_address=NULl,就在DB中用10替换其值)