The difference between Oracle and Maria

Table of contents

sysdate

sequence

pagination

concatenate strings

decode and if

sysdate:

The way it is written in oracle is:

select SYSDATE();
select now();
select SYSDATE() from dual;
select now() from dual;

Note: from dual must be added, otherwise an error will be reported, only sysdate is supported; mysql is different, mysql uses the sysdate() function.

Writing in mysql:

# Maria
CREATE SEQUENCE "seq_00" start with 1 minvalue 1 maxvalue 9223372036854775806 
increment by 1 cache 1000 nocycle

# Oracle
CREATE SEQUENCE "db_name"."seq_00" minvalue 1 maxvalue 
increment by 1 start with 1 cache 20 noorder nocycle nokeep global;

In mysql (1) from dual can be added, or not; (2) there are two functions, SYSDATE() and now() functions.

Reference: The difference between oracle and mysql get the current time

serial number:

The easiest to create, the same for Oracle and Maria:

CREATE SEQUENCE {{EJS0}} start with 1 minvalue 5 maxvalue 10 increment 
by 1 cache 1000 cycle ENGINE=InnoDB;

But the default generated creation script is different:

CREATE SEQUENCE {{EJS1}} start with 5 minvalue 1 maxvalue 10 increment 
by 1 cache 1000 nocycle ENGINE=InnoDB;

The difference is as follows:

  1. The field order of SQL is different, this does not affect;

  2. The case of SQL letters is different. Maria is all lowercase except CREATE SEQUENCE, which is uppercase; Oracle is all uppercase. Not sure if it has something to do with the client?

  3. The value of maxvalue is different. Maria is not a value such as 999…, but a 19-digit number; Oracle is a 28-digit number with 9;

  4. The number of caches is different. Maria, like MySQL, caches 1,000 serial numbers for backup; Oracle has 20;

  5. The number of control fields is different. Oracle has 3 more control fields than Maria, namely: noorder nokeep global;

Noorder is used in conjunction with the cache. When using the cache concurrently, if it is noorder, it will cause the numbers to skip one or several numbers, as the name suggests. Nokeep has tested various situations and found no impact, so I won’t go into too much depth here. global is not tested.

The same points are as follows:

  1. By default, the calculation starts from 1, that is to say, the minimum value is 1;

  2. The default step size, that is, the value added each time, is 1;

  3. The default is nocycle, that is, no cycle. If it is cycle, when the value reaches the maximum value, it will recycle from the set minimum value. Note here:

The initial value must be between the minimum and maximum values ​​(or equal to it), otherwise an error will be reported.

If it is nocycle, the script will report an error when the value exceeds the maximum value, but the reason for the error reported by the two is different:

Oracle’s error:

ORA-08004: sequence xxx exceeds MAXVALUE cannot be instantiated. . .

Maria’s error:

For complex creation methods, you can define the parameter values ​​yourself. I won’t go into details here. You can refer to other excellent articles.

for example:

# Maria checks the next sequence number and the current sequence number 
SELECT  NEXTVAL (seq_01);
 # Maria does not support this method, nor does Oracle. 
# SELECT CURRVAL(seq_01); 
SELECT seq_01.nextval;
 SELECT seq_01.currval;

SELECT seq_01.nextval from seq_01 ;
SELECT NEXTVAL(seq_01) from seq_01;
SELECT seq_01.currval from seq_01

SELECT seq_01.nextval from dual;
SELECT NEXTVAL(seq_01) from dual;
SELECT seq_01.currval from dual;
# SELECT CURRVAL(seq_01) from dual;

In fact, the above code execution will report an error, because we said above that the initial value must be between the minimum and maximum values.

The correct one is as follows:

# Oracle
SELECT seq_01.nextval from dual;
SELECT seq_01.currval from dual;

Check out the next serial number:

Maria supports many ways, as follows:

SELECT 
    ROUND,
    ID,
    NAME,
    AGE,
    LST_UPD_TIME
FROM (
    SELECT
        ROWNUM ROWN,
        T.*
    FROM (
        SELECT
            ID,
            NAME,
            AGE,
            LST_UPD_TIME
        FROM
            USER
        WHERE
            ID = 'id'
    ) T
WHERE
    ROWN = 2;

Oracle supports the following:

SELECT
    ID,
    NAME,
    AGE,
    LST_UPD_TIME
FROM
    USER
LIMIT 0, 10;

Regarding the serial number, I will take the time to write an article and talk about it.

Pagination:

Oracle common paging methods:

select CONCAT(id, userName, passWord) from {{EJS2}} u ;
select CONCAT_WS("-", id, userName, passWord) from {{EJS3}} u ;

Maria pagination SQL:

# Oracle. We assume that the password is purely numeric. 
select * from  user  where  id > 10  order  by to_number( password );

Concatenating strings:

Oracle is two vertical bars: ||.

Maria is concat(str1, str2), or concat(separator, str1, str2). Where str1/str2 is the string to be spliced; separator is the splicing character, that is, what to splicing. An example is as follows:

SELECT * from {{EJS4}} u WHERE passWord+0 < 123456;
SELECT * from {{EJS5}} u WHERE passWord < 123456;
SELECT '00123'=123  from {{EJS6}} u ;
SELECT '00123'=1234  from {{EJS7}} u ;
SELECT * FROM {{EJS8}} u order by passWord + 0;
ELECT * FROM {{EJS9}} u order by passWord;

The values ​​are as follows: 

1zhangsan123456
2lisi123456
3wangwu123456
4maliu123456

1-zhangsan-123456
2-lisi-123456
3-wangwu-123456
4-maliu-123456

String to Number

Oracle:

select to_char(sysdate, 'YYYY') from dual;
select to_char(sysdate, 'MM') from dual;
select to_char(sysdate, 'DD') from dual;

Oracle’s to_number() cannot be placed in the query item, that is, after the select and after the order by, but it cannot have the where condition, otherwise an error will be reported.

Maria:

SELECT date_format(SYSDATE() ,'%Y-%m-%d')  from dual;
SELECT date_format(SYSDATE() ,'%Y')  from dual;
SELECT date_format(SYSDATE() ,'%m')  from dual;
SELECT date_format(SYSDATE() ,'%d')  from dual;

In fact, Maria’s string and number comparisons are the same with or without quotation marks, and they can be compared directly, but when order by is not possible, it must be converted, otherwise there will be results that are not what we want. For example, 123456 is smaller than 456.

The values ​​are as follows:

1 zhangsan 123 2022-02-18 16:02:
32.000 2 lisi 456 2022-02-18 16:02:32.000

1 zhangsan 123 2022-02-18 16:02:
32.000 2 lisi 456 2022-02-18 16:02:32.000

1
1
1
1

0
0
0
0

1 zhangsan 123 2022-02-18 16:02:
32.000 2 lisi 456 2022-02-18 16:02:
32.000 3 wangwu 123456 2022-02-16 11:23:45.000
4 maliu 123456 1:2022-02-16 :45.000 

1 zhangsan 123 2022-02-18 16:02:
32.000 3 wangwu 123456 2022-02-16 11:23:45.000
4 maliu 123456 2022-02-16 11:23:45.000
2 lisi 456 16 :02-18 :32.000

We draw Maria’s conclusions as follows:

  1. Strings and numbers can be directly compared in size, whether there is a number 0 in front of them does not affect the comparison result;

  2. In the case of sorting and comparison, if it is to compare the size of the numbers, it must be converted, otherwise there will be a result that is not ours;

  3. The way to convert a string to a number is to directly + 0.

Get year, month, day

Oracle

select to_char(sysdate, 'YYYY') from dual;
select to_char(sysdate, 'MM') from dual;
select to_char(sysdate, 'DD') from dual;

Maria

SELECT date_format(SYSDATE() ,'%Y-%m-%d')  from dual;
SELECT date_format(SYSDATE() ,'%Y')  from dual;
SELECT date_format(SYSDATE() ,'%m')  from dual;
SELECT date_format(SYSDATE() ,'%d')  from dual;

decode and if

There is a decode function in Oracle. Its function is to take a value of 1 if the condition is satisfied, and a value of 2 if it is not satisfied. For details, please refer to this article: The usage of the decode function in Oracle

Also in Maria there is a similar syntax, that is if.

IF(condition, str1, str2);

Oracle’s trunc function and MySQL’s truncate function

Used to intercept time or value and return the specified value. For details, please refer to this article:

[trunc function usage]

And in Maria there is no trunc function. However, Maria has date_format for time operations. For date_format, please scroll up to see the usage of date_format, and there is a truncate function for numeric values. For details, please refer to this article:

Oracle’s trunc function and MySQL (Maria)’s truncate function

Leave a Comment

Your email address will not be published. Required fields are marked *