Detailed explanation of NULL value in mysql data table

Use NULL value

NULL value means no value or missing value. Columns that allow NULL values ​​also allow rows to be inserted without giving a value for that column. A column that does not allow NULL values ​​will not accept rows with no value for that column; in other words, the column must have a value when inserting or updating a row.

Each table column is either a NULL column or a NOT NULL column. This state is specified by the table definition when it is created. Please look at the following example:

Input:

create table orders
(
order_num          int               NOT NULL AUTO_INCREMENT,
order_date        datetime       NOT NULL,
cust_id                int               NOT NULL,
PRIMARY KEY (order_num)
)ENGINE = InnoDB;

Analysis: This statement creates the orders table used in this book. orders contains 3 columns, namely order number, order date and customer ID. All 3 columns are required, so each column definition contains the keyword NOT NULL . This will prevent columns with no values ​​from being inserted. If you attempt to insert a column that has no values, an error will be returned and the insert will fail.

The next example will create a table with a mix of NULL and NOT NULL columns:

Input:

create table vendors
(
vend_id                 int                  NOT NULL AUTO_INCREMENT,
vend_name         char(50)            NOT NULL,
vend_address     char(50)           NULL,
vend_city             char(50)           NULL,
vend_state          char(5)             NULL,
vend_zip             char(10)           NULL,
vend_country      char(50)           NULL,
PRIMARY KEY (vend_id )
)ENGINE = InnoDB;

Analysis: This statement creates the vendors table used in this book. The Supplier ID and Supplier Name columns are required and therefore specified as NOT NULL. The remaining five columns all allow NULL values, so NOT NULL is not specified. NULL is the default setting. If NOT NULL is not specified, NULL is assumed to be specified.

The difference between mysql null and empty

Understanding NULL Do not confuse NULL values ​​with empty strings. The NULL value is no value, it is not the empty string. This is allowed in NOT NULL columns if you specify '' (two single quotes with no characters in between). The empty string is a valid value, it is not valueless. NULL values ​​are specified with the keyword NULL instead of the empty string.

The above is the detailed content of Detailed explanation of NULL value in mysql data table. For more information, please follow other related articles on the PHP Chinese website!