Shulou(Shulou.com)06/01 Report--

What is the method of setting default values for fields in mysql? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

Methods: 1. Use "CREATE TABLE table name (field name data type DEFAULT default value;)" statement when creating the table; 2, use "ALTER TABLE table name CHANGE COLUMN field name data type DEFAULT default value;" statement setting when modifying the table.

The full name of the default value (Default) is "default value constraint (Default Constraint)", which is used to specify the default value of a field. When you insert a new record in the table, if a field is not assigned a value, the system automatically inserts a default value for that field.

Set default value constraints when creating a table

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

DEFAULT

Where "default" is the default value set for this field, and if it is a character type, it is enclosed in single quotation marks.

Example 1

Create the data table tb_dept3, and the specified department location defaults to the Beijing,SQL statement and the run result as shown below.

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 executed successfully, the field location on the table tb_dept3 has a default value of Beijing, and if the newly inserted record does not specify a department location, it defaults to Beijing.

Note: add default values for columns when you create a table, you can add default values for multiple columns at a time, and you need to pay attention to the data types of different columns.

Add a default value constraint when modifying a table

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

ALTER TABLE CHANGE COLUMN DEFAULT

Example 2

Modify the datasheet tb_dept3, change the default value of the department location to the Shanghai,SQL statement and run the results as shown below.

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: 0mysql > 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 constraint

When a column in a table does not need to set 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 remove the default value constraint for the department location, and the SQL statement and run results are shown below.

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: 0mysql > 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) Thank you for reading! After reading the above, do you have a general idea of how to set default values for fields in mysql? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.