How to cycle with an array in MySQL?

Try to do it without stored routine -

SET @arr = 'hi,hello,good'; -- your array

SELECT COUNT(*) FROM tablex
  WHERE FIND_IN_SET (name, @arr); -- calculate count

Neither of existing answers worked for me, so I ended up implementing my very own (and my very first) MySQL procedure.

PROCEDURE db.loop_through_array()
BEGIN
  DECLARE var varchar(150) DEFAULT 'hi,hello,good';
  DECLARE element varchar(150);

  WHILE var != '' DO
    SET element = SUBSTRING_INDEX(var, ',', 1);      
    SELECT * FROM tablex WHERE name = element;
    
    IF LOCATE(',', var) > 0 THEN
      SET var = SUBSTRING(var, LOCATE(',', var) + 1);
    ELSE
      SET var = '';
    END IF;
  END WHILE;
END

Relational databases don't do arrays, they do scalars, rows and tables. SQL is largely a declarative, rather than procedural, language.

To count entries in a table, use the COUNT aggregate function:

SELECT COUNT(*)
  FROM tablex
  WHERE name IN ("hi", "hello", "good", ...)

If you need to handle a variable number of values to match against in a single statement, you can create a temporary table to hold the values instead of using IN:

SELECT COUNT(*)
  FROM tablex
  JOIN names ON tablex.name=names.name