mysql delete using MySQL delete from database where

可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:

问题:

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