とある日

Advent Calendar2020に参加させていただきました。

MySQL Advent Calendar 2020とRDBMS-GIS(MySQL,PostgreSQLなど) Advent Calendar 2020の2回参加しました。

初めは、参加する予定ではなかったですが枠が空いていたので思い切って参加しました。

去年の自分からすると考えられないことです。

Twitterで、その記事に対するとてもためになる意見がありましたので、

振り返りも兼ねて、試してみようかと思います。

今日は、DBで日付にはDATE型？VARCHAR型どっちの日。

Twitterの投稿からアイデアを得た型についての記事。

アドベントカレンダー初投稿の記事ですね。

ストレージサイズは「INFORMATION_SCHEMA TABLES Table」でなく「ALTER TABLE テーブル名 ENGINE INNODB」後のibdファイルサイズを見たほうがよいかな。手元だとdateが64MB、varcharが84MBになった。＞今日は、DBで日付にはDATE型？VARCHAR型どっちの日。 - 今日はなにの日。 https://t.co/1Qsdr7BiRP
— atsuizo (@atsuizo) 2020年12月14日

ストレージサイズの疑問だったことについて、回答があったので試してみる。

ALTER TABLE 前 ibdファイルサイズ

[root@localhost hobby]# ls -l
合計 173616
-rw-r-----. 1 mysql mysql 62914560 12月  9 16:10 date_version.ibd
-rw-r-----. 1 mysql mysql 83886080 12月  9 16:10 var_version.ibd

確かに、アドベントカレンダーの記事結果とは違う結果となった。

ALTER TABLE

巨大なサイズのテーブルに対するALTER TABLE実行時間の概算方法

ALTER TALBEの仕様ついてはこちらの記事が詳しく解説されている。

mysql> ALTER TABLE date_version  ENGINE INNODB;
Query OK, 0 rows affected (15.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE var_version  ENGINE INNODB;
Query OK, 0 rows affected (20.88 sec)
Records: 0  Duplicates: 0  Warnings: 0

var_versionとdate_versionの2つのテーブルに対して、ALTER TALBEを実行する。

ALTER TABLE 後 ibdファイルサイズ

[root@localhost hobby]# ls -l
合計 181808
-rw-r-----. 1 mysql mysql 67108864 12月 10 08:38 date_version.ibd
-rw-r-----. 1 mysql mysql 88080384 12月 10 08:39 var_version.ibd

前と後で少しサイズが変動しているのがわかる。

そして、var_versionテーブルの方がサイズが大きい。

mysql> SELECT       TABLE_NAME,     TABLE_ROWS,     DATA_FREE,     INDEX_LENGTH,     DATA_LENGTH,     AVG_ROW_LENGTH FROM      information_schema.tables   WHERE     table_schema="hobby" AND (table_name = 'var_version' or table_name = 'date_version')  ORDER BY     (data_length+index_length) DESC\G;
*************************** 1. row ***************************
    TABLE_NAME: var_version
    TABLE_ROWS: 997425
     DATA_FREE: 4194304
  INDEX_LENGTH: 0
   DATA_LENGTH: 48840704
AVG_ROW_LENGTH: 48
*************************** 2. row ***************************
    TABLE_NAME: date_version
    TABLE_ROWS: 998547
     DATA_FREE: 4194304
  INDEX_LENGTH: 0
   DATA_LENGTH: 35192832
AVG_ROW_LENGTH: 35
2 rows in set (0.00 sec)

記事で行っていた方法でサイズを確認。

記事の内容と変わっていてdate_versionテーブルのほうがDATA_LENGTH: 35192832で少ないことがわかる。

ためになるね～。

今日は、MySQL8.0 GISを使って距離と三角形の面積求めてみたの日。

私はつい「地球上の座標を表現できる」推しの話ばかりをしてしまいますが、こういう使い方も面白いですね。例示されている三角形の面積とか普通にやったら結構面倒だし。これ、６つの引数を取るストアドにしても面白いかも。 https://t.co/gKtyJCyDBm #mysql_jp
— 坂井恵(SAKAI Kei) (@sakaik) 2020年12月20日

ってことで、距離を求めるストアドプロシージャと三角形の面積を求めるストアドプロシージャにしたいと思います。

2点間の距離ストアドプロシージャ

CREATE  PROCEDURE `Distance_between_two_points`(IN x1 INT(3),y1 INT(3),x2 INT(3),y2 INT(3))
BEGIN
SELECT 
    st_distance(
        st_geomfromtext(CONCAT('point(',x2,' ',y1,')')),
        st_geomfromtext(CONCAT('point(',x2,' ',y2,')'))
    ) as `points`;
END

シングルクォーテーションをダブルクォーテーションのミスで苦戦したけど完成。

mysql> CALL `Distance_between_two_points`(-4,1,2,2);
+--------+
| points |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

三角形の面積ストアドプロシージャ

CREATE

CREATE PROCEDURE `Area_of_a_triangle` (IN x1 INT(3),y1 INT(3),x2 INT(3),y2 INT(3),x3 INT(3),y3 INT(3))
BEGIN
    SELECT 
        ST_Area(
            ST_GeomFromText(
                CONCAT('Polygon((',x1,' ',y1,',',x2,' ',y2,',',x3,' ',y3,',',x1,' ',y1,'))')
            )
        ) as `area`;
END
;

CALL

mysql> CALL `Area_of_a_triangle`(0, 0,3, 1,2, 3);
+------+
| area |
+------+
|  3.5 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

〆

アドベントカレンダー参加は初めてで、ネタに悩んだりしましたが面白い内容が書けたかなと思います。

アドベントカレンダー記事を読んでいただきありがとうございます。

Twitterで意見をくださった有識者の方々とても勉強になりました、ありがとうございます。

MySQL GISは初めて使ってみたので今後とも、MySQL の機能を使って記事にしたいと思います。

今日はなにの日。

気になったこと勉強になったことのメモ。

今日は、2020年アドベントカレンダーその後の日。

目次