Mysql's NULL and Empty String, mysqlnull

Mysql's NULL and Empty String, mysqlnull

I recently joined Mysql and created a new table yesterday 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_COMMENTfrom information_schema.`COLUMNS` where TABLE_SCHEMA='leo';

Then query the tablist table:

Check which columns have no comment:

select * from tablist where COLUMN_COMMENT is null;

The result is Empty set. However, both the preceding query results and navicat show that the null value indicates the word 'null' In the result set, while the null String indicates null.

After checking the information, I found that the null value of Mysql is different from that of a null String. It is strange that after COLUMN_COMMENT is inserted, the null value is actually a null string (the reason is unknown ).

Useselect * from tablist where COLUMN_COMMENT='';The query is normal.

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 myisam engine, null occupies an additional storage space (1 bit), while null strings do not occupy space at all. At the same time, the null value cannot be stored in the B-tree index, which may cause serious performance problems when the data volume is large.

The two query methods are different: null Value query uses is null/is not null query, and empty string uses = or! = Query.

Summary

The above section describes the NULL and Empty strings in Mysql. I hope they will help you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!