How to set the default value of a MySQL field

Preface:

1. Default value related operations
2. Some usage suggestions

Summarize:

Preface:

In MySQL, we can set default values for table fields. When inserting a new record into a table, if a field is not assigned a value, the system will automatically insert a default value for this field. There is still some knowledge that needs to be understood about default values. In this article, let’s learn about field default values.

1. Default value related operations

We can use the DEFAULT keyword to define default values. Default values are usually used in non-empty columns to prevent errors when entering data into the data table.

When creating a table, we can set a default value for a column. The specific syntax format is as follows:

# Format template <field name> <data type> DEFAULT <default value>

# Examplemysql> CREATE TABLE `test_tb` (
    -> `id` int NOT NULL AUTO_INCREMENT,
    -> `col1` varchar(50) not null DEFAULT 'a',
    -> `col2` int not null DEFAULT 1,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> desc test_tb;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| col1 | varchar(50) | NO | | a | |
| col2 | int(11) | NO | | 1 | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert into test_tb (col1) values ('fdg');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_tb (col2) values (2);
Query OK, 1 row affected (0.03 sec)

mysql> select * from test_tb;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
| 1 | fdg | 1 |
| 2 | a | 2 |
+----+------+------+
2 rows in set (0.00 sec)

From the above experiments, we can see that when the default value is set for the field, when inserting data, if the value of the field is not specified, the default value will be used.

There are other operations about default values, such as modifying default values, adding default values, deleting default values, etc. Let’s look at how this should work.

# Add a new field and set the default value alter table `test_tb` add column `col3` varchar(20) not null DEFAULT 'abc';

# Modify the original default value alter table `test_tb` alter column `col3` set default '3a';
alter table `test_tb` change column `col3` `col3` varchar(20) not null DEFAULT '3b';
alter table `test_tb` MODIFY column `col3` varchar(20) not null DEFAULT '3c';

# Delete the original default value alter table `test_tb` alter column `col3` drop default;

# Increase default value (similar to modification)
alter table `test_tb` alter column `col3` set default '3aa';

2. Some usage suggestions

In fact, not only non-empty fields can set default values, but ordinary fields can also set default values. However, it is generally recommended to set fields to non-empty.

mysql> alter table `test_tb` add column `col4` varchar(20) DEFAULT '4a';
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test_tb;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| col1 | varchar(50) | NO | | a | |
| col2 | int(11) | NO | | 1 | |
| col3 | varchar(20) | NO | | 3aa | |
| col4 | varchar(20) | YES | | 4a | |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

In project development, some default value fields are still frequently used, such as the default is the current time, the default is not deleted, the default value of a certain status is 1, and so on. The following table briefly shows some commonly used default value fields.

CREATE TABLE `default_tb` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key',
  ...
  `country` varchar(50) not null DEFAULT '中国',
  `col_status` tinyint not null DEFAULT 1 COMMENT '1: What does it represent 2: What does it represent...',
  `col_time` datetime NOT NULL DEFAULT '2020-10-01 00:00:00' COMMENT 'What time',
  `is_deleted` tinyint not null DEFAULT 0 COMMENT '0: Not deleted 1: Deleted',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time',
  PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here we also need to remind you that the default value must match the field type. For example, if a field represents a status value, it may take the value 1, 2, 3... Then it is recommended to use the tinyint type for this field instead of the char or varchar type.

Based on my personal experience, I would like to summarize some suggestions on the use of default values:

Setting default values for non-null fields can prevent insertion errors.

Default values can also be set on nullable fields.

It is best to give notes to some status value fields to indicate what status a certain value represents.

The default value must match the field type.

Summarize:

This article mainly talks about the knowledge related to the default value of MySQL fields. It is relatively simple and easy to understand. I hope you can gain something from it.

The above is the detailed content on how to set the default value of MySQL fields. For more information about the default value of MySQL fields, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

How to assign default values to fields when querying MySQL
Add a field to the table in the MySQL command line (field name, whether it is empty, default value)
MySQL table field setting default value (graphic tutorial and pay attention to details)
MySQL table field time setting default value

<<: Three ways to implement text color gradient in CSS

>>: Detailed explanation of the use of Vue image drag and drop zoom component