Using Perl for database migration, from MSSQL to MySQL (-)

There is a project in hand recently, and the database needs to be switched from SQL Server to MySQL. The business reason is not mentioned yet. The younger brother had a headache after obtaining the project, because the project used some new data types of SQL Server, Including geometry, including hierarchyid (cascade ID) this type of SQL Server is a new data type. Of course, since MySQL V5, geometry is supported, but geometry-based computation is not supported. Therefore, this must be considered during data migration. Of course, if this is the case later, we should first import all the data to MySQL.

In the early stage of technology selection, I thought about using ETL tools and several familiar ETL tools, and there are so many tables (this preliminary estimate involves more than 500 tables ...), for fields such as hierarchyid, the conversion must be performed on the source data, including dizziness, brain growth, milk acid, egg pain, and other symptoms. Not long ago, I saw an introduction to Perl in a jar, and mastering Perl will simplify the O & M work of Linx. As a result, we are ready to use Perl to write a data import script. Since I have never used Perl to write any scripts before, even if I have a line of data, I quickly found a Perl electronic book [the fifth edition of the Perl language entry] called "Little camels ".

I am not talking about it.ProgramThe general idea is as follows:

Create a data table first. When creating a table, do not change too many data fields. (I was still thinking, directly split the cascade ID into two fields, ID and parentid. However, during the first round of testing, it is found that because cascading ID is used as a foreign key, during conversion, there will be repeated fields, conflicts, and Trojans, and such foreign keys are not very well automatically associated, I had to give up .)

Import the data again, and use the simplest method to export the data. Check the data from SQL Server and insert it to MySQL. But here I did a test. If I put a whole table in the memory, the server basically does not need to do anything else. So here I split a data table and export 2000 records each time...

Finally, the fields using the cascading ID (hierarchyid) are found, and then the script is written to process the fields in these tables.