Reset MySQL fields to default values?

In MySQL, there are two ways to reset a MySQL field to its default value. One is the default keyword and the other is the default() function.

Case 1: Use the default keyword. The syntax is as follows:

UPDATE yourTableName SET yourColumnName=default where yourCondition;

Case 2: Use the default() function. The syntax is as follows:

UPDATE yourTableName SET yourColumnName=default(yourColumnName) where yourCondition;

To understand the above syntax, let us create a table. The query to create the table is as follows:

mysql> create table Default_Demo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Name varchar(20),
   -> Age int,
   -> Salary float,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.73 sec)

Use the insert command to insert some records into the table. The query is as follows:

mysql> insert into Default_Demo(Name,Age,Salary) values('John',23,405.56);
Query OK, 1 row affected (0.18 sec)
mysql> insert into Default_Demo(Name,Age,Salary) values('Carol',25,1000.98);
Query OK, 1 row affected (0.22 sec)
mysql> insert into Default_Demo(Name,Age,Salary) values('Larry',21,987.24);
Query OK, 1 row affected (0.09 sec)
mysql> insert into Default_Demo(Name,Age,Salary) values('Sam',24,986.10);
Query OK, 1 row affected (0.17 sec)
mysql> insert into Default_Demo(Name,Age,Salary) values('Mike',22,10000.50);
Query OK, 1 row affected (0.17 sec)
mysql> insert into Default_Demo(Name,Age,Salary) values('David',26,100.45);
Query OK, 1 row affected (0.20 sec)

Use the select statement to display all records in the table. The query is as follows:

mysql> select *from Default_Demo;

The following is the output:

+----+-------+------+---------+
| Id | Name  | Age  | Salary  |
+----+-------+------+---------+
|  1 | John  |   23 | 405.56  |
|  2 | Carol |   25 | 1000.98 |
|  3 | Larry |   21 | 987.24  |
|  4 | Sam   |   24 | 986.1   |
|  5 | Mike  |   22 | 10000.5 |
|  6 | David |   26 | 100.45  |
+----+-------+------+---------+
6 rows in set (0.00 sec)

This is the query to reset the MySQL fields to their default values.

Case 1: Use the default keyword. The query is as follows:

mysql> update Default_Demo set Age=Default where Id=6;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Now you can view the table records. The Age column is NULL and the Id is 6. The query is as follows:

mysql> select *from Default_Demo;

The following is the output:

+----+-------+------+---------+
| Id | Name  | Age  | Salary  |
+----+-------+------+---------+
|  1 | John  |   23 | 405.56  |
|  2 | Carol |   25 | 1000.98 |
|  3 | Larry |   21 | 987.24  |
|  4 | Sam   |   24 | 986.1   |
|  5 | Mike  |   22 | 10000.5 |
|  6 | David | NULL | 100.45  |
+----+-------+------+---------+
6 rows in set (0.00 sec)

View ID 6, The Age column has been updated to the default value NULL.

Case 2: Now you can also use the default() function. Here, the Salary column is updated to its default value, where the Id is 6. The query is as follows:

mysql> update Default_Demo set Salary=Default(Salary) where Id=6;
Query OK, 1 row affected (0.21 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Now check the table record with Id 6.

mysql> select *from Default_Demo where Id=6;

The following is the output:

+----+-------+------+--------+
| Id | Name  | Age  | Salary |
+----+-------+------+--------+
|  6 | David | NULL | NULL   |
+----+-------+------+--------+
1 row in set (0.00 sec)

View the salary column default value NULL and the update is successful.

The above is the detailed content of Reset MySQL fields to default values?. For more information, please follow other related articles on the PHP Chinese website!