C言語＋MySQL入門｜SQLファイルを保存する

C言語＋MySQL入門｜SQLファイルを保存する

これまでの記事では、MySQLクライアントからSQLを入力し、データベースやテーブルを作成してきました。商品データの登録や変更、学習用ユーザーの権限確認もできましたね。

ただ、同じ環境をもう一度用意するときに、SQLを最初から入力し直すのは大変です。テーブルの列や制約を書き忘れてしまうこともあります。

そこで、この記事では、テーブルの作成と確認用データの登録をSQLファイルにまとめ、chap03に保存します。

作成するファイルは、次の2つです。

SQLファイルの保存方法に加えて、Windowsのコマンドプロンプトから実行する方法も確認しましょう。

init_inventory.sqlを実行すると、既存の商品データと在庫履歴が削除されます。 学習用のデータを初期状態へ戻してよい場合に実行してください。今のデータを残したい場合は、ファイルを保存するところまで進め、確認用のcheck_inventory.sqlだけを実行できます。

SQLファイルは、実行するSQLを保存したテキストファイル

SQLファイルは、CREATE TABLEやINSERTなどのSQLを記述したテキストファイルです。拡張子には、一般的に.sqlを使います。

MySQLクライアントは、ファイルに書かれたSQLを読み込み、MySQL Serverへ送って実行できます。複数のSQLをまとめて実行する方法を、バッチ実行と呼びます。

C言語のソースファイルと比べると、違いがつかみやすいでしょう。

SQLファイルを保存しただけでは、データベースは変わりません。MySQLクライアントから実行したときに、初めてテーブルの作成やデータの登録が行われます。

また、SQLファイルとMySQL Serverが管理するデータファイルは別のものです。今回のSQLファイルには、学習環境を作るための手順を書き込みます。

図1：SQLファイルからデータベースへ処理が届くまで

この図から分かること

SQLファイルは、MySQLクライアントが読み込む処理の一覧です。

実際にテーブルを作成し、データを保存するのはMySQL Serverです。ファイルの保存と、データベースに対する処理の実行を分けて考えましょう。

chap03に保存する準備をしよう

この記事では、次のフォルダーへSQLファイルを保存します。

C:\c-mysql\chap03

エクスプローラーでC:\c-mysqlを開き、chap03フォルダーがなければ作成してください。

続いて、Visual Studio Codeでchap03フォルダーを開きます。

1. Visual Studio Codeの「ファイル」から「フォルダーを開く」を選びます。
2. C:\c-mysql\chap03を選択します。
3. 新しいファイルを作成します。
4. この記事のSQLを入力または貼り付けます。
5. 指定したファイル名で保存します。

保存時には、次の点を確認しておきましょう。

この記事では、BOMなしのUTF-8で保存します。Visual Studio Codeの文字コード表示では、UTF-8を選んでください。

Windowsの設定によっては拡張子が隠れています。init_inventory.sql.txtのような名前になっていないかも確認しましょう。

mysql>や->などのプロンプト、Query OKなどの実行結果は、SQLファイルに含めません。

初期化後の状態を決めよう

今回の初期化用SQLファイルでは、これまでに作った2つのテーブルを削除し、同じ定義で作り直します。

その後、次の商品を登録します。

在庫履歴テーブルは、データが入っていない状態にします。

商品を変更・削除する記事では、価格や商品名の変更、商品の削除を試しました。今回の初期化では、それらの操作後の状態ではなく、上の3商品がある状態へ戻します。

また、今回は商品情報だけを登録し、入庫や出庫は行いません。そのため、在庫数はすべて0、在庫履歴は0件です。

初期化用のSQLファイルを作ろう

次の内容を、init_inventory.sqlとして保存してください。

先頭のコメントにも書いているとおり、実行するとproductsとstock_historyの既存データが失われます。

商品テーブルと在庫履歴テーブルを作り直すSQL

ファイル名： init_inventory.sql

-- 学習用データベースの初期化
-- 注意：productsとstock_historyの既存データを削除します。
-- 学習用データを初期状態へ戻してよい場合だけ実行してください。

CREATE DATABASE IF NOT EXISTS inventory_db
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

USE inventory_db;

-- 外部キーで参照する側のテーブルから削除します。
DROP TABLE IF EXISTS stock_history;
DROP TABLE IF EXISTS products;

-- 商品テーブルを作成します。
CREATE TABLE products (
    product_id INT NOT NULL AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (product_id),
    CONSTRAINT chk_products_price
        CHECK (price >= 0),
    CONSTRAINT chk_products_stock
        CHECK (stock >= 0)
) ENGINE=InnoDB
  DEFAULT CHARACTER SET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci;

-- 在庫履歴テーブルを作成します。
CREATE TABLE stock_history (
    history_id INT NOT NULL AUTO_INCREMENT,
    product_id INT NOT NULL,
    movement_type VARCHAR(10) NOT NULL,
    quantity INT NOT NULL,
    recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (history_id),
    CONSTRAINT chk_stock_history_type
        CHECK (movement_type IN ('IN', 'OUT')),
    CONSTRAINT chk_stock_history_quantity
        CHECK (quantity > 0),
    CONSTRAINT fk_stock_history_product
        FOREIGN KEY (product_id)
        REFERENCES products (product_id)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT
) ENGINE=InnoDB
  DEFAULT CHARACTER SET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci;

-- 確認用の商品を登録します。
-- 在庫数と登録日時には、それぞれの既定値を使います。
INSERT INTO products (product_name, price)
VALUES
    ('ノート', 180.00),
    ('ボールペン', 120.00),
    ('クリアファイル', 90.00);

-- 最後まで処理が進んだことを表示します。
SELECT 'inventory_db initialized' AS result;

初期化用SQLの処理を確認しよう

データベースがなければ作成する

CREATE DATABASEにIF NOT EXISTSを付けると、同じ名前のデータベースがすでに存在していても、そのまま後続の処理へ進めます。

続くUSE inventory_dbで、この後の操作対象を選択します。

なお、既存のデータベースに対して、このCREATE DATABASEが文字コードや照合順序を変更するわけではありません。今回のファイルでは、作り直す各テーブルにもutf8mb4とutf8mb4_0900_ai_ciを明示しています。

テーブルは、在庫履歴から削除する

stock_historyは、外部キーによってproductsを参照しています。

そのため、削除は次の順序です。

1. 参照する側のstock_historyを削除する。
2. 参照される側のproductsを削除する。

DROP TABLEは、テーブルのデータだけでなく、テーブルの定義も削除します。IF EXISTSを付けておくことで、対象のテーブルがまだない場合にも、そのことを理由としたエラーを避けられます。dev.mysql.com

テーブルは、商品から作成する

作成するときは、削除するときと逆の順序です。

1. 参照される側のproductsを作成する。
2. 参照する側のstock_historyを作成する。

これまでの記事では、テーブルを作った後にALTER TABLEで外部キーを追加しました。

今回は完成したテーブル定義を保存するため、stock_historyのCREATE TABLEの中へ外部キーの定義をまとめています。商品と履歴の関係は、これまでと同じです。

ON DELETE RESTRICTにより、履歴から参照されている商品の削除を制限します。ON UPDATE RESTRICTも、参照されている商品IDの変更を制限する指定です。

確認用データを登録する

INSERTでは、商品名と単価だけを指定しています。

指定していない列には、次の値が設定されます。

この記事の通常の学習環境では、テーブルを作り直した後の3商品には、商品IDとして1、2、3が割り当てられます。

登録日時は実行した時刻になるため、SQLファイルを再実行するたびに変わります。同じテーブル構造と確認用データを用意するためのファイル、と考えてください。

図2：外部キーを考慮したテーブルの作り直し

この図から分かること

外部キーがあるテーブルでは、削除と作成の順序が大切です。

参照する側を先に削除し、参照される側を先に作成すれば、今回の2つのテーブルを関係に沿って作り直せます。外部キーのチェックを無効にする必要はありません。

初期化はROLLBACKで取り消せないことを知っておこう

前の記事では、トランザクションを使ってデータ操作を試し、ROLLBACKで取り消しました。

ただし、今回の初期化用ファイルを同じように扱うことはできません。

MySQLでは、CREATE TABLEやDROP TABLEなどの処理によって、暗黙のコミットが発生します。ファイル全体をSTART TRANSACTIONとROLLBACKで囲んでも、削除したテーブルやそのデータを元に戻せるわけではありません。dev.mysql.com

また、途中のSQLでエラーになった場合、それ以前に成功したテーブルの削除や作成が残ることがあります。

初期化用SQLファイルは、現在のデータを保存するバックアップではありません。 後から登録した商品や在庫履歴が、このファイルへ自動的に書き込まれることもありません。

データを確認するSQLファイルを作ろう

初期化する処理とは別に、現在の状態を確認するファイルも用意しておきましょう。

確認だけなら、データを作り直す必要はありません。このファイルは、学習用の接続ユーザーから実行できます。

接続先と商品データを確認するSQL

ファイル名： check_inventory.sql

-- 操作対象のデータベースを選択します。
USE inventory_db;

-- 認証に使われたアカウントと接続先を確認します。
SELECT
    CURRENT_USER() AS authenticated_account,
    DATABASE() AS current_database;

-- テーブルの一覧を確認します。
SHOW TABLES;

-- 商品データを商品ID順に表示します。
SELECT
    product_id,
    product_name,
    price,
    stock
FROM products
ORDER BY product_id;

-- 在庫履歴の件数を確認します。
SELECT
    COUNT(*) AS history_count
FROM stock_history;

それぞれの処理には、次の役割があります。

このファイルには、INSERT、UPDATE、DELETE、DROP TABLEを含めていません。実行しても、商品の内容や在庫履歴は変更されません。

コマンドプロンプトから初期化用SQLを実行しよう

ここからは、Windowsのコマンドプロンプトで操作します。PowerShellやMSYS2のターミナルではなく、コマンドプロンプトを開いてください。

mysql>が表示されている画面を使う場合は、先にexitでMySQLクライアントを終了します。

文字コードと実行場所を準備する

コマンドプロンプトで、次のコマンドを順番に実行します。

chcp 65001
cd /d "C:\Program Files\MySQL\MySQL Server 8.4\bin"

chcp 65001は、コマンドプロンプトのコードページをUTF-8へ切り替えます。

cd /dは、必要に応じてドライブも切り替えながら、指定したフォルダーへ移動するコマンドです。MySQLのインストール先が異なる場合は、自分の環境に合わせて変更してください。

管理用アカウントで初期化する

既存の商品データと在庫履歴を削除してよいことを確認してから、次を実行します。

mysql --protocol=TCP --default-character-set=utf8mb4 -h 127.0.0.1 -P 3306 -u root -p < C:\c-mysql\chap03\init_inventory.sql

Enter password:と表示されたら、rootに設定したパスワードを入力してください。

主な指定を整理すると、次のようになります。

最後の<はSQLの一部ではありません。コマンドプロンプトが、指定したファイルをMySQLクライアントへ渡すための記号です。

実行結果の例

Enter password:
result
inventory_db initialized

通常のバッチ実行では、対話形式のように、各SQLの後へQuery OKが表示されるわけではありません。

今回のファイルでは、最後のSELECTによってinventory_db initializedを表示します。なお、エラーが発生しても処理を続ける--forceは付けていません。

実行直後に、次のコマンドで終了コードも確認できます。

echo %ERRORLEVEL%

成功時の実行結果の例です。

0

エラーが表示された場合は、その内容を確認しましょう。途中まで処理されている可能性があるため、原因を修正したうえで、再び初期化してよい状態かを確認してから実行します。

学習用ユーザーで確認用SQLを実行しよう

続いて、前の記事までに作成したinventory_userで接続し、確認用ファイルを実行します。

mysql --protocol=TCP --default-character-set=utf8mb4 -h 127.0.0.1 -P 3306 -u inventory_user -p --table < C:\c-mysql\chap03\check_inventory.sql

今度は、inventory_userに設定したパスワードを入力します。

--tableは、結果を表形式で表示する指定です。

実行結果の例

初期化直後には、次の内容が順番に表示されます。ここでは、表示される値を読みやすい表に整理しています。

最初は、認証に使用されたアカウントとデータベースです。

+--------------------------+------------------+
| authenticated_account    | current_database |
+--------------------------+------------------+
| inventory_user@127.0.0.1 | inventory_db     |
+--------------------------+------------------+

続いて、テーブルの一覧が表示されます。

+------------------------+
| Tables_in_inventory_db |
+------------------------+
| products               |
| stock_history          |
+------------------------+

商品データは、次の3件です。

+------------+-----------------------+--------+-------+
| product_id | product_name          | price  | stock |
+------------+-----------------------+--------+-------+
|          1 | ノート                | 180.00 |     0 |
|          2 | ボールペン            | 120.00 |     0 |
|          3 | クリアファイル        |  90.00 |     0 |
+------------+-----------------------+--------+-------+

最後に、在庫履歴の件数が表示されます。

+---------------+
| history_count |
+---------------+
|             0 |
+---------------+

この結果になれば、2つのテーブルと確認用データが用意され、学習用ユーザーから読み取れることを確認できました。

初期化を実行せずに確認用ファイルだけを実行した場合は、現在保存されているデータが表示されます。上の3商品と一致しなくても、それだけでエラーというわけではありません。

接続ユーザーは、テーブルとは分けて管理する

今回のinit_inventory.sqlには、CREATE USERやGRANTを含めていません。

学習用ユーザーは、MySQL Server上にすでに作成しているためです。テーブルを作り直すたびに、ユーザーやパスワードまで作り直す必要はありません。

また、MySQLでは、DROP TABLEを実行しても、そのテーブルに対する個別の権限設定は自動では削除されません。前の記事で設定したproductsとstock_historyへの権限は、同じデータベース名・テーブル名で作り直した後も利用できます。

作業を分けると、次のようになります。

別のMySQL Serverに学習環境を作る場合は、初期化用ファイルの実行に加えて、3.10で扱ったユーザー作成と権限付与が必要です。その後、3.11の手順で権限を確認してください。

パスワードを今回のSQLファイルへ書き込まずに済むため、テーブル定義と確認用データを扱いやすくなります。

図3：保存したSQLファイルを使って学習環境を整える

この図から分かること

初期化と確認を別のSQLファイルにしておけば、目的に合った操作を選べます。

テーブルを作り直すときは管理用アカウントを使い、作成後の確認には学習用ユーザーを使います。第4章のCプログラムでも、この学習用ユーザーを引き続き利用します。

MySQLへログインした状態でもSQLファイルを読み込める

すでにMySQLクライアントへログインしている場合は、sourceを使って確認用ファイルを実行できます。

mysql>が表示されている画面で、次を入力してください。

source C:/c-mysql/chap03/check_inventory.sql;

sourceは、MySQLクライアントがSQLファイルを読み込むためのコマンドです。ここではWindowsのパスを、区切り文字に/を使って指定しています。

実行すると、先ほどと同じように、アカウント、データベース、テーブル一覧、商品データ、履歴件数が表示されます。

読み込まれるのは、MySQLクライアントを動かしているコンピューター上のファイルです。SQLファイルをMySQL Serverのデータ保存フォルダーへ移動する必要はありません。

うまく実行できないときに確認すること

エラーが出たときは、表示されたエラー文と行番号を手がかりに、SQLファイルを修正しましょう。

初期化用ファイルを再実行すると、その時点の商品データや在庫履歴も再び削除されます。単に現在の状態を見たいときは、check_inventory.sqlを使ってください。

第4章へ向けてSQLファイルを残しておこう

chap03には、次の2つのファイルがそろいました。

これからCプログラムを作成している途中で、データの状態を確認したくなったらcheck_inventory.sqlを利用できます。学習用データを初期状態へ戻したいときには、削除される内容を確認してからinit_inventory.sqlを実行しましょう。

次の第4章では、ここまでに準備したinventory_dbと学習用ユーザーを使い、CプログラムからMySQLへ接続していきます。

Follow me!

おすすめ記事

C言語＋MySQL入門｜商品を変更・削除する C言語＋MySQL入門｜商品を登録・表示する C言語＋MySQL入門｜接続ユーザーの権限を確認する C言語＋MySQL入門｜商品テーブルを作成する C言語＋MySQL入門｜データベースを作成する C言語の基本｜保存用ディレクトリーを作成する C言語＋MySQL入門｜在庫履歴テーブルを作成する C言語のきほん｜プログラム保存用ディレクトリーの作成と実行