はじめに

Sequel Ace（macOS の MySQL/MariaDB データベース管理ツール）で、 MySQL データベースのテーブルから SQLダンプファイル（DDL）を export してみたときの手順をまとめます。

ダンプファイルは、データが損失した時のバックアップなどを目的として作成するものです。

環境

macOS Big Sur バージョン: 11.6
Sequel Ace バージョン: 3.5.1
MySQL バージョン: 8.0.29

サンプルとして使用するデータベース、データについては、以前作成したWebアプリのものを使用します。

1. データベース接続

SequelAce のアプリを開き、データベースに接続します。

2. SQLダンプファイル（DDL）の生成

接続されたら、サイドバーのテーブルを一つ選択して、

画面左下から、「Export」→「As SQL dump …」を押します。

ダンプファイルが必要なテーブルにチェックを入れて、

「Export」を押します。

「laravel_local_2022-08-07.sql」というようなファイル名のダンプファイルが export されます。

3. 生成された SQLダンプファイル（DDL）の確認

作成されたファイルの中身は、以下のとおりです。

# ************************************************************
# Sequel Ace SQL dump
# バージョン 20031
#
# https://sequel-ace.com/
# https://github.com/Sequel-Ace/Sequel-Ace
#
# ホスト: localhost (MySQL 8.0.29)
# データベース: laravel_local
# 生成時間: 2022-08-07 13:02:32 +0000
# ************************************************************

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
SET NAMES utf8mb4;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE='NO_AUTO_VALUE_ON_ZERO', SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

# テーブルのダンプ child_read_record
# ------------------------------------------------------------

DROP TABLE IF EXISTS `child_read_record`;

CREATE TABLE `child_read_record` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `read_record_id` bigint unsigned NOT NULL,
  `child_id` bigint unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `child_read_record_read_record_id_foreign` (`read_record_id`),
  KEY `child_read_record_child_id_foreign` (`child_id`),
  CONSTRAINT `child_read_record_child_id_foreign` FOREIGN KEY (`child_id`) REFERENCES `children` (`id`) ON DELETE CASCADE,
  CONSTRAINT `child_read_record_read_record_id_foreign` FOREIGN KEY (`read_record_id`) REFERENCES `read_records` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

# テーブルのダンプ children
# ------------------------------------------------------------

DROP TABLE IF EXISTS `children`;

CREATE TABLE `children` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `family_id` bigint unsigned NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gender_code` int DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `children_family_id_foreign` (`family_id`),
  CONSTRAINT `children_family_id_foreign` FOREIGN KEY (`family_id`) REFERENCES `families` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

LOCK TABLES `children` WRITE;
/*!40000 ALTER TABLE `children` DISABLE KEYS */;

INSERT INTO `children` (`id`, `family_id`, `name`, `gender_code`, `birthday`, `created_at`, `updated_at`)
VALUES
	(1,1,'ゆきまさ',1,'2020-04-01','2022-07-15 06:45:00','2022-07-15 06:45:00'),
	(2,1,'みくり',2,'2021-04-01','2022-07-15 06:45:00','2022-07-15 06:45:00'),
	(3,2,'お子さま',0,NULL,'2022-07-15 06:50:06','2022-07-15 06:50:06');

/*!40000 ALTER TABLE `children` ENABLE KEYS */;
UNLOCK TABLES;

# テーブルのダンプ contacts
# ------------------------------------------------------------

DROP TABLE IF EXISTS `contacts`;

CREATE TABLE `contacts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nickname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `body` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

# テーブルのダンプ failed_jobs
# ------------------------------------------------------------

DROP TABLE IF EXISTS `failed_jobs`;

CREATE TABLE `failed_jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `connection` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `queue` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `exception` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

# テーブルのダンプ families
# ------------------------------------------------------------

DROP TABLE IF EXISTS `families`;

CREATE TABLE `families` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nickname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `introduction` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `families_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

LOCK TABLES `families` WRITE;
/*!40000 ALTER TABLE `families` DISABLE KEYS */;

INSERT INTO `families` (`id`, `name`, `nickname`, `introduction`, `created_at`, `updated_at`)
VALUES
	(1,'guest','ゲスト','ゲストログインユーザー用です。よろしくお願いします。','2022-07-15 06:45:00','2022-07-15 06:45:00'),
	(2,'y6E2867HyrBuG9fT','よんで','よろしくお願いします。','2022-07-15 06:50:06','2022-07-15 06:50:06');

/*!40000 ALTER TABLE `families` ENABLE KEYS */;
UNLOCK TABLES;

# テーブルのダンプ follows
# ------------------------------------------------------------

DROP TABLE IF EXISTS `follows`;

CREATE TABLE `follows` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `family_id` bigint unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `follows_user_id_foreign` (`user_id`),
  KEY `follows_family_id_foreign` (`family_id`),
  CONSTRAINT `follows_family_id_foreign` FOREIGN KEY (`family_id`) REFERENCES `families` (`id`) ON DELETE CASCADE,
  CONSTRAINT `follows_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

# テーブルのダンプ invites
# ------------------------------------------------------------

DROP TABLE IF EXISTS `invites`;

CREATE TABLE `invites` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `family_id` bigint unsigned NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `token` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `invites_token_unique` (`token`),
  KEY `invites_family_id_foreign` (`family_id`),
  CONSTRAINT `invites_family_id_foreign` FOREIGN KEY (`family_id`) REFERENCES `families` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

LOCK TABLES `invites` WRITE;
/*!40000 ALTER TABLE `invites` DISABLE KEYS */;

INSERT INTO `invites` (`id`, `family_id`, `email`, `token`, `created_at`, `updated_at`)
VALUES
	(1,2,'test2@test','9UyZzBbWeNL41Dcr','2022-07-15 06:51:23','2022-07-15 06:51:23');

/*!40000 ALTER TABLE `invites` ENABLE KEYS */;
UNLOCK TABLES;

# テーブルのダンプ likes
# ------------------------------------------------------------

DROP TABLE IF EXISTS `likes`;

CREATE TABLE `likes` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `picture_book_id` bigint unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `likes_user_id_foreign` (`user_id`),
  KEY `likes_picture_book_id_foreign` (`picture_book_id`),
  CONSTRAINT `likes_picture_book_id_foreign` FOREIGN KEY (`picture_book_id`) REFERENCES `picture_books` (`id`) ON DELETE CASCADE,
  CONSTRAINT `likes_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

# テーブルのダンプ migrations
# ------------------------------------------------------------

DROP TABLE IF EXISTS `migrations`;

CREATE TABLE `migrations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

LOCK TABLES `migrations` WRITE;
/*!40000 ALTER TABLE `migrations` DISABLE KEYS */;

INSERT INTO `migrations` (`id`, `migration`, `batch`)
VALUES
	(1,'2014_10_12_100000_create_password_resets_table',1),
	(2,'2019_08_19_000000_create_failed_jobs_table',1),
	(3,'2021_03_02_171654_create_families_table',1),
	(4,'2021_03_02_171655_create_users_table',1),
	(5,'2021_03_02_235959_create_children_table',1),
	(6,'2021_03_03_000000_create_picture_books_table',1),
	(7,'2021_03_05_000000_create_read_records_table',1),
	(8,'2021_03_06_000000_create_child_read_record_table',1),
	(9,'2021_05_10_155314_create_tags_table',1),
	(10,'2021_05_10_171704_create_read_record_tag_table',1),
	(11,'2021_05_11_132606_create_follows_table',1),
	(12,'2021_05_31_235959_create_likes_table',1),
	(13,'2021_06_09_180935_create_invites_table',1),
	(14,'2021_07_09_205018_create_contacts_table',1),
	(15,'2021_07_18_145514_add_column_soft_deletes_users_table',1);

/*!40000 ALTER TABLE `migrations` ENABLE KEYS */;
UNLOCK TABLES;

# テーブルのダンプ password_resets
# ------------------------------------------------------------

DROP TABLE IF EXISTS `password_resets`;

CREATE TABLE `password_resets` (
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `token` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  KEY `password_resets_email_index` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

# テーブルのダンプ picture_books
# ------------------------------------------------------------

DROP TABLE IF EXISTS `picture_books`;

CREATE TABLE `picture_books` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `family_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `google_books_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `isbn_13` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `authors` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `published_date` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `thumbnail_url` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `five_star_rating` int NOT NULL,
  `read_status` int NOT NULL,
  `review` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `picture_books_family_id_foreign` (`family_id`),
  KEY `picture_books_user_id_foreign` (`user_id`),
  CONSTRAINT `picture_books_family_id_foreign` FOREIGN KEY (`family_id`) REFERENCES `families` (`id`),
  CONSTRAINT `picture_books_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

# テーブルのダンプ read_record_tag
# ------------------------------------------------------------

DROP TABLE IF EXISTS `read_record_tag`;

CREATE TABLE `read_record_tag` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `read_record_id` bigint unsigned NOT NULL,
  `tag_id` bigint unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `read_record_tag_read_record_id_foreign` (`read_record_id`),
  KEY `read_record_tag_tag_id_foreign` (`tag_id`),
  CONSTRAINT `read_record_tag_read_record_id_foreign` FOREIGN KEY (`read_record_id`) REFERENCES `read_records` (`id`) ON DELETE CASCADE,
  CONSTRAINT `read_record_tag_tag_id_foreign` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

# テーブルのダンプ read_records
# ------------------------------------------------------------

DROP TABLE IF EXISTS `read_records`;

CREATE TABLE `read_records` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `picture_book_id` bigint unsigned NOT NULL,
  `family_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `memo` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `read_date` date NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `read_records_picture_book_id_foreign` (`picture_book_id`),
  KEY `read_records_family_id_foreign` (`family_id`),
  KEY `read_records_user_id_foreign` (`user_id`),
  CONSTRAINT `read_records_family_id_foreign` FOREIGN KEY (`family_id`) REFERENCES `families` (`id`) ON DELETE CASCADE,
  CONSTRAINT `read_records_picture_book_id_foreign` FOREIGN KEY (`picture_book_id`) REFERENCES `picture_books` (`id`) ON DELETE CASCADE,
  CONSTRAINT `read_records_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

# テーブルのダンプ tags
# ------------------------------------------------------------

DROP TABLE IF EXISTS `tags`;

CREATE TABLE `tags` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tags_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

# テーブルのダンプ users
# ------------------------------------------------------------

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `family_id` bigint unsigned NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nickname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `icon_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `relation` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_name_unique` (`name`),
  UNIQUE KEY `users_email_unique` (`email`,`deleted_at`),
  KEY `users_family_id_foreign` (`family_id`),
  CONSTRAINT `users_family_id_foreign` FOREIGN KEY (`family_id`) REFERENCES `families` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;

INSERT INTO `users` (`id`, `family_id`, `name`, `nickname`, `email`, `email_verified_at`, `password`, `remember_token`, `icon_path`, `relation`, `created_at`, `updated_at`, `deleted_at`)
VALUES
	(1,1,'guest_user','げん(ゲスト)','guest_user@guest.com','2022-07-15 06:45:01',NULL,NULL,'image/1.jpg','パパ','2022-07-15 06:45:01','2022-07-15 06:45:01',NULL),
	(2,1,'guest_partner','ゆい(パートナー)','guest_partner@guest.com','2022-07-15 06:45:01',NULL,NULL,'image/2.jpg','ママ','2022-07-15 06:45:01','2022-07-15 06:45:01',NULL),
	(3,2,'kWJ1A7fhlzUZXDAb','やまて','test@test','2022-07-15 06:50:41','$2y$10$JCbzpFTNRTTbYfh9jxp.u.0JB6AETjnJitwofj3tv4Mi7Gn/GyCTm',NULL,NULL,NULL,'2022-07-15 06:50:06','2022-07-15 06:50:41',NULL);

/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

おわりに

Sequel Ace（macOS の MySQL/MariaDB データベース管理ツール）で、SQLダンプファイル（DDL）を export してみたときの手順をまとめました。

ご覧いただきありがとうございました。

※ 2022/08/15 追記
生成したSQLダンプファイルから dbdiagram.io でER図を自動生成する記事を投稿しました。

Sequel Ace で、 MySQL データベースから SQLダンプファイル（DDL）を生成してみる

はじめに

環境

目次

1. データベース接続

2. SQLダンプファイル（DDL）の生成

3. 生成された SQLダンプファイル（DDL）の確認

おわりに

Sequel Ace で、 MySQL データベースから SQLダンプファイル（DDL）を生成してみる

はじめに

環境

目次

1. データベース接続

2. SQLダンプファイル（DDL） の生成

3. 生成された SQLダンプファイル（DDL）の確認

おわりに

2. SQLダンプファイル（DDL）の生成