背景 最近在开发小伙伴的需求,遇到了一个数据库统计的问题, is not null 结果正确 !=null 结果就不对,然后就激发了获取真理的想法,那必须的查查 咋回事嘞? 开整 在用Mysql的过
最近在开发小伙伴的需求,遇到了一个数据库统计的问题,
is not null 结果正确
!=null 结果就不对,然后就激发了获取真理的想法,那必须的查查
咋回事嘞?
在用Mysql的过程中,你是否存在过如下的几个疑问?
带着疑问,我们来看看NOT NULL和NULL有什么不一样呢?要搞清楚这两个的区别。首先,我们先要理解"空值"和"NULL"的含义:
“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 nullmysql> 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的常见优化建议:
来通过实际例子看看最后一个问题。假如需要查询上述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)
可以看到,不同的查询条件,对于查询的结果区别还是特别大的。所以在使用过程中需要根据业务场景,选择不同的查询条件。
idx_a_b(a, b)
,查询条件使用where b = 1
则无法使用索引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
类型导致索引失效where a = 'testa' or b = 'testb'
,存在索引idx_a(a)
,此时也不会使用索引,除非为b字段也添加索引R 表示为 MySQL 预留关键字
关键字 | 关键字 | 关键字 |
---|---|---|
ACCESSIBLE(R) | ACCOUNT | ACTION |
ADD(R) | AFTER | AGaiNST |
AGGREGATE | ALGoRITHM | ALL(R) |
ALTER(R) | ALWAYS | ANALYSE |
ANALYZE(R) | AND(R) | ANY |
AS(R) | ASC(R) | ASCII |
ASENSITIVE(R) | AT | AUTOEXTEND_SIZE |
AUTO_INCREMENT | AVG | AVG_ROW_LENGTH |
BACKUP | BEFORE(R) | BEGIN |
BETWEEN(R) | BIGINT(R) | BINARY(R) |
BINLOG | BIT | BLOB(R) |
BLOCK | BOOL | BOOLEAN |
BOTH(R) | BTREE | BY(R) |
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 | FLOAT(R) | FLOAT4(R) |
FLOAT8(R) | FLUSH | FOLLOWS |
FOR(R) | FORCE(R) | FOREIGN(R) |
FORMAT | FOUND | FROM(R) |
FULL | FULLTEXT(R) | FUNCTION |
GENERAL | GENERATED(R) | GEOMETRY |
GEOMETRYCOLLECTION | GET(R) | GET_FORMAT |
GLOBAL | GRANT(R) | GRANTS |
GROUP(R) | GROUP_REPLICATION | HANDLER |
HASH | HAVING(R) | HELP |
HIGH_PRIORITY(R) | HOST | HOSTS |
HOUR | HOUR_MICROSECOND(R) | HOUR_MINUTE(R) |
HOUR_SECOND(R) | IDENTIFIED | IF(R) |
IGNORE(R) | IGNORE_SERVER_IDS | IMPORT |
IN(R) | INDEX(R) | INDEXES |
INFILE(R) | INITIAL_SIZE | INNER(R) |
INOUT(R) | INSENSITIVE(R) | INSERT(R) |
INSERT_METHOD | INSTALL | INSTANCE |
INT(R) | INT1(R) | INT2(R) |
INT3(R) | INT4(R) | INT8(R) |
INTEGER(R) | INTERVAL(R) | INTO(R) |
INVOKER | IO | IO_AFTER_GTIDS(R) |
IO_BEFORE_GTIDS(R) | IO_THREAD | IPC |
IS(R) | ISOLATION | ISSUER |
ITERATE(R) | JOIN(R) | JSON |
KEY(R) | KEYS(R) | KEY_BLOCK_SIZE |
KILL(R) | LANGUAGE | LAST |
LEADING(R) | LEAVE ® | LEAVES |
LEFT ® | LESS | LEVEL |
LIKE ® | LIMIT ® | LINEAR(R) |
LINES(R) | LINESTRING | LIST |
LOAD(R) | LOCAL | LOCALTIME(R) |
LOCALTIMESTAMP(R) | LOCK(R) | LOCKS |
LOGFILE | LOGS | LONG(R) |
LONGBLOB(R) | LONGTEXT(R) | LOOP(R) |
LOW_PRIORITY(R) | MASTER | MASTER_AUTO_POSITION |
MASTER_BIND(R) | 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_CERT(R) | MASTER_TLS_VERSION |
MASTER_USER | MATCH(R) | MAXVALUE(R) |
MAX_CONNECTIONS_PER_HOUR | MAX_QUERIES_PER_HOUR | MAX_ROWS |
MAX_SIZE | MAX_STATEMENT_TIME | MAX_UPDATES_PER_HOUR |
MAX_USER_CONNECTIONS | MEDIUM | MEDIUMBLOB(R) |
MEDIUMINT(R) | MEDIUMTEXT(R) | MEMORY |
MERGE | MESSAGE_TEXT | MICROSECOND |
MIDDLEINT(R) | MIGRATE | MINUTE |
MINUTE_MICROSECOND(R) | MINUTE_SECOND(R) | MIN_ROWS |
MOD(R) | MODE | MODIFIES(R) |
MODIFY | MONTH | MULTILINESTRING |
MULTIPOINT | MULTIPOLYGON | MUTEX |
MYSQL_ERRNO | NAME | NAMES |
NATIONAL | NATURAL(R) | NCHAR |
NDB | NDBCLUSTER | NEVER |
NEW | NEXT | NO |
nodeGROUP | NONBLOCKING | NONE |
NOT(R) | NO_WAIT | NO_WRITE_TO_BINLOG(R) |
NULL(R) | NUMBER | NUMERIC(R) |
NVARCHAR | OFFSET | OLD_PASSWORD |
ON(R) | ONE | ONLY |
OPEN | OPTIMIZE(R) | OPTIMIZER_COSTS(R) |
OPTION(R) | OPTIONALLY(R) | OPTIONS |
OR(R) | ORDER(R) | OUT(R) |
OUTER(R) | OUTFILE(R) | OWNER |
PACK_KEYS | PAGE | PARSER |
PARSE_GCOL_EXPR | PARTIAL | PARTITION(R) |
PARTITIONING | PARTITIONS | PASSWORD |
PHASE | PLUGIN | PLUGINS |
PLUGIN_DIR | POINT | POLYGON |
PORT | PRECEDES | PRECISION(R) |
PREPARE | PRESERVE | PREV |
PRIMARY(R) | PRIVILEGES | PROCEDURE(R) |
PROCESSLIST | PROFILE | PROFILES |
PROXY | PURGE(R) | QUARTER |
QUERY | QUICK | RANGE(R) |
READ(R) | READS(R) | READ_ONLY |
READ_WRITE(R) | REAL(R) | REBUILD |
RECOVER | REDOFILE | REDO_BUFFER_SIZE |
REDUNDANT | REFERENCES(R) | REGEXP(R) |
RELAY | RELAYLOG | RELAY_LOG_FILE |
RELAY_LOG_POS | RELAY_THREAD | RELEASE(R) |
RELOAD | REMOVE | RENAME(R) |
REORGANIZE | REPAIR | REPEAT(R) |
REPEATABLE | REPLACE(R) | REPLICATE_DO_DB |
REPLICATE_DO_TABLE | REPLICATE_IGNORE_DB | REPLICATE_IGNORE_TABLE |
REPLICATE_REWRITE_DB | REPLICATE_WILD_DO_TABLE | REPLICATE_WILD_IGNORE_TABLE |
REPLICATION | REQUIRE(R) | RESET |
RESIGNAL(R) | RESTORE | RESTRICT(R) |
RESUME | RETURN(R) | RETURNED_SQLSTATE |
RETURNS | REVERSE | REVOKE(R) |
RIGHT(R) | RLIKE(R) | ROLLBACK |
ROLLUP | ROTATE | ROUTINE |
ROW | ROWS | ROW_COUNT |
ROW_FORMAT | RTREE | SAVEPOINT |
SCHEDULE | SCHEMA(R) | SCHEMAS(R) |
SCHEMA_NAME | SECOND | SECOND_MICROSECOND(R) |
SECURITY | SELECT(R) | SENSITIVE(R) |
SEPARATOR(R) | SERIAL | SERIALIZABLE |
SERVER | SESSION | SET(R) |
SHARE | SHOW(R) | SHUTDOWN |
SIGNAL(R) | SIGNED | SIMPLE |
SLAVE | SLOW | SMALLINT(R) |
SNAPSHOT | Socket | SOME |
SONAME | SOUNDS | SOURCE |
SPATIAL ® | SPECIFIC ® | SQL ® |
SQLEXCEPTION ® | SQLSTATE ® | SQLWARNING ® |
SQL_AFTER_GTIDS | SQL_AFTER_MTS_GAPS | SQL_BEFORE_GTIDS |
SQL_BIG_RESULT(R) | SQL_BUFFER_RESULT | SQL_CACHE |
SQL_CALC_FOUND_ROWS(R) | SQL_NO_CACHE | SQL_SMALL_RESULT(R) |
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 |
SSL(R) | STACKED | START |
STARTING(R) | STARTS | STATS_AUTO_RECALC |
STATS_PERSISTENT | STATS_SAMPLE_PAGES | STATUS |
STOP | STORAGE | STORED(R) |
STRAIGHT_JOIN(R) | STRING | SUBCLASS_ORIGIN |
SUBJECT | SUBPARTITION | SUBPARTITIONS |
SUPER | SUSPEND | SWAPS |
SWITCHES | TABLE(R) | TABLES |
TABLESPACE | TABLE_CHECKSUM | TABLE_NAME |
TEMPORARY | TEMPTABLE | TERMINATED(R) |
TEXT | THAN | THEN(R) |
TIME | TIMESTAMP | TIMESTAMPADD |
TIMESTAMPDIFF | TINYBLOB(R) | TINYINT(R) |
TINYTEXT(R) | TO(R) | TRAILING(R) |
TRANSACTION | TRIGGER ® | TRIGGERS |
TRUE ® | TRUNCATE | TYPE |
TYPES | UNCOMMITTED | UNDEFINED |
UNDO(R) | UNDOFILE | UNDO_BUFFER_SIZE |
UNICODE | UNINSTALL | UNION(R) |
UNIQUE(R) | UNKNOWN | UNLOCK(R) |
UNSIGNED(R) | UNTIL | UPDATE(R) |
UPGRADE | USAGE(R) | USE(R) |
USER | USER_RESOURCES | USE_FRM |
USING(R) | UTC_DATE(R) | UTC_TIME(R) |
UTC_TIMESTAMP(R) | VALIDATION | VALUE |
VALUES(R) | VARBINARY(R) | VARCHAR(R) |
VARCHARACTER(R) | VARIABLES | VARYING(R) |
VIEW | VIRTUAL(R) | WAIT |
WARNINGS | WEEK | WEIGHT_STRING |
WHEN(R) | WHERE(R) | WHILE(R) |
WITH(R) | WITHOUT | WORK |
WRAPPER | WRITE(R) | X509 |
XA | XID | XML |
XOR(R) | YEAR | YEAR_MONTH(R) |
ZEROFILL(R) |
来源地址:https://blog.csdn.net/weixin_40379712/article/details/131172725
--结束END--
本文标题: 【MySQL】 IS NOT NULL 和 != NULL 的区别?
本文链接: https://www.lsjlt.com/news/403727.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0