MySQL datu vārdnīca

MySQL daļu no meta datiem glabā vispiemērotākajā veidā kā tas datubāzēs iespējams – tabulās. MySQL servera datubāzē, kuras nosaukums arī ir mysql, ir bariņš ar tabulām, kurās tiek glabāti lietotāji, tiesības, jaunākās versijās arī procedūras, funkcijas u.c. lietas. Protams, ņemot vērā, ka tās ir MySQL iekšējās tabulas, ir ļoti nevēlami tās ļaut pat skatīties neprivileģētiem lietotājiem. Otra iespēja, kas MySQL jau ir kopš sendienām, ir SHOW komandas.

Ir vēl arī trešā iespēja. Sākot no 5. versijas MySQL ir ieviesuši datu vārdnīcu vairāk vai mazāk atbilstoši ANSI/ISO SQL:2003 standartam, precīzāk tās līmenim F021, kas ietver (atbalsts skatījumiem COLUMNS, TABLES, VIEWS, TABLE_CONSTRAINTS, REFERENTIAL_CONSTRAINTS, un CHECK_CONSTRAINTS). Tas viss tur arī un patiesībā arī mazliet vairāk. Skatamies sīkāk par katru no iespējām atsevišķi.

MySQL datubāze 

MySQL datu bāzu vadības sistēmā galvenā ir MySQL datubāze. Ej nu saproti, kurā brīdī par ko runā ;) Lai labāk runā piemērs:

mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| backup_history            |
| backup_progress           |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
25 rows in set (0.27 sec)

Kā redzams ir vesels bars ar tabulām šai datubāzē. Dažas no tām pieminēšu:
general_log un slow_log satur attiecīgi visus SQL teikumus un SQL teikumus, kas izpildās ilgāk par nokonfigurēto laiku un apstrādā vairāk ierakstus nekā nokonfigurēts. Uzstādīt gana vienkārši pat MySQL darba laikā:

mysql> SET GLOBAL general_log = 1;
Query OK, 0 rows affected (0.05 sec)

mysql> SET GLOBAL log_output='TABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from general_log;
+---------------------+------------------------------------
+-----------+-----------
+--------------+---------------------------+
| event_time          | user_host                         
| thread_id | server_
id | command_type | argument                  |
+---------------------+------------------------------------
+-----------+-----------
+--------------+---------------------------+
| 2009-11-05 23:14:28 | root[root] @ localhost [127.0.0.1]
|         1 |               0
| Query        | select * from general_log |
+---------------------+------------------------------------
+-----------+-----------
+--------------+---------------------------+
1 row in set (0.05 sec)

Protams, nopietnās produkcijas vidēs ar šādām darbībām jābūt ļoti uzmanīgam, jo šāda visu izpildīto SQL teikumu ierakstīšana prasa resursus, kas var pietrūkt pamatfunkcijām, kā arī var saglabāt SQL teikumus (piemēram, CREATE USER) ar visām parolēm atklātā tekstā.

User tabulā, kā to viegli saprast, glabājas MySQL lietotāji. Tā kā MySQL shēma ir salīdzinoši ļoooti vienkārša, tad nedaudz paniekojoties piemēram tos pašus lietotājus var veidot vai dzēst pa taisno šai tabulā, bet nevis izmantojot normālus CREATE USER vai DROP USER teikumus. Protams, ar to aizrauties nevajadzētu, jo tabulas struktūra jaunās versijās var mainīties.

Vēl gribētos pieminēt func tabulu, kurā ir lietotāja definētās funkcijas (faktiski ārējās funkcijas, kurām MySQL tikai nodefinēts interfeiss) un proc tabulu, kurā glbājas saglabātās funkcijas un procedūras.

SHOW komandas

Show komandas jau izsenis ir MySQL specifisks veids, kā iegūt informāciju par datubāzes objektiem. Laika gaitā iespējas, protams, ir augušas un 5.4 versijā jau ir 41 variants šai komandai. Sintakse pārsvarā ir gana vienkārša, piemēram, show tables vai show databases. Rezultāts tiek atspoguļots MySQL pierastajā variantā, tāpāt kā SQL Select teikumiem. Show tables piemērs ir redzams augstāk. Gribētu pieminēt vēl dažus variantus:

mysql> show create table pilsetas;
+----------+--------------------------------------------+
| Table    | Create Table                               |
+----------+--------------------------------------------+
| pilsetas | CREATE TABLE `pilsetas` (
               `id` int(11) NOT NULL,
               `pilsetas_nosaukums` varchar(40) DEFAULT NULL,
               `iedz_skaits` int(11) DEFAULT NULL,
               `apgabals` varchar(40) DEFAULT NULL,
               PRIMARY KEY (`id`)
             ) ENGINE=InnoDB DEFAULT CHARSET=utf8  

Informācijas shēma (information_schema)

Informācijas shēma ir jaunākais, pilnīgākais un rekomendējamais veids, kā iegūt informāciju par MySSQL datu vārdnīcu. Tā sastāv no sistēmas skatījumiem. Lielākie šīs informācijas shēmas lietošanas ieguvumi ir šādi:

  • standarta pieejas veids informācijai – SQL teikumi;
  • dati tikai lasāmi, nav iespējas kaut ko nejauši salauzt;
  • dati atbilstoši lietotāja tiesībām;
  • iespēja veidot patvaļīgus SQL vaicājumus ar tieši nepieciešamo informāciju, nevis kaut ko vairāk vai mazāk.

Nākošajā attēlā var redzēt manis nosacītu datu modeli dažiem biežāk izmantotajām informācijas shēmas skatījumiem (schemata, tables, columns, views, user_privileges, schema_privileges, table_privileges, column_privileges). Datu modelis tika izveidots lietojot datu modelēšanas rīku MySQL Workbench.

MySQL data dictionary

MySQL data dictionary

 Informācijas shēmas skatījumus var sadalīt vairākās nozīmīgās daļās:

  • informācija par MySQL DBVS serveri – datnes (files), tabulu veidi (engines), lietotāju pieslēgumi (processlist), globālās  un sesiju statusi/statistikas (global_status, session_status), globālie un sesiju mainīgie (global_variables, session_variables);
  • informācija par pamatobjektiem – shēmas jeb datubāzes (schemata), tabulas (tables), kolonas(columns), skatījumi (views);
  • informācija par procedurālajiem objektiem – vienreizējiem vai periodiskiem notikumiem (events), procedūrām un funkcijām (routines), trigeriem (triggers);
  • informācija par tiesībām – lietotāju tiesības (user_privileges), tiesības uz shēmām jeb datubāzēm (schema_privileges), tiesības uz tabulām (table_privileges), tiesības uz tabulu kolonām (column_privileges);
  • specifisku dziņu atbilstošās tabulas – (falcon*, innodb*);
  • u.c. skatījumi.

Pilnu pārskaitījumu ar informācijas shēmas skatījumiem, protams, var iegūt MySQL dokumentācijā. Vēl ļoti interesanti ir piestrādājis Roland Bouman, kurš ir izveidojis pilnu MySQL informācijas shēmas diagrammu 5.0 versijai un 5.1 versijai.

4 Responses to MySQL datu vārdnīca

  1. Tvinky says:

    Tajā piemērā kur parādi izvadu komandai: select * from general_log;

    Es ieteiktu izvēlēties sādu pierakstu: select * from general_log\G; Izvadam vajadzētu būt mazliet pārskatāmākam.

  2. rocky says:

    vai ir iespējams dabūt info no šīs shēmas (information_schema) ja lieto oracle db linku ar mysql (generic connectivity)?

    mēģinu jau vairākas dienas, bet rezultāta nav. Cik saprotu tad šī shēma ir kā temporāla tabula un tiek ģenerēta katrai konkrētai sesijai.

    ir kādam kāds padoms vai ieteikums?

  3. Gints Plivna says:

    Man izdodas dabūt arī datus no information_schema, vai Tu raksti priekšā db nosaukumu un tad punktu?
    T.i. man SQLPlusā strādā:
    SQL> select * from “INFORMATION_SCHEMA.TABLES”@localmysql;

    VERSION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH
    ———- ———- ————– ———– ————— ————
    DATA_FREE AUTO_INCREMENT CREATE_TIM UPDATE_TIM CHECK_TIME CHECKSUM
    ———- ————– ———- ———- ———- ———-
    12337 3422259 48 1,4698E+16 48
    48

    BET insteresanti ir tas, ka man tiek nogriezti nost visi varchar lauki gan no info shēmas, gan citām shēmām. Piemēram MYSQLā test shēmā ir tabula:
    mysql> desc test.q;
    +——-+————–+
    | Field | Type |
    +——-+————–+
    | id | int(11) |
    | q | varchar(100) |
    +——-+————–+

    Bet SQLPlusā es dabūju tikai id lauku, kas ir int:
    SQL> select * from test.q@localmysql;

    id
    ———-
    1
    2
    4
    5

    Uz info shēmas skatījumiem, kam ir tikai varchar lauki ir šitāds gļuks:
    SQL> select * from “INFORMATION_SCHEMA.ENGINES”@localmysql;
    select * from “INFORMATION_SCHEMA.ENGINES”@localmysql
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    [Generic Connectivity Using ODBC]Table INFORMATION_SCHEMA.ENGINES has no
    fields. Loading failed
    ORA-02063: preceding 2 lines from LOCALMYSQL

    Tā kā principā info shēma ir ok, tikai tāds sīkums, ka ne visa info nāk ārā🙂

  4. rocky says:

    hmm, interesanti, varbūt man nav kaut kas korekti nokonfigurēts,es lietoju SQL developeri, varbūt vari man parādīt kā tu veidoji saiti oracle?
    ar sintaksi cerams nav problēmu rakstu tā:
    select * from “INFORMATION_SCHEMA.TABLES”@mylink
    saņemu:
    ORA-00942: table or view does not exist
    [Generic Connectivity Using ODBC]
    ORA-02063: preceding 2 lines from MYLINK
    00942. 00000 – “table or view does not exist”
    *Cause:
    *Action:
    Error at Line: 1 Column: 14

    ar citām mysql loģiskajām bāzēm nav problēmu un varu iegūt visus datus, arī no postgres varu dabūt metadatus, gribēju caur oracle izgūt no postgres, oracle un mysql apmēram šāddus datus vienaa vaicajumā

    tabula | datubaaze
    A oracle
    B mysql
    C mysql
    D postgres

    man izdodas ar oracle un postgres viss bet kā lieku klāt mysql tā ir errors, arī atsevišķi nesanāk ar tiem metadatiem.

    otrs nesaprotu – man ODBC datu avots jādefinē informācijas shēmai cits (jo definējot jānorāda datubāze) jeb es viņu varu dabūt caur datubāzi “test” piemēram. Es gan esmu abos veidos izmēginājis bet rezultāts nav, pieļauju ka konfigurācija nekorekta, ja tev ir izdevies.

    linku definēju tādā veidā:
    CREATE DATABASE LINK xxx
    CONNECT TO xxx IDENTIFIED BY xxx
    USING ‘xxx’;

    vari atsūtīt atbildi varbūt uz meilu xxx@xxx – tā varētu būt efektīvāk

    Paldies, gaidīšu atbildi.
    P.S.
    pamēģināšu caur SQL plusu bet šaubos ka nostrādās

Komentēt

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Mainīt )

Twitter picture

You are commenting using your Twitter account. Log Out / Mainīt )

Facebook photo

You are commenting using your Facebook account. Log Out / Mainīt )

Google+ photo

You are commenting using your Google+ account. Log Out / Mainīt )

Connecting to %s

%d bloggers like this: