MySQL null and empty String

Recently new contacts MySQL, yesterday a new table is used to store table structure information:

CREATE TABLE Tablist (table_schema varchar (+), table_name varchar (+), column_name varchar (+), Column_type varchar (40) , is_nullable varchar (TEN), Column_default varchar (+), column_comment varchar (+), REMARK varchar); 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 what columns are not comment so:

SELECT * from tablist where column_comment is null;

The result of the search is actually empty set. However, it can be seen from the above query results and navicat that the null value shows the word ' null ' in the result set, and the empty string appears empty.

After checking the data, I found that the null value of MySQL and the empty string are different, it is strange here column_comment after the INSERT, the null value becomes an empty string (reason is not clear).

Use SELECT * from Tablist where column_comment= "; the query is normal.

NULL columns require additional space in the row to record whether their values is NULL. for MyISAM tables, each of the NULL column takes one bit extra, rounded up to the nearest byte.in MySQL's MyISAM engine, null values occupy extra storage space (1bit), and empty strings do not occupy space at all. At the same time, null values cannot be stored in the B-tree index, which can cause more serious performance problems when the data is large. the query is also different: The null value query uses the IS null/is not NULL query, and the empty string uses = or! = to query.

MySQL null and empty String