select length(''),length(null),length(0),length('0');
从SQL语句结果来看,null并不是0,而是NULL(MySQL 的特殊字段)
1、NULL是MySQL的默认行为,如果字段不声明为NOT NULL,那么它就是NULL的
2、NULL是占用空间的
3、NULL属性非常方便,SQL语句或者代码不需要额外的填充
MySQL难以优化引用为可空列查询,它会使索引、索引统计和值更加复杂。可空列需要更多的空间存储,还需要MySQL内部特殊处理。可空列被索引后,每条记录都要额外的空间,还能导致MyISAM中固定大小的索引列变成可变大小的索引。
NULL列具有以下几大特性:
- NULL的长度不是0;
- 参与查询
- 对索引有影响 唯一键的话可以插入多个NULL的纪录
- 参与运算 与任意类型计算都是NULL
- 参与聚合 select count(column) from table_name,仅计算一个NULL
- 参与排序 正序排列时,NULL会在前面,倒序排列时NULL的纪录会在后面
错误 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''id' INT ( 11 ) NOT NULL AUTO_INCREMENT
不是英文单引号,而是`(波浪线英文标点那个键)
CREATE TABLE 'do_not_use_null' (
'id' INT ( 11 ) NOT NULL AUTO_INCREMENT,
'one' VARCHAR ( 10 ) NOT NULL,
'two' VARCHAR ( 64 ),
'three' VARCHAR ( 64 ),
PRIMARY KEY ( 'id' ),
KEY 'idx_one' ( 'one' ),
KEY 'idx_two' ( 'two' ),
UNIQUE KEY 'idx_three' ( 'three' )
) ENGINE = INNODB DEFAULT CHARSET = utf8;
CREATE TABLE `do_not_use_null` (
`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
`one` VARCHAR ( 10 ) NOT NULL,
`two` VARCHAR ( 64 ),
`three` VARCHAR ( 64 ),
PRIMARY KEY ( `id` ),
KEY `idx_one` ( `one` ),
KEY `idx_two` ( `two` ),
UNIQUE KEY `idx_three` ( `three` )
) ENGINE = INNODB DEFAULT CHARSET = utf8;
初始化一些数据
INSERT INTO do_not_use_null(`id`,`one`,`two`,`three`) VALUES (1,'','a2','a3');
INSERT INTO do_not_use_null(`id`,`one`,`two`,`three`) VALUES (2,'b1',NULL,'b3');
INSERT INTO do_not_use_null(`id`,`one`,`two`,`three`) VALUES (3,'c1','c2',NULL);
INSERT INTO do_not_use_null(`id`,`one`,`two`,`three`) VALUES (4,'d1','d2',NULL);
看一下表结构和数据
DESCRIBE do_not_use_null
SELECT * FROM do_not_use_null
USE test_null;
CREATE TABLE `do_not_use_null` (
`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
`one` VARCHAR ( 10 ) NOT NULL,
`two` VARCHAR ( 64 ),
`three` VARCHAR ( 64 ),
PRIMARY KEY ( `id` ),
KEY `idx_one` ( `one` ),
KEY `idx_two` ( `two` ),
UNIQUE KEY `idx_three` ( `three` )
) ENGINE = INNODB DEFAULT CHARSET = utf8;
DESCRIBE do_not_use_null;
INSERT INTO do_not_use_null(`id`,`one`,`two`,`three`) VALUES (1,'','a2','a3');
INSERT INTO do_not_use_null(`id`,`one`,`two`,`three`) VALUES (2,'b1',NULL,'b3');
INSERT INTO do_not_use_null(`id`,`one`,`two`,`three`) VALUES (3,'c1','c2',NULL);
INSERT INTO do_not_use_null(`id`,`one`,`two`,`three`) VALUES (4,'d1','d2',NULL);
SELECT * FROM do_not_use_null;
DESCRIBE SELECT * FROM do_not_use_null WHERE two != 'abc';
对索引的影响
枚举
枚举字段的值是从“允许值列表”中选择,且这个列表是在创建表结构时就定义好的。
好处:
1、存储数字,数据更加紧凑;
2、允许值提前定义,MySQL可以检查数据的正确性;枚举值和索引以下面的SQL为例来理解
枚举值 | 索引 |
NULL | NULL |
空字符串 | 0 |
male | 1 |
female | 2 |
创建表
CREATE TABLE test_enum(`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
`name` VARCHAR ( 32 ) NOT NULL,
`gender` ENUM('male','female') NOT NULL,
`grade` ENUM('0','1','2') NOT NULL,
PRIMARY KEY ( `id` )
) ENGINE = INNODB DEFAULT CHARSET = utf8;
插入数据
INSERT INTO test_enum(`id`,`name`,`gender`,`grade`) VALUES (1,'Mike','male','1');
INSERT INTO test_enum(`id`,`name`,`gender`,`grade`) VALUES (2,'Mike_2','male','9');
插入第二条报错 1265 - Data truncated for column 'grade' at row 1,原因字符9不在枚举值列中
CREATE TABLE test_enum(`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
`name` VARCHAR ( 32 ) NOT NULL,
`gender` ENUM('male','female') NOT NULL,
`grade` ENUM('0','1','2') NOT NULL,
PRIMARY KEY ( `id` )
) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO test_enum(`id`,`name`,`gender`,`grade`) VALUES (2,'Mike','male','1');
INSERT INTO test_enum(`id`,`name`,`gender`,`grade`) VALUES (2,'Mike_2','male','9');
SELECT * FROm test_enum
SELECT name, gender + 0, grade FROM test_enum
-- 可以插入成功,会认为是索引2 对应枚举值的'1'
INSERT INTO test_enum(`id`,`name`,`gender`,`grade`) VALUES (4,'Mike','male',2);
INSERT INTO test_enum(`id`,`name`,`gender`,`grade`) VALUES (5,'Mike','male','2');
-- 可以插入成功,会认为是索引3 对应枚举值的'2'
INSERT INTO test_enum(`id`,`name`,`gender`,`grade`) VALUES (6,'Mike','male','3');
-- 插入失败,truncate data
INSERT INTO test_enum(`id`,`name`,`gender`,`grade`) VALUES (7,'Mike','male','4');
枚举参与的聚合运算、排序都是跟它对应的索引息息相关的。
换下表名称即可