php - SELECT INTO 还是存储过程？

Question

将其作为存储过程更好还是保持原样？

INSERT INTO `user_permissions` 
    ( `user_id`, `object_id`, `type`, `view`, `add`, `edit`, `delete`, `admin`, `updated_by_user_id` ) 
    SELECT `user_id`, $object_id, '$type', 1, 1, 1, 1, 1, $user_id 
    FROM `user_permissions` 
    WHERE `object_id` = $object_id_2 AND `type` = '$type_2' AND `admin` = 1

您可以用不同的对象来思考这一点，假设您有组和子组。如果有人创建了子组，那么有权访问父组的每个人现在也都可以访问该子组。

我以前从未制作过存储过程，但现在看起来可能是时候了。这个调用可能会经常被调用。

我应该创建一个过程还是性能会微不足道？

Answer 1

来自维基百科Stored Procedure :

Overhead: Because stored procedure statements are stored directly in the database, this may remove all or part of the compilation overhead that is typically required in situations where software applications send inline (dynamic) SQL queries to a database. (However, most database systems implement "statement caches" and other mechanisms to avoid repetitive compilation of dynamic SQL statements.) In addition, pre-compiled SQL statements, while avoiding some overhead, add to the complexity of creating an optimal execution plan because not all arguments of the SQL statement are supplied at compile time. Depending on the specific database implementation and configuration, mixed performance results will be seen from stored procedures versus generic queries or user defined functions.

Avoidance of network traffic: A major advantage with stored procedures is that they can run directly within the database engine. In a production system, this typically means that the procedures run entirely on a specialized database server, which has direct access to the data being accessed. The benefit here is that network communication costs can be avoided completely. This becomes particularly important for complex series of SQL statements.

Encapsulation of business logic: Stored procedures allow for business logic to be embedded as an API in the database, which can simplify data management and reduce the need to encode the logic elsewhere in client programs. This may result in a lesser likelihood of data becoming corrupted through the use of faulty client programs. Thus, the database system can ensure data integrity and consistency with the help of stored procedures.

Delegation of access-rights: In many systems, stored-procedures can be granted access rights to the database which the users who will execute those procedures do not directly have. Thus, the stored procedure becomes the only way that these users have, to do whatever the stored procedure does.

Some protection from SQL injection attacks: Stored procedures can be used to protect against this attack. The parameters will be treated as data even if an attacker inserts SQL commands. Also some DBMS will check the parameter's type.

Disadvantages

Stored procedures are "defined once, used many times." If any changes are necessary, the (one and only one) definition of the stored procedure must be replaced. Dynamic SQL, of course, allows any SQL query to be issued at any time. Any change to a stored procedure instantly impacts every other piece of software, report, etc. (inside or outside of the DBMS) which directly or indirectly refers to it. It is not always possible to determine with certainty exactly what those impacts will be, nor what changes can safely be made without adversely impacting something else.

For various reasons, many organizations strictly limit who is allowed to define and issue a query against the database. Programmers and other users may therefore find themselves having no choice but to implement inefficient solutions to their problems using what stored procedures are available to them, whether or not the procedures are appropriate for this particular ancillary task.

Though not directly related to stored procedures, the movement of business logic to the DBMS is problematic since it is the layer with the more complex scalability issues. Furthermore, some modern DBMS systems (notably from Microsoft SQL Server 2000 onwards) don't offer any performance benefits of using stored procedures against precompiled queries: they are compiled and cached in the same manner as dynamic SQL.

因此，在您的示例中，您希望获得存储过程的封装优势，并且可能应该就这样做。