Support for MySQL database automatic table sharding tool dbshardtools released

I participated in a company project some time ago. This project is characterized by a large amount of data and traffic. Considering using the database horizontal table sharding policy, I did not find a table sharding tool after Google for a long time. Therefore, I wrote a database horizontal table sharding tool that supports ms SQL Server and MySQL, which is better for MySQL.

The following describes how to use the table sharding tool.

First, set the database connection interface

 

Enter the connection name (or click the select box on the right to select from the saved connection) and select the database type (currently MySQL and SQL Server are supported ), enter the IP address, database name, user name, and password, and click test connection. If the connection is successful, the settings are correct.

 

 

Click OK to go to the main interface:

 

When you perform a table sharding operation, you must have a master table. You can manually enter the name of the master table or click the select box on the right and select it from the drop-down list. The table sharding format can be customized, and the table sharding logic can also be customized. The table sharding logic in this example is divided into 1000 table shards and the table name is 4-bit hexadecimal, that is, 0000 ~ 03e7. There are three types of operations: 1. Create a sub-table; 2. modify a sub-table (for example, add or delete fields in all sub-tables); 3. delete a sub-table. After the operation type is selected, DDL is automatically generated (you can modify it as needed ).

 

Click OK to perform the table sharding operation.

 

View the database, table shards shoppingcart_0000 ~ Shoppingcart_03e7 has been generated successfully.

 

 

Custom table sharding Naming Policy

ImplementationInamestrategyInterfaceGettablenumbersMethod to customize the suffix name of a table shard

 
Public InterfaceInamestrategy {String[] Gettablenumbers ();}
 

For example:

 Public   Class Namestrategy: inamestrategy { Private   Readonly   Int _ Startindex;Private   Readonly   Int _ Endindex; Private   Readonly   Int _ Tobase; Private   Readonly   Int _ Totalwidth; Public Namestrategy ( Int Startindex, Int Endindex, Int Tobase, Int Totalwidth) {_ startindex = startindex; _ endindex = endindex; _ tobase = tobase; _ totalwidth = totalwidth ;} Public   String [] Gettablenumbers () {list < String > Tablenames = New List < String > (); For ( Int I = _ startindex; I <= _ endindex; I ++) {tablenames. Add (convert. tostring (I, _ tobase). padleft (_ totalwidth, '0' ));} Return Tablenames. toarray ();}}

 

Then, register the custom naming policy implementation in unity. config.

 <  Register    Type  = "Dbshardtools. Core. inamestrategy, dbshardtools. Core"  Mapto  = "Dbshardtools. Core. namestrategy, dbshardtools. Core"  >          <  Constructor  >            <  Param   Name  = "Startindex"   Value  = "0"   />            <  Param   Name  = "Endindex"   Value  = "999"   />           <  Param   Name  = "Tobase"   Value  = "16"   />            <  Param   Name  = "Totalwidth"   Value  = "4"   />          </  Constructor  >          <  Lifetime   Type  = "Singleton" />      </  Register  >     

This table sharding tool currently supports MySQL better. For example, you can automatically generate DDL statements for table sharding. the SQL Server database must manually paste the table creation script to the DDL text box.

Code download