背景

最近在开发小伙伴的需求，遇到了一个数据库统计的问题，

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_col为varchar类型，查询时使用了where test_col = 1，隐式转为了int类型导致索引失效
      6. 条件中有 or 存在可能不会使用索引。例如：查询条件为where a = 'testa' or b = 'testb'，存在索引idx_a(a)，此时也不会使用索引，除非为b字段也添加索引
      7. 查询结果超过整体结果的25%或三分之一，或者表数据量比较少时，MySQL认为全表扫描代价更小，会导致索引失效

      附录2：MySQL关键字列表

      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）

       
       谷歌搜索引擎免费入口  山东网站开发公司  商务网站制作  品牌营销型网站建设  苏州公司做网站  深圳宝安区房价