Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the implementation of update statements without adding where conditions, many people may not quite understand, in order to make you better understand, the editor summed up the following content, I hope you can get something according to this article.

Chinese New year can not be stable ah, in recent days, everyone is not in the mood to work, the workload is also less. But some colleagues are so happy that they execute the update statement without adding a where condition!

What? I can't believe it's so awesome. Is such a colleague handsome?

In the WeChat group, a series of reactions are going to collapse when I read them. Fortunately, the accident happened in the test environment!

But the test environment can not be manipulated indiscriminately. No, if you have ruined the test environment, how can the test team proceed? Who will carry this pot?

Although my colleagues have some problems in operation, this pot cannot be completely blamed on him. It is also a problem that I give you too much authority. Therefore, I first restore the scene of the accident, and then configure a little safety restrictions, update, delete related operations, no where conditions, can not perform SQL.

In order to demonstrate my recovery operation, I will simplify my data sheet for you first.

Suppose there are 100000 pieces of data in it. Now you need to change the address of the user whose id is equal to 88 to "Shanghai", but there is no where condition added when update.

After the accident, we begin to recover now, online, it should be more complicated, we should lock the table first to prevent the data from being contaminated again. To lock the table is to see which binary log file you are writing.

Analyze the binary log mysql-bin.000024 and find the relevant records in it. When it is updated, it is address=' Shanghai'. We can filter it out in the log.

The filter content is as follows:

You can see that each line is recorded in it, which is why the binglog format must be row. Among them, @ 1, id,name,sex,address, 2, and 3, respectively, correspond to the fields in the table. I believe you can see here a little bit understand, we just need to convert the relevant records into sql statements, re-import the database to complete data recovery.

With regard to filtering and processing into SQL statements, I subsidize it. I did this through shell, and you can also use the binlog2sql tool, which is now more convenient to roll back.

After filtering, we are importing the data. Unlock the table.

Perfect, now all the data has been restored.

However, the question of authority needs to be restricted. So I added the safe-updates configuration to the MySQL configuration file. In this way, any unconditional update and deletion operations will fail. Hint: ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

After reading the above, do you have any further understanding of what happens when executing update statements without adding where conditions? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.