How to insert NULL keyword as value in character type column of MySQL table with NOT NULL constraint?

It is possible to insert the NULL keyword as a value into a character type column with a NOT NULL constraint, because NULL itself is a value. The following example will demonstrate it -

Example

Suppose we have a table test2 which contains character type column "Name" along with NOT NULL constraint. It can be checked from the DESCRIBE statement as follows -

mysql> Describe test2\G
*************************** 1. row ***************************
  Field: id
   Type: int(11)
   Null: NO
    Key:
Default: NULL
  Extra:
*************************** 2. row ***************************
  Field: NAME
   Type: varchar(20)
   Null: NO
    Key:
Default: NULL
  Extra:
2 rows in set (0.03 sec) 

Now, with the help of the following query, we can insert NULL as value in the "Name" column.

mysql> Insert into test2 values(2, 'NULL');
Query OK, 1 row affected (0.06 sec)

mysql> select * from test2;
+----+--------+
| id | NAME   |
+----+--------+
|  1 | Gaurav |
|  2 | NULL   |
+----+--------+
2 rows in set (0.00 sec) 

To understand the difference between "NULL" and "NULL as value" we can run the following two queries -

mysql> delete from test2 where name IS NULL;
Query OK, 0 rows affected (0.00 sec) 

The above query affects 0 rows, which means there are no NULLs OK. You can check from the SELECT query that no rows were deleted.

mysql> select * from test2;
+----+--------+
| id | NAME   |
+----+--------+
|  1 | Gaurav |
|  2 | NULL   |
+----+--------+
2 rows in set (0.00 sec)

mysql> delete from test2 where name = 'NULL';
Query OK, 1 row affected (0.09 sec) 

The above query affects 1 row, which means there is a row with a value of NULL. You can check from the SELECT query that rows with a NULL value in the "NAME" column have been deleted.

mysql> select * from test2;
+----+--------+
| id | NAME   |
+----+--------+
|  1 | Gaurav |
+----+--------+
1 row in set (0.00 sec)

The above is the detailed content of How to insert NULL keyword as value in character type column of MySQL table with NOT NULL constraint?. For more information, please follow other related articles on the PHP Chinese website!