Empty string in not-null column in MySQL?

In PHP, the empty string equals to a NULL value, but in MySQL, the case is the different i.e. empty string is not equal to NULL value. To understand the above syntax, let us create a column with NOT NULL constraint while you can insert an empty string.

Let us create a table. The query to create a table is as follows −

mysql> create table EmptyStringNotNullDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Name varchar(10) not null,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.83 sec)

Now you can insert some records in the table to check the empty string is null or not null. The query to insert record is as follows −

mysql> insert into EmptyStringNotNullDemo(Name) values('John');
Query OK, 1 row affected (0.17 sec)
mysql> insert into EmptyStringNotNullDemo(Name) values('');
Query OK, 1 row affected (0.43 sec)
mysql> insert into EmptyStringNotNullDemo(Name) values('Carol');
Query OK, 1 row affected (0.12 sec)
mysql> insert into EmptyStringNotNullDemo(Name) values('');
Query OK, 1 row affected (0.13 sec)
mysql> insert into EmptyStringNotNullDemo(Name) values('');
Query OK, 1 row affected (0.16 sec)
mysql> insert into EmptyStringNotNullDemo(Name) values('Larry');
Query OK, 1 row affected (0.14 sec)

Display all records from the table using a select statement. The query is as follows −

mysql> select *from EmptyStringNotNullDemo;

The following is the output −

+----+-------+
| Id | Name  |
+----+-------+
|  1 | John  |
|  2 |       |
|  3 | Carol |
|  4 |       |
|  5 |       |
|  6 | Larry |
+----+-------+
6 rows in set (0.00 sec)

Now check the empty string is NULL or NOT NULL in MySQL. If you get a 0 that would mean the empty string is not equal to a NULL otherwise empty string is NULL. The query is as follows −

mysql> select Name, Name IS NULL as EmptyValueIsNotNULL from EmptyStringNotNullDemo;

The following is the output −

+-------+---------------------+
| Name  | EmptyValueIsNotNULL |
+-------+---------------------+
| John  |                   0 |
|       |                   0 |
| Carol |                   0 |
|       |                   0 |
|       |                   0 |
| Larry |                   0 |
+-------+---------------------+
6 rows in set (0.00 sec)

You can achieve with the help of user-defined variable. The syntax is as follows −

SET @anyVariableName=” ”;
UPDATE yourTableName SET yourColumnName= @anyVariableName;

Implement the above syntax for the given table. The query is as follows to declare and define a user variable.

mysql> set @emptyStringValue="";
Query OK, 0 rows affected (0.00 sec)

Update the table with an empty string. The query is as follows −

mysql> update EmptyStringNotNullDemo set Name=@emptyStringValue;
Query OK, 3 rows affected (0.25 sec)
Rows matched: 6 Changed: 3 Warnings: 0

Display all records from the table once again. The query is as follows −

mysql> select *from EmptyStringNotNullDemo;

The following is the output −

+----+------+
| Id | Name |
+----+------+
|  1 |      |
|  2 |      |
|  3 |      |
|  4 |      |
|  5 |      |
|  6 |      |
+----+------+
6 rows in set (0.00 sec)

The column ‘Name’ has been updated.