MySQL список таблиц и их структура

Команды для работы со списком таблиц и их структурой.

список баз данных

SHOW DATABASES;

список таблиц в базе

SHOW TABLES [FROM db_name];

список столбцов в таблице

SHOW COLUMNS FROM таблица [FROM db_name];

показать структуру таблицы в формате «CREATE TABLE»

SHOW CREATE TABLE table_name;

список индексов

SHOW INDEX FROM tbl_name;

привилегии для пользователя.

SHOW GRANTS FOR user [FROM db_name];

значения системных переменных

SHOW VARIABLES;

статистика по mysqld процессам

SHOW [FULL] PROCESSLIST;

общая статистика

SHOW STATUS;

статистика по всем таблицам в базе

SHOW TABLE STATUS [FROM db_name];

статистика по всем таблицам wp_ в базе

SHOW TABLE STATUS LIKE  'wp%'

Проверка настроек кодировки

SHOW VARIABLES LIKE 'coll%';

Проверка настроек кодировки

SHOW VARIABLES LIKE'char%';

Проверка кодировки текущей базы данных

SHOW VARIABLES LIKE "character_set_database";

Для настройки кодировки utf8 по умолчанию
Необходимо внести следующие изменения в файл my.cnf (my.ini):

[mysqld]

init_connect=‘SET collation_connection = utf8_unicode_ci’
character-set-server = utf8
collation-server = utf8_unicode_ci

[client]
default-character-set = utf8

  CREATE TABLE `table_new` FROM `table_orig`;

Информация об установленной версии pkg_info | grep mysql (FreeBSD)

добавить столбец в таблице в базе mysql

ALTER TABLE `table_name` ADD `vcolumn_name` INT( 11 ) NOT NULL;

переименование столбцов в таблице

ALTER TABLE `table` CHANGE COLUMN `test` `materials` INT(10) NOT NULL DEFAULT 0;

внести изменения в столбцы таблицы

ALTER TABLE `table` MODIFY COLUMN `test` SMALLINT NOT  NULL DEFAULT 0, `materials` SMALLINT NOT  NULL DEFAULT 0;

ALTER TABLE tablename ENGINE=MyISAM

В будущем, если включите innodb_file_per_table, сможете уменьшать объем tablespace путем
пересоздания таблиц сразу в InnoDB (т.к. в режиме файл-на-таблицу файл при удалении таблицы
стирается) командой

ALTER TABLE tablename ENGINE=InnoDB

Определение размера таблицы

SELECT
    table_name AS table_name,
    engine,
    ROUND(data_length/1024/1024,2) AS total_size_mb,
    table_rows
FROM
    information_schema.tables
WHERE
    table_schema=DATABASE();

Показывает обьем и количество строк в таблицах MySQL.

Перемещение таблицы MySQL в другую базу

mysql> RENAME TABLE database1.table TO database2.table;

Копирование таблицы MySQL в другую базу

mysql> CREATE TABLE database2.table LIKE database1.table;
mysql> INSERT INTO database2.table SELECT * FROM database1.table;

Копирование таблицы MySQL в другую базу с условием

mysql> CREATE TABLE database2.table LIKE database1.table;
mysql> INSERT INTO database2.table SELECT * FROM database1.table WHERE `row`='1';

Копирование необходимых столбцов таблицы  MySQL в другую базу

mysql> INSERT INTO database2.table(`row_1`, `row_2`, `row_3`) SELECT `row_1`, `row_2`, `row_3` FROM database1.table;

database1— база из которой копируем

database2 — база в которую копируем
table — название таблицы

Текущее состояние кэша

Посмотреть состояние кэша можно с помощью запроса:

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 973      |
| Qcache_free_memory      | 14282000 |
| Qcache_hits             | 3293750  |
| Qcache_inserts          | 252819   |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 66645    |
| Qcache_queries_in_cache | 1342     |
| Qcache_total_blocks     | 3709     |
+-------------------------+----------+
8 rows in set (0.00 sec)

Здесь:

• Qcache_free_memory — объем свободной памяти, отведенной под кэш.
• Qcache_hits — количество запросов, отработанных из кэша.
• Qcache_inserts — количество вставок запросов в кэш.
• Qcache_lowmem_prunes — количество высвобождений памяти из-за наполненности кэша.
• Qcache_not_cached — количество запросов, не подлежащих кэшированию.
• Qcache_queries_in_cache — количество запросов, находящихся в кэше в настоящее время.

Мерой эффективности кэша может служить отношение Qcache_hits / (Qcache_inserts + Qcache_not_cached).

Дополнительные материалы:
[urlspan]MySQL полезные команды [/urlspan]
[urlspan]MySQL работа с пользователи[/urlspan]