Shulou(Shulou.com)06/01 Report--

How to use the MySQL default value constraint? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

Common database constraints:

Default default constraint

Not null: a non-null constraint that specifies that a column is not NULL

Unique: a unique constraint that specifies that the data of a column and a combination of columns cannot be repeated

Primary key: primary key constraint that specifies that the data of a column is not empty, unique, and cannot be repeated

Foreign key: foreign key that specifies that the column record belongs to one record in the main table and references another piece of data

Check: check, specify an expression to validate the specified data

1. Default defines the default value of the column

When a new row is inserted into the table, the column is not explicitly assigned a value, and if a default value for the column is defined, the default value is automatically obtained; if not, (NULL).

-- create a user table CREATE TABLE `test`.`user` (`id` INT (11) NOT NULL AUTO_INCREMENT COMMENT 'id', `name` VARCHAR' name', `sex` TINYINT (1) DEFAULT 1 COMMENT 'gender 1 male and 0 female', PRIMARY KEY (`id`) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci

Add a default constraint to the field:

1. When creating a table: add DEFAULT (value) directly after the field type, depending on the sex field when creating the user table; 2. Add: use sql statement ALTER TABLE `user`MODIFY `sex` TINYINT (1) DEFAULT 1 to add default constraint for adding `sex` field; use sql statement ALTER TABLE `user`MODIFY `name` VARCHAR (225) DEFAULT 'Xiaoming'; add default constraint for adding `name` field; tips: default constraint cannot be used, so ALTER TABLE `user`user`date` DATETIME DEFAULT NOW () AFTER `sex` is not feasible.

Remove the default constraint:

Removing the default constraint operates in the same way as adding the default constraint, modifying the fields of the table; ALTER TABLE `user`MODIFY `sex` TINYINT (1); this removes the default constraint of sex.

Now let's insert a piece of data:

INSERT INTO `user` (`name`) VALUES ('Xiaoming'); SELECT * FROM `user`; result:-- id name sex Xiaoming 1 Murray-

Of course, we can also:

INSERT INTO `user` (`name`, `sex`) VALUES ('Xiaoming', DEFAULT); SELECT * FROM `user`; result:-- id name sex Xiaoming 1 Murray-

If you do this:

INSERT INTO `user` (`sex`) VALUES (DEFAULT); SELECT * FROM `user`; result:-- id name sex (NULL) 1 Murray-

Tips: DEFAULT can be used in both update and query

-- query the default value of the sex field, SELECT DEFAULT (`sex`) FROM `user`;-- update the sex to the default value of UPDATE `user`SET `sex` = DEFAULT WHERE `id` = '1users. Thank you for reading! After reading the above, do you have a general idea of how to use MySQL default value constraints? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, 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.