Detailed explanation of the use of default in MySQL

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

NULL and NOT NULL modifiers

You can add the NULL or NOT NULL modifier after each field to specify whether the field can be empty (NULL).

Or does it mean that data must be filled in (NOT NULL). MySQL specifies fields as NULL modifiers by default. If a field is specified as NOT NULL,

MySQL does not allow null values ​​to be inserted into this field (the null values ​​mentioned here are all NULL) because this is a "rule".

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

However, this rule does not apply to auto-increment columns and TIMESTAMP fields.

Inserting a NULL value into these columns will result in the next auto-incremented value or the current timestamp being inserted.

DEFAULT Modifier

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

When inserting a record, if you forget to pass the value of the field, MySQL will automatically set the default value of the field for you.

/* 
Create the im table and set the name field to the default value 'QQ' 
*/ 
create table im ( 
id int(3) not null, 
name varchar(25) not null default 'QQ' 
); 
/* 
When inserting data, do not pass a value for the name field. MySQL will set a default value for it. The SQL statement you ran has been successfully executed. 
*/ 
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 for a column, MySQL automatically sets a default value based on whether the column is NULL or NOT NULL.

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

If it is a NOT NULL field, MySQL inserts 0 for numeric types and an empty string for string types.

The TIMESTAMP type inserts the current date and time, and the ENUM type inserts the first entry of the enumeration group.

AUTO_INCREMENT Modifier

The AUTO_INCREMENT modifier applies only to INT columns and indicates that MySQL should automatically generate a number for the column.

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

Because it allows developers to create unique identifiers for each record using MySQL.

/* 
The SQL statement you ran has been successfully executed. (Query took 0.0170 seconds) 
*/ 
CREATE TABLE items( 
id int( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , 
label varchar(255) NOT NULL 
); 
/* 
Insert three records, do not specify id, use the default value, and add AUTO_INCREMENT 
The SQL statement you ran has been successfully executed. 
*/ 
insert into items(label) values ​​('xxx'); 
insert into items(label) values ​​('yyy'); 
insert into items(label) values ​​('zzz'); 
/* 
Show all, look at the data, and pay attention to the changes in id*/ 
select * from items; 
/* 
id label 
1 xxx 
2 yyy 
3 zzz 
*/

There can be only one AUTO_INCREMENT column in a MySQL table, and this column must be defined as a key.

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

Summarize

The above is the introduction of the use of default in MySQL by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!