MySQL5.5 automatic partition script

1. Instructions for use:

1. After this script is used as a partition, partitions are automatically added at regular intervals. (For tables that are automatically partitioned, you must manually divide the partitions first)

2. Every 15 days, the timer executes a stored procedure and adds 15 new partitions on the last day of the partition date.

3. In the Script, Auto_partitions. SQL is the stored procedure.

4. In the Script, Timer_event. SQL is the scheduled event Script.

5. MySQL5.5 the EVENT mechanism is not enabled by default. You need to add [mysqld] event_schedld = ON in the my. cnf File

7. Add and open the file online. This is very important. open_files_limit = 5000

Ii. Partition script

1. DELIMITER |
2. DROP PROCEDUREIf exists create_Partition |
3. CREATE PROCEDURECreate_Partition (INDatabaseNameVARCHAR(50 ),INTableNameVARCHAR(50 ))
4. Rochelle end:BEGIN
5. DECLAREMAX_PARTITION_DESCRIPTIONVARCHAR(255)DEFAULT0;
6. DECLAREP_NAMEVARCHAR(255)DEFAULT0;
7. DECLAREP_DESCRIPTIONVARCHAR(255)DEFAULT0;
8. DECLAREIINT DEFAULT1;
9. DECLAREISEXIST_PARTITIONVARCHAR(255)DEFAULT0;
10. SELECTPARTITION_NAMEINTOISEXIST_PARTITIONFROMInformation_schema.PARTITIONSWHERETABLE_SCHEMA = databaseNameANDTABLE_NAME = tableName LIMIT 1;
12. IF ISEXIST_PARTITION <=>"" THEN
13. SELECT "Partition table not is exist" AS "****** ERROR *****";
14. LEAVE L_END;
15. ENDIF;
17. SELECTPartition_descriptionINTOMAX_PARTITION_DESCRIPTIONFROMInformation_schema.PARTITIONSWHERETABLE_SCHEMA = databaseNameANDTABLE_NAME = tableNameORDER BYPartition_descriptionDESCLIMIT 1;
20. IF MAX_PARTITION_DESCRIPTION <=>"" THEN
21. SELECT "Partition table is error" AS "****** ERROR *****";
22. LEAVE L_END;
23. ENDIF;
26. SETMAX_PARTITION_DESCRIPTION =REPLACE(MAX_PARTITION_DESCRIPTION,'\'','');
27. WHILE I <= 15 DO
28. SETP_DESCRIPTION = adddate (MAX_PARTITION_DESCRIPTION, INTERVAL IDay);
29. SETP_NAME =REPLACE(P_DESCRIPTION,'-','');
30. SET@ S = CONCAT ('Alter table', TableName,'Add PARTITION (PARTITION P', P_NAME,'Values less (\'', P_DESCRIPTION,'\'))');
31. SELECT@ S;
32. PREPAREStmt2FROM@ S;
33. EXECUTEStmt2;
34. DEALLOCATE PREPAREStmt2;
35. SETI = I + 1;
36. ENDWHILE;
37. ENDRochelle end; |
38. DELIMITER;

# The input parameter databaseName is the database name and the tableName is the table name.

3. Add event processing

1. DELIMITER |
2. CREATEEVENT auto_set_partitions
3. ONSCHEDULE
4. EVERY 15DAY
5. DO
6. BEGIN
7. CALL create_Partition ('Database _ name','Table _ name');
8. /* If you want to partition multiple tables, you can write multiple CALL calls.
9. CALL create_Partition ('Database _ name','Table _ name');
10. */
11.  END|
12. DELIMITER;

This event is executed every 15 days.