Database – Saoline Tips

MySQL JSONデータにインデックスをつける

投稿日: 2023-02-23 投稿者: Hisa

ここで教えて頂きました。

show index で確認してみる。

mysql> show index from log_livemap_tonoff\G
*************************** 1. row ***************************
        Table: log_livemap_tonoff
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: ID
    Collation: A
  Cardinality: 9525
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: log_livemap_tonoff
   Non_unique: 1
     Key_name: log_lovemap_tonoff_TM_ADD
 Seq_in_index: 1
  Column_name: TM_ADD
    Collation: A
  Cardinality: 6746
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 3. row ***************************
        Table: log_livemap_tonoff
   Non_unique: 1
     Key_name: depiata
 Seq_in_index: 1
  Column_name: NULL
    Collation: A
  Cardinality: 481
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: (cast(json_unquote(json_extract(`TXT_JSON`,_utf8mb4\'$.depiata\')) as char(3) charset utf8mb4) collate utf8mb4_bin)
*************************** 4. row ***************************
        Table: log_livemap_tonoff
   Non_unique: 1
     Key_name: arviata
 Seq_in_index: 1
  Column_name: NULL
    Collation: A
  Cardinality: 449
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: (cast(json_unquote(json_extract(`TXT_JSON`,_utf8mb4\'$.arviata\')) as char(3) charset utf8mb4) collate utf8mb4_bin)
*************************** 5. row ***************************
        Table: log_livemap_tonoff
   Non_unique: 1
     Key_name: updated
 Seq_in_index: 1
  Column_name: NULL
    Collation: A
  Cardinality: 7996
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: (cast(json_unquote(json_extract(`TXT_JSON`,_utf8mb4\'$.updated\')) as char(20) charset utf8mb4) collate utf8mb4_bin)
*************************** 6. row ***************************
        Table: log_livemap_tonoff
   Non_unique: 1
     Key_name: flnum
 Seq_in_index: 1
  Column_name: NULL
    Collation: A
  Cardinality: 7996
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: (cast(json_unquote(json_extract(`TXT_JSON`,_utf8mb4\'$.updated\')) as char(12) charset utf8mb4) collate utf8mb4_bin)
6 rows in set (0.00 sec)

PHP PDO MySQLの検索結果のキャスト

投稿日: 2023-01-25 投稿者: Hisa

PHP PDO MySQLでセレクトした数値結果から判別するプログラムで、=== で厳密型チェックしてる場合、MySQLがTINYINTのフィールドを読み込む際、環境によって(Windows or Linux, PHP7 or PHP8)はうまく読み込めなくなったのでキャストしてみると解決しました。

< コード例 >

SQL

$SQL = <<<EOM
SELECT IS_MAILHTML
FROM m_user_aviation
WHERE 
  ID = :USERID     
EOM;

これはだめ

// パラメータセット //
  $stmt = $dbh->prepare($SQL);
  $stmt->bindvalue(':USERID', $userid, PDO::PARAM_STR);
  $stmt->execute();


  // 格納 //
  //$array = array();
  $mailtype= "";
  while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
   //var_dump ($row["IS_MAILHTML"]) ;
   $mailtype = $row["IS_MAILHTML"] === 1 ? "html" : "text";  
  }

これでオーケー

// パラメータセット //
  $stmt = $dbh->prepare($SQL);
  $stmt->bindvalue(':USERID', $userid, PDO::PARAM_STR);
  $stmt->execute();


  // 格納 //
  //$array = array();
  $mailtype= "";
  while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
   //var_dump ($row["IS_MAILHTML"]) ;
   $mailtype = (int)$row["IS_MAILHTML"] === 1 ? "html" : "text";   // 必ずintにキャスト
  }

[商品価格に関しましては、リンクが作成された時点と現時点で情報が変更されている場合がございます。]

初めてのPHP、MySQL、JavaScript　＆　CSS [ ロビン・ニクソン ]
価格：3960円（税込、送料無料) (2023/2/11時点)

楽天で購入

MySQL8 1億レコードを試す

投稿日: 2021-07-27 投稿者: Hisa

飛行機の航跡データを1分間隔でAPIで取得して追加してます。

1回の追加レコード数が3500から9000位、検索を頻繁に行うカレントテーブルのレコードが多すぎると、検索、更新で速度パフォーマンスが劣化するので、カレントは4日分、4日より古いのは旧テーブルにMySQLのイベントで追加していってます。(4日より多い期間の検索は3テーブルをUNION ALLでつないだ仮想テーブルを SELECT DISTINCT してます)

ユーザーテーブルは利用中ユーザーのライブ地図、ビュー地図のAPI応答を保存していて、主にパストトラック線の描画、ADS-B応答の停止状況のチェックに使ってます。
検索結果にUNIONでマージされるので、更新インターバルが異なるデータからのビュー地図の表示が不自然になり、業務用途では統一性に欠け、目的不明瞭でばつな気がしますが、ホビー用途としては、全ユーザーの利用結果を含めたプレイバック再現が出来て、オープンデータ利用による機能向上の見本みたいなのになるかと思ってます。

旧テーブルはinnoDBのバッファープールを圧迫するといけないのでエンジンはMyISAMにしてます。

カレントテーブルのレコード数は約2200万をキープ、旧テーブルは現在、約6300万で増え続けてるので、足して1億を超えた時に、パフォーマンスが劇的に劣化するようなことがあれば、保存期間を設定して古い追加時刻から削除していくつもりです。

検索処理でのスロークエリの発生状況も気になるところなので、Seleniumとか使ってテストしていきます。

< カレント履歴テーブル >

1分間隔で全世界のADS-B応答を取得した結果を蓄積
保存期間 : 4日

CREATE TABLE `h_pasttrack_all` (

	`CD_ICAO24` CHAR(6) NOT NULL DEFAULT '' COMMENT 'ICAO24機材識別コード' COLLATE 'utf8_general_ci',
	`NM_CALLSIGN` CHAR(10) NOT NULL DEFAULT '' COMMENT '機材コールサイン' COLLATE 'utf8_general_ci',
	`UTM_POSITION` INT(11) NULL DEFAULT '0' COMMENT 'ADS-B送信時刻',
	`UTM_LASTCONTACT` INT(11) NOT NULL DEFAULT '0' COMMENT '最終取得時刻',
	`LAT` FLOAT NOT NULL DEFAULT '-500' COMMENT '緯度',
	`LON` FLOAT NOT NULL DEFAULT '-500' COMMENT '経度',
	`ALT_BARO` FLOAT NOT NULL DEFAULT '0' COMMENT '気圧高度 (m)',
	`ALT_GEO` FLOAT NOT NULL DEFAULT '0' COMMENT '対地高度 (m)',
	`SPEED_GEO` FLOAT NOT NULL DEFAULT '0' COMMENT '対地速度 (m/s)',
	`DEG_TRUE` FLOAT NOT NULL DEFAULT '-1' COMMENT '飛行方向角',
	`RT_VERTICAL` FLOAT NOT NULL DEFAULT '-500' COMMENT '上昇下降率 (m/s)',
	`IS_ONGROUND` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '地上 or 空中フラグ',
	`NM_SQUAWK` CHAR(4) NOT NULL DEFAULT '' COMMENT 'スコークコード' COLLATE 'utf8_general_ci',
	`CD_ACTYPE` CHAR(6) NOT NULL DEFAULT '' COMMENT 'ICAO機材コード' COLLATE 'utf8_general_ci',
	`TM_ADD` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '追加時刻',
	INDEX `h_pasttrack_all_LAT` (`LAT`) USING BTREE,
	INDEX `h_pasttrack_all_LON` (`LON`) USING BTREE,
	INDEX `h_pasttrack_all_IS_ONGROUND` (`IS_ONGROUND`) USING BTREE,
	INDEX `h_pasttrack_all_INM_SQUAWK` (`NM_SQUAWK`) USING BTREE,
	INDEX `h_pasttrack_all_UTM_POSITION` (`UTM_POSITION`) USING BTREE,
	INDEX `h_pasttrack_all_TM_ADD` (`TM_ADD`) USING BTREE,
	INDEX `h_pasttrack_all_CD_ICAO24` (`CD_ICAO24`) USING BTREE,
	INDEX `h_pasttrackall_NM_CALLSIGN` (`NM_CALLSIGN`) USING BTREE,
	INDEX `h_pasttrack_all_CD_ICAO24_TM_ADD` (`CD_ICAO24`, `TM_ADD`) USING BTREE,
	INDEX `h_pasttrack_all_LAT_LON_TM_ADD` (`LAT`, `LON`, `TM_ADD`) USING BTREE
)
COMMENT='OpenSky全機応答航跡履歴'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

< 旧履歴テーブル >

カレントテーブルの4日より前のレコードを蓄積
保存期間 : 現時点では未設定、レコードは増え続ける


        CREATE TABLE `h_pasttrack_old` (
	`CD_ICAO24` CHAR(6) NOT NULL DEFAULT '' COMMENT 'ICAO24機材識別コード' COLLATE 'utf8_general_ci',
	`NM_CALLSIGN` CHAR(10) NOT NULL DEFAULT '' COMMENT '機材コールサイン' COLLATE 'utf8_general_ci',
	`UTM_POSITION` INT(10) NULL DEFAULT '0' COMMENT 'ADS-B送信時刻',
	`UTM_LASTCONTACT` INT(10) NOT NULL DEFAULT '0' COMMENT '最終取得時刻',
	`LAT` FLOAT NOT NULL DEFAULT '-500' COMMENT '緯度',
	`LON` FLOAT NOT NULL DEFAULT '-500' COMMENT '経度',
	`ALT_BARO` FLOAT NOT NULL DEFAULT '0' COMMENT '気圧高度 (m)',
	`ALT_GEO` FLOAT NOT NULL DEFAULT '0' COMMENT '対地高度 (m)',
	`SPEED_GEO` FLOAT NOT NULL DEFAULT '0' COMMENT '対地速度 (m/s)',
	`DEG_TRUE` FLOAT NOT NULL DEFAULT '-1' COMMENT '飛行方向角',
	`RT_VERTICAL` FLOAT NOT NULL DEFAULT '-500' COMMENT '上昇下降率 (m/s)',
	`IS_ONGROUND` TINYINT(3) NOT NULL DEFAULT '0' COMMENT '地上 or 空中フラグ',
	`NM_SQUAWK` CHAR(4) NOT NULL DEFAULT '' COMMENT 'スコークコード' COLLATE 'utf8_general_ci',
	`CD_ACTYPE` CHAR(6) NOT NULL DEFAULT '' COMMENT 'ICAO機材コード' COLLATE 'utf8_general_ci',
	`TM_ADD` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '追加時刻',
	INDEX `h_pasttrack_all_LAT` (`LAT`) USING BTREE,
	INDEX `h_pasttrack_all_LON` (`LON`) USING BTREE,
	INDEX `h_pasttrack_all_IS_ONGROUND` (`IS_ONGROUND`) USING BTREE,
	INDEX `h_pasttrack_all_INM_SQUAWK` (`NM_SQUAWK`) USING BTREE,
	INDEX `h_pasttrack_all_UTM_POSITION` (`UTM_POSITION`) USING BTREE,
	INDEX `h_pasttrack_all_TM_ADD` (`TM_ADD`) USING BTREE,
	INDEX `h_pasttrack_all_CD_ICAO24` (`CD_ICAO24`) USING BTREE,
	INDEX `h_pasttrackall_NM_CALLSIGN` (`NM_CALLSIGN`) USING BTREE,
	INDEX `h_pasttrack_all_CD_ICAO24_TM_ADD` (`CD_ICAO24`, `TM_ADD`) USING BTREE,
	INDEX `h_pasttrack_all_LAT_LON_TM_ADD` (`LAT`, `LON`, `TM_ADD`) USING BTREE
)
COMMENT='OpenSky全機応答航跡履歴旧'
COLLATE='utf8_general_ci'
ENGINE=MyISAM
;

< ユーザーテーブル >

ユーザー設定デフォルト5秒間隔で指定した地図のバウンダリに存在する飛行機のADS=B応答を蓄積
保存期間 : 24時間

CREATE TABLE `h_pasttrack` (
	`ID` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'シーケンス番号',
	`NM_SESSION` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'WEBサーバーセッションID' COLLATE 'utf8_general_ci',
	`CD_ICAO` VARCHAR(4) NOT NULL DEFAULT '' COMMENT 'ICAO航空会社コード, ICAO空港コード' COLLATE 'utf8_general_ci',
	`CD_ICAO24` CHAR(6) NOT NULL DEFAULT '' COMMENT 'ICAO24機材識別コード' COLLATE 'utf8_general_ci',
	`NM_CALLSIGN` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '機材コールサイン' COLLATE 'utf8_general_ci',
	`UTM_POSITION` INT(10) NOT NULL DEFAULT '0' COMMENT 'ADS-B送信時刻',
	`UTM_LASTCONTACT` INT(10) NOT NULL DEFAULT '0' COMMENT '最終取得時刻',
	`LAT` FLOAT NOT NULL DEFAULT '-500' COMMENT '緯度',
	`LON` FLOAT NOT NULL DEFAULT '-500' COMMENT '経度',
	`ALT_BARO` FLOAT NOT NULL DEFAULT '0' COMMENT '気圧高度 (m)',
	`ALT_GEO` FLOAT NOT NULL DEFAULT '0' COMMENT '対地高度 (m)',
	`SPEED_GEO` FLOAT NOT NULL DEFAULT '0' COMMENT '対地速度 (m/s)',
	`DEG_TRUE` FLOAT NOT NULL DEFAULT '-1' COMMENT '飛行方向角',
	`RT_VERTICAL` FLOAT NOT NULL DEFAULT '-500' COMMENT '上昇下降率 (m/s)',
	`IS_ONGROUND` TINYINT(3) NOT NULL DEFAULT '0' COMMENT '地上 or 空中フラグ',
	`NM_SQUAWK` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'スコークコード' COLLATE 'utf8_general_ci',
	`CD_ACTYPE` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'ICAO機材コード' COLLATE 'utf8_general_ci',
	`TM_ADD` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '追加時刻',
	PRIMARY KEY (`ID`) USING BTREE,
	INDEX `h_pasttrack_NM_SESSION_CD_ICAO` (`NM_SESSION`, `CD_ICAO`) USING BTREE,
	INDEX `h_pasttrack_TM_ADD` (`TM_ADD`) USING BTREE,
	INDEX `h_pasttrack_CD_ICAO24` (`CD_ICAO24`) USING BTREE,
	INDEX `h_pasttrack_NM_CALLSIGN` (`NM_CALLSIGN`) USING BTREE,
	INDEX `h_pasttrack_NM_LAT` (`LAT`) USING BTREE,
	INDEX `h_pasttrack_NM_LON` (`LON`) USING BTREE,
	INDEX `h_pasttrack_IS_ONGROUND` (`IS_ONGROUND`) USING BTREE,
	INDEX `h_pasttrack_NM_SQUAWK` (`NM_SQUAWK`) USING BTREE,
	INDEX `h_pasttrack_UTM_POSITION` (`UTM_POSITION`) USING BTREE,
	INDEX `h_pasttrack_CD_ICAO24_TM_ADD` (`CD_ICAO24`, `TM_ADD`) USING BTREE,
	INDEX `h_pasttrack_LAT_LON_TM_ADD` (`LAT`, `LON`, `TM_ADD`) USING BTREE
)
COMMENT='OpenSky応答航跡履歴'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

< パフォーマンス向上策として行ったこと >

1) 文字列のデータ型をVARCHARからCHARに変更 (CHARの方が効率がよいという説あり)
2) 検索条件になる列の複合インデックスの列順 (狭く絞れるのを先に置く)
3) 最初は、PRIMARY KEYにAUTO_INCREMENTの列を存在させたが、一説によると、AUTO_INCREMENTの採番が負荷、メモリ消費なので削除
4) 経緯度データの型はFLOATにしてサイズ節約(OpenSkyの応答もFLOATなのでDOUBLE, DECIMALを使って数mの精度にすることが、無理でそもそも扱ってるデータにそこまでの精度は要求されてない)

MySQL 8 Query Performance Tuning A Systematic Method for Improving Execution Speeds【電子書籍】[ Jesper Wisborg Krogh ]
価格：4618円 (2022/12/19時点)

楽天で購入

【中古】実践ハイパフォ-マンスMySQL 第3版/オライリ-・ジャパン/バロン・シュワルツ（大型本）
価格：4770円（税込、送料無料) (2023/1/29時点)

楽天で購入

MySQL5.7でダンプしたfunctionがMySQL8にレストア出来ない問題の解消

投稿日: 2021-07-25 投稿者: Hisa

MySQL5.7でダンプしたfunctionが、MySQL8にレストア時にエラーでレストア出来ないので調べてましたところ、my.cnfに以下の設定を入れることで解決しました。

[ my.cnf に追加した設定 ]

log_bin_trust_function_creators = 1

ここで教えていただきました。https://hiroasake.blogspot.com/2019/03/mysql.html

MySQL ログの時刻をOSに合わす

投稿日: 2021-07-24 投稿者: Hisa

MySQLのデフォルトのログの時刻がUTCでわかりにくいので、OSの時刻に合わせます。

[ my.cnf ]

log_timestamps=SYSTEM   <=== これを追加

[ 変更前 ]

時刻の末尾がZ

2021-07-24T00:27:57.759453Z 4 [System] [MY-013381] [Server] Server upgrade from '80023' to '80026' started.
2021-07-24T00:28:03.206784Z 4 [System] [MY-013381] [Server] Server upgrade from '80023' to '80026' completed.

[ 変更後 ]

時刻の末尾に +9.00がつく

2021-07-24T09:36:02.475995+09:00 4 [System] [MY-013381] [Server] Server upgrade from '80025' to '80026' completed.
2021-07-24T09:36:02.609224+09:00 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2021-07-24T09:36:02.609856+09:00 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main

MySQL徹底入門第4版 MySQL 8.0対応【電子書籍】[ yoku0825 ]

楽天で購入

MySQL8 初期設定パスワードポリシー

投稿日: 2021-07-24 投稿者: Hisa

MySQL8からユーザーのパスワードポリシーのデフォルトが厳しくなり、かんたんなパスワードが設定できなくなったので、my.cnfで永続的に設定しています。

[ デフォルトの状態 ]


mysql> SHOW variables like '%validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+

[ my.cnfに設定 ]


validate_password.policy=LOW
validate_password.length=4
validate_password.check_user_name=OFF

mysqld を再起動

[ 変更後の状態 ]

[root@???? ?????]# mysql -uroot -p -e "SHOW variables like '%validate_password%'";
Enter password: 
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | OFF   |
| validate_password.dictionary_file    |       |
| validate_password.length             | 4     |
| validate_password.mixed_case_count   | 1     |
| validate_password.number_count       | 1     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 1     |
+--------------------------------------+-------+

15時間でわかるMySQL集中講座 [ 株式会社ハートビーツ　馬場俊彰 ]
価格：3168円（税込、送料無料) (2022/12/18時点)

楽天で購入

シェルスクリプトマガジン（Vol．72（2021年　Ju）ラズパイサーバー／MySQLクラウド／Outsystems
価格：550円（税込、送料無料) (2023/1/30時点)

楽天で購入

MySQL8 JSON TABLE で配列を扱う

投稿日: 2021-07-24 投稿者: Hisa

MySQL8からJSONの配列をレコードとして扱えるので、使ってみました。

[ Table例 ]
空港の滑走路のマスタで、複数の滑走路をJSONにしてます。

CREATE TABLE `m_runway` (
	`CD_IATA` CHAR(3) NOT NULL DEFAULT '' COMMENT 'IATA空港コード' COLLATE 'utf8_general_ci',
	`CD_ICAO` CHAR(4) NOT NULL DEFAULT '' COMMENT 'ICAO空港コード' COLLATE 'utf8_general_ci',
	`LAT` DOUBLE(22,10) NOT NULL DEFAULT '500.0000000000' COMMENT '緯度',
	`LON` DOUBLE(22,10) NOT NULL DEFAULT '500.0000000000' COMMENT '経度',
	`RUNWAYS` JSON NULL DEFAULT NULL COMMENT 'JSON明細',
	`TM_ADD` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '追加時刻',
	INDEX `m_runway_CDIATA` (`CD_IATA`) USING BTREE,
	INDEX `m_runway_CDICAO` (`CD_ICAO`) USING BTREE,
	INDEX `m_runway_LAT` (`LAT`) USING BTREE,
	INDEX `m_runway_LON` (`LON`) USING BTREE
)
COMMENT='滑走路マスタ'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

[ JSON列のJSON ]


[
    {
        "name": "06",
        "width": {
            "km": 0.046,
            "nm": 0.025,
            "feet": 150,
            "mile": 0.028,
            "meter": 45.72
        },
        "length": {
            "km": 2.316,
            "nm": 1.25,
            "feet": 7598,
            "mile": 1.439,
            "meter": 2315.87
        },
        "surface": "Asphalt",
        "trueHdg": 51.2,
        "isClosed": false,
        "location": {
            "lat": 40.6469841,
            "lon": -75.45063
        },
        "hasLighting": true,
        "displacedThreshold": {
            "km": 0,
            "nm": 0,
            "feet": 0,
            "mile": 0,
            "meter": 0
        }
    },
    {
        "name": "13",
        "width": {
            "km": 0.046,
            "nm": 0.025,
            "feet": 150,
            "mile": 0.028,
            "meter": 45.72
        },
        "length": {
            "km": 1.766,
            "nm": 0.954,
            "feet": 5794,
            "mile": 1.097,
            "meter": 1766.01
        },
        "surface": "Asphalt",
        "trueHdg": 123.1,
        "isClosed": false,
        "location": {
            "lat": 40.6551933,
            "lon": -75.44975
        },
        "hasLighting": true,
        "displacedThreshold": {
            "km": 0,
            "nm": 0,
            "feet": 0,
            "mile": 0,
            "meter": 0
        }
    },
    {
        "name": "24",
        "width": {
            "km": 0.046,
            "nm": 0.025,
            "feet": 150,
            "mile": 0.028,
            "meter": 45.72
        },
        "length": {
            "km": 2.316,
            "nm": 1.25,
            "feet": 7598,
            "mile": 1.439,
            "meter": 2315.87
        },
        "surface": "Asphalt",
        "trueHdg": 231.2,
        "isClosed": false,
        "location": {
            "lat": 40.6600342,
            "lon": -75.42926
        },
        "hasLighting": true,
        "displacedThreshold": {
            "km": 0.152,
            "nm": 0.082,
            "feet": 499,
            "mile": 0.095,
            "meter": 152.1
        }
    },
    {
        "name": "31",
        "width": {
            "km": 0.046,
            "nm": 0.025,
            "feet": 150,
            "mile": 0.028,
            "meter": 45.72
        },
        "length": {
            "km": 1.766,
            "nm": 0.954,
            "feet": 5794,
            "mile": 1.097,
            "meter": 1766.01
        },
        "surface": "Asphalt",
        "trueHdg": 303.1,
        "isClosed": false,
        "location": {
            "lat": 40.6465263,
            "lon": -75.4322357
        },
        "hasLighting": true,
        "displacedThreshold": {
            "km": 0,
            "nm": 0,
            "feet": 0,
            "mile": 0,
            "meter": 0
        }
    }
]

[ Select SQL例 ]

NESTED PATH を指定


    SELECT
      r.CD_IATA,
      r.CD_ICAO,
      -500 AS LAT,
      -500 AS LON,
      detail.NM_RUNWAY,
      detail.HDG,
      ROUND(detail.FEET, 0) AS FEET, 
      ROUND(detail.METER, 0) AS METER
    FROM 
      m_runway r, 

      JSON_TABLE(RUNWAYS, '$[*]' 
      COLUMNS (
        `NM_RUNWAY` VARCHAR(100) PATH '$.name',
        HDG INT PATH '$.trueHdg',
        
        NESTED PATH '$.length' 
        COLUMNS (
          FEET FLOAT PATH '$.feet',
          METER FLOAT PATH '$.meter'
        )               
        )
      ) AS detail
    WHERE
      CD_ICAO = 'RJTT'

[ 結果 ]

+---------+---------+------+------+-----------+------+-------+-------+
| CD_IATA | CD_ICAO | LAT  | LON  | NM_RUNWAY | HDG  | FEET  | METER |
+---------+---------+------+------+-----------+------+-------+-------+
| HND     | RJTT    | -500 | -500 | 04        |   35 |  8215 |  2504 |
| HND     | RJTT    | -500 | -500 | 05        |   42 |  8215 |  2504 |
| HND     | RJTT    | -500 | -500 | 16L       |  150 | 11053 |  3369 |
| HND     | RJTT    | -500 | -500 | 16R       |  150 |  9862 |  3006 |
| HND     | RJTT    | -500 | -500 | 22        |  215 |  8215 |  2504 |
| HND     | RJTT    | -500 | -500 | 23        |  222 |  8215 |  2504 |
| HND     | RJTT    | -500 | -500 | 34L       |  330 |  9862 |  3006 |
| HND     | RJTT    | -500 | -500 | 34R       |  330 | 11053 |  3369 |
+---------+---------+------+------+-----------+------+-------+-------+

JavaScript JSON Cookbook Over 80 recipes to make the most of JSON in your desktop, server, web, and mobile applications【電子書籍】[ Ray Rischpater ]
価格：4312円 (2023/1/11時点)

楽天で購入

MySQL8 タイムゾーンの設定

投稿日: 2021-07-24 投稿者: Hisa

MySQL8を新規にインストールした状態で、タイムゾーンがUTCのままなので設定しました。
公式ドキュメントによると、タイムゾーンテーブルのロードが必要のようで、下記コマンドでロードして反映。

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

my.cnf に下の設定を入れます。

#default-time-zone = 'Asia/Tokyo'
default-time-zone='+9:00'

タイムゾーン名、オフセットどちらでも反映できるようです。

ここで教えていただきました。

https://qiita.com/kakudaisuke/items/f4b76fbfd7f89992d971

MySQL徹底入門第4版 MySQL 8.0対応【電子書籍】[ yoku0825 ]

楽天で購入

詳細！PHP 8+MySQL 入門ノート XAMPP+MAMP対応 [ 大重美幸 ]

楽天で購入

MySQL8 デフォルト my.cnf からbinlogを無効化

投稿日: 2021-07-24 投稿者: Hisa

新規にインストールしたMySQL8のサーバーで、データサイズが大きく、10GBを超えていて更新が多いデータベースを運用していましたところ、

1) MySQLのメモリ消費が急増
2) サーバーのロードアベレージが常に高い

状態になったので、調べていたところ、MySQL8からデフォルトでbinlogが有効化された状態であることがわかりました。

my.cnfの設定に binlogの無効化を追加したところ大人しくなりました。

disable-log-bin   <== これを追加

今回扱ってるデータについては、障害があった時の完全な復旧が要求されてないので、この対応で対処出来てますが、そうでない場合は、メモリの増設、高速CPU環境への変更などをお勧めします。

カテゴリー: Database

MySQL 時刻形式経緯度十進変換関数

MySQL JSONデータにインデックスをつける

PHP PDO MySQLの検索結果のキャスト

MySQL8 1億レコードを試す

MySQL5.7でダンプしたfunctionがMySQL8にレストア出来ない問題の解消

MySQL ログの時刻をOSに合わす

MySQL8 初期設定パスワードポリシー

MySQL8 JSON TABLE で配列を扱う

MySQL8 タイムゾーンの設定

MySQL8 デフォルト my.cnf からbinlogを無効化