How to set default value for field in mysql?

Method: 1. When creating a table, use the "CREATE TABLE table name (field name data type DEFAULT default value;)" statement to set it; 2. When modifying the table, use "ALTER TABLE table name CHANGE COLUMN field name data type DEFAULT default value;" statement setting.

(Recommended tutorial: mysql video tutorial)

The full name of the default value (Default) is "Default value" "Default Constraint" is used to specify the default value of a field. When inserting a new record into the table, if a field is not assigned a value, the system will automatically insert a default value for this field.

Set default value constraints when creating a table

When creating a table, use the DEFAULT keyword in the CREATE TABLE statement to set default value constraints , the specific syntax format is as follows:

<字段名> <数据类型> DEFAULT <默认值>;

Among them, "default value" is the default value set for the field. If it is a character type, it must be enclosed in single quotes.

Example 1

Create the data table tb_dept3, and specify the department location as Beijing by default. The SQL statement and running results are as follows.

mysql> CREATE TABLE tb_dept3
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(22),
    -> location VARCHAR(50) DEFAULT 'Beijing'
    -> );
Query OK, 0 rows affected (0.37 sec)

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 |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.06 sec)

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

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

Add a default value constraint when modifying the table

The syntax format for adding a default value constraint when modifying the table is as follows:

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>;

Example 2

Modify the data table tb_dept3 and change the default value of the department location to Shanghai. The SQL statement and running results are as follows.

mysql> ALTER TABLE tb_dept3
    -> CHANGE COLUMN location
    -> location VARCHAR(50) DEFAULT 'Shanghai';
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

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 |       |
+----------+-------------+------+-----+----------+-------+
3 rows in set (0.00 sec)

Delete default value constraints

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

The syntax format for deleting the default value constraint when modifying the table is as follows:

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <字段名> <数据类型> DEFAULT NULL;

Example 3

Modify the data table tb_dept3 to delete the default value constraint of the department position, the SQL statement and The running results are as follows.

mysql> ALTER TABLE tb_dept3
    -> CHANGE COLUMN location
    -> location VARCHAR(50) DEFAULT NULL;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

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)

The above is the detailed content of How to set default value for field in mysql?. For more information, please follow other related articles on the PHP Chinese website!