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
You can use it when creating a tableDEFAULTKeywords set 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 1
Create 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'
-> );
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 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> <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';
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 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 3
Modify 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;
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)MySQL non-null constraint (NOT NULL)
MySQL Non-null constraint (NOT NULL) means that the value of a field cannot be empty. For fields that use non-empty constraints, if the user does not specify a value when adding data, the database system will report an error. It can be implemented through the CREATE TABLE or ALTER TABLE statement. Add the keyword NOT NULL as the qualifier to define a column in the table to constrain the value of the column to be empty.
For example, if the user name is not added in the user information table, then this user information is invalid. At this time, non-empty constraints can be set for the user name field.
Set non-empty constraints when creating table
You can use it when creating a tableNOT NULLKeywords set non-empty constraints, and the specific syntax format is as follows:
<field name> <data type> NOT NULL;
Example 1
Create data table tb_dept4, the specified department name cannot be empty, the SQL statement and the running results are as follows.
mysql> CREATE TABLE tb_dept4
-> (
-> id INT(11) PRIMARY KEY,
-> name VARCHAR(22) NOT NULL,
-> location VARCHAR(50)
-> );
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) | NO | | NULL | |
| location | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.06 sec)Add non-empty constraints when modifying tables
If you forget to set non-empty constraints for the field when creating a table, you can also add non-empty constraints by modifying the table.
The syntax format for setting non-empty constraints when modifying the table is as follows:
ALTER TABLE <Data table name> CHANGE COLUMN <Field name> <Field name> <Data Type> NOT NULL;
Example 2
Modify the data table tb_dept4, the specified department location cannot be empty, the SQL statement and operation results are as follows.
mysql> ALTER TABLE tb_dept4
-> CHANGE COLUMN location
-> location VARCHAR(50) NOT NULL;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tb_dept4;
+----------+-------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+----------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(22) | NO | | NULL | |
| location | varchar(50) | NO | | NULL | |
+----------+-------------+------+-----+----------+-------+
3 rows in set (0.00 sec)Delete non-empty constraints
The syntax rules for deleting non-empty constraints when modifying a table are as follows:
ALTER TABLE <Data table name> CHANGE COLUMN <Field name> <Field name> <Data Type> NULL;
Example 3
Modify the data table tb_dept4 to delete the non-empty constraints of the department location. The SQL statements and running results are as follows.
mysql> ALTER TABLE tb_dept4
-> CHANGE COLUMN location
-> location VARCHAR(50) NULL;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tb_dept4;
+----------+-------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+----------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(22) | NO | | NULL | |
| location | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+----------+-------+
3 rows in set (0.00 sec)This is the article about the implementation of MySQL default values (DEFAULT) and non-null constraints (NOT NULL). For more related contents of MySQL default values and non-null constraints, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!