- 投稿日：2020-07-20T23:08:12+09:00
小ネタ／MySQL 8.0.21 で JSON_VALUE()
コロナ禍のあおりを受けて新機能が少なくなった MySQL 8.0.21 の新機能の 1 つ、
JSON_VALUE()をちょっとだけ試します。（まあ、本来ならマイナーバージョンで新機能の追加をすること自体が普通じゃないんですけどね。マイナーバージョンアップの都度新機能が加わる状況でも、Oracle さんは「メンテナンスリリース」と言い張っている謎。あ、これは dis りなどではなくて、お約束のネタです。）
JSON_VALUE()とは？公式リファレンスマニュアルの説明にあるとおり、従来の
CAST() ASJSON_UNQUOTE()JSON_EXTRACT()の組み合わせを 1 つの関数として、
JSON_VALUE(json_doc, path RETURNING type)の形式で記述することができるようになったものです。
CAST() ASが不要な場合は、
JSON_VALUE(json_doc, path)です（その他、
ON EMPTY・ON ERRORの指定もできます。詳しくは公式リファレンスマニュアルをご覧ください）。関数インデックスとして使う
公式リファレンスマニュアルにも例示がありますが、これは関数インデックスとして使うと便利です。
マニュアルの例示と同じ設計のテーブルで試してみます。
テーブル作成mysql> CREATE DATABASE jsontest; Query OK, 1 row affected (0.01 sec) mysql> USE jsontest; Database changed mysql> CREATE TABLE t1( -> j JSON, -> INDEX i1 ( (JSON_VALUE(j, '$.id' RETURNING UNSIGNED)) ) -> ); 1 row in set (0.00 sec)ちなみに、MySQL Workbench 8.0.21 ではなぜか
UNSIGNEDのところに波線で警告（？）が表示されますが（赤枠）、無視して実行することができます（青枠）。
データを入れてみました。
投入されたデータmysql> SELECT * FROM t1; +----------------------------------+ | j | +----------------------------------+ | {"id": 100, "val": [1, 2, 3]} | | {"id": 101, "val": [4, 5, 6, 7]} | | {"id": 110, "val": [8, 9, 0]} | | {"id": 120, "val": [1, 2]} | | {"id": 122, "val": 3} | | {"id": 130, "val": [4, 5]} | | {"id": 140, "val": [6, 7, 8]} | | {"id": 150, "val": [9, 0, 1, 2]} | | {"id": 200, "val": [3, 4, 5]} | | {"id": 220, "val": [6, 7]} | +----------------------------------+ 10 rows in set (0.00 sec)
さて、実際にやってみます。
試してみる(1)mysql> EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$.id' RETURNING UNSIGNED) = 150\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 10 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)おや？関数インデックスが効いていませんね？
念のため
SHOW CREATE TABLEしてみます。テーブル定義を見てみるmysql> SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `j` json DEFAULT NULL, KEY `i1` ((json_value(`j`, _utf8mb4'$.id' returning unsigned))) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)関数インデックスは
WHERE句で指定するときに「インデックス定義と一致しないと使われない」という仕様なので、もしや…試してみる(2)mysql> EXPLAIN SELECT * FROM t1 WHERE json_value(`j`, _utf8mb4'$.id' returning unsigned) = 150\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ref possible_keys: i1 key: i1 key_len: 9 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)…あ、やっぱり。勝手に付いた
_utf8mb4が邪魔をしていたようです。
ちなみに、
試してみる(3)mysql> EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, _UTF8MB4'$.id' RETURNING UNSIGNED) = 150\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ref possible_keys: i1 key: i1 key_len: 9 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)大文字小文字の違いは大丈夫ですが、
試してみる(4)mysql> EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, _UTF8MB4'$.id') = 150\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 10 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)一部分でも省略すると、関数インデックスは使われません。
余談
かつて、
で X DevAPI を使って、
コレクションの作成// Create Collection const session = await mysqlx.getSession(connectParam); const collection = await session.getSchema(schemaName).createCollection(collectionName); const flag = await collection.createIndex('labels', {fields: [{"field": "$.labels", "type":"CHAR(100)", "array": true}]});コレクションとインデックスを作成したところ、実際に出来上がったテーブルは、
作成されたテーブルCREATE TABLE `image_labeling` ( `doc` json DEFAULT NULL, `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL, PRIMARY KEY (`_id`), KEY `labels` ((cast(json_extract(`doc`,_utf8mb4'$.labels') as char(100) array))) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;でした。
バージョンアップに伴ってこのあたりのテーブル定義も変わっていくんですかね？（Server vs Connector のバージョン依存があると怖い気がする…）。
- Qiitaに投稿したMySQL 8.0関連記事
- MySQL 8.0 の薄い本（無料で配布中！）
- 投稿日：2020-07-20T21:05:14+09:00
【裏技】RDS for MySQL において sql_mode を空白にする【非推奨】
背景
RDS for MySQL の 5.5 から 5.6 にバージョンアップするにあたり、
極力後方互換性を保って移行したいと思い、MySQL 5.5 と同じような動作となるようにパラメータの値を検討していた。sql_mode についても 5.5 ではデフォルト値 (= 空白) に設定していたため、同様の設定を 5.6 にも引き継ぎたいが
MySQL 5.6.6 以降においてデフォルト値は NO_ENGINE_SUBSTITUTION が適用されることになる。これをなんとか空白にしたかった
無理やり空白にする方法
パラメータグループにおいて、sql_mode を 「,」 カンマだけを入力して登録する
そうすると、許可されていない空白が値として登録できました。
"ParameterValue": ""となっていますね。urabe:~ $ aws rds describe-db-parameters --db-parameter-group-name test-mysql-56-params| jq '.Parameters[]| select(.ParameterName == "sql_mode")' { "ParameterName": "sql_mode", "ParameterValue": "", "Description": "Current SQL Server Mode.", "Source": "user", "ApplyType": "dynamic", "DataType": "list", "AllowedValues": "ALLOW_INVALID_DATES,ANSI_QUOTES,ERROR_FOR_DIVISION_BY_ZERO,HIGH_NOT_PRECEDENCE,IGNORE_SPACE,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION,NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_UNSIGNED_SUBTRACTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY,PAD_CHAR_TO_FULL_LENGTH,PIPES_AS_CONCAT,REAL_AS_FLOAT,STRICT_ALL_TABLES,STRICT_TRANS_TABLES,ANSI,DB2,MAXDB,MSSQL,MYSQL323,MYSQL40,ORACLE,POSTGRESQL,TRADITIONAL", "IsModifiable": true, "ApplyMethod": "immediate" } urabe:~ $ご注意
AWS としては動作保証するものではないので、ふーん そんな挙動あるんだ、とだけ思っておいてください。
2020/7/20 現在たまたまそういう動作があったということで、今後動作が変わるかもしれません。
実運用では使わないでくださいね。使われたとしても、どういう問題が起こるか保証できかねます！m(_ _)m！NO_ENGINE_SUBSTITUTION が有効であっても、正しく動作するように対処するのがベストだと思います。
- 投稿日：2020-07-20T18:08:54+09:00
PHPで性格診断アプリを作ってみた
性格診断アプリ
前書き
こんにちは。業務としてWebAppエンジニアリングをかじっている者です。今回はXAMPPで性格診断アプリを作ってみました。今まで自分一人でログイン機能付きのアプリを作ったことがなかったのでいい勉強になりました。XAMPPのインストール手順に関しては別記事をご参照ください。ソースコードはこちらです。
構成
XAMPPをインストールすると
C:\xampp\htdocsがドキュメントルートになります。例えばこのhtdocs直下にindex.htmlやindex.phpなどのindexという名前のファイルを置いた状態で、ブラウザにhttp://localhostと入力するとローカル環境でAppを起動することができます。(事前にXAMPP Control Panel内のApacheをStartさせておいてください)htdocs下の構成は以下のようになります。
C:\xampp\htdocs> ls ディレクトリ: C:\xampp\htdocs Mode LastWriteTime Length Name ---- ------------- ------ ---- d----- 2020/07/13 16:41 css d----- 2020/06/22 10:47 dashboard d----- 2020/07/13 12:45 img d----- 2020/06/22 10:47 webalizer d----- 2020/06/22 10:47 xampp -a---- 2020/07/14 16:19 152 config.php -a---- 2015/07/17 0:32 30894 favicon.ico -a---- 2020/07/15 22:28 5965 home.html -a---- 2020/07/14 16:14 3241 index.php -a---- 2020/07/15 22:29 1524 login.php -a---- 2020/07/14 19:35 909 logout.php -a---- 2020/07/15 22:29 1971 result-list.php -a---- 2020/07/15 22:30 11434 result.php -a---- 2020/07/15 22:03 2174 signUp.phpC:\xampp\htdocs\css> ls ディレクトリ: C:\xampp\htdocs\css Mode LastWriteTime Length Name ---- ------------- ------ ---- -a---- 2020/07/15 13:44 1394 style.cssデータベースのテーブル構成については以下のようになります。
dbname=pd_login テーブル: userdata email varchar(255) utf8mb4_unicode_ci unique key password varchar(255) utf8mb4_unicode_ci テーブル: diagnosis-result email varchar(255) utf8mb4_unicode_ci execution_time datetime friendly int(11) extrovert int(11) emotional int(11) positive int(11) leader int(11)ログイン
htdocs直下にindex.htmlやindex.phpなどのindexという名前のファイルを置いた状態で、ブラウザにhttp://localhostと入力するとindex.phpが表示されます。これがログイン機能の大元になるファイルです。新規登録機能と通常ログイン機能を持たせています。新規登録処理について、メールアドレスとパスワードにはフィルターをかけている。パスワードに関しては正規表現で適切でないものをはじいています。メールアドレスに関してはfilter_varを利用して、RFC822で判定しています。RFC822は判定がガバガバ説があるので、ビジネスで使うときは注意です。パスワードはセキュアにデータベースに保存するためpassword_hash()関数を使ってhash化しています。if (!$email = filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) { echo '入力された値が不正です。'; return false; }if (preg_match('/\A(?=.*?[a-z])(?=.*?\d)[a-z\d]{8,100}+\z/i', $_POST['password'])) { $password = password_hash($_POST['password'], PASSWORD_DEFAULT); } else { echo 'パスワードは半角英数字をそれぞれ1文字以上含んだ8文字以上で設定してください。'; return false; }通常ログイン機能では以下のようにパスワードを処理します。登録時にhash化したパスワードを
password_verify()で照合しています。# login.php if (password_verify($_POST['password'], $row['password'])) { session_regenerate_id(true); //session_idを新しく生成し、置き換える $_SESSION['EMAIL'] = $row['email']; echo 'ログインしました'; session_write_close(); header( "Location: ./home.php" ) ; exit(); } else { echo 'メールアドレス又はパスワードが間違っています。'; return false; }性格診断
index.php内のinputタグから
passwordをpostするとそれをlogin.phpがデータを受け取り、本人確認が済んだらhome.htmlに遷移します。以下がhome.htmlの質問文の箇所です。valueには質問ごとの性格要素の値が入っています。<form method="post" action="result.php"> <h2>第一問</h2> <p>あなたは外で遊ぶより、家で過ごすほうが好きですか？</p> <input type="radio" name="q1" value="1"> かなりそう思う<br> <input type="radio" name="q1" value="2"> そう思う<br> <input type="radio" name="q1" value="3"> どちらとも言える<br> <input type="radio" name="q1" value="4"> あまりそう思わない<br> <input type="radio" name="q1" value="5" required> そう思わない<br>診断結果
home.htmlで入力した値はresult.phpにpostされます。以下はvalueごとの性格要素の値を集計して、4分岐でユーザの性格を判定している箇所です。この例だと質問2と質問6はユーザの興味の対象に関する要素を決定する項目です。この2つの質問のvalueを合算してその合計でユーザの性格を4つに分類しています。# result.php if($q2+$q6>8){ $hito_mono = "非常にモノよりヒト"; $hito_mono_comment = "あなたは特にこの傾向が強く、ヒトと関わっている時間を楽しみに感じています。 仕事を選ぶ際にもなるべくヒトと関わる仕事を選ぶとよいでしょう。"; }elseif($q2+$q6>5){ $hito_mono = "どちらかといえば、モノよりヒト"; $hito_mono_comment = "あなたはヒトと関わる時間に楽しみを感じる一方で、モノにも興味をもてます。 何か自分の興味のあるモノやコトに打ち込みながら、それをヒトとシェアできるバランスの良さをもっています。"; }elseif($q2+$q6>3){ $hito_mono = "どちらかといえば、ヒトよりモノ"; $hito_mono_comment = "あなたはモノやコトに打ち込んでいる瞬間に楽しみを感じる一方で、他人にも興味をもてます。 何か自分の興味のあるモノやコトに打ち込みながら、それをヒトとシェアできるバランスの良さをもっています。"; }else{ $hito_mono = "非常にヒトよりモノ"; $hito_mono_comment = "あなたは特にこの傾向が強く、モノやコトに打ち込んでいる瞬間を楽しみに感じています。 仕事を選ぶ際にもなるべく一人で集中できる仕事を選ぶとよいでしょう。"; }判定結果の履歴
home.htmlに以下のボタンを設置しています。<button class="fixed_btn" onclick="location.href='./result-list.php'">過去の診断結果</button>実は
result.php内でデータベースにそれぞれの結果を保存しています。home.htmlはresult-list.phpに遷移した後、このデータベースに接続します。result-list.phpでは以下のような処理を行っており、ログインしているユーザの# result-list.php function createHtmlTable($result) { $html = "<table border='3' cellspacing='4' cellpadding='4'>"; $ffields = $result->fetch_fields(); $html .= "<tr>"; foreach ($ffields as $val) { $html .= "<th>" . $val->name . "</th>"; } $html .= "</tr>"; foreach ($result as $row) { $html .= "<tr>"; foreach ($ffields as $val) { $value = $row[$val->name]; $html .= "<td>${value}</td>"; } $html .= "</tr>"; } $html .= "</table>"; return $html; }Session
ちなみに、ページ遷移してもログイン時の
Sessionを利用しているからです。login.php内では、以下のような処理をしています。# login.php $_SESSION['EMAIL'] = $row['email'];遷移した後のページでこの
$_SESSION['EMAIL']を使うときは、以下のような記述をする必要があります。<?php session_start();ハマったこと
データベースの接続
文字列の変数はちゃんと
'$hoge'などシングルクオーテーションマーク(あるいはダブルクォーテーションマーク)で囲む必要があります。# result.php $pdo = new PDO('mysql:host=localhost;dbname=pd_login;charset=utf8mb4','root',''); $sql = "INSERT INTO `diagnosis-result`(`email`, `execution_time`, `friendly`, `extrovert`, `emotional`, `positive`, `leader`) VALUES ('$user_email','$current_time',$q2+$q6,$q1+$q7,$q3+$q8,$q5+$q9,$q4+$q10)"; $qry = $pdo->prepare($sql); $qry->execute(); $pdo = null;データベースのデータ型
パスワードをhash化していますが、長さには余裕をもたせる必要があります。私は
varchar(16)とやってしばらくハマっていました。varchar(255)にしたら通りました。参考
- 投稿日：2020-07-20T14:15:32+09:00
SQL テーブルのカラム情報取得
テーブルのカラム情報取得
クエリ
show columns from [テーブル名]; show full columns from [テーブル名]; -- ←コメントまで取得したい場合実行結果
mysql> show columns from users; +---------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name_sei | varchar(255) | NO | | NULL | | | name_mei | varchar(255) | NO | | NULL | | | tel | varchar(255) | YES | | NULL | | | email | varchar(255) | NO | MUL | NULL | | | postal_code | varchar(255) | YES | | NULL | | | address | varchar(255) | YES | | NULL | | | created | datetime | NO | | NULL | | | modified | datetime | NO | | NULL | | +---------------+--------------+------+-----+---------+----------------+ 9 rows in set (0.00 sec)mysql> show full columns from users; +---------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+--------------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +---------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+--------------+ | id | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | | name_sei | varchar(255) | utf8_general_ci | NO | | NULL | | select,insert,update,references | ユーザー名の姓 | | name_mei | varchar(255) | utf8_general_ci | NO | | NULL | | select,insert,update,references | ユーザー名の名 | | tel | varchar(255) | utf8_general_ci | YES | | NULL | | select,insert,update,references | 電話番号 | | email | varchar(255) | utf8_general_ci | NO | MUL | NULL | | select,insert,update,references | メールアドレス | | postal_code | varchar(255) | utf8_general_ci | YES | | NULL | | select,insert,update,references | 郵便番号 | | address | varchar(255) | utf8_general_ci | YES | | NULL | | select,insert,update,references | 住所 | | created | datetime | NULL | NO | | NULL | | select,insert,update,references | | | modified | datetime | NULL | NO | | NULL | | select,insert,update,references | | +---------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+--------------+ 9 rows in set (0.00 sec)その他
既存カラムにコメント追加
ALTER TABLE [テーブル名] CHANGE COLUMN [カラム名] [カラム名] [型] [制約系(例: NOT NULL, DEFAULT 'aaa', etc...)] COMMENT [コメント];auto_incrementの追加
alter table [テーブル名] auto_increment = [id];