Shulou(Shulou.com)06/02 Report--

This article mainly introduces the method of "mysql sub-database sub-table and data initialization migration". In the daily operation, I believe that many people have doubts about the method of mysql sub-database sub-table and data initialization migration. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "mysql sub-database sub-table and data initialization migration method". Next, please follow the editor to study!

Preface

Data migration is an essential step in sub-database and sub-table. There are two kinds of data migration, one is initialization migration, the other is data migration during the online period. The first consideration here is to initialize the migration.

What is initialization migration? In fact, it is to transfer most of the data from the old library to the new database, which is generally aimed at fixed data.

Scene

1. Downtime migration (not recommended) 2. Data double write migration 3. Using canal middleware to migrate

The above three are generally the solutions of data migration. There are steps to migrate a fixed amount of data in both the first and third scenarios. Aside from the pros and cons, selection and implementation of the migration scheme, I will record these later. Today, I will record the problems I encountered when initializing the data and the solutions.

problem

Because I want to initialize the data through sql rather than programmatically, I have a problem here.

I need to decide which library or table to assign to through hash, but the String type of java has a hashCode method, so what about mysql?

After some search, it is found that mysql does not support the hash method, so what to do, we can only write a custom function through the stored procedure. Then let's just get started!

Operation

As can be seen from the figure above, the hashCode of String in java is implemented in this way, and the key point is this line of code.

H = 31 * h + val [I]

In a nutshell, he is actually converting a string into an Char array and then looping. Each loop has a hash value of * 31, followed by the ASCII value of char. The final value of the loop is the hashCode value at the end of the string.

Then we should just follow this logic in the custom function of mysql, as follows:

DELIMITER $$CREATE FUNCTION hash_code (user_id VARCHAR (50)) RETURNS INTBEGINDECLARE result INT DEFAULT 0; DECLARE num INT DEFAULT 1; WHILE (num 1-- > 2.... -- > Integer.MAX_VALUE (2147483647)-- >-2147483648-- >-2147483647-- >-2147483646. -- > 2-> 1-- > 0

Did you find a pattern? Then just do it! Step by step, let's start with a program for adding the same number.

DELIMITER $$CREATE FUNCTION int_add (num BIGINT) RETURNS BIGINTBEGINDECLARE result BIGINT; SET result = num; IF result > = 0 THENIF 2147483647-result > = result THENSET result = result + result; ELSESET result = (2147483648-result) *-2; ENDIF; ELSEIF result > =-1073741824 THENSET result = result + result; ELSESET result = (- 2147483648-result) *-2; ENDIF; ENDIF; RETURN result; END$$DELIMITER

After writing, I found that I was a little stupid. I did a lot of operations and judgments in order to keep the number within the maximum range of INT, so why don't I use BIGINT? It's okay to exceed INT. Just correct his value after exceeding it. Then just change to a simpler way!

DELIMITER $$CREATE FUNCTION int_add_new (num BIGINT, addNum BIGINT) RETURNS BIGINTBEGINDECLARE result BIGINT; SET result = num + addNum; IF result > 2147483647 THENSET result =-2147483648 + (result-2147483648); ELSEIF result

< - 2147483648 THENSET result = 2147483647 - (- 2147483649 - result) ;ENDIF ; RETURN result ; END$$DELIMITER ; 或者直接乘呢 DELIMITER $$CREATE FUNCTION int_multiply (num BIGINT, multiplyNum BIGINT) RETURNS BIGINTBEGINDECLARE result BIGINT ;SET result = (num * addNum) % (2147483648 * 2) ;IF result >

2147483647 THENSET result =-2147483648 + (result-2147483648); ELSEIF result <-2147483648 THENSET result = 2147483647-(- 2147483649-result); ENDIF; RETURN result; END$$DELIMITER

Well, it does look a lot easier. After this step of ok, it is much easier to write the HashCode method by calling this method, as follows:

DELIMITER $$CREATE FUNCTION hash_code (user_id VARCHAR (50), hashNum INT) RETURNS INTBEGINDECLARE result BIGINT DEFAULT 0; DECLARE num INT DEFAULT 1; DECLARE tempNum INT;DECLARE tempResult BIGINT;WHILE (num

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.