William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

MySQL: 2 Things about Stored Procedure

1. How to check definition of stored procedure definition
  • In pma (PhpMyAdmin) GUI, it is quite simple:
    After the tables displayed,
    there is a routines link. Click it, the procedure details are displayed.
  • From command line:
    mysql> select name, db, type from mysql.proc;
    mysql> select name from mysql.proc 
      where db = 'db_name' and `type` = 'PROCEDURE'
    mysql> show procedure status;

    to get the list of stored procedures; then, to show individual procedure:

    mysql> show create procedure proc_name;
    mysql> show create function func_name; 

MySQL’s procedures and functions are not as powerful as PL/SQL or T-SQL. However, for a repeated or complex insert/update process which involve transaction, multi-statements, multi-tables, using procedure is a primary choice.

2. Extract/restore procedures/functions
To dump a database, we use mysqldump; to restore the database, we use mysql command:

$ mysqldump -u test -p <database> > database.sql
$ mysql --verbose --user=test 
  --password=test <database> < database.sql

The issue is: mysqldump will backup by default all the triggers but NOT the stored procedures/functions.
Suppose we have some of procedures/functions, the above mysqldump/mysql are not enough to dump all.

To solve it, there are 2 mysqldump parameters could control this behavior:

  • – routines – FALSE by default
    routines mean procedures/functions, it remarks that by default, all the procedures and functions do not dump.
  • – triggers – TRUE by default
    by defult, all triggers do dump with schema and data.

This means that if we want to dump ALL,
we only need to add the –routines command line parameter:

mysqldump --user=test --password=test 
  --routines > procedures.sql 

The above will dump all database structure/data as well as procedures/functions.

Let’s assume we ONLY want to backup the stored procedures/functions, not the mysql tables and data
(this can be useful to import these into another db/server that has already the data
but not the stored procedures and/or triggers), then we should run something like:

$ mysqldump --routines --no-create-info 
--no-data --no-create-db --skip-opt 
--user=test --password=test 
--verbose <database> > procedures.sql

By using this, we can operate the stored procedures and functions alone or with whole database schema and data for the purpose of database transfer.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: