Home > Database > Mysql Tutorial > body text

How to set default value for field in mysql?

青灯夜游
Release: 2020-10-09 12:10:11
Original
43151 people have browsed it

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.

How to set default value for field in mysql?

(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 <默认值>;
Copy after login

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)
Copy after login

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 <默认值>;
Copy after login

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)
Copy after login

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;
Copy after login

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)
Copy after login

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!

Related labels:
source:php.cn
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 [email protected]
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!