何かやってみるブログ

興味をもったこと、趣味のこと、技術について色々書きます。

[Railsメモ] serializeされるカラムに保存された配列はMySQLでどのように保存されるのか

気になって自分のRailsアプリで試した時のメモです。

テーブル構造

mysql> describe records;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| store_name     | varchar(255) | NO   |     | NULL    |                |
| user_id        | bigint(20)   | NO   | MUL | NULL    |                |
| purchase_price | int(11)      | NO   |     | NULL    |                |
| purchase_date  | date         | NO   |     | NULL    |                |
| created_at     | datetime     | NO   |     | NULL    |                |
| updated_at     | datetime     | NO   |     | NULL    |                |
| label_id       | bigint(20)   | NO   | MUL | NULL    |                |
| tags           | text         | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

試す

文字列の配列が保存された場合

準備

root@50a84ee0394e:/app# bin/rails c
Running via Spring preloader in process 320
Loading development environment (Rails 5.2.6)
[1] pry(main)> Record.first
   (0.6ms)  SET NAMES utf8,  @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'),  @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
  Record Load (0.4ms)  SELECT  `records`.* FROM `records` ORDER BY `records`.`id` ASC LIMIT 1
+----+------------+---------+----------------+---------------+---------------------------+---------------------------+----------+------+
| id | store_name | user_id | purchase_price | purchase_date | created_at                | updated_at                | label_id | tags |
+----+------------+---------+----------------+---------------+---------------------------+---------------------------+----------+------+
| 1  | store1     | 1       | 100            | 2021-09-07    | 2021-09-21 16:37:18 +0900 | 2021-09-21 16:37:18 +0900 | 7        |      |
+----+------------+---------+----------------+---------------+---------------------------+---------------------------+----------+------+
1 row in set
[2] pry(main)> tags = %w(経費 コンビニ)
=> ["経費", "コンビニ"]
[3] pry(main)> Record.first.update(tags: tags)
  Record Load (1.0ms)  SELECT  `records`.* FROM `records` ORDER BY `records`.`id` ASC LIMIT 1
   (0.5ms)  BEGIN
  User Load (0.9ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
  Label Load (0.3ms)  SELECT  `labels`.* FROM `labels` WHERE `labels`.`id` = 7 LIMIT 1
  Record Update (4.4ms)  UPDATE `records` SET `tags` = '---\n- 経費\n- コンビニ\n', `updated_at` = '2021-09-21 07:38:51' WHERE `records`.`id` = 1
   (2.9ms)  COMMIT
=> true

結果

mysql> set charset utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select tags  from records where id = 1\G
*************************** 1. row ***************************
tags: ---

- 経費
- コンビニ

1 row in set (0.00 sec)

独自クラスのオブジェクトの配列が保存された場合

準備

[6] pry(main)> tag1 = Tag.new("red", "経費")
=> #<Tag:0x0000561147d08d20 @color="red", @name="経費">
[7] pry(main)> tag2 = Tag.new("blue", "コンビニ")
=> #<Tag:0x0000561148352d70 @color="blue", @name="コンビニ">
[8] pry(main)> tags = [tag1, tag2]
=> [#<Tag:0x0000561147d08d20 @color="red", @name="経費">, #<Tag:0x0000561148352d70 @color="blue", @name="コンビニ">]
[9] pry(main)> Record.second.update(tags: tags)
   (3.6ms)  SET NAMES utf8,  @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'),  @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
  Record Load (0.9ms)  SELECT  `records`.* FROM `records` ORDER BY `records`.`id` ASC LIMIT 1 OFFSET 1
   (0.3ms)  BEGIN
  User Load (2.1ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 4 LIMIT 1
  Label Load (1.0ms)  SELECT  `labels`.* FROM `labels` WHERE `labels`.`id` = 10 LIMIT 1
  Record Update (7.0ms)  UPDATE `records` SET `tags` = '---\n- !ruby/object:Tag\n  color: red\n  name: 経費\n- !ruby/object:Tag\n  color: blue\n  name: コンビニ\n', `updated_at` = '2021-09-21 08:32:20' WHERE `records`.`id` = 2
   (8.6ms)  COMMIT
=> true
[10] pry(main)> Record.second.tags
  Record Load (0.9ms)  SELECT  `records`.* FROM `records` ORDER BY `records`.`id` ASC LIMIT 1 OFFSET 1
=> [#<Tag:0x0000561148d446d0 @color="red", @name="経費">, #<Tag:0x0000561148d44180 @color="blue", @name="コンビニ">]

結果

mysql> select tags from records where id = 2\G
*************************** 1. row ***************************
tags: ---
- !ruby/object:Tag
  color: red
  name: 経費
- !ruby/object:Tag
  color: blue
  name: コンビニ

1 row in set (0.00 sec)

参考にした記事

qiita.com