以下方法生成1000个整数的结果，然后使用这些整数（n）在逗号分隔的字符串中定位段，并为每个段创建一个新行，以便派生表如下所示：

userid | book      
:----- | :---------
ym0001 | dictionary
ym0002 | textbooks 
ym0001 | textbooks 
ym0002 | dictionary
ym0001 | notebooks

一旦这一点存在，就只需按书本进行分组，即可得出计数。

select
    book, count(*) Counts
from (
    select
           t.userid
         , SUBSTRING_INDEX(SUBSTRING_INDEX(t.books, ',', numbers.n), ',', -1) book
    from (
        select @rownum:=@rownum+1 AS n
        from
        (
           select 0 union all select 1 union all select 2 union all select 3 
           union all select 4 union all select 5 union all select 6 
           union all select 7 union all select 8 union all select 9
        ) a
        cross join (
           select 0 union all select 1 union all select 2 union all select 3 
           union all select 4 union all select 5 union all select 6 
           union all select 7 union all select 8 union all select 9
        ) b
        cross join  (
           select 0 union all select 1 union all select 2 union all select 3 
           union all select 4 union all select 5 union all select 6 
           union all select 7 union all select 8 union all select 9
        ) c
        cross join (select @rownum:=0) r
        ) numbers
    inner join mytable t
      on CHAR_LENGTH(t.books)
        -CHAR_LENGTH(REPLACE(t.books, ',', '')) >= numbers.n-1
    ) d
group by
    book
order by
    book

book       | Counts
:--------- | -----:
dictionary |      2
notebooks  |      1
textbooks  |      2

如果你已经有了一个数字表，那就用它来代替。
b和c的交叉连接动态地生成1000行，如果需要更多的交叉连接，请添加类似于c的其他交叉连接。i、 数字的数量应该超过逗号分隔数据的最大长度
db<>在这里摆弄