mysql procedure? Function?

Question

I need to have a routine/function/stored proc/whatever to archive particular accounts from our 'active' table, to an inactive table.

I can write this out as a group of queries, all executed in order by PHP one at a time, but I'm looking to offload most of the work to mysql because it's not necessary for PHP to be involved here.

Basically, this would get all the data:

insert into credit_archive_acc select  * from credit_acc where uid_usr = n; 
delete from credit_acc where uid_usr =n; 
insert into user_archive_usr select * from user_usr where id_usr = n; 
delete from user_usr where id_usr = n;

(about 3 other tables I'll do this to)

Anyway, I'd like to just be able to do something like: call archive_account(n); and have that do all the work (and as a transaction with rollback if it fails)

Am I asking too much of mysql?

Answer 1

You might want to use scheduled tasks aka events if you want this to be run daily, automatically. You can read about them in the MySQL documentation here.

Keep in mind you need to use MySQL 5.1.6 or newer to do this, if not, time to upgrade (there's been some noteworthy speed improvements in the new version anyway).

Answer 2

Yes, you can write stored procedures in mysql, see http://dev.mysql.com/doc/refman/5.0/en/stored-programs-defining.html, but note the limitations at http://dev.mysql.com/doc/refman/5.0/en/stored-program-restrictions.html

For more information, see http://www.scribd.com/doc/3101271/MySQL-Stored-Procedures-book