An article introducing the method of viewing stored procedures in MySQL

MySQL is a widely used open source relational database management system. Like other database management systems, MySQL also supports the concept of stored procedures. Through stored procedures, common operations and business logic codes can be encapsulated into modules and called directly when needed.

However, in actual development, as the business scale continues to expand, the number of stored procedures will also increase. How to efficiently manage and quickly find these stored procedures becomes particularly important. This article will introduce the method of viewing stored procedures in MySQL, and hope to help you.

1. Use the SHOW PROCEDURE STATUS command to view stored procedures

By using the SHOW PROCEDURE STATUS command, you can view related information about stored procedures in the MySQL database, including stored procedures. Name, creation time, modification time, status, etc.

Show PROCEDURE STATUS [LIKE 'pattern' | WHERE expr]

Among them, pattern represents the pattern of the stored procedure name to be queried, and expr represents a SQL expression. Only when the expression result is true, the query result will be returned. If no parameters are specified, information about all stored procedures will be returned.

The following are some examples:

Query all stored procedures

SHOW PROCEDURE STATUS;

Query the stored procedures whose names start with p_

SHOW PROCEDURE STATUS LIKE 'p_%';

Query Stored procedure with status {Create | Alter | Drop}

SHOW PROCEDURE STATUS WHERE `status` IN ('Create', 'Alter', 'Drop');

2. Use the SHOW CREATE PROCEDURE command to view the stored procedure definition

To view the specific definition of the stored procedure, You can use the SHOW CREATE PROCEDURE command, which will return a SQL statement containing the stored procedure definition.

SHOW CREATE PROCEDURE proc_name;

Among them, proc_name represents the name of the stored procedure to be queried.

The following is an example:

SHOW CREATE PROCEDURE `add_user`;

-- 结果
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Procedure | Create Procedure                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| add_user  | CREATE DEFINER=`root`@`localhost` PROCEDURE `add_user`(IN p_name VARCHAR(32), IN p_age INT, IN p_address VARCHAR(64)) BEGIN INSERT INTO `user`(`name`, `age`, `address`) VALUES (p_name, p_age, p_address); END |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Through the SHOW CREATE PROCEDURE command, you can quickly view the specific implementation of the stored procedure even if you do not know the definition of the stored procedure.

3. Use INFORMATION_SCHEMA to view stored procedures

INFORMATION_SCHEMA is a metadata database supported by MySQL, which contains a large amount of information about the structure and status of the database. In the INFORMATION_SCHEMA.Routines table, you can query all stored procedures of the MySQL database.

SELECT `ROUTINE_NAME`, `ROUTINE_DEFINITION`, `CREATED`, `LAST_ALTERED`
FROM `INFORMATION_SCHEMA`.`ROUTINES`
WHERE `ROUTINE_TYPE` = 'PROCEDURE'
  AND `SPECIFIC_SCHEMA` = 'your_db_name';

Among them, ROUTINE_NAME represents the stored procedure name, ROUTINE_DEFINITION represents the stored procedure definition, CREATED represents the creation time, LAST_ALTERED represents the modification time.

It should be noted that in order to improve query efficiency, the specific database name should be specified in the query statement. If you want to query all databases, you can change the SPECIFIC_SCHEMA field to IS NOT NULL.

Summary

Through the above three methods, you can easily view and manage stored procedures in MySQL. The SHOW PROCEDURE STATUS command provides basic stored procedure information, and the SHOW CREATE PROCEDURE command provides specific stored procedure definitions. Using INFORMATION_SCHEMA you can query stored procedures in all MySQL databases.

In actual development, in addition to viewing the definition and information of stored procedures, you also need to be familiar with the calling methods, parameter transfer, etc. of stored procedures, so that you can quickly use stored procedures to complete business logic when needed.

The above is the detailed content of An article introducing the method of viewing stored procedures in MySQL. For more information, please follow other related articles on the PHP Chinese website!