Note points in MySQL usage (i)

This article is intended for students who have previously engaged in enterprise-level application development because enterprise-class applications, especially financial enterprises, typically use Oracle or DB2 as a database.

compared with the above cost of database software, free MySQL has some limitations, as well as some of the characteristics of MySQL itself to pay attention to.

1. When creating foreign keys, MySQL only supports foreign keys of numeric types.  
This is really disgusting, because the primary key design for a table can have both a physical primary key and a logical primary key. A logical primary key, which is usually not just a number, may contain the English letter of the business description, and the logical primary key cannot be set as a foreign key.
However, if you choose a physical primary key as a foreign key, you must pay particular attention to maintaining the correlation between data when you are doing data migration, especially when importing the data from the test environment into the production environment.
either bring the primary key values in the test database together into the production database, or manually modify the correlation between the data. The latter is prone to error, while the former destroys the physical characteristics of the primary key of the database .
worse, the primary key in the test database already exists in the production database and has to be used to manually modify the association between the data.
so now our database does not have a foreign key association, although this is a double-edged sword, but how much will undermine the integrity of the database design.

2. There can be only one field of the timestamp type with the default current time set in the same table.  
For example, usually when we create a table, there are update_time and create_time. We may want the default values for both fields to be the current time, so that some code processing can be simplified in the business program.
Unfortunately, only one field in the same table can be modified by default Current_timestamp.

The accuracy of the timestamp type of 3.MYSQL is only in seconds.  
This is nothing to say, if the business needs must be accurate to milliseconds, then save the long type of numbers.

4. The Where condition for creating a view cannot contain subqueries.
Example: CREATE VIEW Invalidview SELECT * from a a WHERE a.xxx = (select b.xxx from b b where ...)
even if the above statement removes the previous CREATE View Invalidview section, it will be disabled when the view is created.
One solution is to make a subquery into a table join, and the other is to refer to the part of the field, the middle of select and from, of the subquery's logic.

5. There is no algorithm optimization for subqueries.
For example: SELECT * from a a WHERE a.xxx = (select b.xxx from b b where ...)
when the above statement is between Oracle, the subsequent subquery will only be executed once, but in MySQL it will be executed n times and N is the amount of data in the a table.
Therefore, you should prohibit the use of subqueries.
The solution first is to make the subquery into a table-joined query, and if not, split the query into two queries.

Note points in MySQL usage (i)