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

How to generate HASH index to prevent repeated data insertion, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

The concept that any database has a unique value, a unique index, is to prevent duplicate values from being inserted into the table of the unique index field that has been set in the database. Why this thing is mentioned in MYSQL is mainly due to the design of the underlying storage architecture of MYSQL data and the requirements of distributed database shaping. Most of the time, we set the primary key to the mode of integer self-increment, but unlike the design of ORACLE database, our primary key may be inconsistent with the primary key required by the application system, that is, most of the primary keys designed in ORACLE are not self-increasing. And generally it is not integer, and the difference of MYSQL in this aspect will cause some trouble to the developers who have used ORACLE to design tables, so it is an important problem in the current MYSQL database design that not only to meet the needs of MYSQL's underlying data storage optimization, but also to find a set of MYSQL table design methods that can adapt to the ideas of ORACLE developers.

For example, we have a table in which id is self-increasing and has nothing to do with the business, and if this table needs a unique value to confirm the uniqueness of each row of data, we can use the data summary algorithm to solve some unique value generation problems at the database level.

There are many algorithms that can be used here, such as CRC32, MD5, SHA1, etc., they can calculate according to the input data, and produce a unique value in a certain range, through this unique value to identify the uniqueness of this row of data.

There are several algorithms to choose from here. For example, CRC32 algorithm is generally used to generate a 10-bit unique value in the data check integrity of communication. MD5 algorithm is a kind of information summary algorithm, which generates a 32-bit hexadecimal number. When transferring large files in the Internet, it depends on MD5 to calculate the verification code to ensure the integrity and correctness of data transmission. SHA1 is a set of cryptographic algorithm developed by the United States, through which the data cryptographic algorithm, SHA1, will produce a 16-bit 40-bit password.

We can use related algorithms according to our needs to determine the uniqueness of our row of data.

Here we do the test to build some unique indexes and use different algorithms to generate unique values.

We already have the relevant data, and we are inserting the same data.

It received the wrong report, of course it should have reported it wrong.

Someone may immediately ask, what problem does this solve? if I build a federated unique index of that pile of fields, it will be the same.

It's so simple. I can name at least four advantages of my method that are better than yours. In fact, one is enough. My index is smaller than yours.

If you answer, this is also an advantage, what will happen if you are bigger? I can only laugh again.

At the same time, it is beneficial to do so from the storage of the index B+ tree and the application's unique requirements for each record in the database.

The method such as OK can be used not only on MYSQL, but also in various databases. Through this method, we can accelerate data extraction and quickly generate a complete scheme against duplicate record insertion in the database table. Of course, there are shortcomings.

When you have a large number of data inserts, the method of converting data into "special values" may be a bottleneck that produces the speed of inserting data. If the amount of data is not inserted very large and there are strict requirements for the uniqueness of the data, then using MD5 is a better method, and if it is only to speed up the query, you can use the CRC32 method. Although it is possible to "hit the library" when the amount of data reaches tens of millions, it can offset the performance problems caused by multi-field joint indexes, so why not.

What about his other fatal flaw? It's not mentioned here.

After reading the above, have you mastered how to generate HASH indexes to prevent data from being inserted repeatedly? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.