A SQL execution method for database structure changes

Abstract

This invention discloses a SQL execution method for database structure changes, belonging to the field of database SQL execution. It solves the problem of how to perform cross-server batch SQL execution after database structure changes, thereby improving the efficiency of batch SQL execution and enabling timely handling of problems encountered during SQL execution. The invention creates a central database and sets up a data source information table; accesses the central database and sequentially creates connection information for sub-databases based on the fields contained in the queried data source information table; it traverses the sub-databases and concurrently submits SQL execution tasks; it completely eliminates manual operation from SQL execution to monitoring; developers only need to focus on the SQL code to be executed, writing it directly using the common single-server, single-database approach; it achieves full monitoring of the SQL execution process; it enables unified execution across platforms and database types; and it solves the problem of cross-platform data integration.

Description

Technical Field

[0001] This invention belongs to the field of database SQL execution, specifically a SQL execution method for database structure changes. Background Technology

[0002] When the volume of data increases dramatically, the database needs to be sharded. After sharding, the number of databases and servers becomes particularly large, making it very troublesome to change and adjust the database structure and initialize data during product iterations.

[0003] Currently, adjustments are usually made manually by executing SQL statements one by one on each server and database. This method is very inefficient and cannot monitor the execution status and efficiency of the SQL.

[0004] Therefore, this invention proposes an SQL execution method for database structure changes. Summary of the Invention

[0005] This invention aims to at least solve one of the technical problems existing in the prior art. To this end, this invention proposes a SQL execution method for database structure changes. This method solves the problem of how to perform cross-server batch SQL execution after database structure changes, thereby improving the efficiency of batch SQL execution and enabling timely handling of problems that arise during SQL execution.

[0006] To achieve the above objectives, according to an embodiment of the first aspect of the present invention, a method for executing SQL for database structure changes is provided, comprising:

[0007] Step 1: Create a central database and set up a data source information table; the central database is used to store connection information for the sub-databases;

[0008] Step 2: Access the central database and create connection information for the sub-databases sequentially based on the field information contained in the queried data source information table;

[0009] Step 3: Traverse the sub-databases and concurrently submit SQL execution tasks; Step 3 includes:

[0010] Step S1: Create an execution monitoring log file;

[0011] Step S2: Determine the database type of the sub-database and ensure that the processing logic for sub-databases of different database types is compatible accordingly;

[0012] Step S3: Create a folder of SQL statements to be executed for the corresponding database type, obtain the list of SQL statements to be executed according to the database type of the sub-database, and perform syntax checking and batch compatibility checks on the list of SQL statements to be executed, thereby obtaining an SQL execution task for the list of SQL statements to be executed;

[0013] Step S4: Submit the obtained SQL execution tasks concurrently to multiple database servers for execution.

[0014] Furthermore, the sub-database is used to store source data; the source data is the original business data.

[0015] Furthermore, the central database adopts a table structure; the central database sets up a data source information table for each sub-database, and the data source information table has multiple fields, namely: primary key, data source name, database connection string, database username, password, data source description, data source type, database type, database IP address, and database name.

[0016] Furthermore, the primary key represents a unique identifier for the central database;

[0017] The data source name represents the business name of the sub-database;

[0018] The database connection string represents the connection used to create the sub-database;

[0019] The database username and password represent access permission information for the sub-database;

[0020] The data source description represents a description of the sub-database, facilitating subsequent management, maintenance, and viewing of the sub-database;

[0021] The data source type represents the sub-database type categorized according to different business functions;

[0022] The database type refers to the sub-database types classified according to different database specifications;

[0023] The database IP address represents the IP address of the sub-database to be connected to;

[0024] The database name refers to the name of the sub-database named according to the database specification.

[0025] Furthermore, step two includes:

[0026] Use Python code to access the central database, query the data source information table, and write SQL statements to retrieve data source information.

[0027] The SQL statement used to retrieve data source information retrieves the field information contained in the data source information table in the central database.

[0028] Based on the retrieved field information, connection information for each sub-database is created sequentially.

[0029] Furthermore, the monitoring log file is used to store monitoring logs for each step of the execution process; when an abnormal situation occurs, an email or SMS is sent to the user terminal of the operation and maintenance personnel to notify them to intervene.

[0030] Further, step S3 includes:

[0031] The " / # / " symbol is used to separate the SQL statements to be executed in each SQL file to be executed. By recognizing the " / # / " symbol, the SQL statements to be executed can be split in batches to obtain a complete list of SQL statements to be executed.

[0032] The SQL statements in the obtained list of SQL statements to be executed are subjected to a syntax check. If there are no syntax errors, batch SQL compatibility is performed on the SQL statements in the list of SQL statements to be executed, thereby obtaining an SQL execution task for the list of SQL statements to be executed.

[0033] Furthermore, the encoding formats that enable batch compatibility for executing SQL statements include UTF-8, GB2312, UTF-16, ISO-8859-1, and EUC-JP.

[0034] Compared with the prior art, the beneficial effects of the present invention are:

[0035] This invention first creates a central database and sets up a data source information table; then it accesses the central database and sequentially creates connection information for sub-databases based on the field information contained in the queried data source information table; finally, it traverses the sub-databases and concurrently submits SQL execution tasks; the final step also includes creating an execution monitoring log file; determining the database type of the sub-databases and ensuring compatibility of processing logic for sub-databases of different types; creating a folder of SQL statements to be executed for the corresponding database type; obtaining a list of required SQL statements to be executed based on the database type of the sub-databases; and performing syntax checks and batch compatibility checks on the list of SQL statements to be executed, thereby obtaining an SQL execution task for the list of SQL statements to be executed. The acquired SQL execution tasks are concurrently submitted to multiple database servers for execution. This invention automates the entire process from SQL execution to monitoring, completely eliminating manual operation. Developers only need to focus on coding the SQL to be executed, writing it directly using the common single-server, single-database approach. It achieves full monitoring of the SQL execution process, real-time monitoring of SQL execution efficiency and server execution status. It enables unified execution across platforms and database types, achieving unified SQL execution across different types of databases, thus achieving unified execution monitoring tasks. Furthermore, after executing SQL across multiple sub-databases on multiple servers, the data merging from different sub-databases on different servers can be handled by this task, solving the problem of cross-platform data integration. Attached Figure Description

[0036] Figure 1 This is a schematic diagram of the process of the present invention. Detailed Implementation

[0037] The technical solution of the present invention will be clearly and completely described below with reference to the embodiments. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0038] like Figure 1 As shown, a SQL execution method for database structure changes includes:

[0039] Step 1: Create a central database and set up the data source information table;

[0040] In this application, the central database is used to store connection information of the sub-databases;

[0041] The sub-database is used to store source data; the source data is the original business data.

[0042] In this embodiment, the sub-databases are configured through a database server, and a database server can contain one or more sub-databases;

[0043] The central database adopts a table structure; specifically, the central database sets up a data source information table for each sub-database, and the data source information table has multiple fields, namely: primary key, data source name, database connection string, database username, password, data source description, data source type, database type, database IP address, and database name, etc.

[0044] The primary key represents the unique identifier of the central database;

[0045] The data source name represents the business name of the sub-database;

[0046] The database connection string represents the connection for creating a sub-database; that is, when accessing the sub-database later, a connection can be established through the database connection string, and the corresponding sub-database can only be accessed after the connection is successful.

[0047] The database username and password represent access permission information for the sub-database;

[0048] The data source description represents a description of the sub-database, which facilitates subsequent management, maintenance, and viewing of the sub-database.

[0049] The data source type refers to the sub-database type categorized according to different business functions; such as: central database data source, business database data source, user behavior record database data source, etc.

[0050] The database type refers to the sub-database types classified according to different database specifications; database types include MySQL and SQL Server.

[0051] The database IP address represents the IP address of the sub-database to be connected to, for example, 192.168.70.1;

[0052] The database name refers to the name of a sub-database named according to the database specification; for example, if a sub-database is used to record data on user page access behavior, then the sub-database is named jxc_page_log, and the sub-database with this database name will be used when connecting and accessing it as needed.

[0053] Step 2: Access the central database and create connection information for the sub-databases sequentially based on the field information contained in the queried data source information table;

[0054] Specifically, Python code is used to access the central database created in step one, query the data source information table, and write an SQL statement to retrieve data source information. This SQL statement is compatible with multiple database types such as MySQL and SQL Server. In this embodiment, the SQL statement to retrieve data source information retrieves all the field information listed in step one.

[0055] The SQL statement used to retrieve data source information retrieves the field information contained in the data source information table in the central database.

[0056] Based on the retrieved field information, create the connection information for each sub-database in sequence;

[0057] Step 3: Traverse the sub-databases and concurrently submit SQL execution tasks; the specific process is as follows:

[0058] Step S1: Create an execution monitoring log file;

[0059] The monitoring log file is used to store monitoring logs for each step of the execution process; when an abnormal situation occurs, an email or SMS is immediately sent to the user terminal of the operation and maintenance personnel to notify them to intervene.

[0060] The monitoring log file records detailed operation execution time, database connection details such as database name, SQL statement being executed, detailed exception information thrown when an execution exception occurs, and execution time of each SQL statement;

[0061] Step S2: Determine the database type of the sub-database and ensure that the processing logic for sub-databases of different database types is compatible accordingly;

[0062] For example, if the sub-database is a MySQL database, it is necessary to check whether the corresponding SQL statement conforms to the SQL syntax of the MySQL database; if the sub-database is a SQL Server database, it is necessary to check whether the corresponding SQL statement conforms to the SQL syntax of the SQL Server database.

[0063] Different databases have different SQL syntax and corresponding SQL writing standards. When these specific standards are not met, the database cannot recognize and execute the SQL.

[0064] Step S3: Create a folder of SQL statements to be executed for the corresponding database type, obtain the list of SQL statements to be executed according to the database type of the sub-database, and perform syntax checking and batch compatibility checks on the list of SQL statements to be executed, thereby obtaining an SQL execution task for the list of SQL statements to be executed;

[0065] Specifically, different SQL folders to be executed are created for sub-databases of different database types. Each SQL folder contains multiple SQL files to be executed, and each SQL file contains the corresponding SQL statement to be executed.

[0066] The list of SQL statements to be executed is obtained by batch splitting. In each SQL file to be executed, the " / # / " symbol is used to separate the SQL statements to be executed. By recognizing the " / # / " symbol, the SQL statements to be executed can be split in batches, and finally a complete list of SQL statements to be executed can be obtained.

[0067] The SQL statements in the obtained list of SQL statements to be executed are subjected to a syntax check; if there are no syntax errors, batch SQL compatibility is performed on the SQL statements in the list of SQL statements to be executed; thus obtaining an SQL execution task for the list of SQL statements to be executed.

[0068] In this embodiment, the encoding formats that enable batch compatibility for executing SQL statements include UTF-8, GB2312, UTF-16, ISO-8859-1, and EUC-JP. Compatibility with other encoding formats can be extended at any time if it is necessary.

[0069] It's important to note that the encoding format refers to the encoding used to encode the SQL statement code. For example, when you save an SQL statement file created using Notepad on a Windows system, you can choose the encoding format. If you choose UTF-8, the SQL statement code will be encoded using UTF-8. When you open (decode) it using GBK or other encodings, garbled characters will appear. In this case, the program needs to recognize the encoding format of the SQL statement and decode it using the corresponding encoding format before it can read the normal SQL statement.

[0070] Step S4: Submit the obtained SQL execution tasks concurrently to multiple database servers for execution;

[0071] In this embodiment, concurrent tasks are submitted according to the unit of the database server;

[0072] Considering the performance issues of a single database server, the approach is to submit one SQL execution task to each database server.

[0073] After receiving the SQL execution task, the database server executes the SQL statements in the order they belong to the SQL filenames to be executed within the SQL execution task.

[0074] The above embodiments are only used to illustrate the technical methods of the present invention and are not intended to limit it. Although the present invention has been described in detail with reference to preferred embodiments, those skilled in the art should understand that modifications or equivalent substitutions can be made to the technical methods of the present invention without departing from the spirit and scope of the technical methods of the present invention.

Claims

1. A SQL execution method for database structure changes, characterized in that, include: Step 1: Create a central database and set up a data source information table; the central database is used to store connection information for the sub-databases; Step 2: Access the central database and create connection information for the sub-databases sequentially based on the field information contained in the queried data source information table; Step 3: Traverse the sub-databases and concurrently submit SQL execution tasks; Step 3 includes: Step S1: Create an execution monitoring log file; Step S2: Determine the database type of the sub-database and ensure that the processing logic for sub-databases of different database types is compatible accordingly; Step S3: Create a folder of SQL statements to be executed for the corresponding database type, obtain the list of SQL statements to be executed according to the database type of the sub-database, and perform syntax checking and batch compatibility checks on the list of SQL statements to be executed, thereby obtaining an SQL execution task for the list of SQL statements to be executed; Step S4: Submit the obtained SQL execution tasks concurrently to multiple database servers for execution; The central database adopts a table structure; the central database sets up a data source information table for each sub-database, and the data source information table has multiple fields, namely: primary key, data source name, database connection string, database username, password, data source description, data source type, database type, database IP address, and database name; Step two includes: Use Python code to access the central database, query the data source information table, and write SQL statements to retrieve data source information. The SQL statement used to retrieve data source information retrieves the field information contained in the data source information table in the central database. Based on the retrieved field information, create the connection information for each sub-database in sequence; Step S3 includes: The " / # / " symbol is used to separate the SQL statements to be executed in each SQL file to be executed. By recognizing the " / # / " symbol, the SQL statements to be executed can be split in batches to obtain a complete list of SQL statements to be executed. The SQL statements in the obtained list of SQL statements to be executed are subjected to a syntax check. If there are no syntax errors, batch SQL compatibility is performed on the SQL statements in the list of SQL statements to be executed, thereby obtaining an SQL execution task for the list of SQL statements to be executed.

2. The SQL execution method for database structure changes according to claim 1, characterized in that, The sub-database is used to store source data; the source data is the original business data.

3. The SQL execution method for database structure changes according to claim 1, characterized in that, The primary key represents a unique identifier for the central database; The data source name represents the business name of the sub-database; The database connection string represents the connection used to create the sub-database; The database username and password represent access permission information for the sub-database; The data source description represents a description of the sub-database, facilitating subsequent management, maintenance, and viewing of the sub-database; The data source type represents the sub-database type categorized according to different business functions; The database type refers to the sub-database types classified according to different database specifications; The database IP address represents the IP address of the sub-database to be connected to; The database name refers to the name of the sub-database named according to the database specification.

4. The SQL execution method for database structure changes according to claim 1, characterized in that, The monitoring log file is used to store monitoring logs for each step of the execution process; when an abnormal situation occurs, an email or SMS is sent to the user terminal of the operation and maintenance personnel to notify them to intervene.

5. The SQL execution method for database structure changes according to claim 1, characterized in that, Encoding formats that enable batch compatibility for executing SQL statements include UTF-8, GB2312, UTF-16, ISO-8859-1, and EUC-JP.

Images