mysql set default value

MySQL is a widely used relational database management system. In MySQL, we can set default values ​​for columns in a table to ensure that when a new row is inserted, if the user does not specify a value for the column, the column is automatically filled with the default value. In this article, we will discuss how to set default values ​​in MySQL.

1. What is the default value?

The default value is the value that is automatically populated in the column when a new row is inserted into the table. When the user does not specify a value for the column, the system automatically populates the column with the default value. If the user provides a value, that value will be used as the value for the column.

2. How to set the default value?

In MySQL, we can use the DEFAULT keyword to set a default value. This keyword instructs the system to use default values ​​when inserting new rows. Here is an example:

First, let us create a simple table with three columns: id, username and email:

CREATE TABLE users(
id INT NOT NULL AUTO_INCREMENT ,
username VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL,
PRIMARY KEY (id)
);

Next, we add to the table Add a new column created_at with a default value that will contain the creation date and time of each user:

ALTER TABLE users ADD created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

In the above statement, We have used the ADD keyword to add a new column to the table. We use the DEFAULT CURRENT_TIMESTAMP clause to set the default value of this column to the current timestamp, that is, when inserting a new row, the system will automatically set the created_at column to the current time.

We can also use constants to set default values. For example, if we wish to set a Boolean column, we can use the following statement:

ALTER TABLE users ADD is_active BOOLEAN DEFAULT TRUE;

In the above statement, we use the DEFAULT clause to set The default value for a Boolean column is TRUE.

3. How to modify the default value?

If we want to change the default value of a column, we can use the ALTER TABLE statement. For example, let's change the default value of the created_at column to January 1, 2020:

ALTER TABLE users MODIFY created_at TIMESTAMP DEFAULT '2020-01-01 00:00:00';

In the above statement, we use the MODIFY keyword to change the data type and default value of the column created_at. We specified a default date and time in the DEFAULT clause, which will be automatically used when inserting new rows.

4. How to delete the default value?

If we want to remove the default value of a column, we can use the ALTER TABLE statement. For example, let us delete the default value of column created_at:

ALTER TABLE users ALTER COLUMN created_at DROP DEFAULT;

In the above statement, we use the ALTER COLUMN command to delete the default value of created_at column.

5. How to check the default value?

To view the default value of a column, you can use the DESCRIBE or SHOW CREATE TABLE statement. For example, the following is the use of the DESCRIBE statement to view the details of the users table:

DESCRIBE users;

The output is as follows:

Field	Type	Null	Key	Default	Extra
id	int(11)	NO	PRI	NULL	auto_increment
username	varchar(50)	NO		NULL
email	varchar(50)	NO		NULL
created_at	timestamp	YES		NULL
is_active	tinyint(1)	YES		1

In the above In the table, we can see that the default value of the created_at column is NULL, which indicates that the column has no default value. We can also view the complete creation statement of the users table:

SHOW CREATE TABLE users;

The output is as follows:

CREATE TABLE users (
id int(11) NOT NULL AUTO_INCREMENT,
username varchar(50) NOT NULL,
email varchar(50) NOT NULL,
created_at timestamp NULL DEFAULT NULL,
is_active tinyint(1) DEFAULT '1',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

In the above table, we can see that the default value of the created_at column is NULL.

6. Summary

Setting default values ​​in MySQL is a useful technique. You can use the DEFAULT keyword to set default values, and you can use the ALTER TABLE statement to change or delete default values. You can learn about a column's default value by viewing the table details. Using default values ​​reduces the effort of writing insert statements and ensures that when the user does not provide a value, the system will use the predefined value.

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