C言語＋MySQL入門｜商品テーブルを作成する

C言語＋MySQL入門｜商品テーブルを作成する

前の記事では、テーブルの行と列、そして商品を一意に識別する主キーについて復習しました。今回は、その設計をSQLで表し、inventory_dbに商品テーブルを作成します。

商品テーブルの名前はproductsです。商品ID、商品名、単価、在庫数、登録日時の5つの列を用意し、在庫管理アプリで使う商品の基本情報を保存できるようにします。

この記事では、次の内容を学びます。

• 商品情報に合ったデータ型を選ぶ。
• CREATE TABLEでproductsを作成する。
• 主キー、自動採番、初期値の役割を理解する。
• 単価や在庫数に必要な制約を設定する。
• 作成したテーブルの構造を確認する。

今回は、データを保存する入れ物を作るところまで進めます。実際の商品登録は、後の記事でINSERTを学ぶときに行いましょう。

商品テーブルに保存する情報を決めよう

productsでは、1行で1つの商品を表します。

たとえば、ノートという商品には、商品を識別するID、商品名、単価、現在の在庫数、登録した日時が必要です。これらを、それぞれ独立した列として用意します。

ここでは、商品ごとに価格の通貨を変えず、アプリ全体で同じ通貨を使うものとします。また、在庫は個数で数え、小数の数量は扱いません。

C言語の構造体を思い浮かべると、列はメンバー、1行は1つの商品データに近いイメージです。ただし、MySQLのテーブルでは、データ型に加えて、重複や不正な値を防ぐためのルールも設定できます。

図1：商品情報を5つの列に分ける

この図から分かること

商品情報をひとまとまりの文章として保存するのではなく、意味ごとに列を分けて管理します。

単価と在庫数を別々の列にしておけば、価格だけを変更したり、在庫の少ない商品を検索したりできます。列を適切に分けることが、後のSQL操作を分かりやすくする第一歩です。

作成先のデータベースを確認しよう

MySQLクライアントから管理作業用のアカウントで接続し、mysql>が表示されている状態で操作します。

まず、作成先のinventory_dbを選択しましょう。

USE inventory_db;

実行結果の例

Database changed

続けて、現在選択しているデータベースを確認します。

SELECT DATABASE() AS current_database;

実行結果の例

+------------------+
| current_database |
+------------------+
| inventory_db     |
+------------------+
1 row in set (0.00 sec)

inventory_dbと表示されれば準備完了です。

USEによる選択は、現在の接続に対して有効です。MySQLクライアントを終了して接続し直した場合は、あらためて選択してください。

CREATE TABLEでproductsを作成しよう

テーブルを作成する命令はCREATE TABLEです。

テーブル名に続けて丸括弧を書き、その中に列の定義や制約を並べます。それぞれの定義はカンマで区切り、最後の定義にはカンマを付けません。

以下が、今回使用するSQLの全体です。ファイルとして保存する場合の名前はcreate_products.sqlとします。まずはMySQLクライアントで実行してみましょう。SQLファイルの保存と整理は、3.12であらためて扱います。

商品情報を保存するproductsを作成するSQL

ファイル名： create_products.sql

USE inventory_db;

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;

実行結果の例

Query OK, 0 rows affected (0.03 sec)

処理時間は環境によって異なります。

Query OKは、SQLが正常に実行されたことを示します。0 rows affectedと表示されていても問題ありません。今回はテーブルを作成しただけなので、商品データはまだ1件も登録されていません。

入力中に表示されるmysql>や->は、MySQLクライアントのプロンプトです。SQLの一部として入力する必要はありません。

各列のデータ型と役割を見てみよう

product_idは商品を識別する整数

product_idにはINTを指定しています。INTは整数を保存するデータ型です。

さらに、次の設定を組み合わせています。

AUTO_INCREMENTとPRIMARY KEYは、異なる役割を持ちます。

AUTO_INCREMENTは番号の割り当てを担当し、PRIMARY KEYはIDの重複を防いで、商品を一意に識別できるようにします。

自動採番されるIDには欠番が生じることがあります。商品IDは商品を識別するための値なので、番号が飛んでいても、それだけで異常というわけではありません。

Cプログラムから商品を登録するときも、通常はプログラム側で次の番号を計算せず、MySQLに採番を任せます。

product_nameは最大100文字の商品名

product_nameにはVARCHAR(100)を指定しています。

VARCHARは、長さが変わる文字列を保存するデータ型です。今回の100は、最大文字数を表します。

たとえば、短い商品名と長い商品名を同じ列に保存できます。

• ノート
• ボールペン
• A4サイズのクリアファイル

ここで、C言語の文字配列との違いを押さえておきましょう。

VARCHAR(100)の100は文字数であり、C言語の配列で確保するバイト数とは異なります。

日本語などをUTF-8で扱う場合、1文字が複数バイトになります。後でCプログラムから商品名を読み込むときには、文字数とバイト数を区別してバッファを用意します。

また、NOT NULLはNULLを防ぐ設定です。長さ0の文字列や、空白だけの文字列まで禁止するものではありません。商品名が適切に入力されているかどうかは、アプリ側でも確認します。

今回は商品名に一意制約を付けていないため、同じ名前の商品を複数登録することは可能です。それぞれの商品はproduct_idで区別します。

priceは単価を正確に保存する数値

priceにはDECIMAL(10, 2)を指定しています。

DECIMALは、指定した桁数の範囲で10進数を正確に保存するデータ型です。金額のように、保存する値の精度を大切にしたい場面に向いています。DECIMAL, NUMERIC

括弧の中の数値には、次の意味があります。

そのため、整数部分には最大8桁、小数部分には2桁を保存できます。小数点や符号は、この桁数には含みません。

今回の設計では負の値を禁止するため、保存できる単価の範囲は0.00から99999999.99までです。180円の商品であれば、180.00という値として扱えます。

なお、小数点以下3桁以上の値を渡したときに、必ずエラーになるという意味ではありません。列の桁数に合わせて値が変換されるため、入力値の桁数もアプリ側で確認する必要があります。

stockは現在の在庫数

stockにはINTを指定し、商品の在庫を整数で管理します。

今回の設計では、次の2つのルールを設定しています。

• 在庫数を指定しなかった場合は0にする。
• 負の在庫数を保存できないようにする。

省略時の値はDEFAULT 0で指定します。

たとえば、商品を登録するSQLでstockの指定を省略すると、在庫数には0が入ります。

ただし、DEFAULTは不正な値を自動修正する機能ではありません。負の数を指定したときに0へ置き換える設定ではないことに注意しましょう。

また、この列で管理するのは現在の在庫数です。いつ、何個入庫したかといった履歴は、次の記事で作成するstock_historyに保存します。

created_atは商品を登録した日時

created_atにはDATETIMEを指定しています。DATETIMEは、日付と時刻をまとめて保存するデータ型です。

DEFAULT CURRENT_TIMESTAMPを指定すると、商品登録時にcreated_atを省略した場合、その時点の日時が設定されます。

設定されるのは、テーブルを作成した日時ではなく、商品を登録した日時です。

今回はON UPDATE CURRENT_TIMESTAMPを指定していません。そのため、後から商品名や在庫数を変更しても、それだけでcreated_atが現在日時に更新されることはありません。

登録した時点の日時を保持するための設計です。

制約で商品の基本ルールを守ろう

テーブルに設定する制約は、保存できるデータのルールです。

Cプログラムで入力を確認することも大切ですが、データベースにも制約を設定しておくと、別のプログラムやMySQLクライアントから操作した場合にも同じルールを適用できます。

NOT NULLで値の未設定を防ぐ

今回の5つの列には、すべてNOT NULLを指定しています。

NULLは、値がない、または不明であることを表す特別な状態です。数値の0や、長さ0の文字列とは異なります。

たとえば、在庫数0は在庫がないと分かっている状態です。一方、在庫数がNULLの場合は、在庫数そのものが分からない状態になります。

今回のアプリでは、そのような未確定の在庫数を扱わないため、stockをNOT NULLにしています。

なお、NOT NULLを指定していても、自動採番や初期値が用意された列は、登録時に列の指定を省略できます。

CHECKで単価と在庫数の範囲を制限する

今回のSQLには、次の2つのCHECK制約を設定しています。

CONSTRAINTの後ろに書いているのは、制約を識別するための名前です。エラーが起きたときに、どのルールに違反したのかを判断しやすくなります。

MySQL 8.4では、このように定義したCHECK制約が検査されます。条件を満たさない負の単価や在庫数を保存しようとすると、通常のINSERTやUPDATEはエラーになります。dev.mysql.com

また、CHECKだけではNULLを禁止できません。値が必ず存在することはNOT NULL、値の範囲はCHECKというように、役割を分けて指定しています。

図2：自動設定と制約が商品データを支える

この図から分かること

自動採番やDEFAULTは、値を省略したときの動作を決める設定です。一方、PRIMARY KEY、NOT NULL、CHECKは、保存するデータが守るべきルールです。

この2つを組み合わせることで、登録時の入力を簡潔にしながら、不適切なデータが保存されることを防ぎます。

テーブル全体の設定も確認しよう

CREATE TABLEの閉じ括弧の後ろには、テーブル全体に関する設定を書いています。

ストレージエンジンは、データの保存やアクセスを担当する仕組みです。InnoDBはトランザクションや外部キーを利用でき、これから作る在庫管理アプリでも使います。

文字セットと照合順序は、inventory_dbと同じ設定にしています。今回の環境ではデータベースから引き継ぐこともできますが、作成SQLを読んだときに設定が分かるよう、明示しています。

作成したテーブルを確認しよう

SQLが成功したら、テーブルが存在することと、列が意図したとおりに定義されていることを確認します。

SHOW TABLESでテーブルの存在を確認する

SHOW TABLES;

実行結果の例

+------------------------+
| Tables_in_inventory_db |
+------------------------+
| products               |
+------------------------+
1 row in set (0.00 sec)

productsが表示されれば、選択中のデータベースに商品テーブルが作成されています。

すでにほかのテーブルが存在する場合は、それらも一覧に表示されます。

DESCRIBEで列の構造を確認する

DESCRIBE products;

実行結果の例

+--------------+---------------+------+-----+-------------------+-------------------+
| Field        | Type          | Null | Key | Default           | Extra             |
+--------------+---------------+------+-----+-------------------+-------------------+
| product_id   | int           | NO   | PRI | NULL              | auto_increment    |
| product_name | varchar(100)  | NO   |     | NULL              |                   |
| price        | decimal(10,2) | NO   |     | NULL              |                   |
| stock        | int           | NO   |     | 0                 |                   |
| created_at   | datetime      | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+--------------+---------------+------+-----+-------------------+-------------------+
5 rows in set (0.00 sec)

表示形式によって、大文字と小文字や括弧の有無などが異なる場合があります。

各項目の意味は次のとおりです。

product_idのKeyがPRIになっていること、Extraにauto_incrementがあることを確認しましょう。

ここで注意したいのは、Default欄のNULLです。今回のproduct_nameやpriceでは、これは明示的な既定値がないことを示しており、NULLを保存できるという意味ではありません。NULLを許可するかどうかは、Null欄で確認します。

created_atのDEFAULT_GENERATEDは、既定値に式を使っていることを示します。ほかの列から値を計算する生成列という意味ではありません。

SHOW CREATE TABLEで制約まで確認する

DESCRIBEでは、CHECK制約の条件までは表示されません。テーブル全体の定義を確認したいときは、SHOW CREATE TABLEを使います。

SHOW CREATE TABLE products\G

末尾の\Gは、MySQLクライアントで結果を縦方向に表示するための指定です。この場合、後ろにセミコロンを追加する必要はありません。

実行結果の例

*************************** 1. row ***************************
       Table: products
Create Table: 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 CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

MySQLが整形した定義が表示されるため、入力したSQLと表記が完全に一致するとは限りません。

確認するポイントは、次の4つです。

• product_idが主キーになっている。
• priceとstockにCHECK制約がある。
• ストレージエンジンがInnoDBになっている。
• 文字セットと照合順序が指定どおりになっている。

商品がまだ登録されていないことを確認する

最後に、productsに保存されている行数を調べてみましょう。

SELECT COUNT(*) AS product_count
FROM products;

実行結果の例

+---------------+
| product_count |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

COUNT(*)は、テーブルの行数を数える関数です。AS product_countは、結果に表示する列名を指定しています。

新しく作成した直後なので、商品数は0です。列や制約が定義されていても、INSERTを実行するまでは商品データは追加されません。

図3：テーブルの存在・構造・商品数を確認する

この図から分かること

テーブルが存在すること、構造が正しいこと、商品データが入っていることは、それぞれ別の確認項目です。

今回の到達点は、5つの列と必要な制約を持つproductsが作成され、商品を登録できる状態になっていることです。商品数が0でも、テーブル作成は正常に完了しています。

テーブル作成でエラーが出たときは

CREATE TABLEが失敗した場合は、エラーメッセージと入力内容を確認しましょう。

特に、列の定義の間にカンマがない場合や、最後の制約の後ろに余分なカンマがある場合は、構文エラーになりやすいので注意してください。

productsがすでに存在すると表示された場合は、すぐに削除せず、まず現在の定義を確認します。

CREATE TABLEにIF NOT EXISTSを付ける方法もありますが、既存のテーブルを今回の設計に修正する機能ではありません。定義が異なっていても、その違いを直してくれるわけではないため、構造の確認が必要です。

次は在庫の変化を記録するテーブルへ

productsには、商品の基本情報と現在の在庫数を保存できるようになりました。

ただし、現在の在庫数だけでは、いつ入庫したのか、何個出庫したのかまでは分かりません。

次の記事では、その変化を記録するstock_historyを作成します。商品そのものの情報と、入出庫の記録を分けることで、在庫管理アプリの土台を整えていきましょう。

Follow me!

おすすめ記事

Java超｜メソッドのオーバーライド Java超｜クラスとメソッドのしくみ Java道｜メソッドのオーバーライド Java道｜メソッド（必殺技）を作って使う C言語の基本｜ポインタが変数を指す C言語＋MySQL入門｜在庫履歴テーブルを作成する Java道｜インターフェイスで共通ルールを作る Java超｜拡張for文とソート