【MySQL】 IS NOT NULL 和 != NULL 的区别?
阿里云国内75折 回扣 微信号:monov8 |
阿里云国际,腾讯云国际,低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号:monov8 飞机:@monov6 |
背景
最近在开发小伙伴的需求遇到了一个数据库统计的问题
is not null 结果正确
=null 结果就不对然后就激发了获取真理的想法那必须的查查
咋回事嘞
开整
在用MySQL的过程中你是否存在过如下的几个疑问
- 我的字段类型明明指定的是NOT NULL但是为什么还是可以插入空值呢
- 为什么NOT NULL的效率比NULL更高
- 在查询空字段的记录时是用"select * from table where col <> ‘’ “还是用"select * from table where col is not null”
带着疑问我们来看看NOT NULL和NULL有什么不一样呢要搞清楚这两个的区别。首先我们先要理解"空值"和"NULL"的含义
- 空值是不占用空间的。
- NULL是会占用空间的我们来看看官方对这个NULL的描述。MySQL的官方描述如下
“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.”
怎么理解呢简单举个栗子
假设有一个瓶子空值表示的是瓶子里什么都没有NULL表示的是瓶子里面状态的是空气。可以理解为什么NULL也会占用空间了吧。
下面来通过一个实例例子来测试一下。首先建一个表表引擎使用InnoDB建表语句如下
create table test(
c1 varchar(10) not null,
c2 varchar(10) default null
) engine = InnoDB;
验证插入数据和查询
mysql> insert into test(c1, c2) values(null, 0);
ERROR 1048 (23000): Column 'c1' cannot be null
mysql> insert into test(c1, c2) values('', 0);
Query OK, 1 row affected (0.00 sec)
# null字符串
mysql> insert into test(c1, c2) values('null', 0);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test(c1, c2) values('', null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+------+
| c1 | c2 |
+------+------+
| | 0 |
| null | 0 |
| | NULL |
+------+------+
3 rows in set (0.00 sec)
从上述结果中可以看到NOT NULL的字段是没办法插入NULL值的只能插入空值’'。上面第三个SQL插入的NULL是个字符串格式的NULL对于MyISAM的存储引擎测试的结果和上述结果是一样的。所以第1个疑问也就了解了吧。
对于第2个问题因为NULL值是占了一定空间的所以在MySQL进行字段比较的时候值为NULL的字段也是会参与比较的所以是会对性能有一定的影响。
当字段上包含有索引时由于B树索引是不会存储NULL值的所以在使用这个字段做为查询条件时对性能的影响还是比较大的在平时创建索引的时候应该尽量保证列的值不为NULL。
针对上述的结论有几个针对NULL和NOT NULL的常见优化建议
- MySQL如果不指定列的约束默认就是允许NULLTIMESTAMP类型的字段除外。所以在非必要情况下尽量设置列的约束为NOT NULL。
- 如果列的值为NULL通过这个为NULL的列进行条件查询时MySQL更难做优化因为为NULL的列会让索引的统计和值的比较更加复杂。
- 如果计划在某个列上创建索引那么需要尽量避免这个列中的字段值为NULL。在优化的过程中把NULL改为NOT NULL对性能的提升并不是很明显。所以如果在使用过程中没有问题的话没有必要首先去做NULL到NOT NULL的优化。
来通过实际例子看看最后一个问题。假如需要查询上述test表中c1不为空的所有数据应该使用"<> ‘’"呢还是使用"IS NOT NULL"呢测试结果如下
mysql> select * from test where c1 is not null;
+------+------+
| c1 | c2 |
+------+------+
| | 1 |
| null | 1 |
| | NULL |
+------+------+
3 rows in set (0.00 sec)
mysql> select * from test where c1 <> '';
+------+------+
| c1 | c2 |
+------+------+
| null | 1 |
+------+------+
1 row in set (0.00 sec)
可以看到不同的查询条件对于查询的结果区别还是特别大的。所以在使用过程中需要根据业务场景选择不同的查询条件。
附录
附录1MySQL索引失效的常见情况
- 最左前缀原则。例如存在联合索引
idx_a_b(a, b)
查询条件使用where b = 1
则无法使用索引 - LIKE 前置模糊查询。例如
col_name like '%test'
或col_name like '%test%'
- 索引列使用函数或存在计算。例如存在索引
idx_col(col)
查询条件使用where left(col, 2) = 'te'
- 查询条件使用
is not null
。设计表结构时尽量设置not null
约束 - 字段类型出现隐式转换。例如存在字段
test_col
为varchar
类型查询时使用了where test_col = 1
隐式转为了int
类型导致索引失效 - 条件中有 or 存在可能不会使用索引。例如查询条件为
where a = 'testa' or b = 'testb'
存在索引idx_a(a)
此时也不会使用索引除非为b字段也添加索引 - 查询结果超过整体结果的25%或三分之一或者表数据量比较少时MySQL认为全表扫描代价更小会导致索引失效
附录2MySQL关键字列表
R 表示为 MySQL 预留关键字
关键字 | 关键字 | 关键字 |
---|---|---|
ACCESSIBLER | ACCOUNT | ACTION |
ADDR | AFTER | AGAINST |
AGGREGATE | ALGORITHM | ALLR |
ALTERR | ALWAYS | ANALYSE |
ANALYZER | ANDR | ANY |
ASR | ASCR | ASCII |
ASENSITIVER | AT | AUTOEXTEND_SIZE |
AUTO_INCREMENT | AVG | AVG_ROW_LENGTH |
BACKUP | BEFORER | BEGIN |
BETWEENR | BIGINTR | BINARYR |
BINLOG | BIT | BLOBR |
BLOCK | BOOL | BOOLEAN |
BOTHR | BTREE | BYR |
BYTE | CACHE | CALL ® |
CASCADE ® | CASCADED | CASE ® |
CATALOG_NAME | CHAIN | CHANGE ® |
CHANGED | CHANNEL | CHAR ® |
CHARACTER ® | CHARSET | CHECK ® |
CHECKSUM | CIPHER | CLASS_ORIGIN |
CLIENT | CLOSE | COALESCE |
CODE | COLLATE ® | COLLATION |
COLUMN ® | COLUMNS | COLUMN_FORMAT |
COLUMN_NAME | COMMENT | COMMIT |
COMMITTED | COMPACT | COMPLETION |
COMPRESSED | COMPRESSION | CONCURRENT |
CONDITION ® | CONNECTION | CONSISTENT |
CONSTRAINT ® | CONSTRAINT_CATALOG | CONSTRAINT_NAME |
CONSTRAINT_SCHEMA | CONTAINS | CONTEXT |
CONTINUE ® | CONVERT ® | CPU |
CREATE ® | CROSS ® | CUBE |
CURRENT | CURRENT_DATE ® | CURRENT_TIME ® |
CURRENT_TIMESTAMP ® | CURRENT_USER ® | CURSOR ® |
CURSOR_NAME | DATA | DATABASE ® |
DATABASES ® | DATAFILE | DATE |
DATETIME | DAY | DAY_HOUR ® |
DAY_MICROSECOND ® | DAY_MINUTE ® | DAY_SECOND ® |
DEALLOCATE | DEC ® | DECIMAL ® |
DECLARE ® | DEFAULT ® | DEFAULT_AUTH |
DEFINER | DELAYED ® | DELAY_KEY_WRITE |
DELETE ® | DESC ® | DESCRIBE ® |
DES_KEY_FILE | DETERMINISTIC ® | DIAGNOSTICS |
DIRECTORY | DISABLE | DISCARD |
DISK | DISTINCT ® | DISTINCTROW ® |
DIV ® | DO | DOUBLE ® |
DROP ® | DUAL ® | DUMPFILE |
DUPLICATE | DYNAMIC | EACH ® |
ELSE ® | ELSEIF ® | ENABLE |
ENCLOSED ® | ENCRYPTION | END |
ENDS | ENGINE | ENGINES |
ENUM | ERROR | ERRORS |
ESCAPE | ESCAPED ® | EVENT |
EVENTS | EVERY | EXCHANGE |
EXECUTE | EXISTS ® | EXIT ® |
EXPANSION | EXPIRE | EXPLAIN ® |
EXPORT | EXTENDED | EXTENT_SIZE |
FALSE ® | FAST | FAULTS |
FETCH ® | FIELDS | FILE |
FILE_BLOCK_SIZE | FILTER | FIRST |
FIXED | FLOATR | FLOAT4R |
FLOAT8R | FLUSH | FOLLOWS |
FORR | FORCER | FOREIGNR |
FORMAT | FOUND | FROMR |
FULL | FULLTEXTR | FUNCTION |
GENERAL | GENERATEDR | GEOMETRY |
GEOMETRYCOLLECTION | GETR | GET_FORMAT |
GLOBAL | GRANTR | GRANTS |
GROUPR | GROUP_REPLICATION | HANDLER |
HASH | HAVINGR | HELP |
HIGH_PRIORITYR | HOST | HOSTS |
HOUR | HOUR_MICROSECONDR | HOUR_MINUTER |
HOUR_SECONDR | IDENTIFIED | IFR |
IGNORER | IGNORE_SERVER_IDS | IMPORT |
INR | INDEXR | INDEXES |
INFILER | INITIAL_SIZE | INNERR |
INOUTR | INSENSITIVER | INSERTR |
INSERT_METHOD | INSTALL | INSTANCE |
INTR | INT1R | INT2R |
INT3R | INT4R | INT8R |
INTEGERR | INTERVALR | INTOR |
INVOKER | IO | IO_AFTER_GTIDSR |
IO_BEFORE_GTIDSR | IO_THREAD | IPC |
ISR | ISOLATION | ISSUER |
ITERATER | JOINR | JSON |
KEYR | KEYSR | KEY_BLOCK_SIZE |
KILLR | LANGUAGE | LAST |
LEADINGR | LEAVE ® | LEAVES |
LEFT ® | LESS | LEVEL |
LIKE ® | LIMIT ® | LINEARR |
LINESR | LINESTRING | LIST |
LOADR | LOCAL | LOCALTIMER |
LOCALTIMESTAMPR | LOCKR | LOCKS |
LOGFILE | LOGS | LONGR |
LONGBLOBR | LONGTEXTR | LOOPR |
LOW_PRIORITYR | MASTER | MASTER_AUTO_POSITION |
MASTER_BINDR | MASTER_CONNECT_RETRY | MASTER_DELAY |
MASTER_HEARTBEAT_PERIOD | MASTER_HOST | MASTER_LOG_FILE |
MASTER_LOG_POS | MASTER_PASSWORD | MASTER_PORT |
MASTER_RETRY_COUNT | MASTER_SERVER_ID | MASTER_SSL |
MASTER_SSL_CA | MASTER_SSL_CAPATH | MASTER_SSL_CERT |
MASTER_SSL_CIPHER | MASTER_SSL_CRL | MASTER_SSL_CRLPATH |
MASTER_SSL_KEY | MASTER_SSL_VERIFY_SERVER_CERTR | MASTER_TLS_VERSION |
MASTER_USER | MATCHR | MAXVALUER |
MAX_CONNECTIONS_PER_HOUR | MAX_QUERIES_PER_HOUR | MAX_ROWS |
MAX_SIZE | MAX_STATEMENT_TIME | MAX_UPDATES_PER_HOUR |
MAX_USER_CONNECTIONS | MEDIUM | MEDIUMBLOBR |
MEDIUMINTR | MEDIUMTEXTR | MEMORY |
MERGE | MESSAGE_TEXT | MICROSECOND |
MIDDLEINTR | MIGRATE | MINUTE |
MINUTE_MICROSECONDR | MINUTE_SECONDR | MIN_ROWS |
MODR | MODE | MODIFIESR |
MODIFY | MONTH | MULTILINESTRING |
MULTIPOINT | MULTIPOLYGON | MUTEX |
MYSQL_ERRNO | NAME | NAMES |
NATIONAL | NATURALR | NCHAR |
NDB | NDBCLUSTER | NEVER |
NEW | NEXT | NO |
NODEGROUP | NONBLOCKING | NONE |
NOTR | NO_WAIT | NO_WRITE_TO_BINLOGR |
NULLR | NUMBER | NUMERICR |
NVARCHAR | OFFSET | OLD_PASSWORD |
ONR | ONE | ONLY |
OPEN | OPTIMIZER | OPTIMIZER_COSTSR |
OPTIONR | OPTIONALLYR | OPTIONS |
ORR | ORDERR | OUTR |
OUTERR | OUTFILER | OWNER |
PACK_KEYS | PAGE | PARSER |
PARSE_GCOL_EXPR | PARTIAL | PARTITIONR |
PARTITIONING | PARTITIONS | PASSWORD |
PHASE | PLUGIN | PLUGINS |
PLUGIN_DIR | POINT | POLYGON |
PORT | PRECEDES | PRECISIONR |
PREPARE | PRESERVE | PREV |
PRIMARYR | PRIVILEGES | PROCEDURER |
PROCESSLIST | PROFILE | PROFILES |
PROXY | PURGER | QUARTER |
QUERY | QUICK | RANGER |
READR | READSR | READ_ONLY |
READ_WRITER | REALR | REBUILD |
RECOVER | REDOFILE | REDO_BUFFER_SIZE |
REDUNDANT | REFERENCESR | REGEXPR |
RELAY | RELAYLOG | RELAY_LOG_FILE |
RELAY_LOG_POS | RELAY_THREAD | RELEASER |
RELOAD | REMOVE | RENAMER |
REORGANIZE | REPAIR | REPEATR |
REPEATABLE | REPLACER | REPLICATE_DO_DB |
REPLICATE_DO_TABLE | REPLICATE_IGNORE_DB | REPLICATE_IGNORE_TABLE |
REPLICATE_REWRITE_DB | REPLICATE_WILD_DO_TABLE | REPLICATE_WILD_IGNORE_TABLE |
REPLICATION | REQUIRER | RESET |
RESIGNALR | RESTORE | RESTRICTR |
RESUME | RETURNR | RETURNED_SQLSTATE |
RETURNS | REVERSE | REVOKER |
RIGHTR | RLIKER | ROLLBACK |
ROLLUP | ROTATE | ROUTINE |
ROW | ROWS | ROW_COUNT |
ROW_FORMAT | RTREE | SAVEPOINT |
SCHEDULE | SCHEMAR | SCHEMASR |
SCHEMA_NAME | SECOND | SECOND_MICROSECONDR |
SECURITY | SELECTR | SENSITIVER |
SEPARATORR | SERIAL | SERIALIZABLE |
SERVER | SESSION | SETR |
SHARE | SHOWR | SHUTDOWN |
SIGNALR | SIGNED | SIMPLE |
SLAVE | SLOW | SMALLINTR |
SNAPSHOT | SOCKET | SOME |
SONAME | SOUNDS | SOURCE |
SPATIAL ® | SPECIFIC ® | SQL ® |
SQLEXCEPTION ® | SQLSTATE ® | SQLWARNING ® |
SQL_AFTER_GTIDS | SQL_AFTER_MTS_GAPS | SQL_BEFORE_GTIDS |
SQL_BIG_RESULTR | SQL_BUFFER_RESULT | SQL_CACHE |
SQL_CALC_FOUND_ROWSR | SQL_NO_CACHE | SQL_SMALL_RESULTR |
SQL_THREAD | SQL_TSI_DAY | SQL_TSI_HOUR |
SQL_TSI_MINUTE | SQL_TSI_MONTH | SQL_TSI_QUARTER |
SQL_TSI_SECOND | SQL_TSI_WEEK | SQL_TSI_YEAR |
SSLR | STACKED | START |
STARTINGR | STARTS | STATS_AUTO_RECALC |
STATS_PERSISTENT | STATS_SAMPLE_PAGES | STATUS |
STOP | STORAGE | STOREDR |
STRAIGHT_JOINR | STRING | SUBCLASS_ORIGIN |
SUBJECT | SUBPARTITION | SUBPARTITIONS |
SUPER | SUSPEND | SWAPS |
SWITCHES | TABLER | TABLES |
TABLESPACE | TABLE_CHECKSUM | TABLE_NAME |
TEMPORARY | TEMPTABLE | TERMINATEDR |
TEXT | THAN | THENR |
TIME | TIMESTAMP | TIMESTAMPADD |
TIMESTAMPDIFF | TINYBLOBR | TINYINTR |
TINYTEXTR | TOR | TRAILINGR |
TRANSACTION | TRIGGER ® | TRIGGERS |
TRUE ® | TRUNCATE | TYPE |
TYPES | UNCOMMITTED | UNDEFINED |
UNDOR | UNDOFILE | UNDO_BUFFER_SIZE |
UNICODE | UNINSTALL | UNIONR |
UNIQUER | UNKNOWN | UNLOCKR |
UNSIGNEDR | UNTIL | UPDATER |
UPGRADE | USAGER | USER |
USER | USER_RESOURCES | USE_FRM |
USINGR | UTC_DATER | UTC_TIMER |
UTC_TIMESTAMPR | VALIDATION | VALUE |
VALUESR | VARBINARYR | VARCHARR |
VARCHARACTERR | VARIABLES | VARYINGR |
VIEW | VIRTUALR | WAIT |
WARNINGS | WEEK | WEIGHT_STRING |
WHENR | WHERER | WHILER |
WITHR | WITHOUT | WORK |
WRAPPER | WRITER | X509 |
XA | XID | XML |
XORR | YEAR | YEAR_MONTHR |
ZEROFILLR |
阿里云国内75折 回扣 微信号:monov8 |
阿里云国际,腾讯云国际,低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号:monov8 飞机:@monov6 |