Chrobak Stefan Chrobak Stefan - 1 year ago 68
MySQL Question

MYSQL 'NOT IN' statement with group_concat( table_name ) from information_schema.tables

in my table 'entites' i store some tablenames in column 'tablename' of the used database.
Now i want to get all tablenames of the database who are not in entites.tablename

Here is my statement:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'codeigniter'
AND table_name COLLATE utf8_general_ci NOT IN (
(SELECT concat(tablename) FROM codeigniter.entities)

This works fine, but i'm not shure that this is th best practice:

My database and all tables and columns has 'utf8_unicode_ci' collation,
the information_schema has 'utf8_general_ci' collation.

structur of table 'entites' in database codeigniter :

`tablename` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Answer Source

Your subquery returns a single concatenation string of all* tables in the "codeigniter" schema; unless you expect entities.table to be such a concatenation, you won't get the results you want.

It is the difference between A IN ("B", "C", "D") AND A IN ("B,C,D")

*"ALL" as in all that the setting for the max length of a group_concat call will allow to fit.

My guess is you simply need to not use GROUP_CONCAT.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download