mysql 查询 以逗号分割的数字组成的字符串 中的最小值

  • 背景介绍
  • 实际需求场景
  • 解决该问题的过程
  • 使用到的函数


背景介绍

电子版的书，以一张张的图片存储到库里，然后每篇文章标注了是从包含了那些页，然后要找到每片文章的第一页图片作为该篇文章的封面。此次要重点说明一下，文章包含那些页是用一个字段 以逗号分割的形式存储为了字符串

具体表结构如下：

CREATE TABLE `book_page` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `book_id` bigint(20) unsigned NOT NULL,
  `title` varchar(1024) NOT NULL DEFAULT '',
  `img_url` varchar(1024) NOT NULL DEFAULT '',
  `page_number` int(10) unsigned NOT NULL COMMENT '页面顺序，from 1',
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=154453 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `article` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '' COMMENT '文章名称',
  `book_id` bigint(20) unsigned NOT NULL,
  `pages` varchar(1024) NOT NULL COMMENT '页码数字列表，逗号分隔，示例："1,2,3"',
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=5444 DEFAULT CHARSET=utf8mb4;

实际需求场景

最开始采用的方法是查出来文章关联的所有的页然后对页码进行排序取最小页面的那一页，这样带来一个问题，查文章列表并且要展示文章封面的时候，效率是极其低下的，一个请求响应时间在2s左右

解决该问题的过程

  1. 首先是增加了索引，为book_page 表增加了（book_id, page_number）索引
  2. 单纯加了索引发现，还是很慢，列表页一次请求50条数据，然后当时采用的懒加载方式，一次请求要进行100多次（还有其他逻辑）sql 查询，后面就把懒加载改为了关联查询。
  3. 关联查询同时也带来一个问题就是怎么从一个逗号分割的字符串中找到最小的那个值。
  4. 查sql 函数找到了find_in_set 函数，竟然可以直接判断某个值是否在给定逗号分割的字符串中，欣喜如狂啊，不过仔细一想似乎有点用不到，经测试发现逗号分割的字符串可以直接在in 里面用，也就是说find_in_set(page_number, pages) = page_number in (pages)
  5. 然后有找了least 函数，可以直接获取多个值中的最小值，又是一阵欣喜，按照上面测试结果逗号分割的字符串可以直接在in 里面用，猜想逗号分割的字符串应该也可以直接在least中使用吧，测试发现打脸了，并不行。
  6. 猜想是不是引号的原因，然后使用replace和concat进行加括号操作，concat('\"',REPLACE(`mct`.`pages`,',', '\",\"'), '\"')结果发现不是加单引号还是双信号都不行，无奈只能放弃这条路。
  7. 重新整理思路获取最小值的函数有least和min,min函数是获取多条数据中的最小值，突然灵光一现，可以采用 min+ find_in_set这个组合获取。最后结果是:
SELECT `a`.*,min(p.`page_number`) as page,p.`img_url` as purl FROM `article` `a` left join book_page as p on  a.`book_id` = p.`book_id` and find_in_set(p.`page_number`, a.pages) WHERE (a.book_id != 3720) group by a.id ORDER BY `id` DESC;

使用到的函数

  • find_in_set: FIND_IN_SET(str,strlist)
    如果字符串 str 在由 N 个子串组成的列表 strlist 中，返回一个 1 到 N 的值。一个字符串列表是由通过字符 “,” 分隔的多个子串组成。如果第一个参数是一个常数字符串，并且第二个参数是一个 SET 列类型，FIND_IN_SET() 函数将被优化为使用位运算！如果 str 在不 strlist 中或者如果 strlist 是一个空串，返回值为 0。如果任何一个参数为 NULL，返回值也是 NULL。如果第一个参数包含一个 “,”，这个函数将完全不能工作。1
  • least: LEAST(X,Y,…)
    有两个或更多个参数，返回最小(最小值)的参数。参数使用下列规则进行比较：
    如果返回值用于一个 INTEGER 语境，或所有的参数是整数值，它们作为整数比较。
    如果返回值用于一个 REAL 语境，或所有的参数均是实数值，它们作为实数被比较。
    如果任何一个参数是字母大小写敏感的，参数作为大小写敏感的字符串进行比较。
  • concat:CONCAT(str1,str2,…)
    将参数连接成字符串返回。如果有任何一个参数为 NULL，返回值也为 NULL。可以有超过 2 个的参数。数字参数将被转换为相等价的字符串形式。
  • replace: REPLACE(str,from_str,to_str)
    这个很简单，就是在字符串 str 中所有出现的字符串 from_str 均被 to_str替换，然后返回这个字符串