Record a pitfall of MySQL update statement update

background

Recently, I executed a DML statement during an online operation. I thought it was foolproof, but the application reported that it was not updated and the data remained unchanged. After investigation, I found that I had written the statement incorrectly, which caused the result of the update statement to be inconsistent with my expectations.

Re-enactment

For the convenience of demonstration, create a user table and insert five records at the same time.

create table user(
id int(12) comment 'user primary key id',
name varchar(36) comment 'User name',
age int(12) comment 'age');

insert into user values ​​(1,'one',11),(2,'two',12),(3,'three',13),(4,'four',15),(5,'five',15);

After the execution is completed, the data in the user table is as follows:

+------+-------+------+
| id | name | age |
+------+-------+------+
| 1 | one | 11 |
| 2 | two | 12 |
| 3 | three | 13 |
| 4 | four | 15 |
| 5 | five | 15 |
+------+-------+------+

Now I need to change all ages to 10 and usernames to user - assuming this operation makes sense - the DML statement I submit to the operation and maintenance is as follows:

update user set age=10 and name='user';

When I refresh the user table, I see that all the data in the table after executing the update statement is as follows:

+------+-------+------+
| id | name | age |
+------+-------+------+
| 1 | one | 0 |
| 2 | two | 0 |
| 3 | three | 0 |
| 4 | four | 0 |
| 5 | five | 0 |
+------+-------+------+

A magical thing happened. The age field was all updated to 0, while the name field was not modified at all!

Causes and Corrections

The cause of the error is actually very simple, the update statement is written incorrectly. The syntax of the update statement in MySQL is

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
```sql

If updating multiple fields, adjacent fields should be separated by commas instead of `and`. If the update statement uses "and" as the separator between multiple fields, as I submitted to the operation and maintenance at the beginning, this update statement will eventually become ```sql
update user set age=(10 and name='user');

As a judgment statement with a boolean return value, (10 and name='user') will be mapped to 1 or 0. There is a 99.999% chance that the first updated variable will be updated to incorrect data.

The correct update statement should be

update user set age=10, name='user';

lesson

Before submitting DML statements, try the basic SQL syntax in a test environment to make sure you remember it correctly.

summary

The syntax of the update statement in MySQL is

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

The above is the details of a pitfall of using the MySQL update statement. For more information about MySQL update statements, please pay attention to other related articles on 123WORDPRESS.COM!