How to use MySQL default value constraints

This article will introduce the default constraint, how to use it and the effect after modification.

Recommended courses: MySQL Tutorial.

Commonly used database constraints:

default default constraint;

not null: non-null constraint, specify a certain The column is not NULL;

unique: unique constraint, specifying that the data of a certain column and several column combinations cannot be repeated;

primary key: primary key constraint, specifying that the data of a certain column is not empty, unique, Cannot be repeated;

foreign key: foreign key, specifies that the column record belongs to a record in the main table and refers to another piece of data;

check: check, specifies an expression for verification Specify data;

1. default defines the default value of the column

When inserting a new row into In the table, there is no explicit value assigned to the column. If the default value of the column is defined, the default value will be automatically obtained; if not, it will be (NULL).

 -- 创建一张user表
CREATE TABLE `test`.`user`(  
  `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` VARCHAR(225) COMMENT '姓名',
  `sex` TINYINT(1) DEFAULT 1 COMMENT '性别 1男 0女',
  PRIMARY KEY (`id`)
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci

Add default constraints to the field:

1.在创建表时：直接在字段类型的后面加上 DEFAULT(value),具体看创建user表时的sex字段；
2.在存在表的情况下添加：
  使用sql语句 ALTER TABLE `user` MODIFY `sex` TINYINT(1) DEFAULT 1; 即可为添加 `sex` 字段添加默认约束；
  使用sql语句 ALTER TABLE `user` MODIFY `name` VARCHAR(225)DEFAULT '小明'; 即可为添加 `name` 字段添加默认约束；

tips：默认约束是不可以使用函数的，所以 ALTER TABLE `user` ADD COLUMN `date` DATETIME DEFAULT NOW() AFTER `sex`； 是不可行的。

Remove default constraints:

移除默认约束和添加默认约束操作方式一样，都是修改表的字段；
ALTER TABLE `user` MODIFY `sex` TINYINT(1); 
这样就移除了sex的默认约束。

Now we insert a piece of data:

INSERT INTO `user`(`name`) VALUES('小明');

SELECT * FROM `user`;

结果：
-----------------------------------
id   name   sex 
   小明    1
-----------------------------------

Of course we can also:

INSERT INTO `user`(`name`,`sex`) VALUES('小明',DEFAULT);
SELECT * FROM `user`;

结果：
---------------------------------
id     name     sex 
     小明        1
---------------------------------

If you do this:

INSERT INTO `user`(`sex`) VALUES(DEFAULT);
SELECT * FROM `user`;

结果：
-----------------------------------
id   name    sex 
   (NULL)   1
-----------------------------------

tips: You can use DEFAULT to operate both update and query;

-- 查询sex字段的默认值  SELECT DEFAULT(`sex`) FROM `user`; 
-- 更新sex为默认值 UPDATE `user` SET `sex` = DEFAULT WHERE `id`='1';

The above is the detailed content of How to use MySQL default value constraints. For more information, please follow other related articles on the PHP Chinese website!