MySQL field constraints: null, notnull, default, auto_increment_MySQL

MySQL field constraints: null, notnull, default, auto_increment bitsCN.com

MySQL field constraints: null, not null, default, auto_increment

In MySQL, each field definition contains additional constraints or modifiers, which can be used to add constraints on the input data.

Today, let's take a look at the MySQL field constraints:

NULL and not null modifiers, DEFAULT modifiers, and AUTO_INCREMENT modifiers.

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 the 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 "required ".

[SQL]/* create a friend table, where id, name, and pass cannot be blank */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.

[SQL]/* 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 will set the default value for it. your running 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 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 the field is not null, MySQL inserts 0 for the value type and NULL string for the string type,

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

AUTO_INCREMENT modifier

AUTO_INCREMENT modifier only applies to INT fields, indicating that MySQL should automatically generate a number for this field

(Add 1 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.

[SQL]/* the SQL statement you run has been successfully run. (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, if no id is specified, the default value is used. with AUTO_INCREMENT added, the SQL statement you run has been successfully run. */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.

BitsCN.com