Explain the use of default in MySQL in detail

NULL and NOT NULL modifiers, DEFAULT modifiers, AUTO_INCREMENT modifiers.

NULL and NOT NULL modifiers

You can specify whether the field can be empty (NULL) by appending the NULL or NOT NULL modifier after each field,

Or must you fill in the data (NOT NULL). MySQL specifies the field as the NULL modifier by default, if one field is specified as NOT NULL,

MySQL does not allow inserting null values into this field (all null values mentioned here are NULL), because this is a "regulation".

/* 
 Create a buddy table where id ,name ,pass Neither can be empty  
*/ 
create table friends ( 
id int(3) not null, 
name varchar(8) not null, 
pass varchar(20) not null 
); 
/* 
 Error prompt, id Column cannot be empty  
#1048 - Column 'id' cannot be null 
*/ 
INSERT INTO friends 
VALUES ( 
NULL , 'simaopig', 'simaopig' 
); 

However, this rule does not apply in self-adding columns and TIMESTAMP fields.

Inserting NULL values into these fields will result in the insertion of the next 1 automatically incremented value or the current timestamp.

DEFAULT modifier

You can use the DEFAULT modifier to set a default value for the field.

MySQL will automatically set the default value of this field for you when you forget to pass the value of this field when inserting records.

/* 
 Create im Table, setting the name Field to the default value 'QQ' 
*/ 
create table im ( 
id int(3) not null, 
name varchar(25) not null default 'QQ' 
); 
/* 
 Insert data, do not transfer name The value of the field, MySQL That sets the default value for it  
 You run the  SQL  Statement has been successfully run.  
*/ 
INSERT INTO im( id, name ) VALUES ( 2, 'MSN' ) ; 
INSERT INTO im( id ) VALUES ( 3 ) ; 
SELECT * FROM im LIMIT 0 , 30; 
/* 
id name 
2 MSN 
3 QQ 
*/ 

If the DEFAULT modifier is not specified in a field, MySQL automatically sets the default value depending on whether the field is NULL or NOT or NULL.

If the specified field can be NULL, MySQL sets its default value to NULL.

For the NOT NULL field, MySQL inserts 0 for numeric types, an empty string for string types,

The timestamp type is inserted into the current date and time, and the ENUM type is inserted into Article 1 of the enumeration group.

AUTO_INCREMENT modifier

The AUTO_INCREMENT modifier applies only to the INT field, indicating that MySQL should automatically generate 1 number for this field

(Add 1 each time to the value generated last time). This is very useful for primary keys (described later).

Because it allows developers to use MySQL to create only one identifier for each record.

/* 
 You run the  SQL  Statement has been successfully run.  (  Inquiry cost  0.0170  Seconds  ) 
*/ 
CREATE TABLE items( 
id int( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , 
label varchar( 255 ) NOT NULL 
); 
/* 
 Insert 3 Data, do not specify id Using the default value, plus AUTO_INCREMENT 
 You run the  SQL  Statement has been successfully run.  
*/ 
insert into items(label) values ('xxx'); 
insert into items(label) values ('yyy'); 
insert into items(label) values ('zzz'); 
/* 
 Show it all, look 1 Download the data and pay attention id Change of  
*/ 
select * from items; 
/* 
id label 
1 xxx 
2 yyy 
3 zzz 
*/ 

There can only be 1 AUTO_INCREMENT field in the MySQL table, and this field must be defined as a key.

In addition to field constraints, MySQL also allows table-level constraints such as primary and foreign keys, indexes, and uniqueness constraints.

Summarize