DROP TABLE IF EXISTS `tb_col_null`;
CREATE TABLE `tb_col_null` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
`alias` VARCHAR(5) ,
`bt` TIMESTAMP NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

# 对应auto_increment列传NULL时，会自动生成序列数，而不是NULL
# 对应timestamp NOT NULL列传NULL时，会自动变为默认值，而不是NULL
INSERT INTO `tb_col_null`(`id`, `name`, `alias`, `bt`) VALUES (NULL, 'name', 'alias', NULL);
#
SELECT * FROM tb_col_null ;

# insert data
INSERT INTO `tb_col_null`(`name`, `alias`) VALUES ('name_1', 'a_1'),('name_2', 'a_2'),('name_3', NULL),('name_4', 'a_1'),('name_5', 'a_3');

# SELECT: NULL比任何值都小
SELECT * FROM tb_col_null ORDER BY alias;

# SELECT: 聚合、累计函数会忽略NULL列
SELECT '总行数' AS '',COUNT(*) AS '实际', COUNT(alias) AS '通过null列'
FROM `tb_col_null`
UNION ALL
SELECT 'null列不同值' AS '', COUNT(*) AS '实际', COUNT(DISTINCT alias) AS '通过null列'
FROM (SELECT alias, COUNT(*) AS num FROM `tb_col_null` GROUP BY alias) AS t;

# 用LOAD DATA INFILE读取数据时，如果希望在列中具有NULL值，应在数据文件中使用\N

# MYISAM需要额外存储空间，innodb无影响
每个null列都需要额外1个bit
官方说明

row length = 1
             + (sum of column lengths)
             + (number of NULL columns + delete_flag + 7)/8
             + (number of variable-length columns)

二、NULL列索引

# 增加索引统计信息的复杂度，并且无法优化

# 每行需要额外的1个字节存储

# MYISAM 固定大小变为可变大小，降低索引遍历速度

浅析NULL列