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

This article mainly explains "what are the wrong operations of the database". The explanation in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian and go deep into it slowly to study and learn "what are the wrong operations of the database" together!

1. Do not use chat tools to send sql statements

Usually developers write sql statements, used to chat tools, such as: qq, nail, or Tencent, etc., sent to the team leader or DBA online environment execution. However, due to some chat tools, some special characters will automatically escape, and some messages will be automatically divided into multiple messages due to their long content.

This will cause the team leader or DBA to copy sql that is not necessarily correct.

They need to manually assemble a complete sql, sometimes even replacing escaped characters back to previous special characters, which wastes a lot of extra time. Even if sql is finally spliced, the person who really executes sql must be very empty in his heart.

Therefore, it is strongly recommended that you send the sql statement to be executed online by mail, which can avoid some of the disadvantages of using chat tools and reduce the chance of some misoperations. And there is a file, so that future problems can be traced back to the cause. Many chat tools only keep the last seven days of history, and emails last longer.

Don't use chat tools to send sql statements!

Don't use chat tools to send sql statements!

Don't use chat tools to send sql statements!

Say important things three times, it really can reduce some misoperation.

2. compress sql statements into one line

Sometimes developers write long sql statements, use various joins and unions, and use beautification tools to turn one sql into multiple lines. When copying sql, you can't be sure if sql is complete. (In order to act tough, he also cheated himself, hahaha)

Online environment sometimes need to connect to the database through the command line, such as: mysql, you copy sql statements over, in the command line interface execution, because the screen scroll too fast, then it is impossible to determine whether sql is executed successfully.

For such problems, it is strongly recommended to compress sql statements into one line, remove redundant newlines and spaces, and effectively reduce some misoperations.

sql compression tool recommended: tool.lu/sql/

3. Select before manipulating the data

What needs special explanation is that the operation data in this article mainly refers to modifying and deleting data.

Many times, due to human error, we write the where condition wrong. But without much scrutiny, the sql statement was executed directly. It's okay if the impact range is small, but if it affects tens of thousands, hundreds of thousands, or even millions of rows of data, we may have to cry.

In this case, before operating on the data, change sql to select count(*) statement, such as:

update order set status=1 where status=0;

Amend to read

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

Check the number of records affected by sql execution, so that you know. Also give yourself a chance to test sql correctly and confirm whether it is executed.

4. Operation data sql plus limit

Even if you confirm that the sql statement has no problem through the select statement above, the number of rows affected after execution is correct.

It is also recommended that you do not immediately execute, it is recommended that when you are executing, add limit + select the number of records. For example:

update order set status=1 where status=0 limit 1000;

If there is too much data updated at once, all relevant records will be locked, resulting in long lock waits and user request timeouts.

In addition, adding limit can avoid too much data at one time, which affects the CPU of the server.

There is one more important reason: after adding limit, the influence range of the operation data is completely controllable.

5. Update the modifier and modification time when updating

Many people write update statements that update only the state if they want to change it, regardless of the other fields. For example:

update order set status=1 where status=0;

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

Later, it was found that there was a problem with the business logic, which should not be updated like this. It is necessary to roll back the status.

You may naturally think of this SQL:

update order set status=0 where status=1;

But on second thought, something was wrong.

Does this mean that some of the data previously listed as status 1 will be updated to 0?

This time, she really wanted to cry.

Here's a good habit for you: When updating data, update the modified by and modified time fields at the same time.

update order set status=1,edit_date=now(),edit_user='admin' where status=0;

This allows you to filter data by modifying the person and time fields when restoring data.

The modification time needed later can be easily found through this sql statement:

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

Of course, if this batch update method is not recommended for high concurrency systems, it may lock the table for a certain period of time, causing request timeout.

Some students may ask: Why update the modifier at the same time, only update the modification time?

The main reasons are as follows:

In order to identify abnormal user operations, it is convenient to count and locate problems later.

In some cases, in the process of executing sql statements, the normal user generated data modification time may be exactly the same as yours, resulting in too much data to check when rolling back.

6. More logical deletion, less physical deletion

Deleting data is an essential business scenario in business development.

Some developers are used to designing tables for physical deletion, which can be easily done with a single delete statement based on the primary key.

The reason they give is to save storage space in the database.

The idea was good, but reality was cruel.

What if a very important piece of data is deleted incorrectly and you want to restore it?

There are only eight words left: no data, no recovery. (PS: Maybe it can be restored by binlog binary file)

The above problem becomes easier if the table is designed with logical deletion. To delete data, simply update the state, for example:

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

If there is an exception, 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;7. Make a backup before manipulating data

If only a small amount of data has been modified, or only one or two sql statements have been executed, the correct data can be quickly located when rollback is needed through the above modifier and modification time fields.

However, if the number of rows modified is large and multiple sqls are executed, a lot of modification time is incurred. At this point, you may be stuck trying to figure out what data needs to be rolled back all at once.

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

SQL backups can be used as follows:

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

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

It can also be simplified into a SQL:

create table order_bak_2021031722 select * from`order`;

Copy the data to the new table as you create it.

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

When restoring data, change sql statement to select statement, first find relevant data in backup database, each data corresponds to an update statement, restore to old table.

8. Intermediate results are written to temporary tables

Sometimes, we need to query the id of the record to be updated with a sql first, and then update the data with these ids.

After batch update, it is found that it is not correct, and the data should be rolled back. However, since some data has been updated, the same sql and the same conditions are used at this time, but the same id cannot be found last time.

Then we started panicking.

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

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

After 3 days, if there is no problem, 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. For example:

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.

Due to a small error by the person executing sql statements, the wrong database was entered.

use trade1;

Then I executed this sql statement, and the result was tragic.

A very effective way to prevent this type of problem is to add the database name:

update `trade2`.` order` set status=1,edit_date=now(),edit_user='admin' where status=0;

In this way, even if the sql statement is executed in the wrong database, it has no effect.

10. Restrictions on field additions, deletions and modifications

Many times, we have to do operations on table fields, such as adding, modifying, and deleting fields, but each case is different.

New fields must be allowed to be blank New fields must be allowed to be blank. Why is it designed like this?

Under normal circumstances, if the program adds a new field, it is generally added to the database first, and then the latest code of the program is issued.

Why this order?

Because if you start the program, then add fields to the database. During the period when the program has just been successfully deployed, but the new database field has not yet been added, in the latest program, all additions, deletions and queries using the newly added field will report an exception that the field does not exist.

Okay, so let's just add fields to the database first, then send the program.

If the newly added field in the database is not empty, the latest program has not been issued, and the old code is still running on the line. If there is an insert operation, an exception that the field cannot be empty will be reported. The old code cannot be assigned because of the new non-null fields.

Therefore, the newly added field must be allowed to be empty.

In addition, this design is more concerned with the rollback operation when the program fails to publish. If the newly added field is allowed to be empty, you can not roll back the database, just roll back the code, is it very convenient?

Deleting fields is not allowed

Deleting fields is not allowed, especially mandatory fields must not be deleted.

Why do you say that?

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

If you deploy the program first, you haven't had time to delete the database related table fields. When there is an insert request, because the field in the database is required, an exception that the required field cannot be empty will be reported.

If you delete the relevant table fields in the database first, the program has not yet had time to issue. At this time, all additions, deletions and queries involving the deleted field will report an exception that the field does not exist.

Therefore, on-line environment mandatory fields must not be deleted.

Modify fields according to actual situation

There are three ways to modify fields:

1. Modify field name

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

HarmonyOS Technology Community

if that program is deploy, the names of the table field in the database have not yet been modified. At this time, all additions, deletions and queries involving this field will report an exception that the field does not exist.

If you change the name of the field in the database first, the program has not yet had time to issue. At this time, all additions, deletions and queries related to this field will also report exceptions where the field does not exist.

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

2. Modify field type

When modifying field types, be sure to be compatible with previous data. For example:

Tinyint can be changed to int, but int to tinyint needs to be carefully weighed.

Varchar can be changed to text, but text to varchar needs to be carefully weighed.

3. Modify field length

It is recommended that the field length be increased, and in general, it is not recommended to decrease it. If you want to make it smaller, you should first confirm the maximum length of the field that may appear, so as to avoid the exception that the field is too long during insert operation.

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

Thank you for reading, the above is "what are the wrong operations of the database" content, after the study of this article, I believe that we have a deeper understanding of the wrong operation of the database, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

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.