Jointure avec INFORMATION_SCHEMA (MySql 5.5)

Version imprimable

Bonjour à tous et bonne année

Ce matin je coince sur un truc qui me semblait simple mais qui me fait en fait galéjer. Je souhaite récupérer les noms des tables d'une base via INFORMATION_SCHEMA puis chaque colonne de chaque table, j'ai donc la requête suivante :
Code:

1 2 3 SELECT t1.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES t1 LEFT JOIN information_schema.columns.COLUMN_NAME ON information_schema.columns.TABLE_NAME = t1.TABLE_NAME WHERE t1.TABLE_SCHEMA = 'aaaaa' AND t1.table_name LIKE 'cccc%'
Je ne comprends pas pourquoi cette requête ne fonctionne pas ?

D'avance merci pour votre aide

Cdlt

Salut Bronon.

Aucune difficulté à faire cela.

Voici le script :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
use INFORMATION_SCHEMA;
 
select t.table_schema,
       t.table_name,
       c.column_name
 
from       TABLES   as t
inner join COLUMNS  as c
on   c.table_schema = t.table_schema
and  c.table_name   = t.table_name
 
where t.table_schema like 'base'
and   t.table_name   like 'test';

Et voici le résultat à l'exécution chez moi :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
--------------
select t.table_schema,
       t.table_name,
       c.column_name
 
from       TABLES   as t
inner join COLUMNS  as c
on   c.table_schema = t.table_schema
and  c.table_name   = t.table_name
 
where t.table_schema like 'base'
and   t.table_name   like 'test'
--------------
 
+--------------+------------+-------------+
| table_schema | table_name | column_name |
+--------------+------------+-------------+
| base         | test       | id          |
| base         | test       | category    |
| base         | test       | name        |
| base         | test       | visiteur    |
+--------------+------------+-------------+
 
Appuyez sur une touche pour continuer...

02/01/2016, 13h55
bronon

Merci pour ta réponse, je regarde ceci +tard dans l'AM et cliquerai :plusser:
Cdlt

Je viens de vérifier ça fonctionne nickel !
Maintenant je souhaiterais avoir les résultats sous la forme suivante :

table1 col1 col2 col3… table2 col1 col2 col3… table3 col1 col2 col3… d'ou ma tentative infructueuse avec LEFT JOIN

Ce qui me faciliterai la mise en forme ultérieure à base de listes déroulantes !
Comment faire ?

Salut Bronon.

Bonne année 2016, bonne santé, mes meilleurs vœux !

Normalement, après l'extraction de tes données via MySql, la mise en forme doit se faire en php.

Mais on peut faire le gros du travail en MySql. Voici le script que j'ai utilisé :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
use information_schema;
 
-- ===========================
-- Extraction avec numéroation
-- ===========================
 
select table_schema,
       @i:=if (@prev=table_name,@i+1,1) as num,
       @prev:=table_name as table_name,
       column_name
from columns,
(SELECT @i:=0) as x,
(SELECT @prev:='') as y
 
where table_schema like 'base'
;
 
-- ========================
-- Mise en forme du tableau
-- ========================
 
select t.table_schema,
       t.table_name,
       case num when 1 then column_name else '' end as col1,
       case num when 2 then column_name else '' end as col2,
       case num when 3 then column_name else '' end as col3,
       case num when 4 then column_name else '' end as col4
 
from       TABLES   as t
inner join ( select table_schema,
                    @i:=if (@prev=table_name,@i+1,1) as num,
                    @prev:=table_name as table_name,
                    column_name
             from columns,
            (SELECT @i:=0) as x,
            (SELECT @prev:='') as y
             where table_schema like 'base'
           ) as c
on   c.table_schema = t.table_schema
and  c.table_name   = t.table_name
 
where t.table_schema like 'base'
;

Et voici le résultat à l'exécution :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
--------------
select table_schema,
       @i:=if (@prev=table_name,@i+1,1) as num,
       @prev:=table_name as table_name,
       column_name
from columns,
(SELECT @i:=0) as x,
(SELECT @prev:='') as y
 
where table_schema like 'base'
--------------
 
+--------------+------+------------+-------------+
| table_schema | num  | table_name | column_name |
+--------------+------+------------+-------------+
| base         |    1 | classe     | id          |
| base         |    2 | classe     | lib         |
| base         |    3 | classe     | chef        |
| base         |    1 | eleve      | id          |
| base         |    2 | eleve      | nom         |
| base         |    3 | eleve      | prenom      |
| base         |    4 | eleve      | classe      |
+--------------+------+------------+-------------+
--------------
select t.table_schema,
       t.table_name,
       case num when 1 then column_name else '' end as col1,
       case num when 2 then column_name else '' end as col2,
       case num when 3 then column_name else '' end as col3,
       case num when 4 then column_name else '' end as col4
 
from       TABLES   as t
inner join ( select table_schema,
                    @i:=if (@prev=table_name,@i+1,1) as num,
                    @prev:=table_name as table_name,
                    column_name
             from columns,
            (SELECT @i:=0) as x,
            (SELECT @prev:='') as y
             where table_schema like 'base'
           ) as c
on   c.table_schema = t.table_schema
and  c.table_name   = t.table_name
 
where t.table_schema like 'base'
--------------
 
+--------------+------------+------+------+--------+--------+
| table_schema | table_name | col1 | col2 | col3   | col4   |
+--------------+------------+------+------+--------+--------+
| base         | classe     |      |      | chef   |        |
| base         | classe     | id   |      |        |        |
| base         | classe     |      | lib  |        |        |
| base         | eleve      |      |      |        | classe |
| base         | eleve      | id   |      |        |        |
| base         | eleve      |      | nom  |        |        |
| base         | eleve      |      |      | prenom |        |
+--------------+------------+------+------+--------+--------+
 
Appuyez sur une touche pour continuer...

Dans le premier résultat, j'ai introduit une nouvelle colonne 'num' afin de numéroter chaque nom des colonnes des deux tables en exemple.
J'utilise ensuite, dans le deuxième résultat une présentation sous forme de tableau, où j'introduis quatre colonnes notés col1, col2, col3 et col4 à partir de la numérotation.

Lorsque j'ai num=1, je mets dans col1 le nom de la colonne 1 sinon je mets vide. Et ainsi de suite pour toutes les colonnes de ta table.

Il faudrait normalement regrouper toutes les lignes du même couple (schéma ; table) en une seule.
Oui, mais voilà, le 'group by' fonctionne sur des colonnes de types numériques, mais pas sur des colonnes alphabétiques. Donc sur ce point, je ne sais pas faire.

Tu constates par toi-même que cela devient vite une usine à gaz, juste pour une question de présentation !

@+

02/01/2016, 16h40
bronon

Merci pour ta réponse qui fonctionne mais qui est, comme tu le dis, une usine à gaz.

Je vais faire la mise en forme via php qui semble plus à même de répondre à mes besoins

Encore mille merci

Cdlt

Salut Bronon.

Il y a une autre solution, à laquelle je viens de penser : utiliser le 'group_concat'.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--------------
select table_schema,
       table_name,
       group_concat(column_name) as columns_name
from columns
where table_schema like 'base'
group by table_schema,
         table_name
--------------
 
+--------------+------------+----------------------+
| table_schema | table_name | columns_name         |
+--------------+------------+----------------------+
| base         | classe     | id,lib,chef          |
| base         | eleve      | id,nom,prenom,classe |
+--------------+------------+----------------------+
 
Appuyez sur une touche pour continuer...

Sauf que ton résultat, se trouve dans une seule colonne et non éclaté sur plusieurs colonnes.

@+

Salut Bronon.

On peut faire usage du 'group by' avec la fonction 'group_concat()' pour regrouper toutes les lignes en une seule.
Je t'ai mis juste l'exécution :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
--------------
select t.table_schema,
       t.table_name,
       group_concat(case c.num when 1 then c.column_name else '' end SEPARATOR '') as 'col1',
       group_concat(case c.num when 2 then c.column_name else '' end SEPARATOR '') as 'col2',
       group_concat(case c.num when 3 then c.column_name else '' end SEPARATOR '') as 'col3',
       group_concat(case c.num when 4 then c.column_name else '' end SEPARATOR '') as 'col4',
       group_concat(case c.num when 5 then c.column_name else '' end SEPARATOR '') as 'col5'
from TABLES   as t
inner join ( select table_schema,
                    @i:=if (@prev=table_name,@i+1,1) as num,
                    @prev:=table_name as table_name,
                    column_name
             from columns,
            (SELECT @i:=0) as x,
            (SELECT @prev:='') as y
             where table_schema like 'base'
           ) as c
on   c.table_schema = t.table_schema
and  c.table_name   = t.table_name
 
where t.table_schema like 'base'
 
group by t.table_schema,
         t.table_name
--------------
 
+--------------+------------+------+------+--------+--------+------+
| table_schema | table_name | col1 | col2 | col3   | col4   | col5 |
+--------------+------------+------+------+--------+--------+------+
| base         | classe     | id   | lib  | chef   |        |      |
| base         | eleve      | id   | nom  | prenom | classe |      |
+--------------+------------+------+------+--------+--------+------+
 
Appuyez sur une touche pour continuer...

N'oublie pas ta promesse :

Citation:

Envoyé par Bronon

Merci pour ta réponse, je regarde ceci +tard dans l'AM et cliquerai :plusser: