Details about null, not null, default, and auto_increment in mysql

In mysql, null, not null, default, and auto_increment are common content. Here I will detail the usage of null, not null, default, and auto_increment in msyql.

NULL and not null modifiers:

You can add the NULL or not null modifier after each field to specify whether the field can be NULL or whether data must be filled in (not null ). MySQL specifies the field as the NULL Modifier by default. If a field is not null, MySQL does NOT allow NULL values to be inserted into this field (All NULL values mentioned here are NULL ), because this is "ding ".

The Code is as follows:	Copy code
/* Create a friend table, where id, name, and 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 blank #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 fields will insert the next automatically increased value or current timestamp.

DEFAULT modifier:

You can use the DEFAULT modifier to set a DEFAULT value for the field. When you insert a record and forget to pass the value of this field, MySQL automatically sets the default value for this 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 (25) not null default 'qq' ); /* Insert data without passing the value of the name field. MySQL sets the default value for this field. The SQL statement you run has been run successfully. */ 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 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 its default value to NULL. If it is a not null field, MySQL inserts 0 for the numeric type, an empty string for the string type, the timestamp type inserts the current date and time, and the ENUM type inserts the first in the enumeration group.

AUTO_INCREMENT modifier:

The AUTO_INCREMENT modifier is only applicable to the INT field, indicating that MySQL should automatically generate a number for this field (1 is added to the value generated last time ). This is very useful for primary keys (described later. This allows developers to use MySQL to create unique identifiers for each record.

The Code is as follows:

Copy code

/* The SQL statement you run has been run successfully. (The query takes 0.0170 seconds) */ Create table items ( Id int (5) not null AUTO_INCREMENT primary key, Label varchar (255) NOT NULL ); /* Insert three pieces of data, no id specified, use the default value, plus AUTO_INCREMENT The SQL statement you run has been run successfully. */ Insert into items (label) values ('xxx '); Insert into items (label) values ('yyy '); Insert into items (label) values ('zzz '); /* Show all the data and check the id changes. */ Select * from items; /* Id label 1 xxx 2 yyy 3 zzz

*/

The MySQL table can only have one AUTO_INCREMENT field, which must be defined as a key. In addition to field constraints, MySQL also allows table-level constraints, such as primary keys 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 empty or the default value is automatically added to the primary key

The 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 ( > Id int (4) not null primary key auto_increment, > Name char (20) not null, > Sex int (4) not null default '0 ', > Degree double (16, 2 ));