SELECT data FROM `show tables` MySQLクエリでテーブル情報を取得する方法

MySQLクエリ「SELECT data FROM show tables」でテーブル情報を取得する方法

この解説で学ぶこと

• SHOW TABLES クエリでテーブル情報を選択する方法
• SELECT 句の構文
• よく使用されるテーブル情報
• 取得結果の活用例

サンプルデータベース

この解説では、以下のサンプルデータベースを使用します。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sample_database   |
+--------------------+

テーブル情報を選択する

SELECT * FROM `SHOW TABLES`;

このクエリは、以下の情報を取得します。

• Tables_in_sample_database: テーブル名
• Table_type: テーブルの種類 (BASE TABLE, VIEWなど)
• Engine: 使用されているストレージエンジン
• Version: テーブルのバージョン
• Row_format: 行フォーマット
• Table_rows: テーブル内の行数
• Avg_row_length: 平均行長
• Data_length: データ長
• Max_data_length: 最大データ長
• Index_length: インデックス長
• Data_free: 空きデータ領域
• Auto_increment: AUTO_INCREMENT 値
• Create_time: テーブル作成日時
• Check_time: テーブルチェック日時
• Table_collation: テーブルの照合順序

SELECT 句では、取得したい列名をカンマ区切りで指定します。

SELECT Tables_in_sample_database, Table_type, Engine
FROM `SHOW TABLES`;

このクエリは、テーブル名、テーブルの種類、ストレージエンジンのみを取得します。

• テーブル一覧を画面に表示する
• 特定の条件に合致するテーブルを検索する
• テーブルの構造を分析する
• データベースの容量を調査する

• SHOW TABLES クエリは、デフォルトで現在のデータベースのテーブルのみを表示します。別のデータベースのテーブルを表示するには、FROM 句でデータベース名を指定する必要があります。
• SHOW FULL TABLES クエリは、より詳細なテーブル情報を取得できます。

import mysql.connector

# データベース接続
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="sample_database",
)

# クエリ実行
cursor = connection.cursor()
cursor.execute("SELECT * FROM `SHOW TABLES`;")

# 結果取得
for row in cursor:
    print(row)

# クローズ
cursor.close()
connection.close()

このコードは、以下の出力を生成します。

('customers', 'BASE TABLE', 'InnoDB', 10, 'Dynamic', 10, 16384, 10240, 16384, 0, 1, None, '2023-11-14 10:28:56', '2023-11-14 10:28:56', '2023-11-14 10:28:56', 'utf8mb4_0900_ai_ci')
('orders', 'BASE TABLE', 'InnoDB', 10, 'Dynamic', 10, 16384, 10240, 16384, 0, 1, None, '2023-11-14 10:29:02', '2023-11-14 10:29:02', '2023-11-14 10:29:02', 'utf8mb4_0900_ai_ci')

SELECT *
FROM `SHOW TABLES`
WHERE Tables_in_sample_database LIKE 'cust%';

SELECT *
FROM `SHOW CREATE TABLE` customers;

SELECT table_schema,
       SUM(data_length + index_length) AS total_size
FROM information_schema.tables
GROUP BY table_schema;

MySQL の INFORMATION_SCHEMA データベースには、テーブルに関する情報を含むさまざまなテーブルがあります。これらのテーブルを直接クエリすることで、詳細なテーブル情報を取得できます。

例:

SELECT * FROM INFORMATION_SCHEMA.TABLES;

# 特定のテーブルの情報のみ取得
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'customers';

MySQLクライアントツール

MySQL Workbench や MySQL Shell などの MySQL クライアントツールは、GUI またはコマンドラインインターフェースを使用してテーブル情報を取得することができます。

• MySQL Workbench:

  1. データベース接続
  2. ナビゲーションパネルで "Tables" フォルダを展開
  3. 必要なテーブルを選択
  4. 右クリックメニューから "View Table Data" または "View Table Structure" を選択

mysql> SHOW TABLES;

# 特定のテーブルの情報のみ取得
mysql> DESC customers;

プログラミング言語

Python や Java などのプログラミング言語を使用して、MySQLデータベースに接続し、SHOW TABLES クエリを実行したり、INFORMATION_SCHEMA テーブルを直接クエリしたりすることができます。

• Python:

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="sample_database",
)

cursor = connection.cursor()

# テーブル一覧取得
cursor.execute("SHOW TABLES;")
for row in cursor:
    print(row)

# 特定のテーブルの情報のみ取得
cursor.execute("DESC customers;")
for row in cursor:
    print(row)

cursor.close()
connection.close()

mysqldump コマンド

mysqldump コマンドを使用して、データベース全体または特定のテーブルをダンプすることができます。ダンプファイルには、テーブル構造とデータが含まれます。

mysqldump -u root -p sample_database > database.sql

# 特定のテーブルのみダンプ
mysqldump -u root -p sample_database customers > customers.sql

それぞれの方法のメリットとデメリット