Note: (1) MySql usage

Note: (1) MySql usage

This article is intended for those who previously engaged in enterprise-level application development, because enterprise-level applications, especially financial enterprises, usually use Oracle or DB2 as databases.

Compared with the above-mentioned database software that costs a lot of money, free MySql still has some limitations, as well as some characteristics of MySql itself, pay attention to and pay attention.

1. When creating a foreign key, MySql only supports numeric Foreign keys.
This is really disgusting, because the table's primary key design can both have a physical primary key and a logical primary key. The logical primary key is usually not only a number, but may contain letters indicating the business. Therefore, such a logical primary key cannot be set as a foreign key.
However, if the physical primary key is used as the foreign key, you must pay special attention to the relationship between data during data migration, especially when importing data from the test environment to the production environment.
You can either bring the primary key values in the test database to the production database or manually modify the data association. The latter is prone to errors, and the former will damage the continuous physical characteristics of the database primary key,
Worse, the primary key in the test database already exists in the production database, and you have to manually modify the association between data.
So now our database does not have a foreign key Association. Although this is a double-edged sword, it may damage the integrity design of the database.

2. Only one Timestamp Field of the default current time can be set in the same table.
For example, when we create a table, there are both update_time and create_time. We may want the default values of these two 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.

3. The Timestamp type of MySql is accurate to seconds only.
There is nothing to say about this. If the business needs must be accurate to milliseconds, save the long-type number.

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 preceding statement can run normally without the "create view invalidView" section, it is forbidden to create a view.
One solution is to change the subquery to table join, and the other method is to mention the Field Section of the subquery logic, that is, the middle of select and from.

5. No algorithm optimization is performed on subqueries.
Example: select * from A a where a. xxx = (select B. xxx from B B where ...)
When the preceding statement is in Oracle, The subquery is executed only once, but n is executed n times in MySql. n is the data volume in table.
Therefore, the use of subqueries should be prohibited.
The solution is to convert the subquery into a table join query. If not, split the subquery into two queries.