MySQL in Null,not null,default,auto_increment detailed

Source: Internet
Author: User
Tags numeric modifier modifiers mysql in

NULL and NOT NULL modifiers:

You can add this null or NOT NULL modifier to each field to specify whether the field can be empty (null), or you must fill in the data (not NULL). mysql specifies null modifiers by default, and if a field is specified as not null,mysql, it is not allowed to insert a null value into the field (Null in this case) because it is "turtle".

The code is as follows Copy Code

Create a friend table, where ID, name, and pass cannot be empty
ID int (3) NOT NULL,
Name varchar (8) NOT NULL,
Pass varchar (%) NOT NULL

Error prompts, id column cannot be empty
#1048-column ' id ' cannot be null
INSERT into Friends
NULL, ' Simaopig ', ' Simaopig '

However, in the self-added and timestamp fields, this rule does not apply. Inserting a null value into these fields will result in the insertion of the next automatically incremented value or the current timestamp.

DEFAULT modifier:

You can set a default value for a field by using the default modifier. When you insert a record, when you forget to pass the value of the field, MySQL automatically sets the default value for the field for you.

The code is as follows Copy Code

Create an IM table and set the Name field to the default value ' QQ '
Create TABLE IM (
ID int (3) NOT NULL,
Name varchar NOT NULL default ' QQ '

Inserts the data, does not pass the Name field the value, the MySQL will set the default value for it
The SQL statement you are running has run successfully.
INSERT into IM (ID, name) VALUES (2, ' MSN ');


SELECT * from im LIMIT 0, 30;

ID Name
3 QQ

If the default modifier is not specified in a field, MySQL automatically sets the default value based on whether the field is null or NOT NULL. If the specified field can be null, MySQL sets the default value to NULL. If the is not null field, MySQL inserts 0 for the numeric type, the string type inserts an empty string, the timestamp type inserts the current date and time, and the enum type inserts the first article of the enumeration group.

Auto_increment modifier:

The Auto_increment modifier applies only to the Int field, indicating that MySQL should automatically generate a number for the field (plus 1 each time on the last generated value). This is useful for primary keys (described later). Because it allows developers to use MySQL to create unique identifiers for each record.

The code is as follows Copy Code

The SQL statement you are running has run successfully. (Query takes 0.0170 seconds)
ID int (5) not NULL auto_increment PRIMARY KEY,
Label varchar (255) Not NULL

Inserts three data, does not specify the ID, takes the default value, plus auto_increment
The SQL statement you are running has run successfully.
INSERT into items (label) VALUES (' xxx ');

INSERT into items (label) values (' yyy ');

INSERT into items (label) values (' zzz ');

Show it all, look at the data, look at the change in ID

select * from items;

ID Label
1 xxx
2 yyy
3 zzz


There can be only one 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 UNIQUE constraints

Command: CREATE table < table name > (< Field name 1> < type 1> [,.. < Field name N> < type n>];

For example, to create a table named MyClass,
Field name numeric type data width is null whether the primary key automatically increases the default value

  code is as follows copy code

ID int 4 No Primary key auto_increment  
name Char 20 no       
Sex int 4 no    ;   0
Degree Double 16 is       

mysql> CREATE table MyClass (
&G T ID int (4) NOT null primary key auto_increment,
> Name char (a) not null,
> Sex int (4) is not null default ' 0 ' br> > Degree double (16,2);

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.