Table of Contents
Requirement background
Description of requirements
Requirement realization
UPDATE
  One of the modifiers of
Summary
Home Database Mysql Tutorial In-depth exploration of the usage details of UPDATE in MySQL

In-depth exploration of the usage details of UPDATE in MySQL

Oct 11, 2022 pm 07:32 PM
mysql update

In MySQL, you can use the UPDATE statement to modify and update data in one or more tables. The following article will help you explore the details of the use of UPDATE in MySQL. I hope it will be helpful to you.

In-depth exploration of the usage details of UPDATE in MySQL

Requirement background

I recently received a data migration requirement. The data of the old system was migrated to the new system; the old system did not Business data will be added again, and business operations will be performed on the new system

In order to reduce the impact of migration, data will be migrated in batches, which means that the old and new systems will run in parallel for a period of time

Data is divided Batches are not divided according to the ID range, which means that the IDs of each batch of data are irregular

In addition, in order to ensure the correspondence between the old and new system data, the IDs of the new system use the IDs of the old system as much as possible.

Since the table id is auto-incremented in both the old and new systems, during migration, the id of the old system may have been occupied in the new system, similar to the following

Description of requirements

When migrating data, use the IDs of the old system as much as possible, and conflicting IDs need to be adjusted in batches

  How to adjust this batch of conflicting IDs is exactly what I want to achieve now

My implementation is to preset an ID based on the growth of business data and the maximum ID of the current new system. Starting id

 

 How to write this SQL?

Requirement realization

Some friends may think that this is not simple?

It’s just 5 pieces of data, can’t it be done just like this?

It’s such a simple thing, but there’s so much foreshadowing, sir, do you know how to do it? ?

The poster suddenly realized: My friend, you are so awesome

But if there are a lot of conflicting data (hundreds or thousands), you also Change it one by one?

If you really do this, I really admire you

In-depth exploration of the usage details of UPDATE in MySQL

Obviously, there are more sensible friends

So what should I do? Achieved?

The poster doesn’t want to be too fussy. You can use local variables UPDATE to achieve it, directly access SQL

Let’s look at the actual case

Table tbl_batch_update

## The data is as follows

The execution effect is as follows

After the update

Be more rigorous

How to achieve it? Does

UPDATE also support ORDER BY?

It’s really supported, as shown below

When the original poster usually uses UPDATE, he rarely combines it with ORDER BY and has never tried it. Combined with LIMIT

This attempt made the author feel unfamiliar with UPDATE. What should its complete syntax be like? Let’s read on slowly

UPDATE

The following are all based on the official documentation of MySQL 8.0UPDATE Statement is compiled, it is recommended that you go directly to the official documentation

 Single table syntax

Do you have a lot of questions:

## 

Multiple table syntax

## Compared with single table syntax Table, seemingly simpler, does not support

ORDER BY and LIMIT

LOW_PRIORITY

  One of the modifiers of

UPDATE, used to reduce the priority of SQL When using

LOW_PRIORITY

After that, the execution of UPDATE will be delayed until no other client reads data from the table However, only table-level locks Storage engines only support

LOW_PRIORITY

. The storage engines for table-level locks include: MyISAM, MEMORY and MERGE, so the most commonly used InnoDB is not supported There are very few usage scenarios, just look familiar

 

IGNORE

 

One of the modifiers of UPDATE, used to declare how SQL handles errors during execution If

IGNORE

is not used, UPDATE If an error occurs during execution, it will abort, as shown below

## 

9002

Update When 9003 is generated, the primary key conflicts, and the entire UPDATE is aborted, and 9000 is updated. The successful 9001 will be rolled back, 9003 ~ 9005 has not yet been updated. What will happen if IGNORE

is used?

 

UPDATE

Even if an error occurs during execution, the execution will be completed and the number of affected rows will eventually be returned

The affected rows returned above are 2. Can you tell me which two rows were modified?

For more information about IGNORE, please check: The Effect of IGNORE on Statement Execution

Regarding usage scenarios, parallelism between old and new systems, It may be used when doing data migration. When the primary key or unique key conflicts, just ignore

 ORDER BY

If you are interested in UDPATE If you understand the execution process, it will be easier to understand

  UPDATE There are actually two stages: Check stage, Update stage

Processing row by row, if a row satisfies the WHERE clause, update the row

So, here ORDER BY is the same as ORDER BY## in SELECT # It’s the same effect

Regarding the usage scenarios, you can go back and look at the demand background mentioned earlier,

 

IGNORE Case 1 In fact, you can also use ORDER BY

 

LIMIT

 

LIMIT row_count clause is the row matching limit. Once a row_count row is found that satisfies the WHERE clause, the statement will stop immediately

, regardless of whether the rows actually changed. That is to say

LIMIT is limited to the Check phase and has nothing to do with the Update phase

## 

Note that : There is still a difference from LIMIT in the syntax of

SELECT

 

value DEFAULT

##  

UPDATE

The value of the SET clause is an expression. We can understand, what does this DEFAULT mean? Let’s first look at such a problem. Suppose a column is declared

NOT

NULL, but we update this column to NULL

What will happen

Let’s take a look at SQL_MODE and execute SELECT @@sql_mode; to get the result

  STRICT_TRANS_TABLES indicates that strict mode is enabled, for INSERT and UPDATE statements value Control will be stricter

If we turn off strict mode and look at the execution results

 name Field declaration It becomes NOT NULL. In non-strict SQL mode, set name to NULL is successful, but the changed value is not NULL, but the default value of type VARCHAREmpty string ('')

Summary

1. In strict SQL mode, NOT NULL If the field setting is NULL, an error will be reported directly and the update will fail

  2. Non In strict SQL mode, if the field setting of NOT NULL is NULL, the field value will be set to the field type The corresponding default value

For the default value of the field type, you can view: Data Type Default Values

For the sql_mode, you can view: Server SQL Modes

Normally, the generation environment MySQL is usually in strict mode, so it is enough for everyone to know that there is value DEFAULT

 SET field order

For the following SQL

I believe everyone is very clear

However, what will be the value of the name column in the following SQL

Let’s take a look at the result

Is the value of name a little different from expected?

The SET of a single table UPDATE is performed from left to right, but for multiple tables UPDATE But not, multiple tables UPDATE are not guaranteed to proceed in any specific order

Summary

 1. Whether it is UPDATE or DELETE, there is a process of checking first, and processing each line found

 2. UPDATE in syntax LOW_PRIORITY is rarely used, IGNORE is used occasionally, ORDER BY and LIMIT will be used more often, and they all look familiar

 3. sql_mode is more important Knowledge points are recommended for everyone to master; for production environments, it is strongly recommended to turn on strict mode

Original address: https://www.cnblogs.com/youzhibing/p/16719474.html

Author: Qingshi Lu

[Related recommendations: mysql video tutorial]

The above is the detailed content of In-depth exploration of the usage details of UPDATE in MySQL. For more information, please follow other related articles on the PHP Chinese website!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to optimize MySQL query performance in PHP? How to optimize MySQL query performance in PHP? Jun 03, 2024 pm 08:11 PM

MySQL query performance can be optimized by building indexes that reduce lookup time from linear complexity to logarithmic complexity. Use PreparedStatements to prevent SQL injection and improve query performance. Limit query results and reduce the amount of data processed by the server. Optimize join queries, including using appropriate join types, creating indexes, and considering using subqueries. Analyze queries to identify bottlenecks; use caching to reduce database load; optimize PHP code to minimize overhead.

How to use MySQL backup and restore in PHP? How to use MySQL backup and restore in PHP? Jun 03, 2024 pm 12:19 PM

Backing up and restoring a MySQL database in PHP can be achieved by following these steps: Back up the database: Use the mysqldump command to dump the database into a SQL file. Restore database: Use the mysql command to restore the database from SQL files.

How to insert data into a MySQL table using PHP? How to insert data into a MySQL table using PHP? Jun 02, 2024 pm 02:26 PM

How to insert data into MySQL table? Connect to the database: Use mysqli to establish a connection to the database. Prepare the SQL query: Write an INSERT statement to specify the columns and values ​​to be inserted. Execute query: Use the query() method to execute the insertion query. If successful, a confirmation message will be output.

How to fix mysql_native_password not loaded errors on MySQL 8.4 How to fix mysql_native_password not loaded errors on MySQL 8.4 Dec 09, 2024 am 11:42 AM

One of the major changes introduced in MySQL 8.4 (the latest LTS release as of 2024) is that the "MySQL Native Password" plugin is no longer enabled by default. Further, MySQL 9.0 removes this plugin completely. This change affects PHP and other app

How to use MySQL stored procedures in PHP? How to use MySQL stored procedures in PHP? Jun 02, 2024 pm 02:13 PM

To use MySQL stored procedures in PHP: Use PDO or the MySQLi extension to connect to a MySQL database. Prepare the statement to call the stored procedure. Execute the stored procedure. Process the result set (if the stored procedure returns results). Close the database connection.

KDE Plasma 6.1 brings many enhancements to the popular Linux desktop KDE Plasma 6.1 brings many enhancements to the popular Linux desktop Jun 23, 2024 am 07:54 AM

After several pre-releases, the KDE Plasma development team unveiled version 6.0 of its desktop environment for Linux and BSD systems on 28 February, using the Qt6 framework for the first time. KDE Plasma 6.1 now comes with a number of new features t

How to create a MySQL table using PHP? How to create a MySQL table using PHP? Jun 04, 2024 pm 01:57 PM

Creating a MySQL table using PHP requires the following steps: Connect to the database. Create the database if it does not exist. Select a database. Create table. Execute the query. Close the connection.

The difference between oracle database and mysql The difference between oracle database and mysql May 10, 2024 am 01:54 AM

Oracle database and MySQL are both databases based on the relational model, but Oracle is superior in terms of compatibility, scalability, data types and security; while MySQL focuses on speed and flexibility and is more suitable for small to medium-sized data sets. . ① Oracle provides a wide range of data types, ② provides advanced security features, ③ is suitable for enterprise-level applications; ① MySQL supports NoSQL data types, ② has fewer security measures, and ③ is suitable for small to medium-sized applications.

See all articles