DB(MySQL)からのselect結果をJSON形式で返すAPIを作っていた時の話。
テーブル定義上ではint型で定義しても、JSONレスポンスで返すと何故か文字列に変換されていたので、色々調べてみると、PDO周りの設定が問題でした。その時調べた事とかメモ書き程度にまとめときます。
動作環境はCakePHP3.69とMySQL5.7です。
エミュレータモード
PDOの設定でこんなのがあります。
# PDO::ATTR_EMULATE_PREPARES
詳細は丁寧にまとめて下さっている方々がいるので、以下の参考文献を一読頂ければと思いますが、データベース側が持つプリペアドステートメント機能のエミュレーションをPDO側で行うかどうかを設定するらしいです。
・PDO::ATTR_EMULATE_PREPARESのON/OFF比較
・PHPでデータベースに接続するときのまとめ
エミュレータモードをon/offにした時のパフォーマンスを計測して下さっていますが、onにするとデータベースへの通信回数が減らせるので、確かに高速に動作しています。ただこの設定をon(true)にすると、取得結果の型情報が失われてしまう模様。
これが原因で全ての項目がstring型で返却されてました。
CakePHP3ではどうなってる!?
どうやらPHP5.2以降ではデフォルトtrueになってるようですが、CakePHP3のvendorの中身を確認してみると、MySQLでは何も定義されてなかったので、デフォではONになってるようです。
// vendor/cakephp/src/Database/Driver/Mysql.php
$config['flags'] += [
PDO::ATTR_PERSISTENT => $config['persistent'],
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
];
逆にMySQL以外では全てOFFになってました。
// vendor/cakephp/src/Database/Driver/Postgres.php
$config['flags'] += [
PDO::ATTR_PERSISTENT => $config['persistent'],
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
];
// vendor/cakephp/src/Database/Driver/Sqlite.php
$config['flags'] += [
PDO::ATTR_PERSISTENT => $config['persistent'],
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
];
// vendor/cakephp/src/Database/Driver/Sqlserver.php
$config['flags'] += [
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
];
エミュレーションをOFFにしてみる
config/app.phpのDatasources内でflagsの項目があるので、これに設定すれば変更できます。
// 変更前
'flags' => [],
// 変更後
'flags' => [PDO::ATTR_EMULATE_PREPARES => false],
設定を変更すると確かに型情報が失われずに取得できました。
・・・が別の問題にぶちあたる。
SQLエラー(Invalid parameter number)
プレースホルダのパラメータを再利用してるとエラーになります。
// SQL
WHERE DATETIME1<=:now AND DATETIME2>:now AND DATETIME3<=:now AND
DATETIME4>:now
// PHP
$this->con->execute($sql, ['now' => $now])->fetchAll('assoc');
どうやら静的プレースホルダを処理する場合、パラメータは再利用できないようですね。
これなら動きます。
// SQL
WHERE DATETIME1<=:now1 AND DATETIME2>:now2 AND DATETIME3<=:now3 AND
DATETIME4>:now4
// PHP
$this->con->execute($sql, ['now1' => $now, 'now2' => $now, 'now3' =>
$now, 'now4' => $now])->fetchAll('assoc');
こちらの記事が参考になります。
SQLSTATE[HY093]: Invalid parameter number: number of bound variables
does not match number of