mysql> select id, backup_time from backup_policy limit 2;
+----+-------------+
| id | backup_time |
+----+-------------+
| 21 | Wen,Sun |
| 24 | |
+----+-------------+
2 rows in set (0.00 sec)
mysql> select count(*) from backup_policy where find_in_set('Wen', backup_time);
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from backup_policy where backup_time like '%Wen%';
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
find_in_set函数是查询某个字段(以英文逗号隔开的字符串)中是否包含某个值。
like是比较常用的模糊查询,是把字段整体看成是查询对象。
find_in_set 是精确匹配,字段值以英文 ‘,’ 分隔,而like 是广泛的模糊查询,字段中无需使用分隔符。
两者表示的意义、用法、逻辑是不同的,作用是否相同要看你要查询的结果了。
我想用explain来测试下两者的区别,但是,是失败的
mysql> EXPLAIN select count(*) from backup_policy where find_in_set('Wen', backup_time);
+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | backup_policy | ALL | NULL | NULL | NULL | NULL | 3169 | Using where |
+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select count(*) from backup_policy where backup_time like '%Wen%';
+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | backup_policy | ALL | NULL | NULL | NULL | NULL | 3169 | Using where |
+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
两者的结果是一样的。。。。。。
至于两个sql语句的性能谁高谁低,我感觉这个跟数据库的表结构、索引、数据量等因素有关,我还没有大量数据去测试,这个作业留给大家吧!