mysql - 存储过程在实际项目中用的多吗？

Question

我发现很多教科书，数据库教程视频都涉及到了存储过程，但是我看过很多开源的php项目，里面几乎就没有用到存储过程啊，我看过java项目倒是有部分项目用到过存储过程，这是为什么呢？

已经从业或者开发过大型项目的程序员们你们在实际工作中用到过他吗？

还有就是存储过程里面的逻辑几乎我都可以用程序（java，php）写，为什么还要直接在数据库里面写呢？（换句话说：存储过程的意义是什么？）

Answer 1

1. Stored procedures are very useful. For example, in a common login scenario, you need to record the user’s login record. You can use programming language to achieve this:

  db.connect( "db_host" ).execute_sql( "select count(*) from user_info_table where username = 'you' and pass='123' " );
  db.connect( "db_host" ).execute_sql( "insert into login_table( user_name, log_time )values( 'you', '2017_01_05' )  " );

Here, the first step is to first determine whether the user exists, and the second step is to record the user login log.

Stored procedure implementation: The function of login_user_and_save_result() is to perform user login operations and record the user's login log.

  db.connect( "db_host" ).execute_sql( "call login_user_and_save_result('you', '123') " );
  

The difference is that the programming language implementation requires 2 database connection connect operations and 2 compile sql operations execute_sql; login_user_and_save_result() only requires 1 connect and 0 compile execute_sql operations, because the stored procedure has been compiled at the beginning of its creation Yes, you just need to pass the parameters.
In this way, as the number of users gradually increases, the stored process can save a lot of bandwidth and system resource consumption for the server, and the advantages will gradually show up.

If your boss asks you to add a layer of logic, the user will only be allowed to log in 3 times. After 3 failed attempts, the user will not be allowed to log in. Database operations at the programming language level become a bit more complex.

 arr_result = db.connect( "db_host" ).execute_sql( "select count(*) from user_info_table where username = 'you' and pass='123' and enable_login = 1" ); //
 if( arr_result != 1 ) { //更新登入失败次数
      db.connect( "db_host" ).execute_sql( "insert user_login_fail(username, pass) values( 'you', 'pass' ) " );
 }
 fail_count = db.connect( 'db_host' ).execute_sql( "select count(*) from user_login_fail where username = 'you'" );
 if( fail_count > 3 ) {
      db.connect( "db_host" ).execute_sql( "update user_info_table set enable_login = 0" );
 }

Here, you can find that the programming language requires frequent connections to the db, or that it requires a long-term network connection with the database. If you implement all these login logics in login_user_and_result(), all you have to do is pass in the parameters username and pass, and the system only needs to connect to the db once and compile 0 times, which is much simpler.

2. The role of stored procedures is not only in resource consumption. Now, your program has two ways to log in, one is to log in on the web side, and the other is to log in on the native client side. Assume that the web side is implemented using java web and the client side is implemented using visual c++. If both java web and visual c++ call login_user_and_result() when logging in, the user's consistent login behavior can be maintained and developers can avoid other problems caused by separate implementations. In fact, you can also add various database-level permissions to stored procedures to uniformly control login permissions.

Answer 2

I think stored procedures have two main advantages. First, it is executed on the database side to avoid network communication overhead, thus making it more efficient. Second, stored procedures can directly utilize some high-level abstractions provided by the database, such as transactions, triggers, rewrite rules, etc.

If stored procedures are not used at all, the database is just a storage backend that provides additions, deletions, and modifications, and the advanced functions it provides will be wasted. You can use stored procedures to build business functions, and the front end just calls these functions, similar to the effect of an API.

Answer 3

Implementation, operation and maintenance can be changed by yourself, otherwise for bigger business, you have to go back to the development side to change the code, compile and issue patches

Answer 4

In my project, no stored procedures are used. I have used the view! Companies with larger projects may have a dedicated DBA unit if necessary!

Answer 5

ods, data batch processing in traditional data centers such as data warehouses, or olap will still use stored procedures

Answer 6

Some DBAs use stored procedures or simple logic, and the logs are written to the database...I personally think it is easier to write them in the code

Answer 7

High efficiency, sometimes when the responsibilities are very detailed, the backend may not be able to access the database at will

Answer 8

In my project, stored procedures are used a lot, especially for reports, most of which use stored procedures.
In addition to the above advantages, the scalability of stored procedures will be better, such as cross-database, sub-table union, and some dynamic splicing of SQL. The stored procedures are convenient and intuitive. For future distribution, it will only be used in Splice modifications within stored procedures.

Answer 9

I usually use it for data sorting and migration. For example, if the website is rectified, the data structure changes, a certain table is split, a certain table is merged, a certain field data format changes, etc. Anyway, I only need to use it when the site is upgraded. Other queries that can generally be implemented using code do not want to be implemented using stored procedures

Answer 10

Reputable companies will have a position like DBA, and they will look down on SQL written by programmers, and write thousands of lines of stored procedures to implement complex business.
The advantages of the stored procedure itself are not detailed.