Use of MySQL default value (DEFAULT)

The full name of the default value (Default) is "Default Constraint", which specifies the default value of a column.. When inserting a new record in the table, if no value is assigned to a field, the system will automatically insert the default value for this field.

For example, in the employee information table, if there are more department locations in Beijing, then the department location can default to "Beijing", and the system will automatically assign the value to "Beijing".

Default value constraints are usually used in columns where non-empty constraints have been set, which can prevent errors in data tables when entering data.

Set default value constraints when creating table

When creating a table, you can use the DEFAULT keyword to set the default value constraints. The specific syntax format is as follows:

<Field name> <Data Type> DEFAULT <default value>;

Among them, "Default Value" is the default value set by this field. If it is of character type, it should be enclosed in single quotes.

Example 1Create data table tb_dept3, the specified department location defaults to Beijing, and the SQL statements and run results are as follows.

mysql> CREATE TABLE tb_dept3
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(22),
    -> location VARCHAR(50) DEFAULT 'Beijing'
    -> );

mysql> DESC tb_dept3;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(22) | YES  |     | NULL    |       |
| location | varchar(50) | YES  |     | Beijing |       |
+----------+-------------+------+-----+---------+-------+

After the above statement is successfully executed, the field location on table tb_dept3 has a default value Beijing. If the newly inserted record does not have a specified department location, the default is Beijing.

Note: When creating a table, add default values ​​to columns. You can add default values ​​to multiple columns at once. Pay attention to the data types of different columns.

Add default value constraints when modifying tables

The syntax format for adding default value constraints when modifying tables is as follows:

ALTER TABLE <Data table name>
CHANGE COLUMN <Field name> <Field name> <Data Type> DEFAULT <default value>;

Example 2
Modify the data table tb_dept3 and change the default value of the department location to Shanghai. The SQL statements and operation results are as follows.

mysql> ALTER TABLE tb_dept3
    -> CHANGE COLUMN location
    -> location VARCHAR(50) DEFAULT 'Shanghai';

mysql> DESC tb_dept3;
+----------+-------------+------+-----+----------+-------+
| Field    | Type        | Null | Key | Default  | Extra |
+----------+-------------+------+-----+----------+-------+
| id       | int(11)     | NO   | PRI | NULL     |       |
| name     | varchar(22) | YES  |     | NULL     |       |
| location | varchar(50) | YES  |     | Shanghai |       |
+----------+-------------+------+-----+----------+-------+

Delete the default value constraint

When a column in a table does not need to be set by default, it needs to be deleted from the table.

The syntax format for deleting default value constraints when modifying tables is as follows:

ALTER TABLE <Data table name>
CHANGE COLUMN <Field name> <Field name> <Data Type> DEFAULT NULL;

Example 3Modify the data table tb_dept3 to delete the default value constraints of department locations. The SQL statements and running results are as follows.

mysql> ALTER TABLE tb_dept3
    -> CHANGE COLUMN location
    -> location VARCHAR(50) DEFAULT NULL;

mysql> DESC tb_dept3;
+----------+-------------+------+-----+----------+-------+
| Field    | Type        | Null | Key | Default  | Extra |
+----------+-------------+------+-----+----------+-------+
| id       | int(11)     | NO   | PRI | NULL     |       |
| name     | varchar(22) | YES  |     | NULL     |       |
| location | varchar(50) | YES  |     | NULL     |       |
+----------+-------------+------+-----+----------+-------+
3 rows in set (0.00 sec)

This is the end of this article about the use of MySQL default value (DEFAULT). For more related content on MySQL default value DEFAULT, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!