Automatic table building method, system, readable storage medium and computer device

Abstract

The application provides an automatic table building method, system, readable storage medium and computer equipment, and the method comprises the steps of: creating a data table sharding rule; obtaining a configuration file of a to-be-processed table according to the data table sharding rule, obtaining a sharding table name and a database address corresponding to the sharding table name; reading a metadata table of the to-be-processed table, and obtaining a corresponding sharding table building statement according to the metadata table; and automatically creating a sharding table corresponding to the sharding table name in a database corresponding to the database address according to the sharding table building statement. The application obtains the configuration file of the to-be-processed table by creating the data table sharding rule, obtains the sharding table name and the corresponding database address, obtains the sharding table building statement by reading the metadata table of the to-be-processed table, and automatically creates the sharding table in the database corresponding to the database address according to the sharding table building statement, so that the user can manage the data table according to the configured sharding rule, and the table can be automatically created and modified in the process of database and table splitting.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of computers, and in particular to an automatic table building method and system, a readable storage medium and a computer device. BACKGROUND

[0002] With the rapid development of technology and the improvement of people's living standards, the magnitude of data is also growing exponentially, from GB to TB to PB, and various operations on data are increasingly difficult.

[0003] Database and table splitting is to solve the problem of reduced database performance due to excessive data volume. The original independent database is split into several databases, and the large data table is split into several data tables, so that the data volume of a single database and a single data table is reduced, thereby achieving the purpose of improving database performance.

[0004] The Sharding-JDBC database and table splitting middleware has the problem of too many table shards to manage after configuring the table parsing rules. The harm caused by this problem is that it needs to manually create shard tables every time the project is started, and too many shard tables will have a lot of repetitive work, and when modifying the shard table fields, the tables under other same shard rules need to be modified at the same time. Sharding-JDBC cannot manage the tables after sharding, and additional costs are required to manage these shard tables, such as manual creation or manual modification or manual deletion, which is high in labor cost, and manual deletion may cause accidental deletion. SUMMARY

[0005] The embodiments of the present application provide an automatic table building method, system, readable storage medium and computer device to at least solve the deficiencies in the related art.

[0006] In a first aspect, the embodiments of the present application provide an automatic table building method, comprising:

[0007] creating a data table sharding rule;

[0008] obtaining a configuration file of a to-be-processed table according to the data table sharding rule, to obtain a shard table name and a database address corresponding to the shard table name;

[0009] reading a metadata table of the to-be-processed table, and obtaining a corresponding shard table building statement according to the metadata table;

[0010] automatically creating a shard table corresponding to the shard table name in a database corresponding to the database address according to the shard table building statement.

[0011] In some embodiments, the step of obtaining a corresponding shard table building statement according to the metadata table comprises:

[0012] obtain a SQL configuration file of the memory, parse a create table statement of the SQL configuration file;

[0013] replace a table name of the create table statement of the SQL configuration file by the shard table name, to form a shard table creation statement.

[0014] In some embodiments, the step of automatically creating a shard table corresponding to the shard table name in a database corresponding to the database address according to the shard table creation statement comprises:

[0015] finding a corresponding database according to the database address, the username and the password;

[0016] loading a JDBC driver and establishing a connection with the database;

[0017] creating a Statement object and executing the shard table creation statement;

[0018] judging whether the shard table creation statement is executed successfully;

[0019] if the shard table creation statement is executed successfully, automatically creating a shard table in the database.

[0020] In some embodiments, the method further comprises:

[0021] when the shard table is modified, obtaining information of the shard table before modification and the newly generated shard table, and obtaining a difference between the information of the shard table before modification and the newly generated shard table, to generate a corresponding SQL table creation statement;

[0022] creating a new shard table according to the SQL table creation statement.

[0023] In some embodiments, after the step of judging whether the shard table creation statement is executed successfully, the method further comprises:

[0024] if the shard table creation statement fails to be executed, issuing a warning log.

[0025] In a second aspect, the embodiments of the present application provide an automatic table creation system, comprising:

[0026] a first creation module configured to create a data table sharding rule;

[0027] an obtaining module configured to obtain a configuration file of a to-be-processed table according to the data table sharding rule, to obtain a shard table name and a database address corresponding to the shard table name;

[0028] a processing module configured to read a metadata table of the to-be-processed table, and obtain a corresponding shard table creation statement according to the metadata table.

[0029] The second creating module is configured to automatically create a sharding table corresponding to the sharding table name in a database corresponding to the database address according to the sharding table creation statement.

[0030] In some embodiments, the processing module comprises:

[0031] The parsing unit is configured to acquire a SQL configuration file of the memory, and parse a create table statement of the SQL configuration file;

[0032] The processing unit is configured to replace a table name of the create table statement of the SQL configuration file with the sharding table name to form the sharding table creation statement.

[0033] In some embodiments, the second creating module comprises:

[0034] The searching unit is configured to search for a corresponding database according to the database address, the username and the password;

[0035] The loading unit is configured to load a JDBC driver program and establish a connection with the database;

[0036] The first creating unit is configured to create a Statement object and execute the sharding table creation statement;

[0037] The judging unit is configured to judge whether the sharding table creation statement is executed successfully;

[0038] The second creating unit is configured to automatically create a sharding table in the database if the sharding table creation statement is executed successfully.

[0039] In some embodiments, the system further comprises:

[0040] The comparing module is configured to, when the sharding table is modified, acquire information of the sharding table before modification and the newly generated sharding table, acquire a comparison difference between the information of the sharding table before modification and the newly generated sharding table, and generate a corresponding SQL creation statement;

[0041] The third creating module is configured to create a new sharding table according to the SQL creation statement.

[0042] In some embodiments, the second creating module further comprises:

[0043] The control unit is configured to issue a warning log if the sharding table creation statement fails to be executed.

[0044] In a third aspect, an embodiment of the present application provides a readable storage medium, having stored thereon a computer program, which, when executed by a processor, implements the automatic table building method according to the first aspect.

[0045] In a fourth aspect, an embodiment of the present application provides a computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, and characterized by that the processor implements the automatic table building method according to the first aspect when executing the computer program.

[0046] Compared with the related art, the automatic table building method, system, readable storage medium and computer device provided by the embodiments of the present application can obtain the configuration file of the table to be processed by creating the data table sharding rule, obtain the sharding table name and the corresponding database address, and obtain the corresponding sharding table building statement by reading the metadata table of the table to be processed, and automatically create the sharding table in the database corresponding to the database address according to the sharding table building statement, so that the user can manage the data table according to the configured sharding rule, and the table can be automatically created and modified in the database sharding and table sharding process, thereby avoiding the problem of high cost and easy error in the process of creating, managing and deleting the data table in the manual management of the database sharding and table sharding process.

[0047] The details of one or more embodiments of the present application are presented in the following drawings and description to make other features, objects and advantages of the present application more apparent. BRIEF DESCRIPTION OF DRAWINGS

[0048] The drawings described herein are intended to provide further understanding of the present application, and form a part of the present application. The schematic embodiments of the present application and the description thereof are used to explain the present application, and do not constitute an improper limitation on the present application. In the drawings:

[0049] Figure 1 It is a schematic diagram of the working mode of Sharding-JDBC in the prior art;

[0050] Figure 2 It is a schematic diagram of vertical table sharding in the database sharding and table sharding in the prior art;

[0051] Figure 3 It is a schematic diagram of vertical database sharding in the database sharding and table sharding in the prior art;

[0052] Figure 4 It is a schematic diagram of horizontal database sharding in the database sharding and table sharding in the prior art;

[0053] Figure 5 It is a schematic diagram of horizontal database sharding in the database sharding and table sharding in the prior art;

[0054] Figure 6 It is a flowchart of the automatic table building method in the first embodiment of the present application;

[0055] Figure 7 Code for configuring the sharding rule in the first embodiment of the present application;

[0056] Figure 8 Flow chart for the automatic table building method in the second embodiment of the present application;

[0057] Figure 9 Structure block diagram of the automatic table building system in the third embodiment of the present application;

[0058] Figure 10 Structure block diagram of the computer device in the fourth embodiment of the present application.

[0059] Main element symbol explanation:

[0060] memory 10 acquisition module 12 processor 20 processing module 13 computer program 30 second creation module 14 first creation module 11

[0061] The following detailed description will further describe the present application in combination with the above-mentioned drawings. DETAILED DESCRIPTION

[0062] In order to make the objects, technical solutions and advantages of the present application clearer, the present application is described and explained in combination with the drawings and embodiments below. It should be understood that the specific embodiments described herein are only used to explain the present application and do not limit the present application. Based on the embodiments provided in the present application, all other embodiments obtained by those of ordinary skill in the art without making creative efforts fall within the scope of protection of the present application.

[0063] Obviously, the drawings in the following description are only some examples or embodiments of the present application, and for those of ordinary skill in the art, the present application can be applied to other similar scenarios without making creative efforts based on these drawings. In addition, it can be understood that although the efforts made in this development process can be complex and lengthy, for those of ordinary skill in the art related to the content disclosed in the present application, some design, manufacture or production changes based on the technical content disclosed in the present application are only routine technical means and should not be understood as insufficient disclosure of the present application.

[0064] In the present application, "embodiment" means that the specific features, structures or characteristics described in combination with the embodiment can be included in at least one embodiment of the present application. The phrase appears at various places in the specification does not necessarily refer to the same embodiment, nor is it an independent or alternative embodiment to other embodiments. Those of ordinary skill in the art explicitly and implicitly understand that the embodiments described in the present application can be combined with other embodiments without conflict.

[0065] Unless otherwise defined, technical terms and scientific terms used in the present application shall have the same meaning as commonly understood by one of ordinary skill in the art to which this application pertains. The terms "a", "an", "one", "this", and similar referents in the context of describing the application are to be construed to be open-ended, referring to one or more than one, unless otherwise noted. The terms "including", "comprising", "having" and variations thereof in this application are meant to encompass the possibility of non-exclusive inclusion, such that processes, methods, systems, products, or apparatuses that comprise a list of steps or elements are not limited to only those steps or elements but can include other steps or elements not expressly listed or inherent to such processes, methods, systems, products, or apparatuses. The terms "connected", "coupled", and similar referents in the context of this application are to be construed as not necessarily being limited to a direct connection or coupling, but can also include an indirect connection or coupling, such as through an intermediary. The term "plurality" refers to two or more. The term "and / or" describes association between name objects, indicating that there are three possible cases: one, the objects exist alone, two, the objects exist in combination, and three, the objects exist alone. The character " / " generally represents an "or" relationship between the associated objects. The terms "first", "second", "third", and the like in the present application are merely distinguishing similar objects, and do not represent a specific order of the objects.

[0066] First of all, it needs to be noted that:

[0067] Sharding-JDBC: a database and table splitting middleware; Sharding-JDBC is an open source distributed database middleware developed by Dangdang, and from version 3.0, Sharding-JDBC is included in Sharding-Sphere, and versions after version 4.0 are Apache versions.

[0068] ShardingSphere is an open source distributed database middleware solution ecosystem, which consists of Sharding-JDBC, Sharding-Proxy and Sharding-Sidecar, three independent products, which provide standardized data sharding, distributed transactions and database governance capabilities.

[0069] Sharding-JDBC, which is positioned as a lightweight Java framework, provides additional services at the JDBC layer of Java, and uses client direct connection to the database, providing services in the form of JAR packages, without additional deployment and dependencies, and can be understood as an enhanced version of JDBC driver, fully compatible with JDBC and various ORM frameworks.

[0070] The core function of Sharding-JDBC is data sharding and read-write separation. Through Sharding-JDBC, applications can transparently access each data source that has been sharded and read-write separated using JDBC, without worrying about the number of data sources and how the data is distributed.

[0071] Its features include:

[0072] Suitable for any Java-based ORM framework, such as Hibernete, MyBatis, Spring JDBC Template, or direct use of JDBC

[0073] Based on any third-party database connection pool, such as DBCP, C3P0, Druid, HikariCP, etc.

[0074] Supports any database that implements the JDBC specification, currently supports MySQL, Oracle, SQLServer, and PostgreSQL.

[0075] Please refer to Figure 1 , Figure 1 to show how Sharding-JDBC works. Using Sharding-JDBC requires manual database sharding and table splitting. Add Sharding-JDBC JAR package to the application, and the application operates the sharded database and data table through Sharding-JDBC. Since Sharding-JDBC is an enhancement of JDBC driver, using Sharding-JDBC is like using JDBC driver, and there is no need to specify the specific sharding and table splitting in the application.

[0076] Sharding table: The database table needs to be sharded, for example, A table is divided into two tables A0 and A1. Then A0 and A1 are called sharding tables.

[0077] Sharding table: In order to solve the problem of reduced database performance due to large amount of data, the original independent database is split into several databases, and the large data table is split into several data tables, so that the data volume of single database and single data table is reduced, thereby achieving the purpose of improving database performance.

[0078] Sharding table and database include two parts: sharding and table splitting. In production, it usually includes four ways: vertical sharding, horizontal sharding, vertical table splitting, and horizontal table splitting:

[0079] I. Vertical table splitting:

[0080] Vertical table splitting: Split a table into multiple tables according to fields, and each table stores a part of the fields.

[0081] The following is a case of a commodity query by vertical table splitting:

[0082] Generally, the details of a commodity are not displayed in a commodity list. For example, when a user browses a commodity list, the user will only view the detailed description of a commodity if the user is interested in the commodity. Therefore, the commodity description field in the commodity information has a low access frequency, and the field occupies a large storage space and has a long single data IO time. The commodity name, commodity picture, and commodity price fields in the commodity information have a high data access frequency.

[0083] The commodity information table is split as shown in Figure 2 by considering the access frequency.

[0084] The commodity list can use the following SQL:

[0085] SELECT p.*, r.[geographical area name], s.[store name], s.[reputation]

[0086] FROM [commodity information] AS p

[0087] LEFT JOIN [geographical area] AS r ON p.[place of origin] = r.[region code]

[0088] LEFT JOIN [store information] AS s ON p.id = s.[store]

[0089] WHERE... ORDER BY... LIMIT...

[0090] When obtaining the commodity description, the following SQL is used to obtain the commodity description:

[0091] SELECT *

[0092] FROM [commodity description]

[0093] WHERE [commodity ID] =?

[0094] II. Vertical database splitting

[0095] Vertical database splitting: tables are classified according to business and distributed to different databases. Each database can be placed on a different server.

[0096] The performance of vertical table splitting is improved to a certain extent, but as the data increases, the disk space is limited, and resources such as CPU, memory, etc. are also limited. Therefore, the SELLER_DB (seller database) can be divided into the PRODUCT_DB (commodity database) and the STORE_DB (store database), and the two databases can be distributed to different servers, as shown in Figure 3 .

[0097] Since the commodity information and the commodity description business have high coupling degree, they are stored together in the PRODUCT_DB (commodity database); and the store information is relatively independent, so it is stored separately in the STORE_DB (store database).

[0098] III. Horizontal database splitting

[0099] Horizontal database splitting is to split the data of the same table into different databases according to certain rules, and each database can be placed on a different server.

[0100] After vertical database splitting, the database performance is solved to a certain extent, but as the business volume grows, the PRODUCT_DB (commodity database) single database storage data has exceeded the expected value, and the PRODUCT_DB (commodity database) belongs to a very frequently accessed resource, and a single server cannot support it. At this time, horizontal database splitting can be tried:

[0101] The commodity information with odd store ID and the commodity information with even store ID are placed in two databases, as shown in Figure 4 That is, to operate a certain data, first analyze the store ID to which the data belongs. If the store ID is even, map this operation to the PRODUCT_DB1 (commodity database 1); if the store ID is odd, map the operation to the PRODUCT_DB2 (commodity database 2). The table expression for this operation to access the database name is:

[0102] PRODUCT_DB[store ID%2+1].

[0103] IV. Horizontal table splitting

[0104] Horizontal table splitting is to split the data of the same table into multiple tables in the same database according to certain rules.

[0105] The tables in the PRODUCT_DB_X (commodity database) can also be horizontally split according to the horizontal database splitting idea, and the purpose is to solve the problem of large single table data, as shown in Figure 5

[0106] If the product ID is even, map this operation to the product information 1 table; if the product ID is odd, map the operation to the product information 2 table. The table expression for this operation to access the table name is:

[0107] product information[product ID%2+1].

[0108] As can be seen from the above analysis, database and table splitting can effectively alleviate the performance bottleneck and pressure caused by single machine and single database, break through the bottleneck of network IO, hardware resources, and connection number, but also bring some problems. ​

[0109] 1. Transaction consistency problem: Since the database is distributed, there will be distributed transaction problems

[0110] 2. Cross-node association query: Before the database is divided, the store information can be queried by the following SQL:

[0111] SELECT p.*,r.[geographical area name],s.[store name],s.[credit]

[0112] FROM[product information]AS p

[0113] LEFT JOIN[geographical area]AS r ON p.[place of origin]=r.[region code]

[0114] LEFT JOIN[store information]AS s ON p.id=s.[store]

[0115] WHERE...ORDER BY...LIMIT...

[0116] But after the database is divided, [product information] and [store information] are not in the same database, even not in the same server, and cannot be associated.

[0117] The original association query can be divided into two queries, the first query finds the associated data ID in the result set, and then initiates the second request to get the associated data according to the ID, and finally assembles the data obtained.

[0118] 3. Cross-node paging and sorting function: When querying across multiple databases, the LIMIT paging and ORDER BY sorting problems become more complex. The data needs to be sorted and returned in different shard nodes, and then the result sets returned by different shards are aggregated and sorted again.

[0119] 4. Primary key duplication avoidance: In a database environment, since the data in the table exists in different databases at the same time, the self-incrementing primary key value will not work. The ID generated by a certain partition database cannot guarantee global uniqueness. Therefore, a global primary key needs to be designed separately to avoid cross-primary key duplication.

[0120] 5. Public table: In actual application scenarios, parameter tables, data dictionary tables, etc. are small in data volume, less variable, and belong to high-frequency joint query dependent tables, such as address area tables.

[0121] Such tables can be saved in each database, and all update operations on the public table are sent to all databases for execution.

[0122] Therefore, in the process of using Sharding-JDBC, we can clearly see the initialization process of the table, such as: we configure the sharding rule of the data table A, for example, we want to divide table A into two sub-tables A0 and A1, when we do not pre-create table A0 and A1, we execute a query statement to obtain data from table A, Sharding-JDBC finds A0 and A1 two shards to obtain the data we need, at this time, an error will be reported, prompting that the shard tables A0 and A1 do not exist.

[0123] Here we can clearly see a disadvantage, Sharding-JDBC cannot manage our sharded tables, that is, it cannot pre-create the tables A0 and A1 we need, and when we may need to create more shard tables, we need to pay additional cost to manage these shard tables, such as manually creating or manually modifying or manually deleting.

[0124] Embodiment one

[0125] Please refer to Figure 6 , which is a flowchart of the automatic table building method in the first embodiment of the application, and the method specifically includes steps S101 to S104:

[0126] S101, creating a data table sharding rule;

[0127] In specific implementation, it is necessary to configure the sharding rule, such as dividing table A into two pieces, table A0 and table A1, which needs to be configured in the shardingjdbc related configuration file, please refer to Figure 7 .

[0128] In this application, the configuration parameters include the configuration information of the data source and the configuration of the data table;

[0129] 1. The configuration information of the data source, that is, the database needs to be discovered, and the code is:

[0130] spring.shardingsphere.datasource.names=ds0,ds1;

[0131] 2. The configuration of the data table, that is, the configuration of the corresponding table in the database, and the code is:

[0132] spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{0..1}。

[0133] S102, obtain a configuration file of a table to be processed according to the data table sharding rule, to obtain a sharding table name and a database address corresponding to the sharding table name;

[0134] In the specific implementation, after the program is started, the pre-configured sharding rule is read.

[0135] It is obtained from the configuration file of the configuration rule that the table A needs to create the tables A0 and A1 in the databases DS0 and DS1, and the program needs to be obtained in ShardingDataSource, and the relevant package name is: org.apache.shardingsphere.shardingjdbc.jdbc.core.datasource.ShardingDataSource.

[0136] Through the analysis of the sharding rule, it can be known that A needs to be divided into two pieces, the table names A0 and A1 after sharding. And it needs to be known that the sharding tables A0 and A1 need to be created in which database, therefore, the corresponding database address, username and password can be obtained through the data table sharding rule.

[0137] S103, read the metadata table of the table to be processed, and obtain a corresponding sharding table table creation statement according to the metadata table;

[0138] In the specific implementation, the metadata of the configured initial table A is read, the metadata of the database table is agreed to be the table creation statement of the database table, the configuration is placed in the resource directory, the SQL configuration file is obtained in the program, the table creation statement of the SQL is parsed, the resource file is placed in the resource file directory of the program running, the program is obtained, the SQL statement is read, and the memory can be cached, and waits for the following use;

[0139] The SQL table creation statement read in the memory is used to replace the table name in the basic table, so as to be the table creation statement of the different sharding tables to be created.

[0140] S104, according to the sharding table creation statement, automatically create a sharding table corresponding to the sharding table name in the database corresponding to the database address.

[0141] In the specific implementation, according to the sharding table creation statement, automatically create a sharding table corresponding to the sharding table name in the database corresponding to the database address.

[0142] In the present application, the most original jdbc (a way of connecting a database) is used to connect the database.

[0143] Among them, the database addresses of the databases ds0 and ds1 are:

[0144] Jdbc:mysql: / / localhost:3306 / ds0

[0145] Jdbc:mysql: / / localhost:3306 / ds1

[0146] In the two databases automatically create two tables

[0147] t_order0, t_order1.

[0148] In this application, if the table has been created, the modification operation is executed, the program reads the generated shard table information, compares the difference, and generates the modified SQL statement. If the table is to be executed, the delete operation is performed on the relevant table identifier delete identifier information, which can be customized.

[0149] In summary, the automatic table creation method in the above embodiments of the application obtains the configuration file of the table to be processed by creating a data table sharding rule, obtains the shard table name and the corresponding database address, and obtains the corresponding shard table creation statement by reading the metadata table of the table to be processed. According to the shard table creation statement, the shard table is automatically created in the database corresponding to the database address, so that the user can manage the data table according to the configured shard rule, and the table can be automatically created and modified in the process of database and table splitting, avoiding the problem of high cost and easy error in the process of creating, managing and deleting the data table in the process of manually managing the database and table splitting.

[0150] Embodiment two

[0151] Please refer to Figure 8 , which is an automatic table creation method flow chart in the second embodiment of the application, and the method specifically includes steps S201 to S212:

[0152] S201, creating a data table sharding rule;

[0153] S202, obtaining the configuration file of the table to be processed according to the data table sharding rule, obtaining the shard table name and the database address corresponding to the shard table name;

[0154] S203, reading the metadata table of the table to be processed, obtaining the SQL configuration file in memory, and parsing the create table statement of the SQL configuration file;

[0155] S204, replacing the table name of the create table statement of the SQL configuration file with the shard table name to form the shard table creation statement;

[0156] S205, finding the corresponding database according to the database address, username and password;

[0157] S206, load a JDBC driver program and establish a connection with the database;

[0158] S207, create a Statement object and execute the sharding table creation statement;

[0159] S208, determine whether the sharding table creation statement is executed successfully;

[0160] S209, if the sharding table creation statement is executed successfully, automatically create a sharding table in the database;

[0161] S210, if the sharding table creation statement fails, issue a warning log;

[0162] S211, when modifying the sharding table, obtain the information of the sharding table before modification and the newly generated sharding table, and obtain the comparison difference between the information of the sharding table before modification and the newly generated sharding table, and generate a corresponding SQL table creation statement;

[0163] S212, create a new sharding table according to the SQL table creation statement.

[0164] The above method is implemented in the following manner:

[0165] A, configure the data table sharding rule.

[0166] #Data source one

[0167] sharding.jdbc.datasource.ds0.type = com.alibaba.druid.pool.DruidDataSource

[0168] sharding.jdbc.datasource.ds0.driver-class-name = com.mysql.jdbc.Driver

[0169] sharding.jdbc.datasource.ds0.url = jdbc:mysql: / / ip:3306 / test_0?characterEncoding=utf-8

[0170] sharding.jdbc.datasource.ds0.username = root

[0171] sharding.jdbc.datasource.ds0.password = root123456

[0172] #Data source two

[0173] sharding.jdbc.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource

[0174] sharding.jdbc.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver

[0175] sharding.jdbc.datasource.ds1.url=jdbc:mysql: / / ip:3306 / test_1? characterEncoding=utf-8

[0176] sharding.jdbc.datasource.ds1.username=root

[0177] sharding.jdbc.datasource.ds1.password=root123456

[0178] sharding.jdbc.config.sharding.tables.Test.actual-data-nodes=ds$->{0..1}.Test_$->{0..1}

[0179] B. Parsing configuration fragmentation rules:

[0180] ds$->{0..1}.Test_$->{0..1}, parsed from the expression,

[0181] We need to create corresponding tables Test0 and Test1 in data source 1 ds0 and data source 2 ds1

[0182] C. Read the rules we parsed and perform logical processing in the program:

[0183]

[0184] Read the shardingjdbc configuration file shardingDataSource, which contains the parsed configuration file:

[0185] D. Read the metadata of the shard table:

[0186] CREATE TABLE `Test`(

[0187] `mid` int(10) unsigned NOT NULL AUTO_INCREMENT,

[0188] `name` varchar(200) CHARACTER SET utf8mb4 DEFAULT NULL,

[0189] PRIMARY KEY (`mid`)

[0190] ) ENGINE = InnoDB AUTO_INCREMENT = 3318 DEFAULT CHARSET = utf8;

[0191] CREATE TABLE `Test_0` (

[0192] `mid` int(10) unsigned NOT NULL AUTO_INCREMENT,

[0193] `name` varchar(200) CHARACTER SET utf8mb4 DEFAULT NULL,

[0194] PRIMARY KEY (`mid`)

[0195] ) ENGINE = InnoDB AUTO_INCREMENT = 3318 DEFAULT CHARSET = utf8;

[0196] CREATE TABLE `Test_1` (

[0197] `mid` int(10) unsigned NOT NULL AUTO_INCREMENT,

[0198] `name` varchar(200) CHARACTER SET utf8mb4 DEFAULT NULL,

[0199] PRIMARY KEY (`mid`)

[0200] ) ENGINE = InnoDB AUTO_INCREMENT = 3318 DEFAULT CHARSET = utf8;

[0201] Special note: the final shard table is the two tables we want to create, Test_0, Test_1.

[0202] F, connect the database, create a table:

[0203] The database table creation method is that we connect data through JDBC, and execute SQL;

[0204] 1. Load the JDBC driver program;

[0205] 2. Create a database connection;

[0206]

[0207] 3. Create a Statement object;

[0208] 4. Execute the SQL statement, and process the result;

[0209]

[0210] 5. Close the JDBC object, and close the connection;

[0211]

[0212] It is particularly pointed out that when the result is processed in the fourth step, if the execution fails, a warning log can be printed or an exception can be thrown to stop the program from continuing to run;

[0213] If the table is to be modified, the differences between two database tables are compared, and then a modification method is executed.

[0214] G, after successful creation, there are Test0 and Test1 in ds0, and there are Test0 and Test1 in ds1.

[0215] H, after the creation is completed, the modification and deletion methods can also be executed.

[0216] In summary, the automatic table creation method in the above embodiment of the application obtains the configuration file of a to-be-processed table through creating a data table sharding rule, obtains a sharding table name and a corresponding database address, and obtains a corresponding sharding table creation statement by reading the metadata table of the to-be-processed table, so that the sharding table is automatically created in the database corresponding to the database address according to the sharding table creation statement, and the user can manage the data table according to the configured sharding rule, and the table can be automatically created and modified in the process of database and table splitting, thereby avoiding the problems of high cost and easy errors in the processes of creating, managing and deleting the data table in the process of manually managing the database and table splitting.

[0217] Embodiment three

[0218] Another aspect of the application also proposes an automatic table creation system, please refer to Figure 9As shown in the automatic table building system structure block diagram in the third embodiment of the present application, the automatic table building system comprises:

[0219] The first creating module 11 is configured to create a data table sharding rule.

[0220] The obtaining module 12 is configured to obtain a configuration file of a to-be-processed table according to the data table sharding rule, to obtain a sharding table name and a database address corresponding to the sharding table name.

[0221] The processing module 13 is configured to read a metadata table of the to-be-processed table, and to obtain a corresponding sharding table building statement according to the metadata table.

[0222] Further, the processing module 13 comprises:

[0223] The parsing unit is configured to obtain a SQL configuration file in memory, and to parse a create table statement of the SQL configuration file.

[0224] The processing unit is configured to replace a table name of the create table statement of the SQL configuration file with the sharding table name, to form the sharding table building statement.

[0225] The second creating module 14 is configured to automatically create a sharding table corresponding to the sharding table name in a database corresponding to the database address according to the sharding table building statement.

[0226] Further, the second creating module 14 comprises:

[0227] The searching unit is configured to search for a corresponding database according to the database address, a username and a password.

[0228] The loading unit is configured to load a JDBC driver program, and to establish a connection with the database.

[0229] The first creating unit is configured to create a Statement object, and to execute the sharding table building statement.

[0230] The judging unit is configured to judge whether the sharding table building statement is executed successfully.

[0231] The second creating unit is configured to automatically create a sharding table in the database if the sharding table building statement is executed successfully.

[0232] In some embodiments, the system further comprises:

[0233] The comparing module is configured to, when the sharding table is modified, obtain information of the sharding table before modification and a newly generated sharding table, and to obtain a comparison difference between the information of the sharding table before modification and the information of the newly generated sharding table, to generate a corresponding SQL building statement.

[0234] a third creating module configured to create a new sharding table according to the SQL table creation statement.

[0235] In some embodiments, the second creating module further comprises:

[0236] a control unit configured to issue a warning log or throw an exception if the execution of the sharding table creation statement fails.

[0237] The functions or operation steps realized by the above modules when executed are substantially the same as those of the above method embodiments, and thus will not be repeated here.

[0238] The automatic table creation system provided by the embodiments of the present application has the same implementation principle and technical effects as the above method embodiments. For brevity, the system embodiments not mentioned in the above will be described with reference to the corresponding contents in the above method embodiments.

[0239] Embodiment Four

[0240] The present application also proposes a computer device. Please refer to Figure 10 , which is a computer device in the fourth embodiment of the present application, comprising a memory 10, a processor 20, and a computer program 30 stored in the memory 10 and capable of running on the processor 20. The processor 20 implements the above automatic table creation method when executing the computer program 30.

[0241] The memory 10 comprises at least one type of readable storage medium, including a flash memory, a hard disk, a multimedia card, a card-type memory (e.g. an SD or DX memory, etc.), a magnetic memory, a magnetic disk, an optical disk, etc. The memory 10 can be an internal storage unit of a vehicle in some embodiments, such as a hard disk of the vehicle. The memory 10 can also be an external storage device in other embodiments, such as a plug-in hard disk, a smart media card (SMC), a secure digital (SD) card, a flash card, etc. Further, the memory 10 can comprise both an internal storage unit of a vehicle and an external storage device. The memory 10 can be used not only to store application software and various data installed in the vehicle, but also to temporarily store data that has been output or will be output.

[0242] The processor 20 may, in some embodiments, be an electronic control unit (ECU), a central processing unit (CPU), a controller, a microcontroller, a microprocessor, or other data processing chip, for running program codes or processing data stored in the memory 10, such as executing the access restriction program.

[0243] It should be noted that, Figure 10 The illustrated structure does not constitute a limitation on the computer device, which may, in other embodiments, include fewer or more components than illustrated, or combine certain components, or arrange different components.

[0244] The embodiments of the present application also propose a readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the automatic table building method as described above.

[0245] Those skilled in the art can understand that the logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as a list of executable instructions for implementing the logic function, which can be specifically implemented in any computer readable medium for use by or in conjunction with an instruction execution system, device or apparatus, such as a computer-based system, a system including a processor or other system that can fetch and execute instructions from the instruction execution system, device or apparatus. For the present specification, the "computer readable medium" can be any device that can contain, store, communicate, propagate or transport programs for use by or in conjunction with the instruction execution system, device or apparatus, or in conjunction with these instruction execution systems, devices or apparatus.

[0246] More specific examples (a non-exhaustive list) of the computer readable medium include the following: an electrical connection having one or more wires (electrical devices), a portable computer diskette (magnetic devices), a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber device, and a portable compact disc read-only memory (CDROM). In addition, the computer readable medium can even be paper or other suitable medium on which the program can be printed, as the program can be electronically obtained, for example, by optical scanning of the paper or other medium, followed by editing, interpreting or otherwise processing the program as necessary, and then storing it in a computer memory.

[0247] It should be understood that portions of the present application can be implemented with hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods can be implemented with software or firmware stored in memory and executed by a suitable instruction execution system. For example, if implemented in hardware, as in another embodiment, any of the following technologies, known in the art, or a combination thereof, can be used: discrete logic circuitry having logic gates for implementing logic functions upon an application of data signals, application specific integrated circuits having appropriate combinational logic gates, programmable gate arrays (PGA), field programmable gate arrays (FPGA), and the like.

[0248] The technical features of the above-described embodiments can be combined in any manner. For the sake of brevity, not all possible combinations of the technical features in the above-described embodiments are described, however, as long as the combination of the technical features does not result in a contradiction, it should be considered within the scope of the present disclosure.

[0249] The above-described embodiments only express several implementation manners of the present application, and the description is specific and detailed, but it should not be understood as a limitation on the scope of the patent. It should be noted that for those skilled in the art, without departing from the concept of the present application, a number of modifications and improvements can be made, which are all within the scope of the present application. Therefore, the scope of the patent of the present application should be subject to the appended claims.

Claims

1. An automatic table creation method, characterized in that: Applicable to middleware, including: Create data table sharding rules; Obtain the configuration file of the table to be processed according to the data table sharding rule, and obtain the sharding table name and the database address corresponding to the sharding table name; Read the metadata table of the table to be processed, and obtain the corresponding shard table creation statement according to the metadata table; Automatically create a shard table corresponding to the shard table name in the database corresponding to the database address according to the shard table creation statement; When modifying the shard table, obtain the information of the shard table before modification and the newly generated shard table, obtain the information difference between the shard table before modification and the newly generated shard table, and generate the corresponding SQL table creation statement; Create a new shard table according to the SQL table creation statement; The step of obtaining a corresponding shard table creation statement according to the metadata table includes: Obtain the SQL configuration file in the memory and parse the table creation statement in the SQL configuration file; The table name of the create table statement of the SQL configuration file is replaced by the shard table name to form the shard table creation statement.

2. The automatic table creation method according to claim 1, characterized in that: The step of automatically creating a shard table corresponding to the shard table name in the database corresponding to the database address according to the shard table creation statement includes: Find the corresponding database according to the database address, user name and password; Load the JDBC driver and establish a connection with the database; Create a Statement object and execute the shard table creation statement; Determine whether the shard table creation statement is executed successfully; If the fragment table creation statement is executed successfully, the fragment table is automatically created in the database.

3. The automatic table creation method according to claim 2, characterized in that: After determining whether the shard table creation statement is successfully executed, the method further includes: If the shard table creation statement fails to execute, a warning log is issued.

4. An automatic table creation system, characterized in that: Applicable to middleware, including: The first creation module is used to create data table sharding rules; An acquisition module is used to acquire the configuration file of the table to be processed according to the data table sharding rule, and obtain the sharding table name and the database address corresponding to the sharding table name; A processing module is used to read the metadata table of the table to be processed and obtain a corresponding shard table creation statement according to the metadata table; A second creation module is used to automatically create a shard table corresponding to the shard table name in the database corresponding to the database address according to the shard table creation statement; A comparison module is used to obtain information of the shard table before modification and the newly generated shard table when modifying the shard table, and to obtain the information difference between the shard table before modification and the newly generated shard table, and to generate a corresponding SQL table creation statement; The third creation module is used to create a new shard table according to the SQL table creation statement; The processing module includes: A parsing unit, configured to obtain an SQL configuration file in memory and parse a table creation statement in the SQL configuration file; The processing unit is used to replace the table name of the creation table statement of the SQL configuration file with the fragmentation table name to form the fragmentation table creation statement.

5. The automatic table creation system according to claim 4, characterized in that: The second creation module includes: A search unit, configured to search for a corresponding database according to the database address, user name, and password; A loading unit, used to load a JDBC driver and establish a connection with the database; The first creation unit is used to create a Statement object and execute the shard table creation statement; A judgment unit, configured to judge whether the shard table creation statement is executed successfully; The second creation unit is configured to automatically create a shard table in the database if the shard table creation statement is successfully executed.

6. A readable storage medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the automatic table creation method according to any one of claims 1 to 3 is implemented.

7. A computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein: When the processor executes the computer program, the automatic table creation method according to any one of claims 1 to 3 is implemented.

Images