NULL and Empty String in Mysql

I recently came into contact with MySQL. Yesterday I created a new table to store table structure information:

create table tablist(TABLE_SCHEMA varchar(40),TABLE_NAME varchar(40),COLUMN_NAME varchar(40),COLUMN_TYPE varchar(40),
IS_NULLABLE varchar(10),COLUMN_DEFAULT varchar(40),COLUMN_COMMENT varchar(1000),REMARK varchar(2000));
insert into tablist(TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_TYPE,IS_NULLABLE,COLUMN_DEFAULT,COLUMN_COMMENT)
select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_TYPE,IS_NULLABLE,COLUMN_DEFAULT,COLUMN_COMMENT
from information_schema.`COLUMNS` where TABLE_SCHEMA='leo';

Then query the tablist table:

See which columns have no comments:

select * from tablist where COLUMN_COMMENT is null;

The result I found was Empty set. However, from the above query results and Navicat, we can see that the null value is displayed as the word 'null' in the result set, while the empty string is displayed as empty.

After checking the information, I found that there is a difference between the null value and the empty string in Mysql. It is very strange that after the COLUMN_COMMENT is inserted, the null value turns into an empty string (the reason is unknown).

Use select * from tablist where COLUMN_COMMENT=''; and the query works fine.

NULL columns require additional space in the row to record whether their values ​​are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.

In MySQL's myisam engine, null values ​​take up additional storage space (1 bit), while empty strings take up no space at all. At the same time, null values ​​cannot be stored in B-tree indexes, which will cause serious performance problems when the amount of data is large.

The query methods of the two are also different: null value query uses is null/is not null query, while empty string can be queried using = or !=.

Summarize

The above is what I introduced to you about NULL and Empty String in Mysql. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!