Comparison and learning between MySQL and Oracle

Comparison and learning between MySQL and Oracle

The storage engine in MySQL is very rich, commonly used include InnoDB, MyISAM, and so on. I also read a lot of information and have some knowledge about it, from some reference books, I can see that the SQL section in MySQL is also swept away. I feel that there is no big difference with the SQL section in Oracle.
Because one of the major differences between InnoDB and MyISAM storage engines is the support for transactions, I also copied the idea in oracle.
I did two simple tests. I just wanted to give it a try. I was surprised by the test results.
I created two tables myisam_test and innodb_test to perform an insert operation and rollback. As expected, because Myisam does not support transactions, the insert operation will not roll back, innodb should be available.
Mysql> create table myisam_test (id int) engine = myisam;
Query OK, 0 rows affected (0.01 sec)
Mysql> insert into myisam_test values (1 );
Query OK, 1 row affected (0.00 sec)

Mysql> select * from myisam_test;
+ ------ +
| Id |
+ ------ +
| 1 |
+ ------ +
1 row in set (0.00 sec)

Mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

Mysql> select * from myisam_test; -- this result is expected.
+ ------ +
| Id |
+ ------ +
| 1 |
+ ------ +
1 row in set (0.00 sec)

I was surprised to try the InnoDB part again. I feel that my cognition has encountered problems.
Mysql> create table innodb_test (id int) engine = innodb;
Query OK, 0 rows affected (0.03 sec)

Mysql> insert into innodb_test values (1 );
Query OK, 1 row affected (0.01 sec)

Mysql> select * from innodb_test;
+ ------ +
| Id |
+ ------ +
| 1 |
+ ------ +
1 row in set (0.00 sec)

Mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

Mysql> select * from innodb_test;
+ ------ +
| Id |
+ ------ +
| 1 |
+ ------ +
1 row in set (0.00 sec)

Compared to oracle, because these storage engines are not available, you can use ora_test as the test result.
SQL> create table ora_test (id number );
Table created.
SQL> insert into ora_test values (1 );
1 row created.

SQL> select * from ora_test;
ID
----------
1
SQL> rollback;
Rollback complete.
SQL> select * from ora_test;
No rows selected

As a result, Oracle implicitly starts transactions. For the dml statement in a session, the transaction is automatically started and a TX lock is added.
In MySQL, start transaction is required to display the declaration.
In fact, the declared transaction can also be displayed in Oracle, And the savepoint can be used for it. This is similar to MySQL.
Both of them are open for the transaction commit method and can be flexibly selected. In oracle, you can use sqlplus or driver connection settings.
SQL> set autocommit off;
SQL> show autocommit
Autocommit OFF

In MySQL, autocommit is 1 or 0. 1 indicates automatic submission, and 0 indicates automatic submission is disabled.
Mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

Therefore, the revelation of this case is that many details only need to be tested by yourself. Do not take it for granted. Many ideas cannot be taken for granted. They may be available in this place and may not be available in other places, these details cannot be ignored.