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?

I'd keep an eye on partitioning, too. It's not ready yet, though. dev.mysql.com/doc/refman/5.1/en/partitioning.html — Michael Haren
– Michael Haren, Commented Dec 15, 2008 at 17:37

Egil · Accepted Answer · 2008-12-15 17:38:41Z

1

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

answered Dec 15, 2008 at 17:38

Egil

5,7802 gold badges35 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TravisO · Accepted Answer · 2008-12-15 17:34:54Z

0

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).

answered Dec 15, 2008 at 17:34

TravisO

9,5644 gold badges40 silver badges45 bronze badges

Collectives™ on Stack Overflow

MySQL procedure? Function?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related