Shulou(Shulou.com)06/01 Report--

NULL and NOT NULL modifiers, DEFAULT modifiers, 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 empty (NULL)

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

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

/ * 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 indicates that the 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 the self-increment column and the TIMESTAMP field.

Inserting NULL values into these fields will cause the next automatically incremented value or the current timestamp to be inserted.

DEFAULT modifier

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

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

/ * 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, and the SQL statement for which MySQL will set the default value 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 no DEFAULT modifier is 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 for it.

If it is a NOT NULL field, MySQL inserts 0 for numeric types and empty strings 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 the INT field, indicating that MySQL should automatically generate a number for the field

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

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

/ * the SQL statement you ran has been run successfully. (query takes 0.0170 seconds) * / CREATE TABLE items (id int (5) NOT NULL AUTO_INCREMENT PRIMARY KEY, label varchar (0.0170) NOT NULL); / * insert three pieces of data without specifying id, using the default value, plus AUTO_INCREMENT the SQL statement you ran has been run successfully. * / insert into items (label) values ('xxx'); insert into items (label) values (' yyy'); insert into items (label) values ('zzz'); / * show it all, take a look at the data and note the changes 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.

Summary

The above is the use of default in MySQL introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to the website!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.