MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t(raw varchar(100));
insert into t (raw) values ('this is a test');
select concat('["', replace(raw, ' ', '","'), '"]') from t;

concat('["', replace(raw, ' ', '","'), '"]')

["this","is","a","test"]

select concat('["', replace(raw, ' ', '","'), '"]') into @delimited from t;

select * 
from JSON_TABLE(
  @delimited,
  "$[*]"
  COLUMNS(Value varchar(50) PATH "$")
) d;

Value

this

is

a

test

insert into t (raw) values ('this is also a test'), ('and you can test it');

Records: 2  Duplicates: 0  Warnings: 0

select concat('["', replace(group_concat(raw SEPARATOR '","'), ' ', '","'), '"]') from t;

concat('["', replace(group_concat(raw SEPARATOR '","'), ' ', '","'), '"]')

["this","is","a","test","this","is","also","a","test","and","you","can","test","it"]

select concat('["', replace(group_concat(raw SEPARATOR '","'), ' ', '","'), '"]') into @delimited from t;

SELECT Value, count(*) as vol
FROM JSON_TABLE(
  @delimited,
  "$[*]"
  COLUMNS(Value varchar(50) PATH "$")
) d
GROUP BY Value ORDER BY count(*) DESC;

Value
vol

test
3

this
2

is
2

a
2

also
1

and
1

you
1

can
1

it
1