Scriptorama.nl

Header image showing a keyboard, mouse, laptop and books on design patterns

Metadata SQL

Geplaatst in: SQL op 08.07.06 door Tri Pham

SQL kan een krachtige taal zijn. Metadata SQL is een handige SQL feature die onbekend is bij veel programmeurs. Metadata (van Grieks "meta" en Latijns "data") is data dat informatie over data beschrijft. Waar is dat handig voor? Denk bijvoorbeeld aan O/RM of installatie scripts. In dit artikel zullen we vooral richten op het populaire MySQL en PostgreSQL, al moet het geen probleem zijn om dit te wijzigen voor andere RDMS. Voor MySQL en PostgreSQL zullen we vooral INFORMATION_SCHEMA behandelen.

Show me the data!

We zullen de standaard database 'mysql' gebruiken die elke MySQL database heeft. Laten we simpel beginnen. We willen alle tabellen weten in een database. Hoe doen we dat? INFORMATION_SCHEMA.TABLES zal ons helpen.

PLAIN TEXT
CODE:
  1. mysql> SELECT table_name FROM information_schema.tables
  2. WHERE table_schema = 'mysql';
  3. +---------------------------+
  4. | table_name                |
  5. +---------------------------+
  6. | columns_priv              |
  7. | db                        |
  8. | func                      |
  9. | help_category             |
  10. | help_keyword              |
  11. | help_relation             |
  12. | help_topic                |
  13. | host                      |
  14. | proc                      |
  15. | procs_priv                |
  16. | tables_priv               |
  17. | time_zone                 |
  18. | time_zone_leap_second     |
  19. | time_zone_name            |
  20. | time_zone_transition      |
  21. | time_zone_transition_type |
  22. | user                      |
  23. +---------------------------+
  24. 17 rows in set (0.00 sec)

De volgende stap is de kolommen en haar datatype opvragen. Ook hiervoor gebruiken we INFORMATION_SCHEMA, zo doen we dat:

PLAIN TEXT
CODE:
  1. mysql> SELECT column_name, data_type FROM information_schema.columns
  2. WHERE table_schema = 'mysql' AND table_name = 'func';
  3. +-------------+-----------+
  4. | column_name | data_type |
  5. +-------------+-----------+
  6. | name        | char      |
  7. | ret         | tinyint   |
  8. | dl          | char      |
  9. | type        | enum      |
  10. +-------------+-----------+
  11. 4 rows in set (0.00 sec)

MySQL-only

De vorige SQL queries werken voor MySQL en PosgreSQL, zij hebben allebei INFORMATION_SCHEMA dat eigenlijk views zijn van ISO SQL. MySQL kent ook SQL dat alleen werkt voor haarzelf. Dat is de SHOW syntax. Je kunt beter INFORMATION_SCHEMA gebruiken dan de SHOW syntax, omdat die ook op PostgreSQL werkt (volgens mij ook SQL server, maar dat weet ik niet zeker). We kunnen de vorige SQL queries herschrijven naar de SHOW syntax. Alle tabellen opvragen met met SHOW lijkt bijna op een SELECT statement.

PLAIN TEXT
CODE:
  1. mysql> SHOW tables FROM mysql;
  2. +---------------------------+
  3. | Tables_in_mysql           |
  4. +---------------------------+
  5. | columns_priv              |
  6. | db                        |
  7. | func                      |
  8. | help_category             |
  9. | help_keyword              |
  10. | help_relation             |
  11. | help_topic                |
  12. | host                      |
  13. | proc                      |
  14. | procs_priv                |
  15. | tables_priv               |
  16. | time_zone                 |
  17. | time_zone_leap_second     |
  18. | time_zone_name            |
  19. | time_zone_transition      |
  20. | time_zone_transition_type |
  21. | user                      |
  22. +---------------------------+
  23. 17 rows in set (0.00 sec)

Hetzelfde voor alle kolommen met datatypes, alleen kan je niet met de SHOW syntax alleen vragen naar de kolomnaam en datatype. Je krijgt alles terug, dus ook andere gegevens zoals 'default value' en 'keys'.

Je kunt nog veel meer informatie opvragen zoals foreign keys, alle kolommen met indexes of constraints. Zie de documentatie van je RDMS.

Misschien ook interessant:

Reageer ook!

Let op dat INFORMATION_SCHEMA pas vanaf MySQL 5.0 beschikbaar is voor MySQL, terwijl de SHOW-syntax (en deze is MySQL eigen) al veel langer beschikbaar is.

Om het geheel nog iets completer te maken: SQL Server biedt ook INFORMATION_SCHEMA maar biedt ook z'n informatie aan in de "System Tables" (syscolumns, sysindex, etc.)

Door Mathieu KooimanNo Gravatar op 08.07.06 @ 10:34 am | Permalink

RSS feed for comments on this post.
Leave a comment
Line and paragraph breaks automatic, e-mail address never displayed, HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>