preface

Whether it is development, testing, or DBA, database operations are inevitably involved, such as creating a table, adding a field, adding data, updating data, deleting data, querying data, and so on.

Normally, this is fine, but if you make a mistake with the database, for example:

1. When deleting order data, the where condition was written incorrectly, resulting in many more user orders being deleted.

2. When updating the validity period of the member, the validity period of all members is updated at one time.

3. Fixed online data error, want to restore.

There are many, many more scenarios, I won’t list them all.

What if there is an online environment database misoperation? Is there a cure for regret?

The answer is yes, please look down carefully.

1. Do not use chat tool to send SQL statements

Usually developers write SQL statements, used to chat tools, such as QQ, Dingding, or Tengtong, etc., sent to the team leader or DBA for execution in the online environment. However, some chat tools will automatically escape some special characters, and some messages will be automatically divided into multiple messages because the content is too long.

This can lead to SQL copied by the team leader or DBA not necessarily being correct.

They need to manually concatenate the entire SQL, and sometimes even replace the escaped characters with previous special characters, wasting a lot of extra time. Even if the final SQL splicing good, the real execution of SQL, the heart must be very virtual.

Therefore, it is highly recommended that you email your SQL statements online to avoid some of the drawbacks of using chat tools and reduce the chances of misoperations. And there’s an archive so you can go back to the cause if there’s a problem. Many chat tools only keep a history of the last 7 days.

Don’t use chat to send SQL statements!

Don’t use chat to send SQL statements!

Don’t use chat to send SQL statements!

Say the important thing three times, it can really reduce some of the misoperations.

2. Compress the SQL statement into a single line

Sometimes, developers write long SQL statements, use various joins and unions, and use beautification tools to turn a SINGLE SQL into multiple lines. When you copy your SQL, you are not even sure that your SQL is complete. (In order to pretend to be pushy, I also pit myself, hahaha)

In an online environment, you sometimes need to connect to a database through the command line, such as mysql. If you copy the SQL statement and run it on the command line interface, it is impossible to determine whether the SQL is successfully executed because the screen scrolls too fast.

To solve this problem, you are advised to compress SQL statements into one line and remove redundant line breaks and Spaces, which can effectively reduce misoperations.

The recommended SQL compression tool is tool.lu/ SQL /

3. Select data before operating on it

It should be noted that the operation data in this article mainly refers to modifying and deleting data.

A lot of times, because of human error, we write the wrong where condition. But without much scrutiny, the SQL statement was executed directly. Fortunately, if it affected tens, hundreds of thousands, or even millions of lines of data, we would probably cry.

In this case, change the SQL to a SELECT count(*) statement before manipulating the data, such as:

update order set status=1 where status=0;
Copy the code

To:

select count(*) from order where status=0;
Copy the code

Look up the number of rows affected by the SQL execution to make sure you have a good idea. Also give yourself a chance to test your SQL to see if it is correct and if it is executed.

4. Operation data SQL add limit

Even if the ABOVE SELECT statement confirms that the SQL statement is ok, the correct number of rows will be affected after execution.

You are advised not to execute it immediately, but to add limit + select while executing. Such as:

update order set status=1 where status=0 limit 1000;
Copy the code

If too much data is updated at one time, all relevant rows will be locked, resulting in long lock waits and user requests timed out.

In addition, limit can avoid the impact of too much data on the CPU of the server.

The most important reason is that with limit, the scope of influence of the operation data is completely controllable.

5. Update the modification and the modification time

Many people write update statements and if they want to change the state, they just update the state, regardless of the other fields. Such as:

update order set status=1 where status=0;
Copy the code

This SQL will update all data with status equal to 0 to 1.

It turned out that there was a problem with the business logic and that the update should not have been done and that the status status needed to be rolled back.

At this point, you might naturally think of this SQL:

update order set status=0 where status=1;
Copy the code

But if you think about it, something’s wrong.

Doesn’t this update some of the data where status was previously 1 to 0?

I’m really gonna cry this time, woo woo woo.

In this case, it is a good habit for you to update data with the modification and modification time fields.

update order set status=1,edit_date=now(),edit_user='admin' where status=0; This allows you to filter data through the change and change time fields when restoring data.Copy the code

You can easily find the time required for the next change by using this SQL statement:

select edit_user ,edit_date from `order` order by edit_date desc limit 50;
Copy the code

However, if the system with high concurrency does not recommend this batch update mode, the table may be locked for a certain period of time, resulting in request timeout.

Some students may ask: why update the modifier at the same time, just update the modification time?

The main reasons are as follows:

1. To identify abnormal user operations, facilitate subsequent statistics and problem location.

2. In some cases, data generated by normal users may be modified at exactly the same time as yours during the execution of SQL statements, resulting in too much data being retrieved during the rollback.

6. Use more logical deletes than physical deletes

In business development, deleting data is an essential business scenario.

Some developers tend to design tables for physical deletes, which can be easily done with a single DELETE statement based on the primary key.

The reason they gave was to save storage space in the database.

The idea is good, but the reality is harsh.

What if an important piece of data is deleted incorrectly and you want to recover it?

There are only two words left: no data, cannot recover. (PS: maybe it can be recovered by binlog)

The above problem becomes easier if the logic used to design the table is removed. To delete data, simply update the deletion status, for example:

update order set del_status=1,edit_date=now(),edit_user='admin' where id=123;
Copy the code

If an exception occurs, to restore the data, restore the deleted state of the ID, for example:

update order set del_status=0,edit_date=now(),edit_user='admin' where id=123;
Copy the code

7. Back up data before operating it

If only a small amount of data is changed, or only one or two SQL statements are executed, the above modification and modification time fields can quickly locate the correct data when the rollback is required.

However, if the number of modified rows is large and multiple SQL is executed, a lot of modification time is incurred. At this point, you might have trouble figuring out what data needs to be rolled back at once.

To solve this problem, you can make a backup of the table.

You can use the following SQL backup:

create table order_bak_2021031721 like`order`; insert into order_bak_2021031721 select * from`order`;Copy the code

Create an identical table and copy the data into the new table.

It can also be simplified to a single SQL statement:

create table order_bak_2021031722 select * from`order`; Copy the data to the new table while creating the table.Copy the code

In addition, it is recommended to add bak and time in the table name, on the one hand, to quickly identify which tables are backed up by the table name, and on the other hand, to distinguish between multiple backups. In this case, you are advised to back up the table multiple times. If an exception occurs, roll back the data to the latest backup, which can save a lot of time for repeated operations.

To restore data, change the SQL statement into a SELECT statement and find related data in the backup database. Each data corresponds to an UPDATE statement and restore the data to the old table.

8. Write intermediate results to temporary tables

Sometimes, a SQL query is used to find the IDS of the records to be updated, and then update the data with those ids.

After batch update, the data should be rolled back if the error is found. However, because some data has been updated, the same SQL is used and the same conditions are used, but the same ID can not be found last time.

At this point, we began to panic.

In this case, we can store the id of the first query into a temporary table, and then update the data with the ID in the temporary table as the query criteria.

If you want to restore the data, you simply update the data with the ID in the temporary table as the query condition.

After 3 days, if there are no problems, you can delete the temporary table.

9. The table name must be preceded by the library name

We write SQL without database names for convenience. Such as:

update order set status=1,edit_date=now(),edit_user='admin' where status=0; If there are multiple databases with the same table ORDER, the table structure is exactly the same, but the data is different.Copy the code

Due to a small error by the person executing the SQL statement, the wrong database was entered.

use trade1;
Copy the code

The SQL statement is then executed, with tragic results.

A very effective way to prevent such problems is to add a database name:

update `trade2`.`order` set status=1,edit_date=now(),edit_user='admin' where status=0; This way, it doesn't matter if the SQL statement goes to the wrong database.Copy the code

10. Restrictions on field addition, deletion and modification

Most of the time, we need to perform operations on table fields, such as adding, modifying, and deleting fields, but each case is different.

New fields must be allowed to be empty

New fields must be allowed to be empty. Why is it designed this way?

Normally, if a program adds a new field, it usually adds the field to the database first, and then sends the latest code of the program.

Why this order?

Because if you send the program first, and then add fields to the database. In the program just deployed successfully, but the database has not yet added the new field of this period of time, the latest program, all the use of the new field of the add, delete, change to check SQL will report the field does not exist exception.

Ok, just add the fields to the database first, and then send the program in the order.

Insert (1); insert (1); insert (2); The old code cannot assign because of the new non-empty field.

So new fields must be allowed to be empty.

In addition, this design is more for the rollback operation in the event of a failure of the program release. Wouldn’t it be convenient to roll back the code instead of the database if the newly added fields were allowed to be empty?

Fields are not allowed to be deleted

Deleting fields is not allowed, especially mandatory fields.

Why do you say so?

Assuming the developer has changed the program to not use delete fields, how do you deploy it?

1. If you have deployed the program before you have time to drop the database related table fields. When an INSERT request is made, an exception will be raised indicating that the required field cannot be empty because the field is required in the database.

2. If the first database related table field deleted, the program has not had time to send. In this case, all the additions, deletions, changes, and searches involving the deleted field will report the exception that the field does not exist.

Therefore, the online environment must be filled in fields must not be deleted.

Modify fields as required

There are three cases for modifying fields:

1. Change the field name

Changing field names is also not allowed, similar to the problem of deleting required fields.

1. If the program is deployed, there is no time to change the table field names in the database. At this time, all related to the increase, deletion, change and search of the field will report the exception that the field does not exist.

2. If you change the name of the field in the database first, the program has not had time to send. In this case, all related to the increase, deletion, change and check of the field will also report the exception that the field does not exist.

Therefore, the online environment field name must not be changed.

2. Change the field type

Be sure to make changes to the field type compatible with previous data. Such as:

1. Tinyint to int is fine, but int to tinyint needs to be weighed carefully.

2. Changing varchar to text is ok, but changing text to varchar requires careful consideration.

3. Change the field length

You are advised to change the field length to a larger value. If you must change it to a smaller size, first determine the maximum length that the field can be, to avoid the error that the field is too long during insert operations.

In addition, it is recommended to set a reasonable length to avoid wasting database resources.

conclusion

This article shares 10 ways to reduce database misoperations. Not all scenarios are right for you. Especially in some scenarios with high concurrency or a large amount of data in a single table, you need to choose according to the actual situation. But I am sure that after reading this article, you will have gained something, because most of the methods are applicable to you and will probably save you a lot of detour. I strongly recommend collecting.

One last word (attention, don’t fuck me for nothing)

If this article is of any help or inspiration to you, please pay attention to it. Your support is the biggest motivation for me to keep writing.

Ask for a key three even: like, forward, look.

Pay attention to the public account: [Su SAN said technology], in the public account reply: interview, code artifact, development manual, time management have excellent fan welfare, in addition reply: add group, can communicate with a lot of BAT big factory seniors and learn.