"Pitfalls" of the DML-command Update MySQL

Each programmer who had to work with databases faced DML operators (such as the "data manipulation language"), such as Select , Insert , Delete and Update . The MySQL environment also uses all of the above commands in its arsenal.

These operators logically indicate their purpose - selecting records, inserting new values, updating existing data, full, or according to specified conditions, deleting information in the database. Teaching theoretical materials describe in detail the working principle of each team and their syntax, but there is no mention of difficulties that may arise in practice during use. This material will be devoted to consideration of some of them.

Briefly about DML-operators (Insert)

Before proceeding further, it is necessary to recall again in more detail the purpose of each of the functions. We will be more interested in two operators: Insert and Update , since it is from these that the main difficulties arise when processing large amounts of data .

You need to start with the Insert command , and then go smoothly to Update. The MySQL system, like any other modern DBMS, uses the Insert operation to add new entries to existing database tables. The syntax of this operation is very simple and straightforward. It contains an enumeration of the fields where the values will be entered, the destination is the name of the table - and the list of the entries directly. Each time the Insert command is executed, the database will be updated with new values.

Update Operator

In practice, however, situations often arise that for one set of data, one or more attribute values must be updated. As an example, we can cite the situation when the enterprise underwent a reform with the further renaming of the main departments. In this case, it is necessary to make changes for each department. If only the names change, then the problem is solved very quickly. But if the encoding of each component of the whole production changes, which, as a rule, serves as the primary key, this, in turn, entails changes in the information and for each employee.

To solve this problem, a DML-operator - Update can be applied . The MySQL server, operating with a large number of records, using the update operator, will execute the requested query and solve the problem. But sometimes during the update there are not quite clear and difficult to explain difficulties. It is about the complexity of updating records that will be discussed later.

About what little is said in theory ...

The Update command, as noted above, is used to update existing records in the table. But in practice, clients accessing database servers are not always known, there is a certain set of data in the tables or not. Preliminary verification of the availability of data in the database for subsequent updates leads to time-consuming and wasted server capabilities.

To avoid this, the DBMS includes a special MySQL-Insert * Update construct, in which the insertion or update can be performed independently of each other. That is, when there is an entry for a certain condition in the table, an update will occur. If no data is found for the condition in question, the MySQL server will be able to perform the data addition request.

Update data if there are duplicates

An important component of such an Insert - query in the MySQL database management system is the "On Duplicate Key Update" prefix. The complete syntax of the query is as follows: " insert into test_table (employer_id, name) values (1, 'Abramov') on duplicate key update last_modified = NOW (); ".

Such a request can be used to record the actions of employees, for example, determining the crossing time of the passing enterprise with the subsequent calculation of the time of the break and the identification of delays. In order not to enter several records into the table, it is enough for each employee to keep records with a permanent update. It is the design of the duplicate check that allows you to do this.

Actually about the problems ...

Considering the above example of registering employees' actions at the checkpoint, the problem can be the use of auto- increment fields, which are usually used to fill the values of primary _keys. When using the MySQL Update command in a design with Insert Auto _ increment, the fields are constantly increasing.

Similarly, everything happens when a replacement design is used, in the case of duplicate detection. "Autoincremental" value increases even when it is not necessary. Because of this, there are problems of missing values or overflow of the range, which in the subsequent lead to violations of the performance of database management systems.

The greatest probability of occurrence of a problem

The described problem should be considered by Web developers, as it is most common in multi-user systems (Internet sites, portals, etc.), when a large number of Insert And Update MySQL.

PHP - calls to the database are performed very often. Therefore, the achievement of the maximum value by the fields defined as auto_increment occurs quickly, and when analyzing the difficulties encountered, it is impossible to establish the causes at once.

Therefore, developers are advised to carefully approach the use of the duplicate key on the mysql update command . Select - queries when accessing the database server will work without errors, but adding new records to the database is fraught with unpleasant situations, which subsequently lead to serious problems. As an alternative, it is recommended for the autoincremental fields to initially check the availability of records for them, and then to update them.