Shulou(Shulou.com)05/31 Report--

This article shows you how to set field defaults in MySQL, which is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

1. Default value related actions

We can use the DEFAULT keyword to define the default value, which is usually used in non-empty columns, which prevents the data table from making errors when entering data.

When creating a table, we can set a default value for a column in the following syntax format:

# format template DEFAULT # sample mysql > CREATE TABLE `test_ tb` (- > `id` int NOT NULL AUTO_INCREMENT,-> `col1` varchar (50) not null DEFAULT'a template,-> `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.01sec) mysql > insert into test_tb (col2) values (2); Query OK, 1 row affected (0.03sec) mysql > select * from test_tb +-+ | id | col1 | col2 | +-+ | 1 | fdg | 1 | 2 | a | 2 | +-+ 2 rows in set (0.00 sec)

As can be seen from the above experiments, when the default value of the field is set, when inserting data, if the value of the field is not specified, it will be processed with the default value.

With regard to default values, there are other actions, such as modifying default values, increasing default values, deleting default values, and so on. Let's take a look at how these 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`col3` set default' 3aregions; alter table `test_ tb`change column `col3` varchar (20) not null DEFAULT '3bfields; alter table `test_ tb` MODIFY column `col3`varchar (20) not null DEFAULT' 3cchallenges; # delete the original default value alter table `test_ tb` alter column `col3` drop default # increase the default value (similar to modification) alter table `test_ tb` alter column `col3`set default '3aa'

two。 What are the use suggestions?

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

Mysql > alter table `test_ tb` add column `col4` varchar (20) DEFAULT '4ahe; 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 fields are often used, such as default to the current time, default not deleted, a status value defaults to 1, and so on. Simply use the following table to show some of the commonly used default values fields.

CREATE TABLE `COMMENT tb` (`id`int unsigned NOT NULL AUTO_INCREMENT COMMENT 'self-increment primary key',... `deley` varchar (50) not null DEFAULT 'China', `col_ status` tinyint not null DEFAULT 1 COMMENT'1: what 2: represent...', `col_ time`datetime NOT NULL DEFAULT '2020-10-01 0000int unsigned NOT NULL AUTO_INCREMENT COMMENT' COMMENT', `is_ deleted`tinyint not null DEFAULT 0 COMMENT'0: not deleted `update_ 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

I would also like to remind you that the default value must match the field type. for example, a field represents a status value, possibly with values of 1, 2, 3. Then the tinyint type is recommended for this field, rather than the char or varchar type.

Based on my personal experience, the author summarizes some suggestions on the use of default values:

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

The default value can also be set to the null field.

Some status value fields are best to give comments indicating what state a numeric value represents.

The default value matches the field type.

The above is how to set field defaults in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, 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.