CN110704398B - Database migration method and device from MySQL to Oracle and computer equipment - Google Patents
Database migration method and device from MySQL to Oracle and computer equipment Download PDFInfo
- Publication number
- CN110704398B CN110704398B CN201910945144.7A CN201910945144A CN110704398B CN 110704398 B CN110704398 B CN 110704398B CN 201910945144 A CN201910945144 A CN 201910945144A CN 110704398 B CN110704398 B CN 110704398B
- Authority
- CN
- China
- Prior art keywords
- database
- mysql
- initial
- oracle
- definition information
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
- G06F16/214—Database migration support
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Software Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application relates to a database migration method, a device and computer equipment from MySQL to Oracle, wherein the method comprises the following steps: selecting an initial MySQL database; acquiring table name information of all database tables of an initial MySQL database; circularly traversing the database table according to the table name information to obtain initial table structure definition information; converting according to the database table and the initial table structure definition information to obtain target table structure definition information meeting Oracle requirements; and generating a target Oracle database according to the database table and the target table structure definition information. According to the scheme, the table name information and the initial table structure definition information are acquired, the initial table structure definition information is converted to obtain the target table structure definition information, and finally the corresponding target Oracle database is generated, so that the database can be selected for migration according to actual needs, accurate migration of the database is ensured, and meanwhile, the migration cost is reduced.
Description
Technical Field
The application relates to data migration, in particular to a database migration method, a database migration device and computer equipment from MySQL to Oracle.
Background
MySQL is a relational database management system that keeps data in different tables rather than placing all data in one large warehouse, which increases speed and flexibility.
ORACLE is the most widely used database management system in the world at present, and is used as a general database system, which has complete data management function; as a relational database, it is a product of complete relations; it implements distributed processing functions as a distributed database.
MySQL and Oracel are database management systems commonly used in enterprise business systems, but have the problem of incomplete compatibility, when a database of the business system is migrated from MySQL to Oracle, a corresponding table structure and data are often required to be migrated, a migration tool in a commonly used database client cannot usually export a database required by the user according to specific business requirements, and in the migration process, higher migration cost is brought under the condition that more databases exist.
In the existing database migration tools, in the migration operation, most of the database migration tools have no customizable settings, if any, configurable places are few, and the produced database migration scripts, whether in a format or in a sentence, may not be required by themselves, so that in the migration process, in the case of more databases, higher migration cost is brought.
Disclosure of Invention
The application aims to overcome the defects of the prior art and provides a database migration method, a device and computer equipment from MySQL to Oracle.
In order to achieve the above purpose, the present application adopts the following technical scheme: a database migration method from MySQL to Oracle, comprising the steps of:
selecting an initial MySQL database;
acquiring table name information of all database tables of an initial MySQL database;
circularly traversing the database table according to the table name information to obtain initial table structure definition information;
converting according to the database table and the initial table structure definition information to obtain target table structure definition information meeting Oracle requirements;
and generating a target Oracle database according to the database table and the target table structure definition information.
Further, the step of obtaining the table name information of all database tables of the initial MySQL database includes:
and calling all database tables in the initial MySQL database through a show table statement to acquire corresponding table name information.
Further, the step of circularly traversing the database table according to the table name information to obtain the definition information of the initial table structure includes:
and circularly traversing the corresponding database table through the desc < + > table name < + > statement to obtain initial table structure definition information, wherein the initial table structure definition information comprises a field column name, a data type of the field column, whether the field column is empty, whether the field column is a primary key or a unique key, a default value and additional information.
Further, the step of generating the target Oracle database according to the database table and the target table structure definition information includes:
building a table grammar based on the database of Oracle to generate a table building statement;
splicing all the table-building sentences to generate an SQL definition sentence file;
and importing the SQL definition statement file into Oracle to generate a target Oracle database.
The application also adopts the following technical scheme: a database migration apparatus from MySQL to Oracle, comprising:
the database selection unit is used for selecting an initial MySQL database;
the first acquisition unit is used for acquiring the table name information of all database tables of the initial MySQL database;
the second acquisition unit is used for circularly traversing the database table according to the table name information and acquiring initial table structure definition information;
the format conversion unit is used for converting the database table and the initial table structure definition information to obtain target table structure definition information meeting the Oracle requirement;
and the database generating unit is used for generating a target Oracle database according to the database table and the target table structure definition information.
Further, the first obtaining unit includes a table name obtaining module, configured to call all database tables in the initial MySQL database through a show table statement, and obtain corresponding table name information.
Further, the second obtaining unit includes a table structure obtaining module, configured to obtain initial table structure definition information by circularly traversing the corresponding database table through the desc+ [ table name ] statement, where the initial table structure definition information includes a field column name, a data type of the field column, whether the field column is empty, whether the field column is a primary key or a unique key, a default value, and additional information.
Further, the database generation unit comprises a statement generation module, a statement splicing module and a statement importing module;
the statement generation module is used for generating a statement of building a table based on the database table building grammar of Oracle;
the statement splicing module is used for splicing all the table-building statements to generate an SQL definition statement file;
the statement importing module is used for importing the SQL definition statement file into the Oracle to generate a target Oracle database.
The application also adopts the following technical scheme: a computer device comprising a memory and a processor, the memory having stored thereon a computer program, the processor implementing a database migration method from MySQL to Oracle as claimed in any one of the preceding claims when the computer program is executed.
The application also adopts the following technical scheme: a storage medium storing a computer program which when executed by a processor implements a database migration method from MySQL to Oracle as claimed in any preceding claim.
Compared with the prior art, the application has the beneficial effects that: after the initial MySQL database is selected, table name information and initial table structure definition information are acquired, the initial table structure definition information is converted to obtain target table structure definition information, and finally a corresponding target Oracle database is generated according to the target table structure definition information, so that the database can be selected for migration according to actual needs, accurate migration of the database is ensured, and meanwhile, migration cost is reduced; meanwhile, the method can be fully compatible with Oracle according to the transferred database, so that the transferred database is ensured to be complete, and the data security is improved.
The application is further described below with reference to the drawings and specific embodiments.
Drawings
In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings required for the description of the embodiments will be briefly described below, and it is obvious that the drawings in the following description are some embodiments of the present application, and other drawings may be obtained according to these drawings without inventive effort for a person skilled in the art.
FIG. 1 is a schematic diagram of an application scenario of a database migration method from MySQL to Oracle according to an embodiment of the present application;
FIG. 2 is a schematic flow chart of a database migration method from MySQL to Oracle according to an embodiment of the present application;
FIG. 3 is a schematic sub-flowchart of a database migration method from MySQL to Oracle according to an embodiment of the present application;
FIG. 4 is a schematic block diagram of a database migration apparatus from MySQL to Oracle according to embodiments of the present application;
FIG. 5 is a schematic block diagram of a first obtaining unit of a database migration apparatus from MySQL to Oracle according to an embodiment of the present application;
FIG. 6 is a schematic block diagram of a second obtaining unit of the database migration apparatus from MySQL to Oracle according to embodiments of the present application;
FIG. 7 is a schematic block diagram of a database generation unit of a database migration apparatus from MySQL to Oracle provided by an embodiment of the present application;
fig. 8 is a schematic block diagram of a computer device according to an embodiment of the present application.
Detailed Description
The following description of the embodiments of the present application will be made clearly and fully with reference to the accompanying drawings, in which it is evident that the embodiments described are some, but not all embodiments of the application. All other embodiments, which can be made by those skilled in the art based on the embodiments of the application without making any inventive effort, are intended to be within the scope of the application.
It should be understood that the terms "comprises" and "comprising," when used in this specification and the appended claims, specify the presence of stated features, integers, steps, operations, elements, and/or components, but do not preclude the presence or addition of one or more other features, integers, steps, operations, elements, components, and/or groups thereof.
It is also to be understood that the terminology used in the description of the application herein is for the purpose of describing particular embodiments only and is not intended to be limiting of the application. As used in this specification and the appended claims, the singular forms "a," "an," and "the" are intended to include the plural forms as well, unless the context clearly indicates otherwise.
It should be further understood that the term "and/or" as used in the present specification and the appended claims refers to any and all possible combinations of one or more of the associated listed items, and includes such combinations.
Referring to fig. 1 and fig. 2, fig. 1 is a schematic diagram of an application scenario of a database migration method from MySQL to Oracle according to an embodiment of the present application. FIG. 2 is a schematic flow chart of a database migration method from MySQL to Oracle according to an embodiment of the present application. The method for migrating the database from MySQL to Oracle is applied to terminals (a first terminal and a second terminal), performs data interaction with a server (a MySQL server and an Oracle server), selects an initial MySQL database according to a database selection instruction of the terminal received by the MySQL server, further transmits table name information and initial table structure definition information of all database tables of the initial MySQL database to the terminal, converts the table name information and the initial table structure definition information to obtain target table structure definition information by the terminal, generates an SQL definition statement file according to the target table structure definition information and the database tables at the terminal, and imports the SQL definition statement file into the Oracle server to generate the target Oracle database.
Fig. 2 is a flow chart of a database migration method from MySQL to Oracle according to an embodiment of the present application. As shown in fig. 2, the method includes the following steps S10 to S50.
S10, selecting an initial MySQL database.
In this embodiment MySQL is a relational database management system that keeps data in different tables rather than placing all data in one large warehouse, which increases speed and flexibility. ORACLE is the most widely used database management system in the world at present, and is used as a general database system, which has complete data management function; as a relational database, it is a product of complete relations; it implements distributed processing functions as a distributed database. MySQL and Oracle are two mainstream relational database systems that are slightly different in terms of use, but are compatible with the main SQL query statement, but are not fully compatible in some places, and the existing migration tools cannot export the databases needed by themselves according to specific business requirements.
The terminal issues a corresponding database selection instruction to the MySQL server, selects one or more target databases as an initial MySQL database, and then further migrates the initial MySQL database. According to the scheme, the databases are selected for migration according to actual needs, so that accurate migration of the databases is ensured, and meanwhile, simultaneous migration of other unnecessary databases is avoided, and further migration cost is reduced.
S20, obtaining the table name information of all database tables of the initial MySQL database.
In this embodiment, the data table is composed of three parts, a table name, a field in the table, and a record of the table. The design of the data table structure is to define the table name of the data table, determine which fields the data table contains, the field name, the field type, the width and the like of each field. According to the scheme, the table name information of all database tables of the initial MySQL database is acquired, and the initial table structure definition information of the corresponding data table can be further acquired according to the table name.
In one embodiment, the step S20 specifically includes a step S21.
S21, calling all database tables in the initial MySQL database through a show table statement to acquire corresponding table name information.
In an embodiment, all the data tables under the current initial MySQL database can be displayed through a show table statement, and corresponding table name information is directly obtained according to the displayed data tables.
S30, circularly traversing the database table according to the table name information to obtain the definition information of the initial table structure.
In this embodiment, the data table is composed of three parts, a table name, a field in the table, and a record of the table. The design of the data table structure is to define the table name of the data table, determine which fields the data table contains, the field name, the field type, the width and the like of each field. An initial MySQL database comprises a plurality of database tables, and the scheme can obtain corresponding initial table structure definition information by circularly traversing all the database tables through table names.
In one embodiment, the step S30 specifically includes a step S31.
S31, traversing the corresponding database table through the desc < + > table name > sentence in a circulating way, and obtaining the definition information of the initial table structure.
In one embodiment, by the desc + [ table name ] statement, it can be shown which fields are specifically defined by the data table, and the type size of each field, and which are primary keys, which have constraints, and whether each field defines a default value, etc.
Specifically, the initial table structure definition information includes a field column name, a data type of the field column, whether it is empty, whether it is a primary key or a unique key, a default value, and additional information.
Specifically, a show table sentence and a desc [ table name ] sentence can be written into a Python script, the Python script is imported into a terminal, the script is automatically connected to a MySQL database, and then database definition sentence forms such as show tables and desc [ table name ] sentences are executed to obtain all table name information and table structure definition information.
S40, according to the database table and the initial table structure definition information, converting to obtain target table structure definition information meeting the Oracle requirement.
In this embodiment MySQL and Oracle are two mainstream relational database systems that differ slightly in terms of use, but are compatible with the main SQL query statement, but are not fully compatible in some places. Because MySQL and Oracle are not fully compatible, the acquired database table and the initial table structure definition information are required to be converted through corresponding assembly and conversion rules, so that the target table structure definition information meeting the requirement of the Oracle database is obtained through conversion, the integrity of the migrated database is ensured, and the data security is improved.
In particular, during the conversion process, the desired index, trigger, sequence, etc. may also be generated.
S50, generating a target Oracle database according to the database table and the target table structure definition information.
In this embodiment, after obtaining the target table structure definition information and the data table meeting the requirement of the Oracle database, the terminal may generate a table-building statement according to the table-building grammar of the Oracle database, splice all the table-building statements to generate an SQL definition statement file, and import the SQL definition statement file into the Oracle server to generate the target Oracle database. According to the scheme, the database is selected for migration according to actual needs, so that the accurate migration of the database is ensured, and meanwhile, the migration cost is reduced; meanwhile, the method can be fully compatible with Oracle according to the transferred database, so that the transferred database is ensured to be complete, and the data security is improved.
Referring to FIG. 3, in one embodiment, step S50 includes steps S51-S53.
S51, generating a table construction statement by using the target table structure definition information based on the database table construction grammar of Oracle.
S52, splicing all the table-building sentences to generate an SQL definition sentence file.
S53, importing the SQL definition statement file into an Oracle to generate a target Oracle database.
After the initial MySQL database is selected, table name information and initial table structure definition information are acquired, the initial table structure definition information is converted to obtain target table structure definition information, and finally a corresponding target Oracle database is generated according to the target table structure definition information, so that the database can be selected for migration according to actual needs, accurate migration of the database is ensured, and meanwhile, migration cost is reduced; meanwhile, the method can be fully compatible with Oracle according to the transferred database, so that the transferred database is ensured to be complete, and the data security is improved.
FIG. 4 is a schematic block diagram of a database migration apparatus from MySQL to Oracle according to embodiments of the present application. As shown in fig. 4, the present application also provides a database migration device from MySQL to Oracle, corresponding to the above database migration method from MySQL to Oracle. The database migration device from MySQL to Oracle includes a unit for performing the above database migration method from MySQL to Oracle, and the device may be configured in a desktop computer, a tablet computer, a portable computer, or the like. Specifically, referring to fig. 4, the database migration apparatus from MySQL to Oracle includes a database selection unit 10, a first acquisition unit 20, a second acquisition unit 30, a format conversion unit 40, and a database generation unit 50.
A database selection unit 10 for selecting an initial MySQL database.
In this embodiment MySQL is a relational database management system that keeps data in different tables rather than placing all data in one large warehouse, which increases speed and flexibility. ORACLE is the most widely used database management system in the world at present, and is used as a general database system, which has complete data management function; as a relational database, it is a product of complete relations; it implements distributed processing functions as a distributed database. MySQL and Oracle are two mainstream relational database systems that are slightly different in terms of use, but are compatible with the main SQL query statement, but are not fully compatible in some places, and the existing migration tools cannot export the databases needed by themselves according to specific business requirements.
The terminal issues a corresponding database selection instruction to the MySQL server, selects one or more target databases as an initial MySQL database, and then further migrates the initial MySQL database. According to the scheme, the databases are selected for migration according to actual needs, so that accurate migration of the databases is ensured, and meanwhile, simultaneous migration of other unnecessary databases is avoided, and further migration cost is reduced.
The first obtaining unit 20 is configured to obtain table name information of all database tables in the initial MySQL database.
In this embodiment, the data table is composed of three parts, a table name, a field in the table, and a record of the table. The design of the data table structure is to define the table name of the data table, determine which fields the data table contains, the field name, the field type, the width and the like of each field. According to the scheme, the table name information of all database tables of the initial MySQL database is acquired, and the initial table structure definition information of the corresponding data table can be further acquired according to the table name.
Referring to fig. 5, in an embodiment, the first obtaining unit 20 includes a table name obtaining module 21, where the table name obtaining module 21 is configured to call all database tables in the initial MySQL database through a show table statement to obtain corresponding table name information.
In an embodiment, all the data tables under the current initial MySQL database can be displayed through a show table statement, and corresponding table name information is directly obtained according to the displayed data tables.
The second obtaining unit 30 is configured to obtain the initial table structure definition information by circularly traversing the database table according to the table name information.
In this embodiment, the data table is composed of three parts, a table name, a field in the table, and a record of the table. The design of the data table structure is to define the table name of the data table, determine which fields the data table contains, the field name, the field type, the width and the like of each field. An initial MySQL database comprises a plurality of database tables, and the scheme can obtain corresponding initial table structure definition information by circularly traversing all the database tables through table names.
Referring to fig. 6, in an embodiment, the second obtaining unit 30 includes a table structure obtaining module 31, where the table structure obtaining module 31 is configured to obtain the initial table structure definition information by circularly traversing the corresponding database table by the desc+ [ table name ] statement.
The initial table structure definition information includes a field column name, a data type of the field column, whether it is empty, whether it is a primary key or a unique key, a default value, and additional information.
In one embodiment, by the desc + [ table name ] statement, it can be shown which fields are specifically defined by the data table, and the type size of each field, and which are primary keys, which have constraints, and whether each field defines a default value, etc.
Specifically, a show table sentence and a desc [ table name ] sentence can be written into a Python script, the Python script is imported into a terminal, the script is automatically connected to a MySQL database, and then database definition sentence forms such as show tables and desc [ table name ] sentences are executed to obtain all table name information and table structure definition information.
The format conversion unit 40 is configured to convert the target table structure definition information meeting the Oracle requirement according to the database table and the initial table structure definition information.
In this embodiment MySQL and Oracle are two mainstream relational database systems that differ slightly in terms of use, but are compatible with the main SQL query statement, but are not fully compatible in some places. Because MySQL and Oracle are not fully compatible, the acquired database table and the initial table structure definition information are required to be converted through corresponding assembly and conversion rules, so that the target table structure definition information meeting the requirement of the Oracle database is obtained through conversion, the integrity of the migrated database is ensured, and the data security is improved.
In particular, during the conversion process, the desired index, trigger, sequence, etc. may also be generated.
The database generating unit 50 is configured to generate a target Oracle database according to the database table and the target table structure definition information.
In this embodiment, after obtaining the target table structure definition information and the data table meeting the requirement of the Oracle database, the terminal may generate a table-building statement according to the table-building grammar of the Oracle database, splice all the table-building statements to generate an SQL definition statement file, and import the SQL definition statement file into the Oracle server to generate the target Oracle database. According to the scheme, the database is selected for migration according to actual needs, so that the accurate migration of the database is ensured, and meanwhile, the migration cost is reduced; meanwhile, the method can be fully compatible with Oracle according to the transferred database, so that the transferred database is ensured to be complete, and the data security is improved.
Referring to fig. 7, in an embodiment, the database generation unit 50 includes a sentence generation module 51, a sentence splicing module 52, and a sentence importing module 53.
The statement generation module 51 is configured to generate a table-building statement based on the database table-building grammar of Oracle.
The sentence splicing module 52 is configured to splice all the table-building sentences to generate an SQL definition sentence file.
The statement importing module 53 is configured to import the SQL definition statement file into Oracle to generate the target Oracle database.
According to the database migration device from MySQL to Oracle, after an initial MySQL database is selected, table name information and initial table structure definition information are obtained, the initial table structure definition information is converted to obtain target table structure definition information, and finally a corresponding target Oracle database is generated according to the target table structure definition information, so that the database can be selected for migration according to actual needs, accurate migration of the database is ensured, and meanwhile migration cost is reduced; meanwhile, the method can be fully compatible with Oracle according to the transferred database, so that the transferred database is ensured to be complete, and the data security is improved.
It should be noted that, as those skilled in the art can clearly understand, the specific implementation process of the foregoing database migration device from MySQL to Oracle and each unit may refer to the corresponding description in the foregoing method embodiment, and for convenience and brevity of description, the description is omitted here.
Referring to fig. 8, fig. 8 is a schematic block diagram of a computer device according to an embodiment of the present application. The computer device 500 may be a terminal or a server, where the terminal may be an electronic device with a communication function, such as a smart phone, a tablet computer, a notebook computer, a desktop computer, a personal digital assistant, and a wearable device. The server may be an independent server or a server cluster formed by a plurality of servers.
With reference to FIG. 8, the computer device 500 includes a processor 502, memory, and a network interface 505 connected by a system bus 501, where the memory may include a non-volatile storage medium 503 and an internal memory 504.
The non-volatile storage medium 503 may store an operating system 5031 and a computer program 5032. The computer program 5032 includes program instructions that, when executed, cause the processor 502 to perform a database migration method from MySQL to Oracle.
The processor 502 is used to provide computing and control capabilities to support the operation of the overall computer device 500.
The internal memory 504 provides an environment for the execution of a computer program 5032 in the non-volatile storage medium 503, which computer program 5032, when executed by the processor 502, causes the processor 502 to perform a database migration method from MySQL to Oracle.
The network interface 505 is used for network communication with other devices. It will be appreciated by those skilled in the art that the architecture shown in fig. 8 is merely a block diagram of some of the architecture relevant to the present inventive arrangements and is not limiting of the computer device 500 to which the present inventive arrangements may be implemented, as a particular computer device 500 may include more or fewer components than shown, or may combine some of the components, or have a different arrangement of components.
Wherein the processor 502 is adapted to run a computer program 5032 stored in a memory.
It should be appreciated that in an embodiment of the application, the processor 502 may be a central processing unit (Central Processing Unit, CPU), the processor 502 may also be other general purpose processors, digital signal processors (Digital Signal Processor, DSPs), application specific integrated circuits (Application Specific Integrated Circuit, ASICs), off-the-shelf programmable gate arrays (Field-Programmable Gate Array, FPGAs) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, or the like. Wherein the general purpose processor may be a microprocessor or the processor may be any conventional processor or the like.
Those skilled in the art will appreciate that all or part of the flow in a method embodying the above described embodiments may be accomplished by computer programs instructing the relevant hardware. The computer program comprises program instructions, and the computer program can be stored in a storage medium, which is a computer readable storage medium. The program instructions are executed by at least one processor in the computer system to implement the flow steps of the embodiments of the method described above.
Accordingly, the present application also provides a storage medium. The storage medium may be a computer readable storage medium.
The storage medium may be a U-disk, a removable hard disk, a Read-Only Memory (ROM), a magnetic disk, or an optical disk, or other various computer-readable storage media that can store program codes.
Those of ordinary skill in the art will appreciate that the elements and algorithm steps described in connection with the embodiments disclosed herein may be embodied in electronic hardware, in computer software, or in a combination of the two, and that the elements and steps of the examples have been generally described in terms of function in the foregoing description to clearly illustrate the interchangeability of hardware and software. Whether such functionality is implemented as hardware or software depends upon the particular application and design constraints imposed on the solution. Skilled artisans may implement the described functionality in varying ways for each particular application, but such implementation decisions should not be interpreted as causing a departure from the scope of the present application.
In the several embodiments provided by the present application, it should be understood that the disclosed apparatus and method may be implemented in other manners. For example, the device embodiments described above are merely illustrative. For example, the division of each unit is only one logic function division, and there may be another division manner in actual implementation. For example, multiple units or components may be combined or may be integrated into another system, or some features may be omitted, or not performed.
The steps in the method of the embodiment of the application can be sequentially adjusted, combined and deleted according to actual needs. The units in the device of the embodiment of the application can be combined, divided and deleted according to actual needs. In addition, each functional unit in the embodiments of the present application may be integrated in one processing unit, or each unit may exist alone physically, or two or more units may be integrated in one unit.
The integrated unit may be stored in a storage medium if implemented in the form of a software functional unit and sold or used as a stand-alone product. Based on such understanding, the technical solution of the present application is essentially or a part contributing to the prior art, or all or part of the technical solution may be embodied in the form of a software product stored in a storage medium, comprising several instructions for causing a computer device (which may be a personal computer, a terminal, a network device, etc.) to perform all or part of the steps of the method according to the embodiments of the present application.
While the application has been described with reference to certain preferred embodiments, it will be understood by those skilled in the art that various changes and substitutions of equivalents may be made and equivalents will be apparent to those skilled in the art without departing from the scope of the application. Therefore, the protection scope of the application is subject to the protection scope of the claims.
Claims (6)
1. A method for migrating a database from MySQL to Oracle, comprising the steps of:
selecting an initial MySQL database;
acquiring table name information of all database tables of an initial MySQL database;
circularly traversing the database table according to the table name information to obtain initial table structure definition information;
converting according to the database table and the initial table structure definition information to obtain target table structure definition information meeting Oracle requirements;
generating a target Oracle database according to the database table and the target table structure definition information;
the method comprises the steps that a terminal sends a corresponding database selection instruction to a MySQL server, one or more target databases are selected as initial MySQL databases, and migration is carried out on the initial MySQL databases;
the step of obtaining the table name information of all database tables of the initial MySQL database comprises the following steps:
calling all database tables in the initial MySQL database through a show table statement to acquire corresponding table name information;
the step of circularly traversing the database table according to the table name information to obtain the definition information of the initial table structure comprises the following steps:
the corresponding database table is circularly traversed through desc < + > statement, and initial table structure definition information is obtained, wherein the initial table structure definition information comprises field column names, data types of the field columns, whether the field columns can be empty, whether the field columns are primary keys or unique keys, default values and additional information;
the step of calling all database tables in the initial MySQL database through the show table statement to obtain the corresponding table name information comprises the following steps:
all the data tables under the current initial MySQL database are displayed through the show table statement, and corresponding table name information is directly obtained according to the displayed data tables.
2. The method for migrating a database from MySQL to Oracle according to claim 1, wherein the step of generating the target Oracle database from the database table and the target table structure definition information comprises:
building a table grammar based on the database of Oracle to generate a table building statement;
splicing all the table-building sentences to generate an SQL definition sentence file;
and importing the SQL definition statement file into Oracle to generate a target Oracle database.
3. A database migration apparatus from MySQL to Oracle, comprising:
the database selection unit is used for selecting an initial MySQL database;
the first acquisition unit is used for acquiring the table name information of all database tables of the initial MySQL database;
the second acquisition unit is used for circularly traversing the database table according to the table name information and acquiring initial table structure definition information;
the format conversion unit is used for converting the database table and the initial table structure definition information to obtain target table structure definition information meeting the Oracle requirement;
the database generating unit is used for generating a target Oracle database according to the database table and the target table structure definition information;
the database selection unit is also used for sending a corresponding database selection instruction to the MySQL server by the terminal, selecting one or more target databases as an initial MySQL database, and migrating the initial MySQL database;
the first acquisition unit comprises a table name acquisition module which is used for calling all database tables in the initial MySQL database through a show table statement to acquire corresponding table name information;
the second acquisition unit comprises a table structure acquisition module, a database table processing module and a database processing module, wherein the table structure acquisition module is used for circularly traversing a corresponding database table through a desc < + > table name < + > statement to acquire initial table structure definition information, and the initial table structure definition information comprises a field column name, a data type of the field column, whether the field column name can be empty, whether the field column name is a primary key or a unique key, a default value and additional information;
the table name acquisition module is also used for displaying all the data tables under the current initial MySQL database through a show table statement and directly acquiring corresponding table name information according to the displayed data tables.
4. A database migration apparatus from MySQL to Oracle according to claim 3, wherein the database generation unit comprises a sentence generation module, a sentence splicing module, and a sentence importing module;
the statement generation module is used for generating a statement of building a table based on the database table building grammar of Oracle;
the statement splicing module is used for splicing all the table-building statements to generate an SQL definition statement file;
the statement importing module is used for importing the SQL definition statement file into the Oracle to generate a target Oracle database.
5. A computer device comprising a memory and a processor, the memory having stored thereon a computer program, the processor implementing the method of database migration from MySQL to Oracle of any one of claims 1 to 2 when the computer program is executed.
6. A storage medium storing a computer program which, when executed by a processor, implements a method of database migration from MySQL to Oracle as claimed in any one of claims 1 to 2.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910945144.7A CN110704398B (en) | 2019-09-30 | 2019-09-30 | Database migration method and device from MySQL to Oracle and computer equipment |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910945144.7A CN110704398B (en) | 2019-09-30 | 2019-09-30 | Database migration method and device from MySQL to Oracle and computer equipment |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN110704398A CN110704398A (en) | 2020-01-17 |
| CN110704398B true CN110704398B (en) | 2023-08-25 |
Family
ID=69197740
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201910945144.7A Active CN110704398B (en) | 2019-09-30 | 2019-09-30 | Database migration method and device from MySQL to Oracle and computer equipment |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN110704398B (en) |
Families Citing this family (13)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111694812A (en) * | 2020-05-06 | 2020-09-22 | 五八有限公司 | Data migration method and data migration device |
| CN111596899A (en) * | 2020-05-12 | 2020-08-28 | 重庆锐云科技有限公司 | Database migration method, system, equipment and storage medium based on Java development |
| CN111708753A (en) * | 2020-05-18 | 2020-09-25 | 北京金山云网络技术有限公司 | Method, device and equipment for evaluating database migration and computer storage medium |
| CN111984621B (en) * | 2020-07-17 | 2022-08-30 | 新华三大数据技术有限公司 | Heterogeneous database migration method and device and storage medium |
| CN112181951B (en) * | 2020-10-20 | 2022-03-25 | 新华三大数据技术有限公司 | Heterogeneous database data migration method, device and equipment |
| CN112328696A (en) * | 2020-11-17 | 2021-02-05 | 北京金和网络股份有限公司 | Method and device for synchronizing data in MySQL database to SQL Server database |
| CN112749151A (en) * | 2021-01-13 | 2021-05-04 | 叮当快药科技集团有限公司 | Data migration method and device among different types of databases and storage medium |
| CN112817934A (en) * | 2021-01-21 | 2021-05-18 | 厦门熵基科技有限公司 | Data migration method, device, equipment and computer readable storage medium |
| CN113111101A (en) * | 2021-03-18 | 2021-07-13 | 武汉中地众思科技有限公司 | Database conversion method |
| CN113407514A (en) * | 2021-05-21 | 2021-09-17 | 济南浪潮数据技术有限公司 | Method, device and equipment for migrating database and readable medium |
| CN113486104B (en) * | 2021-07-15 | 2024-05-31 | 山东齐鲁数通科技有限公司 | Database storage process conversion method and device, terminal equipment and storage medium |
| CN117349267B (en) * | 2023-12-04 | 2024-03-22 | 和元达信息科技有限公司 | Database migration processing method and system |
| CN118260311B (en) * | 2024-05-29 | 2024-08-09 | 天津南大通用数据技术股份有限公司 | Distribution key selection method, system, equipment and product in database migration |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101789021A (en) * | 2010-02-24 | 2010-07-28 | 浪潮通信信息系统有限公司 | Universal configurable database data migration method |
| CN102402559A (en) * | 2010-09-16 | 2012-04-04 | 中兴通讯股份有限公司 | Database upgrade script generating method and device |
| CN103514223A (en) * | 2012-06-28 | 2014-01-15 | 阿里巴巴集团控股有限公司 | Data synchronism method and system of database |
| CN104462119A (en) * | 2013-09-18 | 2015-03-25 | 腾讯科技（深圳）有限公司 | Data migration method and device |
| CN109284281A (en) * | 2018-09-10 | 2019-01-29 | 北京百度网讯科技有限公司 | Method and apparatus for migrating data |
Family Cites Families (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US8818949B2 (en) * | 2011-12-30 | 2014-08-26 | Bmc Software, Inc. | Systems and methods for migrating database data |
-
2019
- 2019-09-30 CN CN201910945144.7A patent/CN110704398B/en active Active
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101789021A (en) * | 2010-02-24 | 2010-07-28 | 浪潮通信信息系统有限公司 | Universal configurable database data migration method |
| CN102402559A (en) * | 2010-09-16 | 2012-04-04 | 中兴通讯股份有限公司 | Database upgrade script generating method and device |
| CN103514223A (en) * | 2012-06-28 | 2014-01-15 | 阿里巴巴集团控股有限公司 | Data synchronism method and system of database |
| CN104462119A (en) * | 2013-09-18 | 2015-03-25 | 腾讯科技（深圳）有限公司 | Data migration method and device |
| CN109284281A (en) * | 2018-09-10 | 2019-01-29 | 北京百度网讯科技有限公司 | Method and apparatus for migrating data |
Also Published As
| Publication number | Publication date |
|---|---|
| CN110704398A (en) | 2020-01-17 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110704398B (en) | Database migration method and device from MySQL to Oracle and computer equipment | |
| CN110633292B (en) | Query method, device, medium, equipment and system for heterogeneous database | |
| CN111177231B (en) | Report generation method and report generation device | |
| CN108334609B (en) | Method, device, equipment and storage medium for realizing JSON format data access in Oracle | |
| US9563679B2 (en) | Adaptive warehouse data validation tool | |
| CN110781183B (en) | Processing method and device for incremental data in Hive database and computer equipment | |
| CN103927607A (en) | Data processing method and device | |
| CN110688544A (en) | Method, device and storage medium for querying database | |
| WO2019223136A1 (en) | Data acquisition method and apparatus, and computer device and storage medium | |
| CN115599769A (en) | Data migration method and device, electronic equipment and storage medium | |
| CN110362630B (en) | Data management method, device, equipment and computer readable storage medium | |
| WO2017107130A1 (en) | Data query method and database system | |
| CN103678591A (en) | Device and method for automatically executing multi-service receipt statistical treatment | |
| WO2019109514A1 (en) | Datasheet backup method, device, electronic apparatus and medium | |
| CN113609128B (en) | Method, device, terminal equipment and storage medium for generating database entity class | |
| CN108460077B (en) | Index analysis method, system and computer readable storage medium | |
| CN110069532A (en) | Data query method, apparatus, computer readable storage medium and electronic equipment | |
| CN104345897A (en) | Deformed character input method and system | |
| US9588998B2 (en) | Protecting storage data during system migration | |
| CN108595552A (en) | Data cube dissemination method, device, electronic equipment and storage medium | |
| CN115328917A (en) | Query method, device, equipment and storage medium | |
| CN114968917A (en) | Method and device for rapidly importing file data | |
| CN108334621B (en) | Database operation method, device, equipment and computer readable storage medium | |
| CN112417012A (en) | Data processing method and system | |
| WO2019095582A1 (en) | Method and device for navigating to target location, storage medium and terminal |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |