Utilisateur:Gribeco/MySQL

Un article de Wikipédia, l'encyclopédie libre.

Sommaire

[modifier] Connexion et syntaxe

Connexion depuis toolserver : mysql frwiki_p

mysql> select cur_title from cur where cur_title like 'Terre-Neuve%';
+-------------------------+
| cur_title               |
+-------------------------+
| Terre-Neuve             |
| Terre-Neuve-et-Labrador |
| Terre-Neuve-et-Labrador |
| Terre-Neuve-et-Labrador |
| Terre-Neuve_et_Labrador |
+-------------------------+
5 rows in set (0.00 sec)

[modifier] Commandes

show tables; Liste des tables
describe cur; Champs d'une table

[modifier] Tables et champs

Description des tables et des champs

[modifier] Liste des tables

mysql> show tables;
+--------------------+
| Tables_in_frwiki_p |
+--------------------+
| archive            |
| categorylinks      |
| cur                |
| externallinks      |
| hitcounter         |
| image              |
| imagelinks         |
| interwiki          |
| langlinks          |
| logging            |
| math               |
| oldimage           |
| page               |
| pagelinks          |
| recentchanges      |
| revision           |
| site_stats         |
| templatelinks      |
| text               |
| updates            |
| user               |
| user_groups        |
| user_ids           |
+--------------------+
23 rows in set (0.02 sec)

[modifier] Table page

Détails : meta:Page table

mysql> describe page;
+-------------------+---------------------+------+-----+---------+-------+
| Field             | Type                | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+-------+
| page_id           | int(8) unsigned     | NO   |     | 0       |       |
| page_namespace    | int(11)             | NO   |     | 0       |       |
| page_title        | varchar(255)        | NO   |     |         |       |
| page_restrictions | tinyblob            | NO   |     |         |       |
| page_counter      | bigint(20) unsigned | NO   |     | 0       |       |
| page_is_redirect  | tinyint(1) unsigned | NO   |     | 0       |       |
| page_is_new       | tinyint(1) unsigned | NO   |     | 0       |       |
| page_random       | double unsigned     | NO   |     | 0       |       |
| page_touched      | varchar(14)         | NO   |     |         |       |
| page_latest       | int(8) unsigned     | NO   |     | 0       |       |
| page_len          | int(8) unsigned     | NO   |     | 0       |       |
+-------------------+---------------------+------+-----+---------+-------+

[modifier] Table revision

mysql> describe revision;
+----------------+---------------------+------+-----+---------+-------+
| Field          | Type                | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| rev_id         | int(8) unsigned     | NO   |     | 0       |       |
| rev_page       | int(8) unsigned     | NO   |     | 0       |       |
| rev_text_id    | int(8) unsigned     | NO   |     | 0       |       |
| rev_comment    | tinyblob            | NO   |     |         |       |
| rev_user       | int(5) unsigned     | NO   |     | 0       |       |
| rev_user_text  | varchar(255)        | NO   |     |         |       |
| rev_timestamp  | varchar(14)         | NO   |     |         |       |
| rev_minor_edit | tinyint(1) unsigned | NO   |     | 0       |       |
| rev_deleted    | tinyint(1) unsigned | NO   |     | 0       |       |
+----------------+---------------------+------+-----+---------+-------+

mysql> select rev_id, rev_page, rev_user, rev_user_text, rev_timestamp from revision where rev_user_text like 'Gribeco' order by rev_timestamp desc limit 20;
+----------+----------+----------+---------------+----------------+
| rev_id   | rev_page | rev_user | rev_user_text | rev_timestamp  |
+----------+----------+----------+---------------+----------------+
| 11305135 |   803468 |    24358 | Gribeco       | 20061031012724 |
| 11305114 |  1120370 |    24358 | Gribeco       | 20061031012438 |
| 11304966 |   347408 |    24358 | Gribeco       | 20061031010730 |
| 11304957 |  1120353 |    24358 | Gribeco       | 20061031010708 |
| 11304952 |   926922 |    24358 | Gribeco       | 20061031010632 |
| 11304321 |     8568 |    24358 | Gribeco       | 20061031000659 |
| 11304319 |     6162 |    24358 | Gribeco       | 20061031000651 |
| 11304311 |  1120290 |    24358 | Gribeco       | 20061031000621 |
| 11304308 |   681138 |    24358 | Gribeco       | 20061031000609 |
| 11297026 |   413157 |    24358 | Gribeco       | 20061030181413 |
| 11296991 |    73945 |    24358 | Gribeco       | 20061030181251 |
| 11292558 |    30776 |    24358 | Gribeco       | 20061030152646 |
| 11292465 |  1118929 |    24358 | Gribeco       | 20061030152313 |
| 11292449 |  1116566 |    24358 | Gribeco       | 20061030152233 |
| 11292335 |   527899 |    24358 | Gribeco       | 20061030151755 |
| 11292312 |   527899 |    24358 | Gribeco       | 20061030151701 |
| 11292110 |   447862 |    24358 | Gribeco       | 20061030150711 |
| 11281826 |  1116055 |    24358 | Gribeco       | 20061030044556 |
| 11279925 |  1116055 |    24358 | Gribeco       | 20061030003720 |
| 11279732 |  1087813 |    24358 | Gribeco       | 20061030002701 |
+----------+----------+----------+---------------+----------------+

mysql> select count(*) from revision where rev_user_text like '82.240.65.115';
+----------+
| count(*) |
+----------+
|       15 |
+----------+
1 row in set (0.02 sec)

[modifier] Table recentchanges

recentchanges contient les modifications faites dans le dernier mois (sur wp:fr). Par rapport à revision, elle contient le nom de la page.

mysql> describe recentchanges;
+-------------------+---------------------+------+-----+---------+-------+
| Field             | Type                | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+-------+
| rc_id             | int(8)              | NO   |     | 0       |       |
| rc_timestamp      | varchar(14)         | NO   |     |         |       |
| rc_cur_time       | varchar(14)         | NO   |     |         |       |
| rc_user           | int(10) unsigned    | NO   |     | 0       |       |
| rc_user_text      | varchar(255)        | NO   |     |         |       |
| rc_namespace      | int(11)             | NO   |     | 0       |       |
| rc_title          | varchar(255)        | NO   |     |         |       |
| rc_comment        | varchar(255)        | NO   |     |         |       |
| rc_minor          | tinyint(3) unsigned | NO   |     | 0       |       |
| rc_bot            | tinyint(3) unsigned | NO   |     | 0       |       |
| rc_new            | tinyint(3) unsigned | NO   |     | 0       |       |
| rc_cur_id         | int(10) unsigned    | NO   |     | 0       |       |
| rc_this_oldid     | int(10) unsigned    | NO   |     | 0       |       |
| rc_last_oldid     | int(10) unsigned    | NO   |     | 0       |       |
| rc_type           | tinyint(3) unsigned | NO   |     | 0       |       |
| rc_moved_to_ns    | tinyint(3) unsigned | NO   |     | 0       |       |
| rc_moved_to_title | varchar(255)        | NO   |     |         |       |
| rc_patrolled      | tinyint(3) unsigned | NO   |     | 0       |       |
| rc_ip             | char(0)             | NO   |     |         |       |
+-------------------+---------------------+------+-----+---------+-------+

[modifier] Table archive

archive contient les modifications supprimées.

mysql> describe archive;
+---------------+-----------------+------+-----+---------+-------+
| Field         | Type            | Null | Key | Default | Extra |
+---------------+-----------------+------+-----+---------+-------+
| ar_namespace  | int(11)         | NO   |     | 0       |       |
| ar_title      | varchar(255)    | NO   |     |         |       |
| ar_text       | char(0)         | NO   |     |         |       |
| ar_comment    | tinyblob        | NO   |     |         |       |
| ar_user       | int(5) unsigned | NO   |     | 0       |       |
| ar_user_text  | varchar(255)    | NO   |     |         |       |
| ar_timestamp  | varchar(14)     | NO   |     |         |       |
| ar_minor_edit | tinyint(1)      | NO   |     | 0       |       |
| ar_flags      | tinyblob        | NO   |     |         |       |
| ar_rev_id     | bigint(1)       | NO   |     | 0       |       |
| ar_text_id    | bigint(1)       | NO   |     | 0       |       |
+---------------+-----------------+------+-----+---------+-------+

[modifier] Table user

+-------------------+-----------------+------+-----+---------+-------+
| Field             | Type            | Null | Key | Default | Extra |
+-------------------+-----------------+------+-----+---------+-------+
| user_id           | int(5) unsigned | NO   |     | 0       |       |
| user_name         | varchar(255)    | NO   |     |         |       |
| user_registration | varchar(14)     | YES  |     | NULL    |       |
| user_editcount    | int(11)         | YES  |     | NULL    |       |
+-------------------+-----------------+------+-----+---------+-------+

[modifier] Table logging

Contient les journaux depuis fin 2004.

mysql> describe logging;
+---------------+------------------+------+-----+----------------+-------+
| Field         | Type             | Null | Key | Default        | Extra |
+---------------+------------------+------+-----+----------------+-------+
| log_type      | varchar(10)      | NO   |     |                |       |
| log_action    | varchar(10)      | NO   |     |                |       |
| log_timestamp | varchar(14)      | NO   |     | 19700101000000 |       |
| log_user      | int(10) unsigned | NO   |     | 0              |       |
| log_namespace | int(11)          | NO   |     | 0              |       |
| log_title     | varchar(255)     | NO   |     |                |       |
| log_comment   | varchar(255)     | NO   |     |                |       |
| log_params    | blob             | NO   |     |                |       |
+---------------+------------------+------+-----+----------------+-------+

[modifier] Tables non mises à jour

[modifier] Table cur

Cette table n'est plus mise à jour.

mysql> describe cur;
+-------------------+---------------------+------+-----+---------+-------+
| Field             | Type                | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+-------+
| cur_id            | int(8) unsigned     | NO   |     | 0       |       |
| cur_namespace     | tinyint(2) unsigned | NO   |     | 0       |       |
| cur_title         | varchar(255)        | NO   |     |         |       |
| cur_text          | mediumtext          | NO   |     |         |       |
| cur_comment       | tinyblob            | NO   |     |         |       |
| cur_user          | int(5) unsigned     | NO   |     | 0       |       |
| cur_user_text     | varchar(255)        | NO   |     |         |       |
| cur_timestamp     | varchar(14)         | NO   |     |         |       |
| cur_restrictions  | tinyblob            | NO   |     |         |       |
| cur_counter       | bigint(20) unsigned | NO   |     | 0       |       |
| cur_is_redirect   | tinyint(1) unsigned | NO   |     | 0       |       |
| cur_minor_edit    | tinyint(1) unsigned | NO   |     | 0       |       |
| cur_is_new        | tinyint(1) unsigned | NO   |     | 0       |       |
| cur_random        | double unsigned     | NO   |     | 0       |       |
| inverse_timestamp | varchar(14)         | NO   |     |         |       |
| cur_touched       | varchar(14)         | NO   |     |         |       |
+-------------------+---------------------+------+-----+---------+-------+
16 rows in set (0.01 sec)

mysql> select cur_timestamp from cur
    -> order by cur_timestamp desc
    -> limit 10;
+----------------+
| cur_timestamp  |
+----------------+
| 20050702225542 |
| 20050702225524 |
| 20050702225524 |
| 20050702225517 |
| 20050702225515 |
| 20050702225511 |
| 20050702225511 |
| 20050702225502 |
| 20050702225458 |
| 20050702225457 |
+----------------+

[modifier] Table text

Cette table n'est plus mise à jour.

mysql> describe text;
+-------------------+---------------------+------+-----+---------+-------+
| Field             | Type                | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+-------+
| old_id            | int(8) unsigned     | NO   |     | 0       |       |
| old_namespace     | tinyint(2) unsigned | NO   |     | 0       |       |
| old_title         | varchar(255)        | NO   |     |         |       |
| old_text          | mediumtext          | NO   |     |         |       |
| old_comment       | tinyblob            | NO   |     |         |       |
| old_user          | int(5) unsigned     | NO   |     | 0       |       |
| old_user_text     | varchar(255)        | NO   |     |         |       |
| old_timestamp     | varchar(14)         | NO   |     |         |       |
| old_minor_edit    | tinyint(1)          | NO   |     | 0       |       |
| old_flags         | tinyblob            | NO   |     |         |       |
| inverse_timestamp | varchar(14)         | NO   |     |         |       |
+-------------------+---------------------+------+-----+---------+-------+

[modifier] Salebot

mysql> desc user;
+-----------------------------+---------------------+------+-----+---------+-------+
| Field                       | Type                | Null | Key | Default | Extra |
+-----------------------------+---------------------+------+-----+---------+-------+
| name                        | varchar(255)        | NO   | PRI |         |       |
| id                          | int(8) unsigned     | YES  |     | NULL    |       |
| creation_time               | int(11) unsigned    | YES  |     | NULL    |       |
| last_action_time            | int(11) unsigned    | YES  |     | NULL    |       |
| last_action_time_str        | varchar(30)         | YES  |     | NULL    |       |
| last_page                   | varchar(255)        | YES  |     | NULL    |       |
| action_count                | int(8) unsigned     | YES  |     | NULL    |       |
| edit_count                  | int(8) unsigned     | YES  |     | NULL    |       |
| new_page_count              | int(8) unsigned     | YES  |     | NULL    |       |
| vandalism_count             | int(8) unsigned     | YES  |     | NULL    |       |
| reverted_by_human_count     | int(8) unsigned     | YES  |     | NULL    |       |
| warn_edit_count             | int(8) unsigned     | YES  |     | NULL    |       |
| spam_count                  | int(8) unsigned     | YES  |     | NULL    |       |
| rollback_made_count         | int(8) unsigned     | YES  |     | NULL    |       |
| bot_revert_count            | int(8) unsigned     | YES  |     | NULL    |       |
| bot_impossible_revert_count | int(8) unsigned     | YES  |     | NULL    |       |
| ignore_user                 | tinyint(1) unsigned | YES  |     | NULL    |       |
| stop_edits                  | tinyint(1) unsigned | YES  |     | NULL    |       |
| is_proxy                    | tinyint(1) unsigned | YES  |     | NULL    |       |
| fqdn                        | varchar(255)        | YES  |     | NULL    |       |
+-----------------------------+---------------------+------+-----+---------+-------+

[modifier] Voir aussi