SQL Server migration to the pits that MySQL encounters

Background

For a variety of reasons, cost, scalability, etc., the company decided to migrate the online business from SQL Server to MySQL RDS.

The migration process mainly includes program modification and database migration. Program Modifications We skip the talk, we focus on database migration.

Approximate process

Because it is heterogeneous database, do not find the real-time data synchronization method (if the hero can be heterogeneous real-time synchronization, but also more advice), all use ETL tool timing synchronization data.

The synchronized data includes insert and update data (fortunately there is no physical delete in the business).

Before you can switch the database, synchronize the data again.

After the switch is done, data is checked and the last synchronization is made to the SQL Server and transferred to MySQL.

Done!!!

Those pits

This article mainly describes the migration process of those pits, mainly structural and grammatical.

1, timestamp timestamp data type

Both SQL Server and MySQL have timestamp data types, but the two implementations differ greatly.

In SQL Server, this type indicates the relative order in which data modifications occur in the database, whose value is essentially an incrementing number of a bigint type, regardless of time and date, which is not reread at the DB instance level.

In MySQL, this type indicates the relative order in which data modifications occur in the database, and it is essentially a time when the table level is likely to be duplicated.

The pit is the field of this type is the key field of our business for the user to pull data from the server.

Finally, the data for this field is migrated from SQL Server to Mysql,mysql in addition to a Timastamp data type, the program adds a judgment mechanism to the bigint value.

2, character set and emoji emoticons

The emoji expression can be saved directly by default in SQL Server. The start of the migration process did not notice the problem. The problem was discovered only after the historical data migration was complete. Omg!!

You need to use UTF8MB4 to save this data in MySQL.

3, index cannot exceed size

MySQL index length can not exceed 767 bytes, this is a big hole AH!!!

This can only be an optimized data type.

For example, some fields save the device's macid, the data type is varchar (50), after investigation found that MacID only 12 bits, can be modified to varchar (12).

Whether int can be changed to tinyint or smallint, whether can use Latin1 instead of UTF8 (of course, it is best to use UTF8 uniformly), etc.

This is primarily a data type optimization, and a later article will be devoted to data type optimization.

4,varchar (max) type

MySQL does not have this data type, and if you use Power Designer reverse engineering, from SQL Server to MySQL, the data type will change to char (1). You need to use the text type in MySQL.

5, common incompatible syntax

In SQL Server, one uses top to fetch the first few rows of data, and MySQL uses limit to complete this function. This is relatively good modification.

The two methods of using temporal tables are also different.

The format of stored procedures and functions.

6,CTE recursion

In a recursive query operation, SQL Server has a very good function is the CTE recursive query.

7,over window Query

The over window function in SQL Server is also a feature I like very much, such as grouping sorting, grouping scope and so on.

SQL Server migration to the pits that MySQL encounters