【MySQL】SELECTで取得した値がNULL値だった場合に別の値として取得する

今のプロジェクトで使ったのでメモ

現在API担当としてシステム開発を行なっているのですが、返却値として「NULL」を
返却せずに「0」を返却して欲しいという要望があったので、
クエリで何とかならないかと思ってCOALESCE関数という関数を見つけました。


関数の内容としては
リストの最初の非 NULL 値を戻すか、非 NULL 値がない場合は NULL を戻します。
と言うことが書いてありました。
翻訳がアレなのでちょっとわかりにくいと思います。

つまり、引数は可変長引数となっており、いくつでも(？)引数を指定することが可能です。
その上で、順番に指定されている引数をチェックしていき、「NULL」でない値を返却します。
「NULL」しか無かった場合は「NULL」を返却します。

例)
mysql> SELECT COALESCE(NULL,1);
        -> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
        -> NULL


例えば下記のようなテーブルがあったとします。
+---------+--------+-------+
| user_id | name   | value |
+---------+--------+-------+
|       1 | user01 |     5 |
|       2 | user02 |  NULL |
+---------+--------+-------+

上記のテーブルを次のSELECTで取得した場合
SELECT 
  * 
FROM 
  `user`
+---------+--------+-------+
| user_id | name   | value |
+---------+--------+-------+
|       1 | user01 |     5 |
|       2 | user02 |  NULL |
+---------+--------+-------+

「value」カラムにNULLが入ったまま返却されてきます。


次に上記のCOALESCE関数を使用してSELECTを実行します。
SELECT 
  `user`.`user_id`, 
  `user`.`name`, 
  COALESCE(`user`.`value`, 0) AS `value` 
FROM 
  `user`
+---------+--------+-------+
| user_id | name   | value |
+---------+--------+-------+
|       1 | user01 |     5 |
|       2 | user02 |     0 |
+---------+--------+-------+

という風に先程「NULL」が入っていたところが「0」として返却されてきます。

本来このような使い方をする関数では無いのかもしれないですが、この関数のお陰で
楽ができました。

終わり

【MySQL】UPDATE文でサブクエリを使う時の注意

ハマりかけたのでメモ

MySQLのUPDATE文でサブクエリを使う際の注意点です。

下記のようにUPDATE文では更新対象をサブクエリの問い合わせに
使用することはできません。
UPDATE `user` SET 
  `user`.`name` = 'test01'
WHERE 
  `user`.`id` = 
  (
    SELECT 
      `user`.`id`
    FROM 
      `user`
    WHERE 
      `user`.`id` = 1
  )
こんなUPDATE文は通常有り得ないですが・・・


終わり

【MySQL】サブクエリを使った時のメモ

久しぶりの更新です。

今回はMySQLのお話です。
会社で最近SQLを書くことがありまして、その際のメモです。

MySQLにはサブクエリという機能がありSQLステートメント中に別のSQLステートメントを
指定することができます。
つまり入れ子になったSQLステートメントのリソースをSQLステートメントに使用できる
ということです。

例えばFROM句やWEHRE句などですね

それを使って今回SQLを書きました。

まずサブクエリの書き方として「()」で加工必要があります。
(SELECT 
  *
FROM 
  `dual`
)
こんな感じですね。

実際にWHERE句に指定するとこうなります。
SELECT 
  * 
FROM 
  `user`
WHERE 
  `user_id` IN
  (
    (
      SELECT 
        `user_id`
      FROM   
        `dual`
    )
  )
WHERE句に指定する場合には上記のようなSQLでいいのですが、
FROM句に指定する場合にはもう一手間必要になります。

FROM句に指定する場合はこうなります。

SELECT 
  * 
FROM 
  (
    SELECT 
      `user_id`
    FROM   
      `dual`
    ORDER BY
      `user_id` DESC
  ) AS `dual`
このようにASでエイリアスをつけてやる必要があります。

メリットとしては普段出力することしかできないMAXのみの取得や
GROUP BYでグループ集計する前にORDER BYで並べ替えを先に行うことができる等が
あげられるかと思います。
ただ、デメリットとしてパフォーマンスが落ちてしまいます。

終わり

【MySQL】SQL_CALC_FOUND_ROWSを使って取得件数を取得する

ということでMySQLネタ２つ目です。

今回仕事でページャを作ることになったのですが、その際のメモです。

SQLでLIMITを取得してページごとに取得する位置を変えられるのはいいのですが、
LIMIT句をつけているといちいちLIMIT句なしで全件カウントしないといけません。

全部実装し終わってから教えてもらったのですが、

「SQL_CALC_FOUND_ROWS」というのがあるそうです。

これは、LIMIT句が付いているクエリでも結果の取得件数を取得できるらしいです。

SELECT 
 SQL_CALC_FOUND_ROWS
 *
FROM 
 `emp`
LIMIT 0, 20;
こういう風にセットします。

セットした後

SELECT 
FOUND_ROWS();
これで取得できます。

SELECTを二回走らせるのに代わりはないですが。
記述が楽なので次から使っていこうと思います。


終わり

【MySQL】さくらサーバのMySQLにコンソールでログインする【さくらサーバ】

別に会社とか仕事で関係無かったのですが、自分で借りている　「さくらサーバ」　に
ちょっと用事があったのでその際のメモです。

ご存知の通り、「さくらのレンタルサーバ」ではMySQLを使えるわけですが、
「同じサーバ内にMySQL」はありません。
ですので

mysql -u ユーザ名 -p
とやってもパスワード入力画面は出てきますが、エラーが表示されてしまいます。

そこで、「-h」オプションをつけてやります。
MySQLコマンドでは「-h」をつけることによって外部のサーバに接続することができます。

mysql -u ユーザ名 -h ホスト名 -p
これで接続できました。
ホスト名はプログラムでいつも接続している
ホスト名(mysql***.db.sakura.ne.jpこんな感じのやつ)でOKです。

終わり

【PHP】PHP+MySQLでトランザクション【MySQL】

PHP＋MySQLでトランザクションをするお話です。

基本的にPHPでシステム開発を行うときは何かしらのフレームワークを使うことが多いですが、
今回はPHPのベタ書きでMySQLのトランザクション処理の仕組みについて書いてみたいと
思います。

まずトランザクション処理とは
複数テーブルの更新や挿入など別々のテーブルがお互いを参照するようなデータを
操作するときに使います。

例えば、テーブルAとテーブルBがあるとします。
テーブルBではテーブルAのPKをFKとして参照しているといった場合にどちらか一方が
こけてしまった場合データの整合性がとれなくなるためシステムを破壊しかねないです。

ですのでこのような更新をDBで行う場合はトランザクション処理は必須となります。

今回はざっくり書いてみます。
<?php

// 接続文字列
$server = 'サーバ名';
$db     = 'DB名';
$user   = 'ユーザ名';
$pass   = 'パスワード';

// MySQL接続
$con = @mysql_connect($server, $user, $pass);
if (!$con)
{
    exit('DBに接続できませんでした。');
}

// DB選択
mysql_select_db($db, $con);

try
{
    // 自動コミットをオフ
    $query = 'set autocommit = 0';
    mysql_query($query, $con);

    // トランザクションスタート
    $query = 'begin';
    mysql_query($query, $con);

    // DBにデータをINSERT
    $query  = "insert info `emp` (`id`, `name`)values('0001', 'user')";
    $result = mysql_query($query, $con);

    // クエリが成功したかどうかのチェック
    if ($result === true)
    {
        // 正常終了なのでコミット
        $query = 'commit';
        mysql_query($query, $con);
        echo 'コミットしました。';
    }
    else
    {
        // 更新失敗なのでロールバック
        $query = 'rollback';
        mysql_query($query, $con);
        echo 'ロールバックしました。';
    }

}
catch (Exception $e)
{
    // 例外が発生したのでロールバック
    $query = 'rollback';
    mysql_query($query, $con);
    echo 'ロールバックしました。';
}

// 接続をクローズ
mysql_close($con);
このような流れになるかと思います。
また、MySQLには罠がありまして、デフォルトで自動でコミットされるようになっております。

それをtryの直後に「set autocommit = 0」でオフにしています。
autocommitが1の場合はトランザクション処理中であろうともクエリが成功した場合
その場でコミットされてしまいます。

終わり

【MySQL】コマンドラインからUPDATE、INSERTするときの注意

今日会社でDB作成中にハマったのでメモ

普段はPhpMyAdminからデータを入れたりするのですが、
今回の案件が膨大なデータ量だったのでコマンドラインから
SQL文を実行する事になったときにハマりました。


はい、文字コードです。

INSERTをする際に文字コードを指定しないといけなかったみたいで
すべて文字化けしたままDBに挿入されていました…

その際の解決方法をメモしておきます。

下記を指定するだけでした。
SET NAMES 'utf8';
これは、今回のやり取りでどの文字コードを使うという指定をするものです。
ですので、これを指定したあとにSQL文を実行すると指定した文字コードでデータが
挿入されます。

これで無事データの挿入が出来ました。
次から気をつけよう。

【追記】
SET NAMESにはSQLインジェクションの脆弱性があるようで
コマンドライン上から使用するのは大丈夫なようですが、アプリケーションから
利用する場合は注意が必要ということらしいです。

終わり

【MySQL】MySQLは大文字と小文字を区別しない

という訳でMySQLは大文字と小文字を区別しません。
今回システムを作っていて初めて気づきました。

その件について解決方法と言うか対策を調べたのでメモしておきます。

まず、今のところ大文字と小文字を区別しないのは下記の２つです。

・char
・varchar

それ以外にあるかどうかはまだ確認していません。

その解決方法ですが、大文字と小文字を区別したいカラムに「BINARY属性」を指定します。

これによって大文字小文字を区別できるようになります。


終わり