Menu
I have a mysql database table that I want to partition by date, particularly by month & year. However, when new data is added for a new month, I don't want to need to manually update the database.
When I initially create my database, I have data in Nov 09, Dec 09, Jan 10, etc. Now when February starts, I'd like a Feb 10 partition automatically created. Is this possible?
379
Auto-partitioning, in networking, is an Ethernet component used as a safety net to prevent corrupt data transmission and data loss while isolating defective devices, ports, or network lines.
MySQL supports several types of partitioning as well as subpartitioning; see Section 22.2, “Partitioning Types”, and Section 22.2. 6, “Subpartitioning”. Section 22.3, “Partition Management”, covers methods of adding, removing, and altering partitions in existing partitioned tables.
We can create a partition in MySQL using the CREATE TABLE or ALTER TABLE statement. Below is the syntax of creating partition using CREATE TABLE command: CREATE TABLE [IF NOT EXISTS] table_name. (column_definitions)
MySQL 5.7 supports explicit selection of partitions and subpartitions that, when executing a statement, should be checked for rows matching a given WHERE condition.
There are a few solutions out there, if you want a total solution, check this post out on kickingtyres. It's a basic combination of a stored procedure handling the partition analysis and creation (with some logging!).
All you need to do is adjust it to your partition type (the example uses a bigint partition) and schedule the procedure to run with the MySQL Event Scheduler.
145
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
