MySql中每行多值属性的计数值

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  interests VARCHAR(100)
);

INSERT INTO users (id, name, interests)
VALUES (1, 'John', 'reading,traveling,sports'),
       (2, 'Jane', 'music,photography'),
       (3, 'Tom', 'cooking,gardening');

SELECT id, name, interests,
       LENGTH(interests) - LENGTH(REPLACE(interests, ',', '')) + 1 AS interests_count
FROM users;

+----+------+-----------------------+-----------------+
| id | name | interests             | interests_count |
+----+------+-----------------------+-----------------+
| 1  | John | reading,traveling,sports | 3               |
| 2  | Jane | music,photography      | 2               |
| 3  | Tom  | cooking,gardening      | 2               |
+----+------+-----------------------+-----------------+