mysql 查询 not null,Mysql查询is null，is not null，！=使用或索引

引言

建表语句

CREATE TABLE `test` (

`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增id',

`account_id` varchar(50) NOT NULL COMMENT '账号',

`account_name` varchar(50) COMMENT '账号名',

`create_time` datetime NOT NULL COMMENT '创建时间',

`update_time` datetime NOT NULL COMMENT '更新时间',

PRIMARY KEY (`id`),

KEY `ak_test_account_id` (`account_id`),

KEY `ak_test_account_name` (`account_name`)

) ENGINE=InnoDB COMMENT='测试表';

插入数据

def main():

with connection.cursor() as cursor:

sql = 'insert into test (account_id, account_name, create_time, update_time) values(%s,%s,now(),now())'

cursor.execute(sql, ("1000", None))

cursor.execute(sql, ("1001", None))

cursor.execute(sql, ("1002", None))

values = []

for i in range(100):

salt = ''.join(random.sample(

string.ascii_letters + string.digits, 8))

one = [salt, "tom"]

values.append(one)

for i in range(1000):

salt = ''.join(random.sample(

string.ascii_letters + string.digits, 8))

one = [salt, "jack"]

values.append(one)

n = cursor.executemany(sql, values)

connection.commit()

查询结果

mysql_q.png

查询结果分析

由图可知,is null, is not null, !=查询, 是可以使用索引的。这和mysql的查询优化有关。不要轻易被网上的一些文章误导。