How to delete empty data in mysql

In mysql, you can use the delete statement with "NULL" to delete empty data. This statement is used to delete data records in the table. "NULL" is used to indicate that the data is empty. The syntax is "delete from table Name where field name = ' ' OR field name IS NULL;".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to delete empty data in mysql

Use the delete command to delete blank rows in MySQL.

The syntax is as follows

delete from yourTableName where yourColumnName=' ' OR yourColumnName IS NULL;

The above syntax will delete blank lines and NULL lines.

To understand this concept, let us create a table. The query to create the table is as follows

mysql> create table deleteRowDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> StudentName varchar(20)
   -> );

Use the insert command to insert some records into the table.

The query is as follows

mysql> insert into deleteRowDemo(StudentName) values('John');
mysql> insert into deleteRowDemo(StudentName) values('');
mysql> insert into deleteRowDemo(StudentName) values('');
mysql> insert into deleteRowDemo(StudentName) values(NULL);
mysql> insert into deleteRowDemo(StudentName) values('Carol');
mysql> insert into deleteRowDemo(StudentName) values('Bob');
mysql> insert into deleteRowDemo(StudentName) values('');
mysql> insert into deleteRowDemo(StudentName) values('David');

Use the select statement to display all records in the table.

The query is as follows

mysql> select *from deleteRowDemo;

The following is the output

+----+-------------+
| Id | StudentName |
+----+-------------+
| 1  | John        |
| 2  |             |
| 3  |             |
| 4  | NULL        |
| 5  | Carol       |
| 6  | Bob         |
| 7  |             |
| 8  | David       |
+----+-------------+
8 rows in set (0.00 sec)

This is the query to delete blank rows as well as NULL

mysql> delete from deleteRowDemo where StudentName='' OR StudentName IS NULL;

Now, let us check the table records again.

The query is as follows

mysql> select *from deleteRowDemo;

The following is the output

+----+-------------+
| Id | StudentName |
+----+-------------+
| 1  | John        |
| 5  | Carol       |
| 6  | Bob         |
| 8  | David       |
+----+-------------+
4 rows in set (0.00 sec)

Recommended learning:mysql video tutorial

The above is the detailed content of How to delete empty data in mysql. For more information, please follow other related articles on the PHP Chinese website!