CN110704398A - 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
- CN110704398A CN110704398A CN201910945144.7A CN201910945144A CN110704398A CN 110704398 A CN110704398 A CN 110704398A CN 201910945144 A CN201910945144 A CN 201910945144A CN 110704398 A CN110704398 A CN 110704398A
- Authority
- CN
- China
- Prior art keywords
- database
- oracle
- mysql
- initial
- structure definition
- 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.)
- Granted
Links
Images
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 invention relates to a method, a device and computer equipment for migrating a database 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 acquire initial table structure definition information; converting 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 obtained, the initial table structure definition information is converted to obtain the target table structure definition information, the corresponding target Oracle database is generated finally, the database can be selected for migration according to actual needs, the accurate migration of the database is guaranteed, and meanwhile the migration cost is reduced.
Description
Technical Field
The invention relates to data migration, in particular to a method, a device and computer equipment for migrating a database from MySQL to Oracle.
Background
MySQL is a relational database management system that keeps data in different tables instead of putting all the data in one large repository, 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 complete relational product; it implements distributed processing functions as a distributed database.
MySQL and Oracle are commonly used database management systems in enterprise business systems, but the problem of incomplete compatibility exists, when a database of a 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 export the required database according to specific business requirements, and higher migration cost is brought under the condition that more databases are used in the migration process.
In the existing database migration tool, no customizable setting is provided in the migration operation, and even if the customizable setting is available, the configurable places are few, and the produced database migration script, no matter the format or the statement, may not be necessarily wanted by the user, so in the migration process, under the condition that the number of databases is large, the high migration cost is brought.
Disclosure of Invention
The invention aims to overcome the defects of the prior art and provides a method, a device and computer equipment for migrating a database from MySQL to Oracle.
In order to achieve the purpose, the invention adopts the following technical scheme: a method for migrating a database from MySQL to Oracle 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 acquire initial table structure definition information;
converting 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 table name information of all database tables of the initial MySQL database includes:
and calling all database tables in the initial MySQL database through the 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 initial table structure definition information includes:
and circularly traversing the corresponding database table through desc + [ table name ] statements to acquire initial table structure definition information, 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 main keys or unique keys, default values and additional information.
Further, the step of generating a target Oracle database according to the database table and the target table structure definition information includes:
generating a table building statement by the target table structure definition information based on the database table building grammar of Oracle;
splicing all the table building statements to generate an SQL definition statement file;
and importing the SQL definition statement file into Oracle to generate a target Oracle database.
The invention also adopts the following technical scheme: a device for migrating a database 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 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 to acquire the 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 Oracle requirements;
and the database generation 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 circularly traverse a corresponding database table through a desc + [ table name ] statement, and obtain initial table structure definition information, where the initial table structure definition information includes a field column name, a data type of the field column, whether the field column is available, 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 import module;
the statement generation module is used for generating a table building statement by using the target table structure definition information 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;
and the statement import module is used for importing the SQL definition statement file into Oracle to generate a target Oracle database.
The invention also adopts the following technical scheme: a computer device comprising a memory having stored thereon a computer program and a processor that, when executed, implements a database migration method from MySQL to Oracle as claimed in any preceding claim.
The invention also adopts the following technical scheme: 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 preceding claim.
Compared with the prior art, the invention has the beneficial effects that: according to the method, after an initial MySQL database is selected, the table name information and the initial table structure definition information are obtained, 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 according to the target table structure definition information, so that the database can be selected for migration according to actual needs, the accurate migration of the database is guaranteed, and the migration cost is reduced; meanwhile, the moved database can be completely compatible with Oracle, the completeness of the moved database is guaranteed, and the data security is improved.
The invention is further described below with reference to the accompanying drawings and specific embodiments.
Drawings
In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings needed to be used in the description of the embodiments are briefly introduced below, and it is obvious that the drawings in the following description are some embodiments of the present invention, and it is obvious for those skilled in the art to obtain other drawings based on these drawings without creative efforts.
Fig. 1 is a schematic view of an application scenario of a database migration method from MySQL to Oracle according to an embodiment of the present invention;
FIG. 2 is a schematic flow chart of a database migration method from MySQL to Oracle according to an embodiment of the present invention;
FIG. 3 is a sub-flow diagram of a database migration method from MySQL to Oracle according to an embodiment of the present invention;
FIG. 4 is a schematic block diagram of a database migration apparatus from MySQL to Oracle according to an embodiment of the present invention;
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 invention;
fig. 6 is a schematic block diagram of a second obtaining unit of the database migration apparatus from MySQL to Oracle according to the embodiment of the present invention;
fig. 7 is a schematic block diagram of a database generation unit of a database migration apparatus from MySQL to Oracle according to an embodiment of the present invention;
FIG. 8 is a schematic block diagram of a computer device provided by an embodiment of the present invention.
Detailed Description
The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are some, not all, embodiments of the present invention. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.
It will be understood that the terms "comprises" and/or "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 invention herein is for the purpose of describing particular embodiments only and is not intended to be limiting of the invention. As used in the specification of the present invention 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 this specification and the appended claims refers to and includes any and all possible combinations of one or more of the associated listed items.
Referring to fig. 1 and fig. 2, fig. 1 is a schematic view of an application scenario of a database migration method from MySQL to Oracle according to an embodiment of the present invention. Fig. 2 is a schematic flowchart of a database migration method from MySQL to Oracle according to an embodiment of the present invention. The MySQL-to-Oracle database migration method is applied to terminals (a first terminal and a second terminal), data interaction is carried out between the MySQL server and the Oracle server, the MySQL server receives a database selection instruction of the terminal, an initial MySQL database is selected, table name information and initial table structure definition information of all database tables of the initial MySQL database are further transmitted to the terminals, the terminals carry out conversion to obtain target table structure definition information, then SQL definition statement files are generated by the terminals according to the target table structure definition information and the database tables, and the target Oracle database is generated by leading the terminals into the Oracle server.
Fig. 2 is a schematic flowchart of a database migration method from MySQL to Oracle according to an embodiment of the present invention. As shown in fig. 2, the method includes the following steps S10 to S50.
And S10, selecting an initial MySQL database.
In this embodiment, MySQL is a relational database management system that maintains data in different tables, rather than placing all the data in one large repository, 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 complete relational product; it implements distributed processing functions as a distributed database. MySQL and Oracle are two mainstream relational database systems, which are slightly different in use, although compatible with main SQL query statements, some places still cannot be completely compatible, and the existing migration tool cannot export the required database according to specific business requirements.
And the terminal sends 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 to be migrated according to actual needs, so that the databases are guaranteed to be accurately migrated, meanwhile, other unnecessary databases are prevented from being migrated simultaneously, and then the migration cost is reduced.
And S20, acquiring the table name information of all database tables of the initial MySQL database.
In this embodiment, the data table is composed of three parts, namely a table name, fields in the table, and records of the table. Designing a 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, etc. of each field. According to the scheme, the table name information of all database tables of the initial MySQL database is obtained, and the initial table structure definition information of the corresponding data table can be further obtained according to the table names.
In an embodiment, the step S20 specifically includes the step S21.
And S21, calling all database tables in the initial MySQL database through the show table statement, and acquiring corresponding table name information.
In an embodiment, all data tables in the current initial MySQL database can be displayed through the show table statement, and corresponding table name information is directly acquired according to the displayed data tables.
And S30, circularly traversing the database table according to the table name information to acquire the initial table structure definition information.
In this embodiment, the data table is composed of three parts, namely a table name, fields in the table, and records of the table. Designing a 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, etc. 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 an embodiment, the step S30 specifically includes the step S31.
And S31, circularly traversing the corresponding database table through desc + [ table name ] statement to acquire the initial table structure definition information.
In one embodiment, through the desc + [ table name ] statement, it can be shown which fields are specifically defined by the data table, and the type sizes of the respective fields, and which are primary keys, which are constraints, and whether the respective fields define default values, etc.
Specifically, the initial table structure definition information includes a field column name, a data type of the field column, whether it is available, whether it is a primary key or a unique key, a default value, and additional information.
Specifically, the show table statement and the desc + [ table name ] statement may be written into a Python script, which is imported into the terminal, and the script is automatically connected to the MySQL database, and then the database definition statements, such as show tables and desc + [ table name ] statements, are executed to obtain all table name information and table structure definition information.
And S40, converting the database table and the initial table structure definition information to obtain target table structure definition information meeting Oracle requirements.
In this embodiment, MySQL and Oracle are two main-stream relational database systems, which are slightly different in use, and although compatible with the main SQL query statement, there are some places where they are not completely compatible. Because MySQL is not completely compatible with Oracle, the acquired database table and initial table structure definition information need to be converted through corresponding conversion rules, so that target table structure definition information meeting the requirements of the Oracle database is obtained through conversion, the integrity of the migrated database is ensured, and the data security is improved.
Specifically, during the conversion process, desired indexes, triggers, sequences, etc. may also be generated.
And 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 requirements of the Oracle database, the terminal may generate table building statements according to the database table building syntax of Oracle, and concatenate 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 migration cost is reduced while the database is guaranteed to be accurately migrated; meanwhile, the moved database can be completely compatible with Oracle, the completeness of the moved database is guaranteed, and the data security is improved.
Referring to FIG. 3, in one embodiment, step S50 specifically includes steps S51-S53.
And S51, generating a table building statement by using the target table structure definition information based on the database table building grammar of Oracle.
And S52, splicing all the table building statements to generate an SQL definition statement file.
And S53, importing the SQL definition statement file into Oracle to generate a target Oracle database.
According to the method, after an initial MySQL database is selected, the table name information and the initial table structure definition information are obtained, 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 according to the target table structure definition information, so that the database can be selected for migration according to actual needs, the accurate migration of the database is guaranteed, and the migration cost is reduced; meanwhile, the moved database can be completely compatible with Oracle, the completeness of the moved database is guaranteed, and the data security is improved.
Fig. 4 is a schematic block diagram of a database migration apparatus from MySQL to Oracle according to an embodiment of the present invention. As shown in fig. 4, the present invention also provides a database migration apparatus from MySQL to Oracle, corresponding to the above database migration method from MySQL to Oracle. The device for migrating the database from MySQL to Oracle comprises a unit for executing the method for migrating the database from MySQL to Oracle, and the device can be configured in a desktop computer, a tablet computer, a portable computer, and the like. Specifically, referring to fig. 4, the apparatus for migrating a database from MySQL to Oracle includes a database selecting unit 10, a first obtaining unit 20, a second obtaining unit 30, a format converting unit 40, and a database generating unit 50.
And the database selection unit 10 is used for selecting an initial MySQL database.
In this embodiment, MySQL is a relational database management system that maintains data in different tables, rather than placing all the data in one large repository, 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 complete relational product; it implements distributed processing functions as a distributed database. MySQL and Oracle are two mainstream relational database systems, which are slightly different in use, although compatible with main SQL query statements, some places still cannot be completely compatible, and the existing migration tool cannot export the required database according to specific business requirements.
And the terminal sends 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 to be migrated according to actual needs, so that the databases are guaranteed to be accurately migrated, meanwhile, other unnecessary databases are prevented from being migrated simultaneously, and then the migration cost is reduced.
The first obtaining unit 20 is configured to obtain table name information of all database tables of the initial MySQL database.
In this embodiment, the data table is composed of three parts, namely a table name, fields in the table, and records of the table. Designing a 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, etc. of each field. According to the scheme, the table name information of all database tables of the initial MySQL database is obtained, and the initial table structure definition information of the corresponding data table can be further obtained according to the table names.
Referring to fig. 5, in an embodiment, the first obtaining unit 20 includes a table name obtaining module 21, and 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 data tables in the current initial MySQL database can be displayed through the show table statement, and corresponding table name information is directly acquired according to the displayed data tables.
The second obtaining unit 30 is configured to cycle through the database table according to the table name information, and obtain the initial table structure definition information.
In this embodiment, the data table is composed of three parts, namely a table name, fields in the table, and records of the table. Designing a 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, etc. 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, and the table structure obtaining module 31 is configured to loop through the corresponding database table through a desc + [ table name ] statement to obtain the initial table structure definition information.
The initial table structure definition information includes field column names, data types of the field columns, whether it is nullable, whether it is a primary key or a unique key, default values, and additional information.
In one embodiment, through the desc + [ table name ] statement, it can be shown which fields are specifically defined by the data table, and the type sizes of the respective fields, and which are primary keys, which are constraints, and whether the respective fields define default values, etc.
Specifically, the show table statement and the desc + [ table name ] statement may be written into a Python script, which is imported into the terminal, and the script is automatically connected to the MySQL database, and then the database definition statements, such as show tables and desc + [ table name ] statements, are executed to obtain all table name information and table structure definition information.
And the format conversion unit 40 is used for converting the database table and the initial table structure definition information to obtain target table structure definition information meeting Oracle requirements.
In this embodiment, MySQL and Oracle are two main-stream relational database systems, which are slightly different in use, and although compatible with the main SQL query statement, there are some places where they are not completely compatible. Because MySQL is not completely compatible with Oracle, the acquired database table and initial table structure definition information need to be converted through corresponding conversion rules, so that target table structure definition information meeting the requirements of the Oracle database is obtained through conversion, the integrity of the migrated database is ensured, and the data security is improved.
Specifically, during the conversion process, desired indexes, triggers, sequences, etc. may also be generated.
And the database generating unit 50 is used for 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 requirements of the Oracle database, the terminal may generate table building statements according to the database table building syntax of Oracle, and concatenate 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 migration cost is reduced while the database is guaranteed to be accurately migrated; meanwhile, the moved database can be completely compatible with Oracle, the completeness of the moved database is guaranteed, 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 concatenation module 52 and a sentence import module 53.
And a statement generating module 51, configured to generate a table building statement based on the table building syntax of the database of Oracle for the target table structure definition information.
And the statement splicing module 52 is configured to splice all the table building statements to generate an SQL definition statement file.
And the statement import module 53 is used for importing the SQL definition statement file into Oracle to generate a target Oracle database.
According to the database migration device from MySQL to Oracle, after the initial MySQL database is selected, the table name information and the initial table structure definition information are obtained, 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 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 guaranteed, and migration cost is reduced; meanwhile, the moved database can be completely compatible with Oracle, the completeness of the moved database is guaranteed, and the data security is improved.
It should be noted that, as can be clearly understood by those skilled in the art, the specific implementation process of the above database migration apparatus 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, no further description is provided herein.
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 composed of a plurality of servers.
Referring 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 comprises 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 the computer program 5032 on the non-volatile storage medium 503, and when the computer program 5032 is executed by the processor 502, the processor 502 can be enabled to execute a database migration method from MySQL to Oracle.
The network interface 505 is used for network communication with other devices. Those skilled in the art will appreciate that the configuration shown in fig. 8 is a block diagram of only a portion of the configuration relevant to the present teachings and does not constitute a limitation on the computer device 500 to which the present teachings may be applied, and that a particular computer device 500 may include more or less components than those shown, or combine certain components, or have a different arrangement of components.
Wherein the processor 502 is adapted to run a computer program 5032 stored in the memory.
It should be understood that, in the embodiment of the present Application, the Processor 502 may be a Central Processing Unit (CPU), and the Processor 502 may also be other general-purpose processors, Digital Signal Processors (DSPs), Application Specific Integrated Circuits (ASICs), Field-Programmable Gate arrays (FPGAs) or other Programmable logic devices, discrete Gate or transistor logic devices, discrete hardware components, and the like. Wherein a general purpose processor may be a microprocessor or the processor may be any conventional processor or the like.
It will be understood by those skilled in the art that all or part of the flow of the method implementing the above embodiments may be implemented by a computer program instructing associated hardware. The computer program includes program instructions, and the computer program may 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 invention also provides a storage medium. The storage medium may be a computer-readable storage medium.
The storage medium may be a usb disk, a removable hard disk, a Read-Only Memory (ROM), a magnetic disk, or an optical disk, which can store various computer readable storage media.
Those of ordinary skill in the art will appreciate that the elements and algorithm steps of the examples described in connection with the embodiments disclosed herein may be embodied in electronic hardware, computer software, or combinations of both, and that the components and steps of the examples have been described in a functional general in the foregoing description for the purpose of illustrating clearly 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 implementation. 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 invention.
In the embodiments provided in the present invention, it should be understood that the disclosed apparatus and method may be implemented in other ways. For example, the above-described apparatus embodiments 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, various elements or components may be combined or may be integrated into another system, or some features may be omitted, or not implemented.
The steps in the method of the embodiment of the invention can be sequentially adjusted, combined and deleted according to actual needs. The units in the device of the embodiment of the invention can be merged, divided and deleted according to actual needs. In addition, functional units in the embodiments of the present invention may be integrated into one processing unit, or each unit may exist alone physically, or two or more units are integrated into one unit.
The integrated unit, if implemented in the form of a software functional unit and sold or used as a stand-alone product, may be stored in a storage medium. Based on such understanding, the technical solution of the present invention essentially or partially contributes to the prior art, or all or part of the technical solution can be embodied in the form of a software product, which is stored in a storage medium and includes instructions for causing a computer device (which may be a personal computer, a terminal, or a network device) to execute all or part of the steps of the method according to the embodiments of the present invention.
While the invention has been described with reference to specific embodiments, the invention is not limited thereto, and various equivalent modifications and substitutions can be easily made by those skilled in the art within the technical scope of the invention. Therefore, the protection scope of the present invention shall be subject to the protection scope of the claims.
Claims (10)
1. A method for migrating a database from MySQL to Oracle is characterized by comprising 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 acquire initial table structure definition information;
converting 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.
2. The method for migrating the database from MySQL to Oracle according to claim 1, wherein the step of obtaining the table name information of all the database tables of the initial MySQL database comprises the following steps:
and calling all database tables in the initial MySQL database through the show table statement to acquire corresponding table name information.
3. The method for migrating the database from MySQL to Oracle according to claim 2, wherein the step of circularly traversing the database table according to the table name information to obtain the initial table structure definition information comprises:
and circularly traversing the corresponding database table through desc + [ table name ] statements to acquire initial table structure definition information, 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 main keys or unique keys, default values and additional information.
4. The method for migrating a database from MySQL to Oracle according to claim 1, wherein the step of generating a target Oracle database according to the database table and the target table structure definition information includes:
generating a table building statement by the target table structure definition information based on the database table building grammar of Oracle;
splicing all the table building statements to generate an SQL definition statement file;
and importing the SQL definition statement file into Oracle to generate a target Oracle database.
5. An apparatus for migrating a database 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 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 to acquire the 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 Oracle requirements;
and the database generation unit is used for generating a target Oracle database according to the database table and the target table structure definition information.
6. The apparatus for migrating a database from MySQL to Oracle according to claim 5, wherein 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 to obtain corresponding table name information.
7. The apparatus for migrating a database from MySQL to Oracle according to claim 6, wherein the second obtaining unit includes a table structure obtaining module, configured to loop through a desc + [ table name ] statement through a corresponding database table to obtain initial table structure definition information, where the initial table structure definition information includes a field column name, a data type of the field column, whether the field column is available, whether the field column is a primary key or a unique key, a default value, and additional information.
8. The apparatus for migrating a database from MySQL to Oracle according to claim 5, 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 table building statement by using the target table structure definition information 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;
and the statement import module is used for importing the SQL definition statement file into Oracle to generate a target Oracle database.
9. A computer device comprising a memory having stored thereon a computer program and a processor that, when executed, implements the database migration method from MySQL to Oracle as claimed in any one of claims 1 to 4.
10. A storage medium storing a computer program which, when executed by a processor, implements a method of database migration from MySQL to Oracle according to any one of claims 1 to 4.
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 true CN110704398A (en) | 2020-01-17 |
| CN110704398B 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) |
Cited By (13)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111596899A (en) * | 2020-05-12 | 2020-08-28 | 重庆锐云科技有限公司 | Database migration method, system, equipment and storage medium based on Java development |
| CN111694812A (en) * | 2020-05-06 | 2020-09-22 | 五八有限公司 | Data migration method and data migration device |
| CN111708753A (en) * | 2020-05-18 | 2020-09-25 | 北京金山云网络技术有限公司 | Method, device and equipment for evaluating database migration and computer storage medium |
| CN111984621A (en) * | 2020-07-17 | 2020-11-24 | 新华三大数据技术有限公司 | Heterogeneous database migration method and device and storage medium |
| CN112181951A (en) * | 2020-10-20 | 2021-01-05 | 新华三大数据技术有限公司 | 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 |
| CN113486104A (en) * | 2021-07-15 | 2021-10-08 | 山东齐鲁数通科技有限公司 | Database storage process conversion method and device, terminal equipment and storage medium |
| CN117349267A (en) * | 2023-12-04 | 2024-01-05 | 和元达信息科技有限公司 | Database migration processing method and system |
| CN118260311A (en) * | 2024-05-29 | 2024-06-28 | 天津南大通用数据技术股份有限公司 | Distribution key selection method, system, equipment and product in database migration |
Citations (6)
| 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 |
| US20130173546A1 (en) * | 2011-12-30 | 2013-07-04 | Bmc Software, Inc. | Systems and methods for migrating database data |
| 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 |
-
2019
- 2019-09-30 CN CN201910945144.7A patent/CN110704398B/en active Active
Patent Citations (6)
| 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 |
| US20130173546A1 (en) * | 2011-12-30 | 2013-07-04 | Bmc Software, Inc. | Systems and methods for migrating database data |
| 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 |
Cited By (17)
| 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 |
| CN111984621A (en) * | 2020-07-17 | 2020-11-24 | 新华三大数据技术有限公司 | Heterogeneous database migration method and device and storage medium |
| CN112181951A (en) * | 2020-10-20 | 2021-01-05 | 新华三大数据技术有限公司 | Heterogeneous database data migration method, device and equipment |
| 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 |
| CN113486104A (en) * | 2021-07-15 | 2021-10-08 | 山东齐鲁数通科技有限公司 | Database storage process conversion method and device, terminal equipment and storage medium |
| CN113486104B (en) * | 2021-07-15 | 2024-05-31 | 山东齐鲁数通科技有限公司 | Database storage process conversion method and device, terminal equipment and storage medium |
| CN117349267A (en) * | 2023-12-04 | 2024-01-05 | 和元达信息科技有限公司 | Database migration processing method and system |
| CN117349267B (en) * | 2023-12-04 | 2024-03-22 | 和元达信息科技有限公司 | Database migration processing method and system |
| CN118260311A (en) * | 2024-05-29 | 2024-06-28 | 天津南大通用数据技术股份有限公司 | Distribution key selection method, system, equipment and product in database migration |
Also Published As
| Publication number | Publication date |
|---|---|
| CN110704398B (en) | 2023-08-25 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110704398B (en) | Database migration method and device from MySQL to Oracle and computer equipment | |
| CN111177231B (en) | Report generation method and report generation device | |
| CN110633292B (en) | Query method, device, medium, equipment and system for heterogeneous database | |
| CN108334609B (en) | Method, device, equipment and storage medium for realizing JSON format data access in Oracle | |
| CN110334109B (en) | Relational database data query method, system, medium and electronic device | |
| CN110781183B (en) | Processing method and device for incremental data in Hive database and computer equipment | |
| CN107102995B (en) | Method and device for determining SQL execution plan | |
| CN111435406A (en) | Method and device for correcting database statement spelling errors | |
| CN108959294B (en) | Method and device for accessing search engine | |
| CN103678591A (en) | Device and method for automatically executing multi-service receipt statistical treatment | |
| CN113609128B (en) | Method, device, terminal equipment and storage medium for generating database entity class | |
| CN110069532A (en) | Data query method, apparatus, computer readable storage medium and electronic equipment | |
| CN113190576A (en) | Data processing method and device, computer equipment and readable storage medium | |
| CN107273293B (en) | Big data system performance test method and device and electronic equipment | |
| US9588998B2 (en) | Protecting storage data during system migration | |
| CN111324614B (en) | Table configuration method and device, terminal equipment and storage medium | |
| CN114860362A (en) | Interface updating method and device | |
| CN115328917A (en) | Query method, device, equipment and storage medium | |
| CN111190916A (en) | Visual business data operating system and method | |
| CN111813769B (en) | Data processing method and device | |
| CN114510534A (en) | Data synchronization method, device, equipment and storage medium | |
| WO2019153547A1 (en) | Database operation method, apparatus and device, and computer-readable storage medium | |
| CN110874246A (en) | Module loading method, system and equipment | |
| CN114328719B (en) | Database statement synchronization method, system, electronic device and computer readable storage medium | |
| CN112506944B (en) | Data standard conversion access method, device, equipment and medium between service systems |
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 |