可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:
问题:
Within my database, I have a large number of tables which contain dates. These are expiry dates to be precise.
Currently, I perform the following commands to remove any rows which are older than a specified date using:
DELETE FROM tablename WHERE DATE(datepoint) < '-03-18'
1) Is there a way I can instead search and delete from an entire database instead of manually changing the table name and running the command each time?
回答1:
You could use a PHP function, Anyway I wouldn't recommend deleting any records, just filter your queries for the last 3 months only
回答2:
Perhaps consider having expiry dates in their own table EXPIRY_DATES. It sounds like it could be an important concept in your domain. Then have your other tables point to the correct expiry date using a foreign key. Using on delete cascade rules from EXPIRY_DATES would clean out records from all child tables when you delete one expiry date from EXPIRY_DATES.
回答3:
you shoould use procedure (or function):
Define a procedure :
mysql> CREATE PROCEDURE delete_from_table(OUT table_name char(20))
-> BEGIN
-> delete from table_name WHERE DATE(datepoint) < '-03-18';
-> END//
mysql> delimiter ;
CALL procedure :
mysql> CALL delete_from_table(@table1);
mysql> CALL delete_from_table(@table2);
mysql> CALL delete_from_table(@table3);