How to set the default value of a MySQL field

How to set the default value of a MySQL field

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

Recommend

Example code of CSS responsive layout system

Responsive layout systems are already very common...

Nginx Service Quick Start Tutorial

Table of contents 1. Introduction to Nginx 1. Wha...

Detailed explanation of the use of Vue mixin

Table of contents Use of Vue mixin Data access in...

Detailed explanation of MySQL semi-synchronization

Table of contents Preface MySQL master-slave repl...

How to execute Linux shell commands in Docker

To execute a shell command in Docker, you need to...

How to move a red rectangle with the mouse in Linux character terminal

Everything is a file! UNIX has already said it. E...

Docker private repository management and deletion of images in local repositories

1: Docker private warehouse installation 1. Downl...

Zabbix monitoring docker application configuration

The application of containers is becoming more an...

Two ways to clear table data in MySQL and their differences

There are two ways to delete data in MySQL: Trunc...

Analyze the working principle of Tomcat

SpringBoot is like a giant python, slowly winding...

Keepalived implements Nginx load balancing and high availability sample code

Chapter 1: Introduction to keepalived The purpose...

FlashFXP ftp client software registration cracking method

The download address of FlashFXP is: https://www....

Implementation of communication between Vue and Flask

Install axios and implement communication Here we...

How to implement variable expression selector in Vue

Table of contents Defining the HTML structure Inp...

A brief discussion on the magical slash in nginx reverse proxy

When configuring nginx reverse proxy, the slashes...