【MySQL】 IS NOT NULL 和 != NULL 的区别?

这篇具有很好参考价值的文章主要介绍了【MySQL】 IS NOT NULL 和 != NULL 的区别?。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

背景

最近在开发小伙伴的需求,遇到了一个数据库统计的问题,
is not null 结果正确
!=null 结果就不对,然后就激发了获取真理的想法,那必须的查查
咋回事嘞?
【MySQL】 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如果不指定列的约束,默认就是允许NULL,TIMESTAMP类型的字段除外。所以在非必要情况下,尽量设置列的约束为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)

可以看到,不同的查询条件,对于查询的结果区别还是特别大的。所以在使用过程中需要根据业务场景,选择不同的查询条件。

附录

附录1:MySQL索引失效的常见情况
  1. 最左前缀原则。例如:存在联合索引idx_a_b(a, b),查询条件使用where b = 1则无法使用索引
  2. LIKE 前置模糊查询。例如:col_name like '%test'col_name like '%test%'
  3. 索引列使用函数或存在计算。例如:存在索引idx_col(col),查询条件使用where left(col, 2) = 'te'
  4. 查询条件使用 is not null。设计表结构时尽量设置 not null约束
  5. 字段类型出现隐式转换。例如:存在字段test_colvarchar类型,查询时使用了where test_col = 1,隐式转为了int类型导致索引失效
  6. 条件中有 or 存在可能不会使用索引。例如:查询条件为where a = 'testa' or b = 'testb',存在索引idx_a(a),此时也不会使用索引,除非为b字段也添加索引
  7. 查询结果超过整体结果的25%或三分之一,或者表数据量比较少时,MySQL认为全表扫描代价更小,会导致索引失效
附录2:MySQL关键字列表

R 表示为 MySQL 预留关键字文章来源地址https://www.toymoban.com/news/detail-480488.html

关键字 关键字 关键字
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)

到了这里,关于【MySQL】 IS NOT NULL 和 != NULL 的区别?的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 云服务器SVN仓库搭建(以阿里云为例)

    远程连接阿里云服务器 安装svn(注意需要root权限使用命令sudo su) yum install subversion 安装成功后查看svn版本 svnserve --version  创建版本库的根目录 mkdir /var/svn 创建代码仓库 svnadmin create /var/svn/test    当前生成的目录结构 此处为svn的配置文件 创建用户名和密码 编辑passwd文件 创建

    2024年02月14日
    浏览(41)
  • 计算机网络——计算机网络体系结构

    1.1 概念 一般认为,计算机网络是一个将分散的,具有独立功能的计算机系统,通过通信设备与线路连接起来,由功能完善的软件实现资源共享的信息传递的系统,简而言之,计算机网络就是一些 互联的,自治的计算机系统的集合 1.2 组成 (1)从组成部分:由 硬件,软件,

    2024年02月15日
    浏览(52)
  • appium解决报错:ModuleNotFoundError: No modulenamed ‘selenium.webdriver.common.options‘

    出现这个错误是因为selenium与Appium-Python-Client版本不匹配。 appium: selenium: selenium要4.0版本以上 卸载selenium3.141: 如果安装selenium4.0 ** 会提示如果安装了,appium-python-client 2.7.1,那就要安装selenium~=4.1,这样依赖才匹配。selenium3.141和selenium4.0,4.1相差不是很大,但是selenium不同版本里

    2024年02月11日
    浏览(41)
  • SpringBoot+Prometheus+Grafana 监控面板(项目配置方式【入侵】)

    提示:本文使用SpringBoot 简单样例,介绍基础配置和使用方法 包含内容:Docker、SpringBoot、Maven、 Prometheus、Grafana等 提示:本文包含官网内容介绍,具体更项目的学习,请参照各官网文档,谢谢 官网:https://prometheus.io/ 文档地址:https://prometheus.io/docs/introduction/overview/ 使用领先

    2024年02月16日
    浏览(51)
  • 《动手学深度学习 Pytorch版》 10.6 自注意力和位置编码

    在注意力机制中,每个查询都会关注所有的键-值对并生成一个注意力输出。由于查询、键和值来自同一组输入,因此被称为 自注意力(self-attention),也被称为内部注意力(intra-attention)。本节将使用自注意力进行序列编码,以及使用序列的顺序作为补充信息。 给定一个由

    2024年02月06日
    浏览(44)
  • ubuntu22.04编译安装使用gstreamer指南

    ubuntu发行版22.04,该发行版内置Gstreamer1.20.1,gstreamer源码最新版本为1.20.3,差距不大 下载gstreamer源码 安装git 下载gstreamer 安装meson gstreamer1.60以后(不包含1.60),使用meson+ninja来构建 安装glib gstreamer是基于glib-gobject来实现的 安装libsoup 安装libunwind 安装libdw 安装g-ir-scanner 系统中

    2024年02月05日
    浏览(74)
  • 【Python 中的 plt.hist 函数详解】

    plt.hist 函数用于绘制直方图。直方图是一种用来表示数据分布的图形,它将数据分成若干个区间,然后统计每个区间中数据的数量,最终以柱状图的形式展示出来。 直方图主要用于可视化数据的分布情况。它将数据划分为一系列的区间(也称为箱子或柱子),然后计算每个区

    2024年04月13日
    浏览(44)
  • FDTD算法及其应用

    一、电磁场问题数值计算方法         有许多的数值计算方法用于解决电磁场问题。其中,一些最常用的方法包括:         1.有限元法(Finite Element Method,FEM):这种方法将连续的求解域离散化为有限个小的单元,对每个单元进行数值求解,然后将所有单元的解组

    2024年01月25日
    浏览(35)
  • idea2021配置Git&GitHub&账号登录授权

    下载地址:https://git-scm.com/downloads 安装很简单,这里不多废话。 点击 GitManage Remotes…点\\\"+\\\"号添加别名和仓库地址 转圈圈的同时会弹出浏览器,打开授权界面、 点击授权按钮后,输入账号密码登录,并再次点击授权按钮 最终出现下面提示,则over! over之后再去idea看,发现已

    2023年04月08日
    浏览(43)
  • 【0002】JDK1.7安装和环境变量配置(Windows7操作系统)

    链接:https://pan.baidu.com/s/1ZJTlD-bRw9VCNA5qY-ZU-A  提取码:3d4h 在Windows7操作系统下安装JDK1.7及配置环境变量。其它版本的JDK及操作系统安装步骤,基本上没有太大的差异,所以此文也可以指导安装其它系统中的不同版本的JDK。 先安装JDK再配置环境变量 JDK版本:JDK-7u80-windows-x64版本

    2024年03月25日
    浏览(52)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包