Mysql procedure? Function?
I need to have a routine / function / stored proc / whatever to archive certain accounts from our "active" table to an inactive table.
I can write this as a bunch of queries, all executed in PHP order one at a time, but I'm looking to dump most of the work into mysql because it doesn't need PHP to be involved.
Basically, this will 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 will do this)
Anyway, I would like to just do something like: call archive_account (n); and they do all the work (and as a rollback transaction if it doesn't work)
Am I asking for too much mysql?
source to share
You might want to use scheduled tasks aka events if you want this to run daily, automatically. You can read about them in the MySQL documentation here .
Keep in mind that you need to use MySQL 5.1.6 or newer to do this, if not, it's time to upgrade (there were some noticeable speed improvements in the new version anyway).
source to share
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 in 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
source to share