MySQL中查询一个字符串字段的值不为空到底该怎么写?

原创
07/13 15:02
阅读数 196

如果你看过SQL开发规范,肯定知道尽量不要对字段使用函数

但是,就好像三大范式中我只遵循第一范式而忽略后面两个范式一样,千万不要教条!

如果你要查询表中指定的字符串类型的字段的值不为空时,通常都是column_name != '',也可能有人会写LENGTH(column_name) > 0,或者CHAR_LENGTH(column_name) > 0

先看一下构造数据的SQL:

DROP TABLE IF EXISTS `not_empty_query`;
CREATE TABLE `not_empty_query` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `related_id` varchar(50) NOT NULL DEFAULT '',
    PRIMARY KEY(`id`)
) ENGINE=InnoDB COMMENT '查询字段值不为空';

-- 创建填充数据的存储过程
DROP PROCEDURE IF EXISTS `mysp_fill_table_not_empty_query`;
DELIMITER $$
CREATE PROCEDURE `mysp_fill_table_not_empty_query`(in n int)
BEGIN
    DECLARE i INT UNSIGNED;
    SET i = 0;
    START TRANSACTION;
    WHILE i < n DO
        INSERT INTO `not_empty_query`(`related_id`)VALUES(uuid());
        SET i = i + 1;
    END WHILE;
    COMMIT;
END $$
DELIMITER ;

-- 调用存储过程, 插入100w行数据
CALL `mysp_fill_table_not_empty_query`(1000000);

DROP PROCEDURE IF EXISTS `mysp_fill_table_not_empty_query`;

上面的代码向表not_empty_query中插入了100万行数据。(代码中产生的每行的长度都是一样,这里只是为了方便)

然后来看下3种查询字段值不为空的SQL的explain结果:

mysql> EXPLAIN SELECT SQL_NO_CACHE COUNT(*) FROM `not_empty_query` WHERE `related_id` != '';
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | not_empty_query | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 996442 |    90.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

mysql> EXPLAIN SELECT SQL_NO_CACHE COUNT(*) FROM `not_empty_query` WHERE LENGTH(`related_id`) > 0;
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | not_empty_query | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 996442 |   100.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

mysql> EXPLAIN SELECT SQL_NO_CACHE COUNT(*) FROM `not_empty_query` WHERE CHAR_LENGTH(`related_id`) > 0;
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | not_empty_query | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 996442 |   100.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

唯一的差别在于filtered字段, 只有第一个related_id != ''是 90.00。这个其实是没有多大意义的。(更多信息可参考官方文档MySQL explain中的filtered的意义

根据MySQL存储数据的格式我们知道,每一行数据的所有字段的内容是连续存储在一起的的(text等类型除外),对于int、char(n)这种定长字段,我们从字段定义中就知道它们的长度,对于varchar(n)这种只有最大长度的字符串字段,必须存储其字节长度值。

对于这三种判断字段是否为空的方法,其处理过程应该是:

    1. column_name != '' 取出字段的值, 然后和空字符串进行比较
    1. LENGTH(column_name) > 0 从行数据的元数据中直接取出字节长度值, 然后与0比较
    1. CHAR_LENGTH(column_name) > 0 取出字段的值, 然后根据CHARSET计算字符串长度, 然后与0比较

根据上面的分析, 理论认为其查询速度是: 2 > 1 > 3

我们在本地多次执行的结果, 与理论分析一致:

方式 4次执行时间(s)
1. column_name != '' 0.19, 0.19, 0.19, 0.19
2. LENGTH(column_name) > 0 0.17, 0.17, 0.16, 0.16
3. CHAR_LENGTH(column_name) > 0 0.25, 0.25, 0.25, 0.25

当然, 如果表的字段比较多,或者总长度比较大,或者包含了text等类型的字段,则情况又更为复杂,这里暂不讨论。

展开阅读全文
加载中

作者的其它热门文章

打赏
0
0 收藏
分享
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部