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

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

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 open phpmyadmin How to open phpmyadmin Apr 10, 2025 pm 10:51 PM

You can open phpMyAdmin through the following steps: 1. Log in to the website control panel; 2. Find and click the phpMyAdmin icon; 3. Enter MySQL credentials; 4. Click "Login".

MySQL: An Introduction to the World's Most Popular Database MySQL: An Introduction to the World's Most Popular Database Apr 12, 2025 am 12:18 AM

MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.

How to use single threaded redis How to use single threaded redis Apr 10, 2025 pm 07:12 PM

Redis uses a single threaded architecture to provide high performance, simplicity, and consistency. It utilizes I/O multiplexing, event loops, non-blocking I/O, and shared memory to improve concurrency, but with limitations of concurrency limitations, single point of failure, and unsuitable for write-intensive workloads.

MySQL's Place: Databases and Programming MySQL's Place: Databases and Programming Apr 13, 2025 am 12:18 AM

MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen

Why Use MySQL? Benefits and Advantages Why Use MySQL? Benefits and Advantages Apr 12, 2025 am 12:17 AM

MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.

How to connect to the database of apache How to connect to the database of apache Apr 13, 2025 pm 01:03 PM

Apache connects to a database requires the following steps: Install the database driver. Configure the web.xml file to create a connection pool. Create a JDBC data source and specify the connection settings. Use the JDBC API to access the database from Java code, including getting connections, creating statements, binding parameters, executing queries or updates, and processing results.

How to view sql database error How to view sql database error Apr 10, 2025 pm 12:09 PM

The methods for viewing SQL database errors are: 1. View error messages directly; 2. Use SHOW ERRORS and SHOW WARNINGS commands; 3. Access the error log; 4. Use error codes to find the cause of the error; 5. Check the database connection and query syntax; 6. Use debugging tools.

Monitor Redis Droplet with Redis Exporter Service Monitor Redis Droplet with Redis Exporter Service Apr 10, 2025 pm 01:36 PM

Effective monitoring of Redis databases is critical to maintaining optimal performance, identifying potential bottlenecks, and ensuring overall system reliability. Redis Exporter Service is a powerful utility designed to monitor Redis databases using Prometheus. This tutorial will guide you through the complete setup and configuration of Redis Exporter Service, ensuring you seamlessly build monitoring solutions. By studying this tutorial, you will achieve fully operational monitoring settings

See all articles