SQLの結果のフィールド名が空白になってしまう現象に遭遇した

Posted on 2018年9月17日 21:28 by amyu

先日、会社の後輩に作ってもらったストアドで、なぜか一部、ストアドで返ってくる結果のフィールド名が空白になってしまうという現象が発生しました()。

具体的には下記のようなSQLとなっていた。

SELECT
    fruit_id          -- ID
    ,fruit_name	　　　 -- 名前
FROM fruit

SELECT

fruit_id -- ID

,fruit_name 　　　 -- 名前

FROM fruit

結果：

見てわかる通り、fruit_idのフィールド名は取得できているのだけど、fruit_nameのほうは空白になっている。試しに、コピペで順序を逆にしてみても、fruit_nameのほうが空白になった。

一見すると、何の変哲もないSQL。じっと見ていても何も分からなかったので、とりあえずコメントも消してみてもう少しシンプルな形にしてみようかと思ったときにちょっとだけ違和感。なんだかカーソルの動きが一部だけ大きいような……。

そう思って試しにテキストエディタに貼り付けてみたらわかりました。

fruit_nameのほうは、コメントの左側に全角スペースが入っています。

ただ、それでもなぜカラム名が空白になるのかすぐには分かりませんでした。何でこんな現象が起こるのかなと。

ですが、ちょっと考えてわかりました。これは、フィールド名は空文字ではなく、全角スペースになっているんだろいうことに。つまり、下記のようなSQLと同じようになっているということです。

つまり、別名をつけるASキーワードが省略されたと判断されて、フィールド名が全角スペースになっていたということです(今回試したのはMySQLですが、多分他のSQLでも同様の現象が発生するのではないかと思います)。

それにしても、なぜSQLはASを省略していいという仕様になったのだろうか。ASを省略しているSQLはよく見かけるけど、個人的に分かりにくいし、今回みたいによく分からないバグの原因にもなりかねない気がするのだけど。

MySQLの SELECT … INTO 構文で結果を変数に書き込むときの注意

Posted on 2018年9月6日 22:27 by amyu

返信

Pocket

先日少しはまった話。

MySQLにはSELECT … INTO 構文というものがあります。これを利用すると簡単にクエリー結果を変数に格納することができます。
参考：MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.9.1 SELECT … INTO 構文

仕事で、MySQLのストアドを利用して、指定のIDとOUTパラメータを引数にセットし、OUTパラメータに引数に対応する値を返すというストアドを同僚が作っていました。そのストアドはSELECT … INTO 構文を使って下記のようになっていました。

CREATE DEFINER=`root`@`localhost` PROCEDURE `P_GetFruit`(IN p_id INT, OUT p_name VARCHAR(45))
BEGIN
    SELECT fruit_name
    INTO @name
    FROM fruit
    WHERE fruit_id = p_id
    LIMIT 1;
    SET p_name = @name;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `P_GetFruit`(IN p_id INT, OUT p_name VARCHAR(45))

BEGIN

SELECT fruit_name

INTO @name

FROM fruit

WHERE fruit_id = p_id

LIMIT 1;

SET p_name = @name;

END

実際のテーブルは下記のようになっているとします。

この時、p_idに1を指定すると、p_nameには「りんご」という値が入ります。

もちろん、p_idに2を指定すると、p_nameには「みかん」という値が入ります。

では、ここでp_idに10を指定するとどうなるでしょう。fruid_idが10のfruit_nameはないから、結果はnullになる。同僚もそうなると思ったようですが、違いました。

結果は一つ前に実行したときに取得した「みかん」を取得。プログラム作って動かしていた自分は、最初、「やけに同じ結果が返ってくるなぁ。てっきりnullが返ってくると思ったのだけど」なんて呑気に思い、しばらくバグに気づきませんでした。

これはなぜかというと、MySQLの変数はセッション変数で、つなげている間は結果が保持されるためだそうです。
参考：mysqlでユーザ定義変数をつかう – 眠すぎて明日が見えない

なので、上記のように変数に格納したい場合は、いったんnullで初期化する記述を追加したほうがいいです。

CREATE DEFINER=`root`@`localhost` PROCEDURE `P_GetFruit`(IN p_id INT, OUT p_name VARCHAR(45))
BEGIN
    SET @name = null;
    SELECT fruit_name
    INTO @name
    FROM fruit
    WHERE fruit_id = p_id
    LIMIT 1;
    SET p_name = @name;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `P_GetFruit`(IN p_id INT, OUT p_name VARCHAR(45))

BEGIN

SET @name = null;

SELECT fruit_name

INTO @name

FROM fruit

WHERE fruit_id = p_id

LIMIT 1;

SET p_name = @name;

END

ただ、この程度だったら直接outパラメータを指定したほうがシンプルかもしれないですけどね。

CREATE DEFINER=`root`@`localhost` PROCEDURE `P_GetFruit`(IN p_id INT, OUT p_name VARCHAR(45))
BEGIN
    SELECT fruit_name
    INTO p_name
    FROM fruit
    WHERE fruit_id = p_id
    LIMIT 1;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `P_GetFruit`(IN p_id INT, OUT p_name VARCHAR(45))

BEGIN

SELECT fruit_name

INTO p_name

FROM fruit

WHERE fruit_id = p_id

LIMIT 1;

END

それより、WorkBenchがちょくちょく結果を表示してくれないことがあった困る。何で、「1 row(s) returned」って書いてあるのに、表示してくれないことがあるんだ……。

MySQL8のデフォルト照合規則「utf8mb4_0900_ai_ci」について調べてみた

Posted on 2018年8月19日 21:57 by amyu

返信

Pocket

前からたまに、MySQLを仕事で使ってきたのですが、照合規則については詳しくなかった自分。現在の仕事で、ひらがなでもカタカナ、小文字でも大文字、全角でも半角が検索でヒットするシステムを作ってほしいとのことで少ししらべてみました。

ちなみに、寿司ビール問題(MySQL と寿司ビール問題 – かみぽわーる)というのは聞いたことあったので、ちょっとは問題あるんだろうなと思ったら、MySQL8では解決されているようです(寿司=ビール問題 : MySQL 8.0でのUTF8サポート入門 (MySQL Server Blogより) | Yakst)。

ただし、何やらデフォルトの文字コードだと日本語の照合にいろいろ問題があるとのこと。
参考：MySQL Innovation Day Tokyo で MySQL 8 の文字コードについて話した – @tmtms のメモ

というのも、デフォルトの「utf8mb4_0900_ai_ci」だと、アクセント違いは同じ文字の扱いになり、大文字小文字も同じ文字という扱いになるそうです。これがどういうことかというと、「はは」と検索したのに、「ばば」や「パパ」がヒットするということです。なんじゃそりゃ。

例えば「びょういん」で検索すると、下記のようになります(ID13～18は半角カタカナです)。

なお、このテーブルには「ひ゛ょういん」や「ひ゜よういん」もいれているのですが、それはヒットしませんでした。

ちなみに、like演算子で、「びょう」としても全部ヒットするわけではないらしいです。半角カタカナの「ビョウイン」や「ピヨウイン」などがでてきてないですね。濁点や半濁点は別の扱いなんでしょうか。

そういえば、先ほど紹介したMySQL8の話のページで知ったのですが、「株式会社」＝「㍿」となるそうです。面白いですね。

ただ、これもlike演算子だとヒットせず。

内部的にどういう違いがあるんだろう？　like演算子だと厳密ってわけでもないみたいだし。

ちなみに、仕事ではいろいろ試した結果、照合順序は「はは」と「ばば」と「ぱぱ」を区別する「utf8mb4_0900_as_ci」を使うことにしました。これだと、ひらがなでもカタカナ、小文字でも大文字、全角でも半角が検索でヒットするので。ただし、「びょういん」＝「びよういん」にはなってしまうという。これだけ解決されれば、期待通りだったんだけどなぁ。日本語と英語の大文字小文字の違いを同じような扱いにしないでほしい。

jquery.ajaxに代わる、Fetch APIについて

Posted on 2018年6月10日 00:02 by amyu

返信

Pocket

最近のJavaScriptでは、jQueryに依存しない書き方が増えてきたようです。ただ、確かにセレクターなら、document.querySelectorAllメソッドを使えばいいだけなんですが、個人的にjQueryで便利なところは、ajaxだと思ってるんです。いちいち、XMLHttpRequestがどうちゃらこうちゃらとか書きたくないし、だいいち覚えられない。だから自分は、jQueryからはなかなか離れられないだろうし、離れたとしてもAjaxようの共通関数は実装することになるんだろうなぁ。と思っていたら、最近になってFetch APIなんてものを知りました。

最近のWeb技術界隈は追えていないと思ったらこんなものが。というわけで、調べて使ってみました。

HTML

<!DOCTYPE html>
<html lang="ja">
<head>
    <meta charset="UTF-8">
    <title>Fetch APIテスト</title>
</head>
<body>
    <form id="frm">
        <label>2倍にする数値:<input type="number" id="num"></label><br>
        <label>ハッシュ化する文字列:<input type="text" id="str"></label><br>
        <input type="button" value="Fetch APIテスト" id="btn"><br>
        結果：<output style="border:1px solid black;" id="output"></output>
    </form>
    <script>
        document.getElementById('btn').addEventListener('click', function(){
            let num = document.getElementById('num').value;
            let str = document.getElementById('str').value;

            fetch('return_json.php', {
                method : 'POST',
                body : JSON.stringify({"num":num, "str":str})
            }).then(function(response){
                console.dir(response);
                return response.json();
            }).then(function(data){
                console.dir(data);
                document.getElementById('output').innerHTML = JSON.stringify(data);
            });
        });
    </script>
</body>
</html>

<!DOCTYPE html>

<head>

<title>Fetch APIテスト</title>

</head>

<body>

<label>2倍にする数値:<input type="number" id="num"></label><br>

<label>ハッシュ化する文字列:<input type="text" id="str"></label><br>

結果：<output style="border:1px solid black;" id="output"></output>

</form>

document.getElementById('btn').addEventListener('click', function(){

let num = document.getElementById('num').value;

let str = document.getElementById('str').value;

fetch('return_json.php', {

method : 'POST',

body : JSON.stringify({"num":num, "str":str})

}).then(function(response){

console.dir(response);

return response.json();

}).then(function(data){

console.dir(data);

document.getElementById('output').innerHTML = JSON.stringify(data);

});

</script>

</body>

</html>

PHP

<?php
$params = json_decode(file_get_contents('php://input'), true);
$num = intval($params["num"]) * 2;
$sha256 = hash_hmac('sha256', $params["str"], False);
$ret = array('num' => $num, 'sha256' => $sha256);
echo (json_encode($ret));
?>

<?php

$params = json_decode(file_get_contents('php://input'), true);

$num = intval($params["num"]) * 2;

$sha256 = hash_hmac('sha256', $params["str"], False);

$ret = array('num' => $num, 'sha256' => $sha256);

echo (json_encode($ret));

サンプル：Document

やってることは、サーバーに数値と文字列をPOSTで渡すと、数値を2倍、文字列をSHA-256でハッシュ化した結果をJSON形式で返すというもの。例えば、数値を123、文字列をabcとすると下記のようになりました。

確かに数値は2倍になって、文字列はハッシュ化して返ってきました。ちょっとよく分からなかったのが、最初にResponseオブジェクトからどういう形式で値を取得するかという指定が必要なこと。これがPromiseオブジェクトで返ってくるので、いちいちthenを二回書かなきゃいけないという。response.json()で返ってくる値がPromiseオブジェクトじゃなくて、JSONオブジェクト(多重表現)になってるほうが直感的でわかりやすい気がするのだけど、多分、こうなってるメリットはあるんだろうと思う。このへんは多分、自分がPromiseについてよく分かってないから疑問に思ってるのだと思うので、もう少しこのへんも勉強していこうと思う。

ところで最初、fetchのbody部分を「body: new FormData(document.getElementById(‘frm’))」と指定しようと思ったのですが、それだといまいちメリットがないなと思ったら、JSONで渡せることを知ったのでそうしました。PHP側で『json_decode(file_get_contents(‘php://input’), true);』とすればいいそうです。初めて知りました。JSONのまま渡せるなら、便利ですね。これからはそうしようかな。

【SQL】特定のデータがあるグループのみ抽出する方法

Posted on 2018年3月25日 22:01 by amyu

返信

Pocket

SQLで集計する時に、ある特定の値があるグループのみ抽出したいということがあると思います。先日、仕事でそういうことをしたいということがあったのですが、すぐには思いつかず、仕事を終えた後に思いついたので、紹介したいと思います。

今回、使うのは下記のようなサンプルテーブル。テーブル名はtest1で、フィールドがグループ番号、名前、年齢、点数とします(名前と年齢は今期見てるアニメからとっています)。

ここから、中学生(12～14歳)がいるグループのみのグループ番号と平均点を取得するSQLを取得するには下記のようにすればとれます。

SELECT group_id, AVG(score)
FROM test1
GROUP BY group_id
HAVING MAX(CASE WHEN age BETWEEN 12 AND 14 THEN 1 ELSE 0 END) = 1
ORDER BY group_id

SELECT group_id, AVG(score)

FROM test1

GROUP BY group_id

HAVING MAX(CASE WHEN age BETWEEN 12 AND 14 THEN 1 ELSE 0 END) = 1

ORDER BY group_id

上記の結果が下記です。

確かに、グループに中学生のいる1と2のみの平均点が表示されました。

上記のSQLは何をしているかというと、まずはGROUP BYで全データを集計し、HAVINGで12～14歳の年齢がいるグループのみに絞っています。

なぜ、これで絞れるかというと、CASEの条件(ここでは「age BETWEEN 12 AND 14」)に一致した値があれば1という値が含まれ、無ければ0しかないということになるからです。それぞれの行にCASE文の結果を付与すると下記のようになります。

SELECT *, CASE WHEN age BETWEEN 12 AND 14 THEN 1 ELSE 0 END
FROM test1

1 2	SELECT *, CASE WHEN age BETWEEN 12 AND 14 THEN 1 ELSE 0 END FROM test1

そこからMAX関数で最大値をとってくるので1が含まれていれば1、1が含まれていなければ0となるので、1に等しい値を抽出した結果、条件に一致したグループのみを取得できることになります。

逆に、特定のデータが入っていないもののみを抽出したいという場合は、「= 1」の箇所を「<> 1」とするか、「= 0」とすることで、取得することができます。

なお、HAVINGを使わなくても、サブクエリーを使うことで同じことができます。

SELECT group_id, avg
FROM (
	SELECT group_id, AVG(score) AS avg, MAX(CASE WHEN age BETWEEN 12 AND 14 THEN 1 ELSE 0 END) AS flg
	FROM test1
	GROUP BY group_id
) AS table1
WHERE flg = 1
ORDER BY group_id

SELECT group_id, avg

FROM (

SELECT group_id, AVG(score) AS avg, MAX(CASE WHEN age BETWEEN 12 AND 14 THEN 1 ELSE 0 END) AS flg

FROM test1

GROUP BY group_id

) AS table1

WHERE flg = 1

ORDER BY group_id

ただ、HAVINGを使ったほうがシンプルなので、基本的にはHAVINGを使ったほうがいいと思います(実行計画のコストも、HAVINGを使ったほうが少しだけ低いようです)。

HAVINGは今までほとんど使ってこなかったのですが、使い始めると結構便利なことに気づきました。いろいろ応用もできそうなので、使いこなしていきたいです。

2018/03/26追記：そういえば、この記事を書こうと最初に思ったときは、条件は中学生以下、つまり「MAX(CASE WHEN age <= 14 THEN 1 ELSE 0 END) = 1」にしようとしていました。ただ、これだと、「MIN(age) <= 14」でもいいことに気づき、中学生が含まれるかどうかという条件に変更しました。こういう条件は、シンプルに考えられるようになりたいです。

while(isプログラマ)

ころころ興味対象が変わるプログラマの技術ブログ(多分)

カテゴリーアーカイブ: プログラミング

SQLの結果のフィールド名が空白になってしまう現象に遭遇した

MySQLの SELECT … INTO 構文で結果を変数に書き込むときの注意

MySQL8のデフォルト照合規則「utf8mb4_0900_ai_ci」について調べてみた

jquery.ajaxに代わる、Fetch APIについて

HTML

PHP

【SQL】特定のデータがあるグループのみ抽出する方法