The difference between Oracle and Mysql

Posted by endlyss on Thu, 20 Jan 2022 02:34:07 +0100

1, Main differences

1. Software specification:

Oracle is a large database, which occupies a lot of memory;
Mysql is a lightweight database, which is lightweight and occupies less memory;

ps: I pulled the image of the two with docker. mysql is 545M in size, while oracle11g requires 6.85G

2. Expenses:

Oracle charges. If you have a problem, you can find the customer service;
Mysql is open source and free of charge. If you have any problems, you should solve them yourself

3. Data volume

There is no problem with the amount of Oracle single table data in the scale of tens of millions or even hundreds of millions;
The data volume of Mysql single table should be controlled at the level of millions;

4. Concurrency

Oracle uses row level locks and does not rely on indexes to lock a row. The granularity of lock resources is small, so the concurrency is high
Only InnoDb supports row locks in Mysql, and row locks can only be used by relying on indexes, otherwise table locks will be used, so the concurrency is low

5. Persistence

Oracle has online logs to recover data loss caused by unexpected downtime
If Mysql goes down unexpectedly, assuming that the data has not been written from the cache to the disk, data loss will occur

6. Master slave replication

Oracle supports multi machine disaster recovery. When there is a problem with the primary database, it can automatically switch to the primary database
Mysql master-slave replication is relatively simple. When there is a problem with the master database, you need to manually configure which is the master database

7. Transaction isolation level

Oracle defaults to the isolation level of read committed, which will cause the problem of non repeatable read + unreal read
Mysql is the isolation level of repeatable read by default. There will only be unreal reading

8. Authority

oracle's permission has the concept of user group;
mysql permissions, without the concept of user group;

9. Different usage

2, Examples of usage differences

2.1 database structure hierarchy

oracle tables are created under each user;
mysql tables are created under each database;

2.2 field type

Common field types are different

Common field types in oracle: Number, character varchar(2)
Field types commonly used in mysql: numeric int, double, character varchar

Expansion Description:

oracle's varchar is generally not used. It is the sql standard type and allows empty strings. oracle has developed a type called varchar2, which does not allow empty strings, and can be compatible with future oracle versions
Although oracle has float and binary_float,binary_double is different from number:
(1) First of all, scale is not allowed to be specified for these three types, that is, the number of decimal places. I tried these fields. Scale is changed to 2. Click Save in navicat, or it changes back to 0;
(2) Secondly, binary_float,binary_double, these two have smaller storage space (number needs 22 bytes at most, while they need 5 and 9 bytes respectively)

The field types of time are different

The time type of oracle is date or timestamp, including month, day, hour, minute and second
mysql time types include not only datetime and timestamp, but also date that only contains month, year and day, and time that only contains hours, minutes and seconds

2.3 processing of ID increment

oracle needs to manually create a new sequence + trigger;

-- Step 1: create sequence( ps: You can also navicat (create)
create sequence SEQ_T_LOCALOBTMIND
-- Verify whether the sequence can get the value successfully
select SEQ_T_LOCALOBTMIND.nextval from dual

-- Step 2: set trigger( ps: Please ensure that the table name and fields are capitalized, otherwise the trigger execution will fail)
create or replace trigger T_LOCALOBTMIND_INSERT_ID
before insert on "T_LOCALOBTMIND" for each row
begin
	select SEQ_T_LOCALOBTMIND.nextval into:NEW.ID from dual;
end;

-- Step 3: Test
INSERT INTO "T_LOCALOBTMIND"("DDATETIME", "OBTID", "WDIDF") VALUES (TO_DATE('2021-06-03 08:22:04', 'SYYYY-MM-DD HH24:MI:SS'), 'G1121', '11.34');

mysql has its own auto increment function

2.4 paging

oracle needs to use rownum and sub query to do it;

-- Check No. 10-20 Records. 10. 20 numbers represent the line number of the record
select * from (
	SELECT emp.*, rownum as rowno from emp
)t_target
where rowno >= 10 and rowno < 20

mysql can be accessed directly through limit

-- Check No. 10-20 Records. The first 10 indicates the starting subscript, and the second 10 indicates how many records to take
SELECT * FROM `tb_user`
limit 10, 10

2.5 time processing

Time subtraction

For oracle, for the first 7 days of calculation, the time is reduced by 7. For the first 1 hour of calculation, the time is reduced by 1 / 24. The same is true for addition:

select TO_DATE('2021-05-30 15:51:20', 'yyyy-mm-dd hh24:mi:ss') -7 from dual;
-- 7 days before calculation, result: 2021-05-23 15:51:20

select TO_DATE('2021-05-30 15:51:20', 'yyyy-mm-dd hh24:mi:ss') - 1/24 from dual;
-- One hour before calculation, result: 2021-05-30 14:51:20

Different keywords are required 7 days or 1 hour before mysql calculation:

select date_sub('2021-05-30 21:00:40' ,interval 7 day)
-- 7 days before calculation, result: 2021-05-23 21:00:40

select date_sub('2021-05-30 21:00:40' ,interval 1 hour)
-- One hour before calculation, result: 2021-05-30 20:00:40

Time subtraction of timestamp type

The fields of timestamp type in oracle are subtracted, and the result is days, hours, minutes and seconds

SELECT MIN_T, MAX_T, MAX_T - MIN_T 
FROM "T_TIMESTAMP"
-- Result: 2021-07-06 20:29:20.000000	2021-07-07 20:29:22.000000	+000000001 00:00:02.000000

In mysql, the values of two timestamp types are subtracted. If you want to know how many days and seconds are different, you need to use the function

SELECT min_t, max_t, TIMESTAMPDIFF(second,min_t,max_t) FROM `t_timestamp`
-- result:
2021-07-03 21:01:22	2021-07-04 21:01:22	86400
2021-07-04 21:02:01	2021-07-04 21:02:06	5
2021-07-04 21:03:58	2021-07-04 21:04:01	3

String to time

oracle uses to_date() function, example

select TO_DATE('2021-05-30 15:51:20', 'yyyy-mm-dd hh24:mi:ss') from dual;
-- 24 Conversion of hour system, result: 2021-05-30 15:51:20

select TO_DATE('2021-05-30 11 pm:51:20', 'yyyy-mm-dd am hh12:mi:ss') from dual;
-- 12 Conversion of hour system, result: 2021-05-30 23:51:20

mysql string conversion time, using str_to_date() function, example:

select STR_TO_DATE('2021-05-30 15:51:20','%Y-%m-%d %H:%i:%s');
-- Result: 2021-05-30 15:51:20
-- tip: myql This format is not difficult to remember, Y,m,d,H,i,s，Just remember that two letters are capitalized

Time to string

oracle uses to_char() function, which is a powerful function that can format time and numbers

select to_char(current_date, 'yyyy-mm-dd hh24:mi:ss') from dual;
-- 24 Conversion of hour system, result: 2021-05-30 16:06:52

select to_char( TO_DATE('2021-05-30 15:51:20', 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd am hh12:mi:ss') from dual;
-- 12 Conversion of hour system, result: 2021-05-30 03 PM:51:20

mysql uses date_format() function

select DATE_FORMAT(current_timestamp,'%Y-%m-%d %H:%i:%s');
-- Result: 2021-05-30 16:10:14

Query current date + time

oracle example:

select CURRENT_TIMESTAMP from dual;
-- Result: 2021-05-30 16:19:10.640466 +08:00

select current_date from dual;
-- Result: 2021-05-30 16:16:54

select SYSDATE from dual;
-- Result: 2021-06-07 20:54:57

mysql also has current_timestamp is a query, but the format is very friendly. Example:

select CURRENT_TIMESTAMP;
-- Result: 2021-05-30 16:21:16

select now();
-- Result: 2021-05-30 16:17:41
-- I usually use this. It's relatively simple and good memory

select sysdate()
-- Result: 2021-06-07 20:55:38

2.6 character processing

quotation marks

oracle strings can only be wrapped in single quotes

select concat('%', 'G1120') from dual
-- result:%G1120

mysql strings can be enclosed in single quotation marks or double quotation marks

select concat("%", 'G1120')
-- result:%G1120

Empty string

Empty strings are not allowed in oracle. If a field in update is' ', you will find that the field value becomes null
In mysql, empty strings are allowed

Case sensitive

oracle creates tables in uppercase by default, which can be set to lowercase

-- If the table name is enclosed in quotation marks, it is case sensitive
CREATE TABLE "TUSER"."t_localobtmind2" (
"ID" NUMBER,
"DDATETIME" DATE,
"OBTID" VARCHAR2 ( 20 ),
"WDIDF" NUMBER ( 10, 2 )
)
-- Result: a t_localobtmind2 Table of

-- The default generated table name is uppercase
CREATE TABLE t_localobtmind3 (
"ID" NUMBER,
"DDATETIME" DATE,
"OBTID" VARCHAR2 ( 20 ),
"WDIDF" NUMBER ( 10, 2 )
)
-- Result: a T_LOCALOBTMIND3 Table of

mysql creates tables in lowercase by default

-- The default is lowercase. Whether you wrap it in quotation marks or not, the generated table name is lowercase
CREATE TABLE T_TYPE3 (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c_date` datetime DEFAULT NULL,
  `c_time` time DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
-- Result: a t_type3 Table of

CREATE TABLE `T_TYPE4` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c_date` datetime DEFAULT NULL,
  `c_time` time DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
-- Result: a t_type4 Table of

2.7 multi condition judgment

tip: although some places on the Internet say that oracle also has the use of if, it is only applicable to stored procedures and functions, not to the execution of a single sql

oracle uses the decode() function and supports the use of case when:

-- decode()function
select DECODE(2, 
1, 'wind speed',
2, 'rainfall',
3, 'Wave height',
null)
from dual;
-- result:
rainfall

-- case when Formula matching

select sal, CASE 
	when sal >= 5000 then 'High salary'
	when sal >= 2000 then 'Medium salary'
	WHEN sal > 800 THEN 'Low salary'
	ELSE 'other'
END as cn
from emp;
-- Result: 800	other
1600	Low salary
1250	Low salary
2975	Medium salary
...

-- case when How to write 2: value matching
select sal, CASE sal
	when 5000 then 'High salary'
	when 1600 then 'Medium salary'
	WHEN 800 THEN 'Low salary'
	ELSE 'other'
END as cn
from emp;
-- result:
800	Low salary
1600	Medium salary
1250	other
2975	other
...

For mysql conditional judgment, it seems that only case when is a more appropriate choice

-- mysql Also decode()Function, but it is used to encrypt a string
select @savePwd:= ENCODE('admin123','diy_key');
select DECODE(@savePwd,'diy_key');
-- result:(BLOB) 8 bytes
-- If in navicat Inside, select view-Text, can see is admin123
`
-- case when Formula matching:
set @var = 2001;
select CASE
	WHEN @var >= 5000 THEN 'High salary'
	when @var >= 2000 then 'Medium salary'
	when @var >= 800 then 'Low salary'
	ELSE 'other'
END as cn
-- Result: medium salary


-- case when How to write 2: value matching
-- select @var := 2;
set @var = 2;
select CASE @var
	WHEN 1 THEN
		'one'
	when 2 then
		'two'
	ELSE
		'other'
END as cn
-- Results: II

-- mysql Simple conditional judgment
set @factory=1;
select IF(@factory=1,'wind speed','Other elements')
-- result:
wind speed

2.8 line number display

oracle can use rownum to display:

select rownum, username from all_users;
-- result:
1	TUSER
2	TEST
3	BI
4	PM
5	SH
6	IX
7	OE
8	HR
9	SCOTT
10	OWBSYS_AUDIT

mysql can be implemented using variables

SELECT 
    @rowno:=@rowno+1 as rowno,
    t_data.* 
from 
    user_info t_data ,
    (select @rowno:=0) t_init;
-- result:
1	3	wanger	2		2021-01-31 22:02:18
2	5	aaa	2232		2021-01-31 22:02:17

2.9 decimal formatting

oracle uses to_char() function:

select to_char(211.125456,'99999999999990.99') from dual;
-- Where rounding is required, result: 211.13
select to_char(211.1,'99999999999990.99') from dual;
-- If the decimal places are less than 2, the result is 211.10
select to_char(0,'99999999999990.99') from dual;
-- In case of special value 0, result: 0.00

mysql uses the format() function:

select FORMAT(211.125,2);
-- Where rounding is required, result: 211.13
select FORMAT(211.1,2);
-- If the decimal places are less than 2, the result is 211.10
select FORMAT(0,2);
-- In case of special value 0, result: 0.00

2.10 query statement

The query of oracle must include the from example:

select current_date from dual;
-- Result: 2021-05-30 16:16:54

The from keyword is not mandatory for mysql queries, but it is also supported for querying the dual table. Example:
```
select now();
-- Result: 2021-05-30 16:17:41
```

2.11 implementation of row column conversion

oracle row to column conversion requires the help of connect by syntax and regexp_substr() function


select REGEXP_SUBSTR(codes, '[^,]+', 1, rownum) from(
    select 'G1120,G1121,G1122,G1123' as codes from dual
) t_base
connect by rownum <= LENGTH(codes) - LENGTH(REPLACE(codes, ',', '')) + 1
-- result:
G1120
G1121
G1122
G1123

-- analysis: regexp_substr() This function can intercept the contents of a string through regular. The first parameter is the source string, the second parameter is the regular expression, that is, the interception rule, the third parameter represents the matching regular expression from the first character, and the fourth parameter represents the matching content
-- result:
G1120
G1121
G1122
G1123


-- Regular string segmentation
SELECT 
REGEXP_SUBSTR ('G1120,G1121,G1122,G1123', '[^,]+', 1,2) code
from dual 
-- result:
G1121
-- Returns the content of the second match


-- Generate digital sequence result set
select rownum from dual
connect by rownum <= 3;
-- result:
1
2
3

The row to column conversion of mysql requires the special usage of on < in join and substring_index() function

  select SUBSTRING_INDEX(subStr,',', -1) code from(
      select 
      codes, help_topic_id, SUBSTRING_INDEX(codes,',',help_topic_id + 1) subStr
      from
      (
          select 'G1120,G1121,G1122,G1123' as codes
      ) t_base
      inner join mysql.help_topic t_help
      on t_help.help_topic_id <= LENGTH(t_base.codes) - LENGTH(REPLACE(t_base.codes,',',''))
  ) t_with_id
  
  -- Idea:
  -- adopt mysql A row to column auxiliary table, tip: That table has 643 records at most
  -- LENGTH(REPLACE(t_code.codes,',','')Indicates how many characters are left except commas
  -- LENGTH(t_code.codes) - LENGTH(REPLACE(t_code.codes,',','')) Indicates how many commas there are
  -- first SUBSTRING_INDEX()Function, the obtained contents are:
  G1120
  G1120,G1121
  G1120,G1121,G1122,
  G1120,G1121,G1122,G1123
  -- the second SUBSTRING_INDEX()Function, the obtained contents are:
  G1120
  G1121
  G1122,
  G1123

3, Reference blog

https://blog.csdn.net/qq_35371323/article/details/95862866 (memory, cost, isolation level, etc. are mentioned)
https://www.cnblogs.com/xu-cceed3w/p/8824199.html (paging and concurrency are mentioned)
https://www.cnblogs.com/bailing80/p/11440927.html (the usage of id self increment and date is mentioned)
https://zhuanlan.zhihu.com/p/39651803 (database structure and field type are mentioned)
https://blog.csdn.net/andyguan01_2/article/details/86995794 (the best suggestions for single table are mentioned)
https://blog.csdn.net/weixin_39634067/article/details/113135429 (there are reasons why mysql may lose data)

Topics: Database MySQL Oracle

Programmer Think

The difference between Oracle and Mysql

1, Main differences

1. Software specification:

2. Expenses:

3. Data volume

4. Concurrency

5. Persistence

6. Master slave replication

7. Transaction isolation level

8. Authority

9. Different usage

2, Examples of usage differences

2.1 database structure hierarchy

2.2 field type

2.3 processing of ID increment

2.4 paging

2.5 time processing

2.6 character processing

2.7 multi condition judgment

2.8 line number display

2.9 decimal formatting

2.10 query statement

2.11 implementation of row column conversion

3, Reference blog

Hot Topics