Как посмотреть структуру таблицы mysql

Посмотреть структуру таблицы mysql для версий 5.7 и 8

В данном руководстве будет продемонстрировано 3 способа посмотреть структуру таблицы с помощью таких операторов как:

• DESCRIBE в сокращённой форме DESC;
• SHOW;
• EXPLAIN.

Используемое программное обеспечение:

• MySQL 5.7.33;
• MySQL 8.0;
• Windows 10 21H2 (сборка ОС 19044.1889).

Во всех экспериментах будет использована системная одноимённая база данных mysql, т.к. данная база присутствует во всех версиях СУБД MySQL.

Использование DESCRIBE в MySQL

Сразу хотелось бы уточнить, что в mysql сокращённую форму DESC можно расшифровать двумя способами:

1. DESC — это DESCRIBE;
2. DESC — означающий descending, используется при сортировке в ORDER BY клаузуле, например ORDER BY id DESC, что означает сортировка в обратном порядке.

1-й случай относится к текущей теме, 2-й — нет.

Для начала конечно же необходимо подключиться к СУБД MySQL через консоль.

Подключаемся к mysql 5.7

 mysql -P 3307 -uroot -p mysql 

• mysql в начале — это консольная утилита mysql.exe
• -P — параметр в верхнем регистре означающий port/порт;
• -u — параметр означающий user/пользователь;
• -p — параметр в нижнем регистре означающий пароль/password
• mysql в конце — это системная база данных, также можно использовать абсолютно другую базу данных, к которой есть доступ.

Подключаемся к MySQL 8.0

 mysql -P 3306 -uroot mysql 

Версия 8.0 настроена без пароля для пользователя root.

При подключении имя базы данных, в нашем случае mysql, можно не использовать, тогда придётся использовать оператор use:

Далее используется таблица user в базе mysql:

DESCRIBE user;

Проверим работу сокращённого оператора DESC:

DESC user;

С помощью встроенного справочника можно посмотреть информацию о данном операторе DESCRIBE:

help describe;

для версии mysql 5.7

для версии mysql 8.0:

Использование в MySQL show columns from table

В данном блоке рассмотрю использование оператора SHOW COLUMNS, которого будет достаточно для изучения структуры таблицы.

 SHOW COLUMNS FROM user; 

Оператор SHOW с таким синтаксисом можно использовать если наше подключение находится в контексте нужной нам базы данных, о чём свидетельствует параметр «Current database: mysql«

mysql 5.7

mysql 8.0

 SHOW COLUMNS FROM user FROM mysql; 

Если подключение не находится в контексте нужной нам базы или вообще не в контексте какой-либо базы, то можно и нужно указать оператору базу с помощью клаузулы FROM:

mysql 5.7

mysql 8.0

SHOW COLUMNS FROM mysql.user;

Также не будучи в контексте какой-либо базы допускается использование так называемого полного квалификатора, т.е. перед именем таблицы использовать имя базы данных через точку: .

mysql 5.7

mysql 8.0

Также настоятельно рекоменду воспользоваться встроенным руководством по данному оператору:

 help show columns; 

которое присуствует как в MySQL 5.7

так и в mysql 8

Использование оператора EXPLAIN в MySQL

Оператор EXPLAIN редко используется для просмотра структуры таблицы, но знать о его использовании в таком контексте тоже необходимо:

 EXPLAIN user; 

В первом случае также неоходимо быть в контексте нужной базы данных:

MySQL 5.7

MySQL 8.0

 EXPLAIN mysql.user; 

Во втором случае контекст базы данных не обязателен т.к. используется полный квалификатор:

MySQL 5.7

MySQL 8.0

Также по традиции рекомендую ознакомиться с официальной документацией, встроенной в MySQL:

 help EXPLAIN; 

MySQL 5.7

MySQL 8.0

Как узнать структуру таблицы mysql

Бывает такая ситуация, когда phpmyadmin не настроен и нет под рукой ни какой сторонней программы, а нужно делать выборку из талицы по нескольким полям название, которых вы конечно же не помните. Для того чтобы просмотреть структуру таблицы mysql, список всех колонок в таблице проще всего выполнить команду:

SHOW CREATE TABLE имя таблицы

Таким образом вы можете узнать не только список столбцов, но и способ хранения данных, кодировку и сортировку.

Если у вас проблемы с работай mysql обращайтесь к нам [email protected], мы поможем решить ваши вопросы.

Похожие статьи:

1. Восстановление или ремонт таблиц или индексов mysql
2. Выборка из таблицы mysql в файл
3. Перенос таблицы БД mysql по файлам
4. Бэкап одной таблицы в базе MySQL

Как получить информацию о структуре БД для документации

Из этой статьи вы узнаете, зачем нужно отслеживать метаданные в ИТ-проектах и какие существуют основные подходы к документированию баз данных, а также познакомитесь с примерами SQL запросов, которые помогут получить сведения о структуре и составе реляционной базы данных.

Это поможет любому участнику команды, а в первую очередь системному аналитику, решить такие задачи:

• Корректно сформулировать задачу на доработку системы в ситуации, когда её БД не описана
• Оценить актуальность имеющейся документации на БД
• Выполнить инвентаризацию информационных ресурсов
• Проводить мониторинг состояния БД на проекте

Время на чтение статьи: 14 минут
Оглавление

Какие метаданные полезно знать о своей БД?

При работе над любым ИТ-проектом полезно иметь возможность посмотреть на данные своих систем с высоты птичьего полёта.

Вот какая информация пригодится вам в первую очередь.

• Физическая структура данных: объекты БД (в первую очередь — таблицы и поля, их наименования, типы данных, допустимые диапазоны значений, ключи, а также — пользователи, индексы, представления и т. п.)
• Объём данных, частота их обновления, дата последнего обновления БД
• Модель данных (логическая, концептуальная) — ключевые атрибуты и сущности, связь между ними
• Смысл и значение данных, объяснение основных сущностей на языке бизнеса
• Распределение данных (количество пустых полей, кардинальность (количество уникальных и повторяющихся значений)
• Ответственный за данные — кто может пояснить смысл данных или помочь исправить найденные проблемы в них

Что такое словарь данных и зачем он нужен

Мы будем подразумевать под «словарем данных» (англ. — data dictionary) справочник или централизованное описание метаданных, дающее представление о структуре и содержании данных. У этого термина есть и другое значение, в рамках данной статьи не используемое: словарём данных называют технику моделирования, дополняющую требования при проектировании информационных систем, в культуре Systems Analysis and Design.

Мы все знаем, что в целостном и удобном для использования виде найти такую информацию по своему проекту аналитику бывает непросто. Структура таблиц базы данных попросту не описана в документации. Почему же так происходит?

Моделирование предметной области и проектирование баз данных

Воркшоп для системных аналитиков и не-разработчиков уровня джун или мидл, которые хотят спроектировать логическую модель базы данных и изучить основы нормализации баз данных. Особенно актуально для тех, кто еще не знаком с базами данных.

Традиционный подход для старта проектирования БД

Почему так редко задумываются о документировании БД?

• Это самая неинтересная для программистов часть

• На старте проекта БД создаётся по интуиции и видению разработчиков

• Для данных редко выделяется отдельный архитектор или просто ответственное лицо в команде

• По мере роста проекта требования к БД сложно выделить из множества требований на доработку

Вначале всё кажется очевидным, но по мере роста проекта оказывается, что единого описания данных и их структуры не существует, также как и носителя информации об этом среди участников команды. Иногда оказывается, что устройство БД уже усложнилось до такой степени, что приходится организовывать целую исследовательскую экспедицию для восстановления этой информации. В идеале нужно поддерживать актуальность информации о данных системы, и делать это можно разными способами.

Какие средства документирования можно применять для описания БД?

Инструмент, тип документации	Какую информацию можно найти	Примеры и комментарии	Что почитать
Специальные платформы управления данными Data Management.	•‎ Каталог данных (Data Catalog) — единый источник сведений о всех информационных активах организации. •‎ Список полей и атрибутов •‎ Метаданные — например, информацию о размере таблиц, дате последнего обновления •‎ Информацию об ответственных лицах •‎ Бизнес-глоссарий	Дороги в применении, используются в крупных только в больших компаниях, актуальны для проектов создания DWH (централизованное персистентное хранение данных всего предприятия для аналитики).	Что такое каталог данных? Видео от DIS Group

Как вы уже поняли, если перед вами стоит задача понять смысл и структуру данных вашей системы, то придётся обращаться к нескольким источникам. Рыться в документации полезно, но утомительно. Наверно, вам уже пришла в голову мысль, что лучший способ понять вашу базу данных «as is» будет заключаться в том, чтобы подключиться к ней и «пощупать её руками».

Источники информации о БД

Как можно получить информацию о данных, используемых в системе?

Способ	Описание	Пример
Использовать специализированные инструменты, обеспечивающие возможность просмотра объектов БД.	Способ позволит визуально быстро понять, какие объекты есть в вашей базе данных. Можное осмотреть список таблиц и о каждой из них узнать что-то из контекстного меню или другой части внутри GUI используемого средства. Однако, получать, хранить и передавать информацию в такой форме неудобно.	Документация к редактору объектов DBeaver
Вывести информацию об объектах БД с помощью соответствующих SQL запросов к служебным таблицам.	В РСУБД существуют служебные таблицы, которые содержат интересующие нас сведения об объектах базы данных. Вы можете строить несложные SQL запросы к служебным таблицам и системным каталогам БД (помимо таблиц со схемой, среди служебных таблиц обычно есть много интересного).	Использование схемы данных в SQL Server

Не все из перечисленных способов могут быть доступны аналитику. Например, возможность просмотра полной схемы БД бывает ограничена только для администраторов, а построение запросов к служебным таблицам требует знания особенностей конкретной СУБД. В следующих частях статьи вы найдёте примеры таких запросов и сможете начать использовать их прямо сейчас.

Как получить информацию о БД

Как быстро получить словарь данных для популярных БД?

Несколько слов по данному вопросу от Александра Кротова.

Полагаю, многие системные и бизнес-аналитики сталкивались с задачей инвентаризации информационных ресурсов организации, то есть с работой по описанию имеющихся в наличии автоматизированных систем, баз данных, локальных АРМ-ов и других ИТ-объектов, играющих определённую роль в бизнес-процессах. Как правило, такие работы проводятся в целях анализа состояния и эффективности ИТ-инфраструктуры внутри компании или на стороне заказчика, либо на фазе обследования в проектах, связанных с доработкой существующих систем. Зачастую работа по документированию текущего состояния дел возлагается на системного аналитика или бизнес-аналитика. В этой статье мы не будем обсуждать всё, что может быть связано с подобной инвентаризацией, а остановимся на подходах к обследованию одного типа ИТ-ресурсов компании — её баз данных.

В идеале, конечно, такое обследование должно начинаться с изучения документации на используемые базы данных, но проблема в том, что далеко не всегда (а по моему опыту, так и почти никогда) такая документация существует. А если документация на БД существует, то крайне редко бывает достаточно полной и актуальной.

Что делать в такой ситуации? Первое, что пришло бы мне в голову, это провести для всех баз данных так называемый «реверс-инжиниринг» (reverse engineering). Можно было бы изучить имеющиеся данные и их структуру, взять могучее универсальное средство моделирования, построить ER-модели, сформировать текстовое описание и приступить к более глубокому анализу полученных материалов. Но здесь мы можем натолкнуться на ряд проблем: универсальные средства моделирования, способные работать с разными СУБД, стоят, как правило, дорого, и далеко не каждый работодатель будет готов потратить деньги на приобретение подобного программного продукта. А вероятность того, что в процессе инвентаризации информационных ресурсов придётся столкнуться с так называемым «зоопарком», то есть разбродом и шатанием в платформах, СУБД и их версиях, достаточно высока. Иначе и задача наведения порядка в информационном хозяйстве вообще вряд ли бы возникла.

И вот, в ситуации, когда «зоопарк» есть, а актуальной документации и подходящего инструмента для её создания нет, можно пойти по другому пути и попробовать сформировать необходимую для анализа документацию на основе так называемых «словарей данных» — служебных таблиц, в которых реляционная (и не только) СУБД хранит описание своих объектов — таблиц, полей, представлений, ключей, индексов и многого другого. В том или ином виде такие словари должны быть в любой СУБД, хотя структура их может сильно различаться. В общем, я решил посмотреть, как подобные словари устроены, взяв для примера несколько популярных СУБД: MySQL, PostgreSQL, Oracle, SQLite, MS Access. А задачу я себе поставил простую: для каждой СУБД написать SQL-запрос, который сформирует описание таблиц и полей выбранных баз данных. Только хотел бы предупредить, что для разных версий одной и той же СУБД запросы могут немного отличаться, но, думаю, это и так очевидно.

Словарь данных для MySQL

Начал я с MySQL. Здесь всё оказалось достаточно просто. Можно использовать стандартную схему под названием INFORMATION_SCHEMA (говорят, даже ANSI на неё свой стандарт оформила для разных СУБД). Схема содержит таблицы, в которых можно легко найти все необходимые метаданные. В нашем случае достаточно соединить таблицы TABLES (таблицы) и COLUMNS (поля) по именам схемы и таблицы, выбрать нужные атрибуты, присвоить понятные псевдонимы, задать условия выборки (например, по имени схемы и типу объектов, как на примере ниже). Получится примерно такой запрос:

Получим результат вот такого вида
(показано на фрагменте отчёта для учебной БД «Студенты»):

Таблица	Комментарий к таблице	№ п.п	Поле	Комментарий к полю	Тип	Ключ	NULL
EMPLOYEE	Сотрудники	1	ID	Идентификатор	int(11)	PK	NO
EMPLOYEE	Сотрудники	2	LAST_NAME	Фамилия	varchar(45)	NO
EMPLOYEE	Сотрудники	3	FIRST_NAME	Имя	varchar(45)	NO
EMPLOYEE	Сотрудники	4	MIDDLE_NAME	Отчество	varchar(45)	YES
EMPLOYEE	Сотрудники	5	BIRTHDAY	День рождения	date	YES
GRADE	Оценки	1	ID	Идентификатор	int(11)	PK	NO
GRADE	Оценки	2	STUDENT_ID	ИД студента в группе	int(11)	YES
GRADE	Оценки	3	MODULE_NUM	Номер модуля	int(11)	YES
GRADE	Оценки	4	GRADE	Оценка	int(11)	YES
GROUP_ST	Учебные группы	1	ID	Идентификатор	int(11)	PK	NO
GROUP_ST	Учебные группы	2	GROUP_CODE	Код группы	varchar(45)	YES
GROUP_ST	Учебные группы	3	TEACHER_ID	ИД сотрудника-преподавателя	int(11)	YES
GROUP_ST	Учебные группы	4	CURATOR_ID	ИД сотрудника-куратора	int(11)	YES
STUDENT	Студенты	1	ID	Идентификатор	int(11)	PK	NO
STUDENT	Студенты	2	LAST_NAME	Фамилия	varchar(45)	NO
STUDENT	Студенты	3	FIRST_NAME	Имя	varchar(45)	NO
STUDENT	Студенты	4	MIDDLE_NAME	Отчество	varchar(45)	YES
STUDENT	Студенты	5	BIRTHDAY	День рождения	date	YES

Единственное, надо не забывать, что запрос вернёт только те объекты БД, на просмотр которых у текущего пользователя есть привилегии. И ещё я бы обратил внимание на атрибут ORDINAL_POSITION, который был использован в составе оператора ORDER BY после TABLE_NAME (имя таблицы). Атрибут предназначен для хранения порядка полей, заданного в DDL-скрипте при создании таблицы (оператор CREATE TABLE) или установленного разработчиком позже (кстати, возможность изменять порядок полей после создания таблицы реализована далеко не во всех СУБД, MySQL здесь обогнал многих). Но зачем хранить исходный порядок полей, если теория реляционных баз данных утверждает, что порядок полей в таблице не несёт никакой смысловой нагрузки? Полагаю, эта возможность реализована исключительно для удобства пользователя. Разработчик, создавая таблицу, задаёт порядок полей не просто так, а на основе определённой бизнес-логики, и вправе ожидать, что выполнив запрос типа SELECT * FROM … без явно указанного порядка полей, или создав отчёт на основе словаря данных, как мы это сделали выше, он получит поля в порядке, соответствующем заложенной логике, а не как попало. Аналогичные по сути атрибуты с порядковым номером поля в таблице, есть и в других СУБД, что мы увидим ниже.

Командная строка MySQL

Небольшая памятка по работе с сервером MySQL из командной строки.

Подключение к MySQL через консоль

Получить информацию об установленной версии MySQL

mysql -V

Для подключения к mysql в консоли наберите команду

mysql -h you_sql_server -u user_name -p

• h — хост c MySQL. Если подключаемся с локальной машины, параметр можно опустить
• u — имя пользователя MySQL (root или другой пользователь MySQL)
• p — пароль, который будет предложено ввести после нажатия enter

Приглашение командной строки изменится, это значит, сервер MySQL ждёт от вас команд.

mysql>

Запросы должны оканчиваться точкой с запятой. Длинные запросы удобно разбивать enter-ом для перехода на новую строку, а после полного написания запроса поставить точку с запятой и выполнить его.

Для отключения от MySQL нужно написать exit или (в unix-системах) нажать комбинацию клавиш ctrl+с.

Для вывода всех баз данных на сервере используйте команду show databases.

SHOW DATABASES;

Выберите нужную базу данных командой use.

USE db_name;

Теперь можно вводить запросы.

Чтобы подключиться к MySQL и сразу выбрать нужную базу

mysql -u user_name -h host_name db_name -p

Полезные команды MySQL

Показать все таблицы выбранной базы данных в текущей БД.

SHOW TABLES;

Показать все таблицы базы данных db_name.

SHOW TABLES FROM db_name;

Показать список столбцов в таблице table_name в текущей БД

SHOW COLUMNS FROM table_name;

Показать список столбцов в таблице table_name из БД db_name

SHOW COLUMNS FROM table_name FROM db_name;

Вывести структуру нужной таблицы

DESCRIBE table_name;

Показать структуру таблицы, будет выведен sql-запрос на её создание через «CREATE TABLE».

SHOW CREATE TABLE table_name;

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

SHOW VARIABLES;

Показать список выполняющихся в настоящий момент запросов.

SHOW PROCESSLIST;

Общая статистика MySQL.

SHOW STATUS;

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

SHOW TABLE STATUS FROM db_name;

Что бы выполнять запросы к MySQL из консоли, не всегда требуется предварительно подключаться к mysql. Параметр -e позволяет исполнить команду, вывести результат на экран, после чего отключиться от сервера MySQL. Например, можно вывести список таблиц базы данных.

mysql -uroot -e 'SHOW TABLES' db_name && echo done

Управление базами данных

Создание базы данных из консоли сервера

$ mysqladmin -u root -p create db_name

Удаление базы данных из консоли сервера

mysqladmin -u root -p drop db_name

Создание базы данных db_name из консоли MySQL

CREATE DATABASE db_name COLLATE utf8_general_ci;

Удаление базы данных db_name из консоли MySQL

DELETE DATABASE db_name;

Замена в поле одной подстроки на другую

UPDATE table SET field=replace(field,'original string','new string');

Работа с пользователями

Вывести список пользователей

SELECT User,Host FROM mysql.user;

Показать список прав пользователя user

SHOW GRANTS FOR user_name FROM db_name;

Создать нового пользователя

CREATE USER 'user'@'host' IDENTIFIED BY'password';

host — здесь имя хоста, доменное имя или ip адрес, с которого пользователь сможет подключаться к серверу, например user@localhost.

Чтобы создаваемый пользователь смог подключаться к серверу MySQL с любого IP адреса или хоста (за исключением localhost), можно использовать символ процента, вот так

CREATE USER 'user'@'%' IDENTIFIED BY 'password';

Чтобы разрешрешить пользователю подключаться вообще со всех хостов, придётся создать для него две учётные записи.

CREATE USER 'user'@'%' IDENTIFIED BY 'password'; CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

Добавить указанные привилегии для таблиц БД db_name пользователю user@localhost

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX ON db_name.* TO 'user'@'localhost';

Чтобы изменить права пользователю, иногда удобно сначала сбросить все права

REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'localhost';

А затем установить нужные с помощью GRANT, как было показано выше.

Добавить все привилегии для таблиц БД db_name пользователю user@localhost

GRANT ALL PRIVILEGES ON db_name.* TO 'user'@'localhost';

Удаление привилегий пользователя user@localhost для БД db_name:

REVOKEALLON db_name.* FROM 'user'@'localhost';

Сделать из пользователя суперпользователя и дать полный доступ ко всем БД на сервере

GRANT ALL ON *.* TO 'user'@'localhost';

Удалить пользователя user@localhost

DROP USER user@localhost;

FLUSH PRIVILEGES;

Изменить пароль пользователя в консоли MySQL

SET PASSWORD FOR 'user'@'localhost' = PASSWORD('new_password'); FLUSH PRIVILEGES;

UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE User='user_name'; FLUSH PRIVILEGES; service mysqld restart;

Установить пароль для пользователя user в консоли сервера.

$ mysqladmin -uuser_name password'password'

Исправление и оптимизация баз данных в MySQL

Чтобы исправить ошибки в поврежденной таблице, в консоли MySQL выполните:

> REPAIR TABLE table_name;

Если же в БД много поврежденных таблиц можно воспользоваться командой mysqlcheck.

Проверить db_name на ошибки.

$ mysqlcheck -p db_name

Восстановление и оптимизация всех БД

$ mysqlcheck -Aor -p

• p – использовать пароль
• -A, —all-databases – проверять все базы данных
• -o, —optimize – оптимизировать
• -r, —repair – восстанавливать повреждённые
• —auto-repair – автоматическое восстановление

Бэкап MySQL из командной строки

Экспорт базы MySQL

$ mysqldump -u username -p db_name > dump.sql

Дамп нескольких баз

$ mysqldump -u username -p -B db_name1 db_name2 > dump.sql

Дамп всех баз на сервере

$ mysqldump -u username -p -A > dump.sql

Дамп только структуры базы, без данных

$ mysqldump -u username -p --no-data db_name > database.sql

Дамп структуры одной таблицы mysql, без данных:

$ mysqldump -u username -p -h host db_name table_name --no-data > /path/dump.sql

Развернуть базу данных MySQL из дампа

$ mysql -u username -p db_name < dump_to_restore.sql

Клонирование таблиц

Скопировать структуру и ключи таблицы, без копирования данных.

CREATE TABLE NEW_TableName LIKE OLD_TableName

Клонировать таблицы базы данных со всеми данными.

CREATE TABLE NEW_TableNameSELECT *FROM OLD_TableName

CREATE TABLE NEW_TableName LIKE OLD_TableName INSERT INTO NEW_TableName SELECT * FROM OLD_TableName;

На этом всё. Но вы можете поддержать проект. Даже небольшая сумма поможет нам писать больше полезных статей.

Если статья помогла или понравилась, пожалуйста поделитесь ей в соцсетях.