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

This article mainly introduces "DML operation of table data in MySQL". In daily operation, I believe that many people have doubts about the DML operation of table data in MySQL. 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 "DML operation of table data in MySQL". Next, please follow the editor to study!

(1) suggestions for writing DML sentences

(1)。 The @ number alternative field name is not allowed in the DML statement

An unreasonable way of writing:

UPDATE table_name SET @ 1=NOW () WHERE @ 2room1

The correct way to write:

UPDATE table_name SET column_name1=NOW () WHERE column_name2=1

(2)。 UPDATE OR DELETE disable limit clause

An unreasonable way of writing:

UPDATE table_name SET column_name1=NOW () WHERE column_name2=1 LIMIT 1

The correct way to write:

UPDATE table_name SET column_name1=NOW () WHERE column_name2=1

(3)。 The INSERT statement needs to write the relationship between the value and the field.

An unreasonable way of writing:

INSERT INTO table_name VALUES (NOW (), DATE_ADD (NOW (), INTERVAL + 1 DAY))

The correct way to write:

INSERT INTO table_name (gmt_create,gmt_modify) VALUES (NOW (), DATE_ADD (NOW (), INTERVAL + 1 DAY))

(4)。 Less use of uncertain functions in DML statements

Commonly used uncertainty functions: UUID (), RAND (), SYSDATE () and other functions, if there is no special use, please replace them with deterministic functions.

(2) DELETE OR UPDATE with a large amount of data

For some reasons and operational purposes, you may need to clean up a lot of data in the database or change the value of a field. Give two examples:

During the period of special rectification of ① network, it is necessary to delete a large number of contents containing certain keywords.

② gives a range of 100-1000 game coins to gamers who meet certain conditions (for example, rank, online duration).

Given two examples of data modification requirements, if they are done directly according to the relevant requirements, one needs to use a fuzzy query, and the other data update condition does not have a reasonable index available, which may result in table object table-level locks being locked for a long time and blocking other change type data operation services. Therefore, we have to adopt a more reasonable approach and suggest the following steps to be implemented:

① designs and creates a table tmp_pk_data to record the primary key of the record to be modified and the relevant information needed

② gives priority to running a SQL command or stored procedure on the slave database, and writes the primary key and related data to the table TMP _ pk_data

③ writes a stored procedure that uses cursor loop control to obtain tmp_pd_data information, updates or deletes the data of the target table according to the primary key, and recommends that this operation be done on the standby database (Note: it must be in dual-primary replication mode before it can be executed on the standby database)

(3) DELETE of regular clean-up data

Clean up the regular data regularly, and give priority to classifying the data manipulation of the target table:

If ① is log data, you can use partition table instead. For example, if you delete data by date, you can use date as data partition condition, and then add or delete partitions to clean up the data.

If ② is the UPDATE/DELETE/SELECT manipulation condition of data, which is consistent with or is included in the rules of regularly cleaning data, you can consider using partitioned tables, and then achieve the goal of data cleaning by deleting partitions.

If ③ cannot be solved by using partition tables, you can consider referring to the content of "DELETE OR UPDATE with a large amount of data" introduced in the previous section.

(4) DML skills of large amount of data in Mmurm architecture.

Clean up the regular data regularly, and give priority to classifying the data manipulation of the target table:

If ① is log data, you can use partition table instead. For example, if you delete data by date, you can use date as data partition condition, and then add or delete partitions to clean up the data.

If ② is the UPDATE/DELETE/SELECT manipulation condition of data, which is consistent with or is included in the rules of regularly cleaning data, you can consider using partitioned tables, and then achieve the goal of data cleaning by deleting partitions.

If ③ cannot be solved by using partition tables, you can consider referring to the content of "DELETE OR UPDATE with a large amount of data" introduced in the previous section.

At this point, the study on "DML operation of table data in MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.