Every programmer who had to work with databases came across DML-operators ( translated from English - "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 talk about their purpose - selecting records, inserting new values, updating existing data, or, according to conditions, to delete information in the database. The teaching theoretical materials describe in detail the principle of work of each team and their syntax, but nowhere to find references to 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 moving on, it is necessary to once again remind in more detail about the purpose of each of the functions. We will be more interested in two operators: Insert and Update , since it is from them that the main difficulties arise when processing large amounts of data .
You need to start with the Insert command , and then smoothly go to Update. The MySQL system, like any other modern DBMS, uses the Insert operation to add new records to existing database tables. The syntax for this operation is very simple and straightforward. It contains an enumeration of the fields where the values will be entered, the destination - the name of the table - and directly a list of the entered data. Each time you execute the Insert command, the database will be updated with new values.
Update statement
However, in practice, quite often situations arise that for a certain data set it is necessary to update one or more attribute values. As an example, we can cite the situation when the company underwent a reformation 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 entire production is changed, which, as a rule, serves as the primary key, then this, in turn, entails changes in the information for each employee.
To solve the problem under consideration, the DML operator Update can be used . The MySQL server, operating with a large number of records, with the help of the update operator, will fulfill the required query and solve the problem. But sometimes during the update, not quite clear and difficult to explain difficulties arise. It is about the difficulties that updating records causes, we will discuss further.
What little is said in theory ...
The Update command, as noted above, is used to update existing records in a table. But in practice, clients accessing database servers do not always know if a particular set of data exists in tables or not. A preliminary check of the availability of data in the database for subsequent updates leads to time and wasted server capabilities.
To prevent this from happening, the DBMS has a special MySQL construction - Insert * Update , in which the insert or update can be performed independently of each other. That is, when there is an entry in the table for a certain condition, an update will occur. If no data is found for the condition under consideration, the MySQL server will be able to fulfill the request for adding data.
Duplicate data update
An important component of such an Insert query in the MySQL database management system is the “On Duplicate Key Update” prefix. The full query syntax 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 register the actions of employees, for example, determining the time of crossing a checkpoint with subsequent calculation of the break time and identifying delays. In order not to make several entries in the table, it is enough for each employee to keep records with constant updating. It is the duplicate checker design that allows this.
Actually about the problems ...
Considering the above example of registering employee actions at the checkpoint, one problem is the use of auto- increment fields, which are usually used to populate primary key values. When using the MySQL Update command in a construction with Insert auto _ increment, fields are constantly increasing.
Similarly, everything happens when a replacement design is used, in case duplicates are found. The “auto-incrementing" value increases even when this is not necessary. Because of this, there are problems of missing values or overflowing the range, which subsequently lead to disruptions in the performance of database management systems.
Most likely problem
The described problem must be taken into account by Web developers, since it is most common in multi-user systems (Internet sites, portals, etc.), when a large number of Insert procedures are performed in the system and Update MySQL.
PHP - calls to the database are performed very often. Therefore, reaching the maximum value with the fields defined as auto_increment occurs quickly, and when analyzing the difficulties encountered, it is not possible to establish the reasons immediately.
Therefore, developers are advised to carefully use the on duplicate key construct in 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 auto-increment fields to initially check for entries for them, and then update them.