How to create a VIEW that unions date partitioned tables dynamically based on a sliding date window
I have a table with millions of records that I would like to partition on date for maintenance convenience, so table_开发者_开发技巧2011_01_01, table_2011_01_02.
For calculations I would like to have a view that unions those tables dynamically based on a sliding date window. For example a view that unions the tables of the last three days. In the mysql VIEW documentation I couldn't find an easy way to make the underlying select dynamically UNION tables for the last 3 days.
What's a good way to do that in MYSQL?
If you partition a table, there's no need to address the separate partitions expicitly.
MySQL will do that for you.
So you don't need a union
and you don't need to specify tables, you can just write a select that accesses a single table, if multiple partitions are needed, MySQL will union them automatically.
Links:
http://dev.mysql.com/doc/refman/5.5/en/partitioning.html
Be sure to read: http://dev.mysql.com/doc/refman/5.5/en/partitioning-limitations.html
精彩评论