Mysql структура таблицы: The OpenNET Project: MySQL

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

2022-08-19 10:13:00

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

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

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

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

Во всех экспериментах будет использована системная одноимённая база данных mysql, т.к. данная база присутствует во всех версиях СУБД 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:

use <database_name>;

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

DESCRIBE user;

версия 5.7

Версия 8. 0

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

DESC user;

Для MySQL 5.7:

Для MySQL 8.0:

С помощью встроенного справочника можно посмотреть информацию о данном операторе 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;

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

mysql 5.7

mysql 8.0

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

help show columns;

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

так и в mysql 8

Использование оператора EXPLAIN <table> в 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

 

Таблицы (структура) Базы Данных WordPress — Знакомство с WordPress

WordPress в качестве базы данных использует MySQL v5.7 или выше, ИЛИ MariaDB v10.3 или выше.

Ниже все таблицы имеют дефолтный префикс wp_. У вас этот префикс может быть другой — его можно изменить в файле wp-config.php, там он указывается в переменной:

$table_prefix = 'wp_';

Названия всех таблиц находятся в объекте wpdb и получить, например, название таблицы wp_posts с текущим префиксом установленном в конфигурационном файле можено так: $wpdb->posts.

Таблицы WordPress (обычная установка)

Схема базы данных WordPress — структура всех таблиц

Записи — подробнее

wp_posts

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

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

Гибкость таблицы обеспечивается столбцом post_type, который указывает, является ли строка постом, страницей, вложением, пунктом меню или другим типом.

wp_postmeta

Дополняет таблицу $wpdb->posts. Хранит дополнительные данные записей (постов) их еще называют метаполя.

В этой таблице хранится любая дополнительная информация об отдельных постах. Это таблица использует пары ключ/значение для хранения данных. Эта техника используется в WordPress в ряде таблиц по всей базе данных, что позволяет ядру WordPress, плагинам и темам хранить неограниченное количество данных с разными ключами.

Пользователи — подробнее

wp_users

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

wp_usermeta

Дополнительная информация о пользователях, такая как Имя, Ник, права и прочее.
Мета поля для таблицы $wpdb->users.

Комментарии — подробнее

wp_comments

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

wp_commentmeta

Мета поля (дополнительные данные) для таблицы wp_commentmeta.

Таксономии — подробнее

wp_terms

Таблица содержащая в себе базовую информацию каждом элементе таксономии.

wp_termmeta

Таблица содержащая в себе дополнительные поля для таблицы $wpdb->terms.

wp_term_taxonomy

Таблица с информацией о таксономиях, их описание. Данные в таблице wp_terms еще не являются таксономиями — эта таблица расширяет данные каждого термина и присваиваем каждому термину таксономию к которой он относится.

wp_term_relationships

Таблица связывающая таксономии с контентом (постами, записями и т.п.). Каждая строка этой таблицы определяет связь между постом (объектом) в wp_posts и термином таксономии из таблицы wp_term_taxonomy.

Остальные таблицы

wp_options

Таблица опций (настроек).

wp_links

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

Таблицы WordPress (Multisite)

Когда сайт WordPress преобразуется в многосайтовую установку, создается «сеть» подсайтов. Текущий сайт становится первым подсайтом сети. В базе данные этот сайт становится сайтом сети (wp_site), а каждый подсайт обозначается как блог (wp_blogs).

wp_blogs

Данные всех под-сайтов сети хранятся в этой таблице.

wp_blogmeta

Появилась в WP 5.1. Тут храниться служебная информация о блогах. Например: db_version, db_last_updated. С появлением этой таблицы вам не нужно использовать таблицу wp_options для хранения некоторых данных о под-сайте и каждый раз использовать switch_to_blog(), когда такие данные нужны.

wp_blog_versions

Содержит текущую версию базы данных каждого сайта. Данные обновляются при обновлении БД для каждого сайта сети.

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

wp_registration_log

Содержит данные, когда был зарегистрирован блог сети.
В этой таблице регистрируются пользователи, которые регистрируют новый сайт после его активации.

wp_signups

Содержит пользователей, которые были зарегистрированы через базовую регистрацию WordPress со страницы:

Администрация > Супер Админ > Настройки.

В этой таблице хранятся данные о блогах, которые были зарегистрированы, но не активированы, когда сеть позволяет регистрировать новые сайты. После активации сайта запись удаляется и создается запись в wp_blogs.

wp_site

Содержит, адреса основных сайтов.

Эта таблица всегда будет содержать одну сеть, хотя структура таблицы позволяет использовать несколько сетей в одной базе данных. Это не было реализовано в самом WordPress, но может быть реализовано с помощью плагинов, например: WP Multi Network или Networks for WordPress.

wp_sitemeta

Данные сайтов: различные опции, включая администратора сайта.

Эта таблица похожа на wp_options, только — это опции работающие для всей сети сайтов а не для конкретного под-сайта. Здесь хранится конфигурация (опции), связанные с сетью, а также другие данные, например, настройки плагинов, которые должны быть доступны для всей сети (для любого сайта сети).

wp_users

Список пользователей всех сайтов сети. Это общая таблица пользователей для всей сети. Это привычная таблица, только в мультисайт версии добавляются еще 2 поля: spam и delete.

wp_usermeta

Содержит мета-данные пользователей. Настройки пользователя для разных сайтов сети.

Базовые таблицы каждого сайта сети

Таблицы сайта сети: wp_posts, wp_options и т.д. Для каждого сайта сети создаются одинаковые таблицы, но с разным префиксом, например, wp_2_posts. Здесь 2 это id подсайта.

Для подстайтов создается следующий набор таблиц:

wp_2_options
wp_2_posts
wp_2_postmeta
wp_2_comments
wp_2_commentmeta
wp_2_terms
wp_2_termmeta
wp_2_term_relationships
wp_2_term_taxonomy
wp_2_links

Механизмы хранения данных в MySQL

Механизм хранения — это часть базы данных, которая отвечает за чтение и запись данных. Начиная с версии MySQL 5.5, движком хранения по умолчанию является InnoDB. Это наиболее часто используемый механизм хранения данных, поскольку он имеет блокировку на уровне строк вместо полной блокировки на уровне таблицы (что очень важно при выполнении экспорта/резервного копирования mysqldump), поддерживает транзакции (позволяя фиксировать и откатывать SQL-запросы), а также имеет полную поддержку внешних ключей и ограничений отношений.

MyISAM был механизмом хранения данных, который в прошлом использовался в WordPress, и, возможно, у вас все еще есть старые сайты WordPress, работающие на нем. Некоторые сайты могут даже иметь смесь таблиц, использующих как MyISAM, так и InnoDB.

Совет: Конвертируйте MyISAM в InnoDB с помощью phpMyAdmin, чтобы повысить производительность базы данных.

Возможно, вы сталкивались с проблемами кодировки символов при переносе базы данных WordPress с одного сервера на другой и задавались вопросом, что это за наборы символов и коллизии, которые упоминаются в статьях поддержки. Так что же такое наборы символов и коллизии?

Набор символов MySQL — это набор символов, допустимых в строке. В алфавите 26 символов — от a до z. Каждой букве присвоен номер, например, a = 1, b = 2, c = 3 и так далее. Буква — это символ, а связанное с ней число — кодировка.

Комбинация всех букв от a до z и соответствующих им цифровых кодировок составляет набор символов. MySQL поддерживает множество наборов символов, которые позволяют хранить практически любой символ в строке.

MySQL collation — это набор правил, используемых для сравнения символов в определенном наборе символов. Для сравнения строк база данных использует номера кодировки символов. Примером правила коллирования является коллирование без учета регистра, когда строки сравниваются, даже если они состоят из символов нижнего или верхнего регистра. Коллизии могут быть довольно сложными, как описано в документации MySQL:

большинство collations имеют множество правил, не только для различения регистра букв, но и для различения акцентов («акцент» — это знак, прикрепленный к символу, как в немецком Ö), а также для сопоставления нескольких символов (например, правило, согласно которому Ö = OE в одной из двух немецких колляций).
MySQL позволяет задавать наборы символов и колляции на четырех уровнях: сервер, база данных, таблица и колонка.

Для сайтов WordPress рекомендуемый набор символов — utf8mb4, а рекомендуемая коллизия — utf8mb4_unicode_ci. В WordPress 4.2 таблицы были переведены с кодовой таблицы utf8 на utf8mb4, что позволяет хранить 4-байтовые символы, а это значит, что в базе данных можно хранить любые символы Unicode. Peter Tasker написал отличное руководство для разработчиков о том, как работает Unicode.

Как получить структуру базы данных в MySQL через запрос?

Можно ли каким-то образом получить структуру базы данных MySQL или просто какую-то таблицу с помощью простого запроса?

Или есть другой способ, как мне это сделать?

Я думаю, что вам нужно DESCRIBE

 DESCRIBE table;
 

Вы также можете использовать ШОУ-ТАБЛИЦЫ

 ШОУ-ТАБЛИЦЫ;
 

, чтобы получить список таблиц в вашей базе данных.

1

Чтобы получить всю структуру базы данных в виде набора операторов CREATE TABLE, используйте mysqldump:

 mysqldump имя_базы_данных --compact --no-data
 

Для отдельных таблиц добавьте имя таблицы после имени базы данных в mysqldump. Вы получите те же результаты с SQL и SHOW CREATE TABLE:

 SHOW CREATE TABLE table;
 

Или DESCRIBE, если вы предпочитаете список столбцов:

 DESCRIBE table;
 

3

Взгляните на ИНФОРМАЦИЯ_СХЕМА . СТОЛЫ стол. Он содержит метаданные обо всех ваших таблицах.

Пример:

 SELECT * FROM `INFORMATION_SCHEMA`.`TABLES`
ГДЕ TABLE_NAME НРАВИТСЯ 'table1'
 

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

3

используя это:

 SHOW CREATE TABLE `users`;
 

даст вам DDL для этой таблицы

 DESCRIBE `users`
 

перечислит столбцы в этой таблице

5

 ВЫБЕРИТЕ *
ИЗ INFORMATION_SCHEMA. COLUMNS
ГДЕ TABLE_SCHEMA = 'test' AND TABLE_NAME = 'products';
 

, где Table_schema — имя базы данных

2

Это запрос SHOW CREATE TABLE. Вы также можете запросить SCHEMA TABLES.

 SHOW CREATE TABLE YourTableName;
 

Вариант первого ответа, который мне показался полезным

Откройте командную строку и введите (вам не нужно входить на сервер mysql)

 mysqldump -hlocalhost -u -p  --compact --no-data > mysql.dmp
 

1

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

SHOW CREATE TABLE TABLE_NAME;

DESC TABLE_NAME;

ПОКАЗАТЬ ПОЛНЫЕ СТОЛБЦЫ ИЗ TABLE_NAME; (для свойств столбца)

EXPLAIN TABLE_NAME;

DESCRIBE TABLE_NAME;

В следующем примере

игровая площадка имя базы данных и оборудование имя таблицы

Другой способ — использовать SHOW-COLUMNS:5. 5 (доступно также для 5.5> )

 $ mysql -uroot -p<пароль> -h<хост> -P<порт> -e \
    "ПОКАЖИТЕ КОЛОННЫ ИЗ СПОРТИВНОГО.ОБОРУДОВАНИЯ"
 

И вывод:

 mysql: [Предупреждение] Использование пароля в интерфейсе командной строки может быть небезопасным.
+-------+-------------+------+-----+---------+---- ------------+
| Поле | Тип | Нуль | Ключ | По умолчанию | Экстра |
+-------+-------------+------+-----+---------+---- ------------+
| идентификатор | интервал(11) | НЕТ | ПРИ | НУЛЕВОЙ | авто_инкремент |
| тип | varchar(50) | ДА | | НУЛЕВОЙ | |
| квант | интервал(11) | ДА | | НУЛЕВОЙ | |
| цвет | варчар (25) | ДА | | НУЛЕВОЙ | |
+-------+-------------+------+-----+---------+---- ------------+
 

Можно также использовать mysqlshow-client (также доступен для 5.5> ) следующим образом:

 $ mysqlshow -uroot -p<пароль> -h<хост> -P<порт> \
    игровое оборудование
 

И вывод:

 mysqlshow: [Предупреждение] Использование пароля в интерфейсе командной строки может быть небезопасным. 
База данных: детская площадка Таблица: оборудование
+-------+--------------+-----+------+ -----+---------+-+--- --+---------+
| Поле | Тип | сортировка | Нуль | Ключ | По умолчанию | Экстра | Привилегии | Комментарий |
+-------+--------------+-----+------+ -----+---------+-+--- --+---------+
| идентификатор | интервал(11) | | НЕТ | ПРИ | | авто_инкремент | выбрать, вставить, обновить, ссылки | |
| тип | varchar(50) | latin1_swedish_ci | ДА | | | | выбрать, вставить, обновить, ссылки | |
| квант | интервал(11) | | ДА | | | | выбрать, вставить, обновить, ссылки | |
| цвет | варчар (25) | latin1_swedish_ci | ДА | | | | выбрать, вставить, обновить, ссылки | |
+-------+--------------+-----+------+ -----+---------+-+--- --+---------+
 

ВЫБЕРИТЕ ИМЯ_СТОЛБЦА ОТ INFORMATION_SCHEMA . КОЛОННЫ ГДЕ TABLE_SCHEMA = ‘bodb’ AND TABLE_NAME = ‘abc’;

работает для получения всех имен столбцов

В настоящее время люди используют DESC вместо DESCRIPTION . Например:- пользователей DESC;

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Обязательно, но не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания и подтверждаете, что прочитали и поняли нашу политику конфиденциальности и кодекс поведения.

MySQL Копировать таблицу с примером

Сводка : В этом руководстве вы узнаете, как копировать таблицу в той же базе данных или из одной базы данных в другую, используя операторы CREATE TABLE и SELECT.

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

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

Чтобы скопировать данные из таблицы в новую таблицу, используйте операторы CREATE TABLE и SELECT следующим образом:

 CREATE TABLE new_table
ВЫБЕРИТЕ столбец, столбец2, столбец3
ОТ
    существующая_таблица;  Язык кода: SQL (язык структурированных запросов) (sql)  

Сначала MySQL создает новую таблицу с именем, указанным в операторе CREATE TABLE. Структура новой таблицы определяется результирующим набором Оператор SELECT . Затем MySQL заполняет данные, поступающие из инструкции SELECT , в новую таблицу.

Чтобы скопировать частичные данные из существующей таблицы в новую, используйте предложение WHERE в операторе SELECT следующим образом:

 CREATE TABLE new_table
ВЫБЕРИТЕ столбец1, столбец2, столбец3
ОТ
    существующая_таблица
ГДЕ
    условия;  Язык кода: SQL (язык структурированных запросов) (sql)  

Перед созданием очень важно проверить, существует ли таблица, которую вы хотите создать. Для этого вы используете Предложение IF NOT EXIST в операторе CREATE TABLE . Полная команда копирования данных из существующей таблицы в новую выглядит следующим образом:

 CREATE TABLE new_table
ВЫБЕРИТЕ столбец1, столбец2, столбец3
ОТ
    существующая_таблица
ГДЕ
    условия;  Язык кода: SQL (язык структурированных запросов) (sql)  

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

Чтобы скопировать данные из одной таблицы, а также все зависимые объекты таблицы, вы используете следующие операторы:

 CREATE TABLE IF NOT EXISTS new_table LIKE существующая_таблица;
ВСТАВИТЬ новую_таблицу
ВЫБЕРИТЕ * ИЗ существующей_таблицы;  Язык кода: SQL (язык структурированных запросов) (sql)  

Нам нужно выполнить два оператора. Первый оператор создает новую таблицу new_table путем дублирования существующей таблицы . Второй оператор вставляет данные из существующей таблицы в новая_таблица .

Примеры таблиц копирования MySQL

Следующий оператор копирует данные из таблицы office в новую таблицу с именем office_bk в образце базы данных classicmodels .

 СОЗДАТЬ ТАБЛИЦУ, ЕСЛИ НЕ СУЩЕСТВУЕТ office_bk
ВЫБЕРИТЕ ИЗ
    офисы;  Язык кода: SQL (язык структурированных запросов) (sql)  

Мы можем проверить копию, запросив данные из таблицы office_bk следующим образом:

 ВЫБЕРИТЕ
    *
ОТ
    офисы_бк;  Язык кода: SQL (язык структурированных запросов) (sql)  

В случаях, когда мы хотим скопировать офисы только в США, мы можем добавить предложение WHERE в инструкцию SELECT следующим образом:

 CREATE TABLE ЕСЛИ НЕ СУЩЕСТВУЕТ office_usa
ВЫБИРАТЬ *
ОТ
    офисы
ГДЕ
    country = 'USA'  Язык кода: SQL (язык структурированных запросов) (sql)  

Следующий оператор получает все данные из office_usa 9Таблица 0008.

 ВЫБЕРИТЕ
    *
ОТ
    офисы_США;  Язык кода: SQL (язык структурированных запросов) (sql)  

Предположим, мы хотим скопировать не только данные, но и все объекты базы данных, связанные с таблицей office , мы используем следующие операторы:

 CREATE TABLE office_dup НРАВИТСЯ офисы;
ВСТАВИТЬ office_dup
ВЫБЕРИТЕ * ИЗ офисов;  Язык кода: SQL (язык структурированных запросов) (sql)  

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

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

 CREATE TABLE destination_db.new_table
НРАВИТСЯ source_db.existing_table;
ВСТАВИТЬ target_db.new_table
ВЫБИРАТЬ *
ИЗ source_db.existing_table;  Язык кода: SQL (язык структурированных запросов) (sql)  

Первый оператор создает новую таблицу new_table в целевой базе данных (destination_db) путем дублирования существующей таблицы (existing_table) из исходной базы данных (source_db).

Вторые операторы копируют данные из существующей таблицы в базе данных-источнике в новую таблицу в базе данных назначения.

Давайте посмотрим на следующий пример.

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

 CREATE DATABASE IF NOT EXISTS testdb;  Язык кода: SQL (язык структурированных запросов) (sql)  

Во-вторых, мы создаем таблицу office в testdb , копируя ее структуру из 9Таблица 0007 office в базе данных classicmodels .

 СОЗДАТЬ ТАБЛИЦУ testdb.offices КАК classicmodels.offices;  Язык кода: SQL (язык структурированных запросов) (sql)  

В-третьих, мы копируем данные из таблицы classimodels.offices в таблицу testdb.offices.

 ВСТАВИТЬ testdb.offices
ВЫБИРАТЬ *
ОТ классических моделей.офисов;  Язык кода: SQL (язык структурированных запросов) (sql)  

Проверим данные из testdb.