MySQLの列をJSON形式で更新する方法をわかりやすく解説

MySQLの列をJSON形式で更新する方法

データベースにJSONのような形式でレコードを保存・表示したい場合、MySQLのconcat()関数を使うと簡単に実現できます。この記事では、サンプルテーブルを作成し、concat()を使って列をJSON形式の文字列で更新するまでの一連の手順を、実行例とともに解説します。

1. サンプルテーブルの作成

まず、以下のコマンドでテーブルを作成します。

mysql> create table DemoTable1373
    -> (
    -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> StudentDetails text
    -> );
Query OK, 0 rows affected (0.86 sec)

このテーブルは、自動採番される主キー「StudentId」と、学生情報を格納するTEXT型の「StudentDetails」の2つの列を持っています。

2. レコードの挿入

次に、insertコマンドでレコードを追加します。今回はStudentDetails列には何も値を設定せずに登録します。

mysql> insert into DemoTable1373 values();
Query OK, 1 row affected (0.27 sec)
mysql> insert into DemoTable1373 values();
Query OK, 1 row affected (0.08 sec)
mysql> insert into DemoTable1373 values();
Query OK, 1 row affected (0.18 sec)

3. 登録内容の確認

select文ですべてのレコードを表示してみましょう。

mysql> select * from DemoTable1373;

実行結果は以下の通りです。

+-----------+----------------+
| StudentId | StudentDetails |
+-----------+----------------+
|        1 | NULL           |
|        2 | NULL           |
|        3 | NULL           |
+-----------+----------------+
3 rows in set (0.00 sec)

StudentDetails列がすべてNULLになっていることが確認できます。

4. concat()を使ってJSON形式で更新する

以下が、MySQLのフィールドをJSON形式の文字列で更新するクエリです。concat()の中で複数の文字列と数値を連結し、波括弧で囲んだJSON風の文字列を組み立てています。

mysql> update DemoTable1373 set StudentDetails=concat("{" "StudentName:", " John ,"," StudentAge:", 21,","," StudentCountryName: "," US","} ");
Query OK, 3 rows affected (0.14 sec)
Rows matched: 3  Changed: 3 Warnings: 0

5. 更新結果の確認

もう一度テーブルのレコードを確認しましょう。

mysql> select * from DemoTable1373;

実行結果：

+-----------+---------------------------------------------------------------+
| StudentId | StudentDetails                                                  |
+-----------+---------------------------------------------------------------+
|        1 | {StudentName: John , StudentAge:21, StudentCountryName:  US}  |
|        2 | {StudentName: John , StudentAge:21, StudentCountryName:  US}  |
|        3 | {StudentName: John , StudentAge:21, StudentCountryName:  US}  |
+-----------+---------------------------------------------------------------+
3 rows in set (0.00 sec)

すべてのレコードのStudentDetails列が、JSON形式のような文字列で一括更新されました。

補足：より正確なJSONを扱うならJSON型とJSON_OBJECT()がおすすめ

上記の方法はTEXT型の列にJSON風の文字列を保存しているだけなので、MySQLによる構文チェックは行われません。MySQL 5.7以降を使用している場合は、JSON型の列を定義すると、不正なJSONの保存を防止でき、JSON_EXTRACT()などの関数で特定のキーの値を効率的に取り出せるようになります。

mysql> CREATE TABLE Students (
    ->   StudentId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   StudentDetails JSON
    -> );

また、正しいJSON文字列を生成したい場合は、JSON_OBJECT()関数を使うとキーと値をそのまま渡すだけで済むため、concat()で引用符を組み立てるよりも安全で読みやすくなります。

mysql> UPDATE DemoTable1373
    -> SET StudentDetails = JSON_OBJECT('StudentName','John','StudentAge',21,'StudentCountryName','US');

用途に応じて、手軽なconcat()方式と、検証機能を持つJSON型・JSON_OBJECT()方式を使い分けるのがおすすめです。