MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable(
   userid VARCHAR(6) 
  ,books  VARCHAR(30) 
);
INSERT INTO mytable(userid,books) VALUES
  ('ym0001','dictionary,textbooks,notebooks')
,  ('ym0002','textbooks,dictionary');
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 select 1 union select 2 union select 3 
           union select 4 union select 5 union select 6 
           union select 7 union select 8 union select 9
        ) a
        cross join (
           select 0 union select 1 union select 2 union select 3 
           union select 4 union select 5 union select 6 
           union select 7 union select 8 union select 9
        ) b
        cross join  (
           select 0 union select 1 union select 2 union select 3 
           union select 4 union select 5 union select 6 
           union select 7 union select 8 union 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