Tables:¶
Voir les tables de la base courante:¶
mysql [database]> SHOW TABLES;
MariaDB [wordpress]> SHOW TABLES;
+-----------------------+
| Tables_in_wordpress |
+-----------------------+
| wp_commentmeta |
| wp_comments |
| wp_links |
| wp_options |
| wp_postmeta |
| wp_posts |
| wp_term_relationships |
| wp_term_taxonomy |
| wp_termmeta |
| wp_terms |
| wp_usermeta |
| wp_users |
+-----------------------+
12 rows in set (0.003 sec)
Structure d’une table:¶
mysql [database]> DESCRIBE table;
MariaDB [wordpress]> DESCRIBE wp_terms;
+------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+----------------+
| term_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(200) | NO | MUL | | |
| slug | varchar(200) | NO | MUL | | |
| term_group | bigint(10) | NO | | 0 | |
+------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.006 sec)
Renommer une table:¶
mysql [database]> ALTER TABLE table AS new_table;
MariaDB [zenphoto]> ALTER TABLE _tags RENAME AS _new-tags;
Créer une table:¶
mysql [database]> CREATE TABLE table;
MariaDB [mydatabase]> CREATE TABLE animal (nom VARCHAR(20), maitre VARCHAR(20), espece VARCHAR(20), sexe CHAR(1), naissance DATE, mort DATE);
Query OK, 0 rows affected (0.138 sec)
Charger des données dans une table:¶
mysql [database]> INSERT INTO table VALUES ('','');
MariaDB [mydatabase]> INSERT INTO animal VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Query OK, 1 row affected (0.012 sec)
mysql [database]> LOAD DATA LOCAL INFILE "file.txt" INTO TABLE table;
MariaDB [mydatabase]> LOAD DATA LOCAL INFILE "evenements.txt" INTO TABLE evenement;
Query OK, 11 rows affected, 3 warnings (0.017 sec)
Records: 11 Deleted: 0 Skipped: 0 Warnings: 3
Le fichier .txt doit être dans le dossier qui contient les tables:
/opt/homebrew/var/mysql/mydatabase master 13:26:16
❯ ls -la
total 208
drwx------ 8 bruno admin 256 sep 6 13:26 .
drwxr-xr-x 18 bruno admin 576 sep 4 08:00 ..
-rw-rw---- 1 bruno admin 741 sep 4 09:17 animal.frm
-rw-rw---- 1 bruno admin 98304 sep 4 09:17 animal.ibd
-rw-rw---- 1 bruno admin 67 sep 4 08:00 db.opt
-rw-rw---- 1 bruno admin 1377 sep 6 11:18 evenement.frm
-rw-rw---- 1 bruno admin 98304 sep 6 11:18 evenement.ibd
-rw-r--r-- 1 bruno admin 529 sep 6 13:26 evenements.txt
Vérification des tables :¶
2 solutions:
1) CHECK TABLE table;
MariaDB [wordpress]> CHECK TABLE wp_terms;
+--------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| wordpress.wp_terms | check | status | OK |
+--------------------+-------+----------+----------+
1 row in set (0.008 sec)
2) mysqlcheck
mysqlcheck verrouille chaque table en lecture seule (la base est alors inaccessible pour les autres processus pendant ce temps) pour vérification ou réparation.
❯ mysqlcheck -u root -ppassword wordpress
wordpress.wp_commentmeta OK
wordpress.wp_comments OK
wordpress.wp_links OK
wordpress.wp_options OK
wordpress.wp_postmeta OK
wordpress.wp_posts OK
wordpress.wp_term_relationships OK
wordpress.wp_term_taxonomy OK
wordpress.wp_termmeta OK
wordpress.wp_terms OK
wordpress.wp_usermeta OK
wordpress.wp_users OK
Optimiser:¶
Les bases InnoDB ne supportent pas l’option OPTIMIZE.
A la place,MySQL crée une nouvelle table, y copie toutes les lignes, efface l’ancienne table, renomme la nouvelle et lance ANALYSE
❯ mysqlcheck -u root -psncfp1p2 -o --all-databases
mysql.column_stats Table is already up to date
mysql.columns_priv Table is already up to date
mysql.db OK
mysql.event Table is already up to date
...
wordpress.wp_commentmeta
note : Table does not support optimize, doing recreate + analyze instead
status : OK
wordpress.wp_comments
note : Table does not support optimize, doing recreate + analyze instead
status : OK
wordpress.wp_links
note : Table does not support optimize, doing recreate + analyze instead
status : OK
Dernière mise à jour:
September 15, 2021