Advantages and disadvantages of MySQL internal code

There are four types of MySQL internal code: stored procedures, stored functions, events, triggers.

Stored Procedures & stored functions: Advantages: Internal execution, closest to the data, and also on the server can save bandwidth and network delay code reuse, can easily unify business rules, ensure certain behavior is consistent, also can provide some security for the application. Simplifies code maintenance and version updates. Helps improve security and provides finer granularity of permission control. Cache execution plan, if repeated calls can reduce consumption. Maintenance is simple, no external dependencies better between development and database maintenance Personnel Division. Cons: MySQL does not provide good development and debugging tools to write debugging difficulties. Inefficient, stored procedures use a limited number of functions, difficult to write complex string maintenance functions, but also difficult to implement too complex logic. Deployment brings additional complexity. Bring in additional internal storage code. There is a security risk to the server to bring additional pressure MySQL does not have resource consumption control process, if the stored procedure error, may directly drag the server to death.    Difficult to debug. Triggers: Cons: Limited functionality. You can define up to one trigger at a time for each table. MySQL only supports "row-based triggering"-so the trigger is for a record. Instead of the entire SQL statement, if the notconsistent dataset is very large, it will be inefficient. and triggers can mask the jobs behind the server, affect potential efficiencies, and hinder performance troubleshooting. Trigger issues are difficult to troubleshoot, especially performance issues triggers can cause deadlocks and lock waits, and SQL execution fails if the trigger fails. Triggers do not guarantee an update of atomicity. Pros: Reduce communication between client and server simplify application logic improved performance can be used to automatically update anti-normalized data or summarize summary table data. Events (direct ROLLUP): Internal implementations of MySQL typically encapsulate complex code as stored procedures and invoke events through call to be initialized in an independent event dispatch process, which has no relationship to the thread that handles the link. Changshu is not accepted and there is no return value. Used for periodic maintenance tasks.

Advantages and disadvantages of MySQL internal code