著者:takahashi
“MySQL版SSMS”なGUIツール MySQL Workbench
Microsoft SQL ServerというRDBMSには、GUIでDBやテーブル、データの編集ができる”SQL Server Management Studio(SSMS)”が無料で用意されています。
SQL Server Management Studio (SSMS) のダウンロード – Microsoft
一方MySQLはサードパーティ製のツール以外ではあまり充実している印象がなかったのですが、先日、公式のツールとして公開されている”MySQL Workbench”というソフトウェアで、実はSSMS同様GUIでDBを操作できる機能があることを知りました。
接続したMySQL内のスキーマやテーブルの一覧を表示したり、現在DBへ保存済みのデータ一覧をGUI上で確認したり、編集したりもできます。
さらにパフォーマンス解析などを行うことも可能です。
CLI環境とSQL文だけでもMySQLは問題なく操作できますが、開発やデバック時はCLIだけではとても効率が悪い場合もあります。
このツールは無料で利用できるので、MySQLを使ったある程度規模の大きいシステムを構築する際は、是非利用してみてはいかがでしょうか。
著者:takahashi
MySQL 8.0でユーザーを作成する方法
MySQL8.0絡みでまたしても躓いたので記事にします。
新しくWebアプリ用のユーザーを追加するために、いつも通り下のようなSQL文を実行しました。
GRANT ALL ON test.* TO testuser@'localhost' IDENTIFIED BY 'password123' WITH GRANT OPTION;
ところが、
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'password123' WITH GRANT OPTION' at line 1シンタックス…エラー…？？？
どこか文字が全角になってたりするのだろうな～と最初はおもって何度も確認したのですが、特におかしな点は見つからず…
ちなみに後で同じSQL文を他のMySQL 5系のMySQLサーバーで試してみたところ
mysql> GRANT ALL ON test.* TO testuser@'localhost' IDENTIFIED BY 'password123' WITH GRANT OPTION;
Query OK, 0 rows affected (0.06 sec)こんな感じで普通に通りました。
いやな予感がして調べたところ、ある事実が発覚。
MySQL8.0ではGRANT構文でユーザを作成できない – guro_chanの手帳
上記記事によると、いままではそれとなくGRANT構文でユーザーも一緒に作成できてしまう挙動だったものを、 MySQL 8.0では明確にできないように変更したのだとか…
ではどうすればいいのか、という話ですが、下記のような2行のSQL文を実行することで、同様の操作を行うことができます。
create user 'ユーザー名'@'アクセスを許可するアクセス元IPもしくはホスト名' identified by 'パスワード'; grant all on アクセスを許可するDB名.アクセスを許可するテーブル名 to '1行目で指定したユーザー名'@'一行目で指定したアクセス元IPもしくはホスト名' with grant option;
最初に書いたSQL文をこの形式に変換すると下記のようになります。
create user 'testuser'@'localhost' identified by 'password123'; grant all on test.* to 'testuser'@'localhost' with grant option;
実行してみます。
MySQL [(none)]> create user 'testuser'@'localhost' identified by 'password123';
Query OK, 0 rows affected (0.05 sec)
MySQL [(none)]> grant all on test.* to 'testuser'@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)こんどはちゃんと通りましたね。
リモート接続や指定されたDB以外はuseできないかなども確認しましたが、ちゃんと意図したとおりの動作になることを確認できました。
こんごはこちらのSQL文を使っていけば問題なさそうですね。
しかし、今まで使えていたSQL文を突然使用不能にした上に、警告メッセージもなくただシンタックスエラーにしてしまうというのは…なかなか乱暴な気がします…(汗
著者:takahashi
MySQL 8.0に更新したらownCloudがMySQLに接続できなくなった場合の対処法
先日Ubuntu(18.04)をアップデートしたところ、インストールしてあったMySQLが5系から自動で8.0に更新されました。
この状態で普段から使用しているストレージサービスをサーバー上で動作させることのできる”ownCloud”を動かしたところ…
SQLSTATE[HY000] [2054] Server sent charset unknown to the client. Please, report to the developers上のようなエラーが発生。
調べたところ、どうやらMySQL8.0ではデフォルトの文字コードが設定ファイルで未定義になっているとエラーになるようです。
ということで、/etc/mysql/my.cnfに下記のように指定を追記します。(文字コードは必要に応じて変更した方がよさそうな気がします。)
[mysqld] collation-server = utf8mb4_unicode_ci character-set-server = utf8mb4
ただし、これだけだと
SQLSTATE[HY000] [2054] The server requested authentication method unknown to the clientといったエラーが出てしまいますので、もう一行
default_authentication_plugin=mysql_native_passwordの追記が必要です。
つまり合わせると、下記の4行を追記すればOKです。
[mysqld]
collation-server = utf8mb4_unicode_ci
character-set-server = utf8mb4
default_authentication_plugin=mysql_native_passwordこれでエラーが消えて正常に動作するようになりました。
著者:asaba
【sql】INTO句で一発で新規テーブルを作る方法
ここ最近はsqlのリファレンスも積極的に読むようになりました。
zendframeworkも最初見た時はなんじゃこりゃjavaみたいと困惑することもありましたが
今になってようやくその戸惑いも消えつつあります。
普通のINSERT句で新規にテーブルを作って与えられた値をレコードに突っ込むのは
簡単ですが、そのレコードの値を新たに作った新規テーブルにまた入れるとなると骨が折れますよね。
今回良いなと思ったINTO句は、一つの命令文で新規テーブル追加と旧テーブル両方にレコード
を追加することができます。一文でできるって素晴らしいです。
SELECT 名前、価格、産地、消費期限 INTO 消費期限切れテーブル FROM 野菜テーブル WHERE 消費期限 20190809
ここでは野菜テーブルから、消費期限切れレコードの値以下の行を抽出しています。例えば
ここに消費期限レコードの値が20190801や20180122に該当すれば、新規の消費期限切れテーブルに
移送されるということです。
一時的に目的のレコードを抜き出せるので、在庫処理などに向いてそうで便利そう。
著者:asaba
【zendframework】行の要素を一行ずつ巡回する方法
ここ最近phpでsqlに触れる機会が少しあったので、せっかくなので配列に一行ずつアクセスする
処理を勉強がてらブログに残していきます。
まずテーブルを指定して、行を配列の要素として返すにはtoArray()メソッドを使います。
toArray()メソッドは、テーブルにある一つの行を配列の要素として持たせることができるメソッドで
カラムと値はkey=>valueの形で連想配列として扱われます。
</pre>
$Tables = new Tables();
$rowset = $Tables->fetchAll();
$rowsetArray = $rowset->toArray();
$rowCount = 1;
foreach ($rowsetArray as $rowArray) {
echo "row #$rowCount:\n";
foreach ($rowArray as $column => $value) {
echo "\t$column => $value\n";
}
++$rowCount;
echo "\n";
}
<pre>
一つ目のforeachで行を一つずつ見ていき、ネストされたforeachでは行のカラムの要素を一つずつ
見て一つずつ出力しているのが分かります。
これを上手く使えば、全列にアクセスして該当した値に対して指定した数値以下の値を書き換える・削除する
といった柔軟な動きを降り入れることができるのでキホンとして覚えておきたいですね。
もう少しフレームワークの動きとsqlの基礎が分かってきたらもう少し掘り下げていじってみたいと思います。
著者:杉浦
StrategyパターンとSQL文の構築
Strategyパターンはコーディングのやり方の一つです。
Strategy パターン – Wikipedia
画像は増補改訂版 Java言語で学ぶデザインパターン入門 ｜ 結城 浩 ｜ コンピュータ・IT ｜ Kindleストア ｜ Amazonより引用。
Strategyパターンはとある目的を達成するためのアルゴリズムをごっそり代えることを容易にするプログラミングパターンです。業務においては仕様がごっそり変わっても、コードへの影響を小さく抑えるために用います。場当たり的な変更を繰り返すと仕様が変わって、関係を持つクラスやメソッドが変わって、更に共通箇所が変わって、変わった共通箇所に合わせて全く関係ない場所が変わって……なんてことも起き得ます。Strategyパターンを実現する方法の一つは、変わることが予想される領域をあらかじめまとめて、まとめた部分の入出力の型を固定する、というものです。変わることが予想される領域は主に業務知識（八百屋さんのプログラムなら八百屋さんの行う業務についての知識）によって把握します。業務知識に重点を置く設計をドメイン駆動設計と言い、ドメイン駆動設計の説明はエリック・エヴァンスのドメイン駆動設計 ｜ Eric Evans, 和智右桂, 牧野祐子, 今関剛 ｜ 工学 ｜ Kindleストア ｜ Amazonに詳しく載っているらしいです。私は買ったはいいですが未だに半分も理解できていません。
ドメイン設計ほど大仰でなくとも簡単にStrategyパターンを用いることのできる部分があります。それがSQL文の構築です。データベースからデータを取得する時が特に典型です。データ取得のSQL文の中身は、任意の種類の値を任意の条件で任意の順で取得する、という言葉で抽象化できます。種類がselect, join, group by句あたり、条件がwhrere, join句あたり、順がorder by句になりやすいです。joinが二つあるのは関係するデータを引っ張ってくる理由に見たい、絞り込みたいになりやすいからです。見えない値による順番で値を引っ張ってきたいことはレアです。例えば次の様にクエリ構築をまとめた関数を作れます。
/** 次の様にニュースモデルに記述 */
public function scopeNewsListSelectQuery(QueryBuilder $query){
return // 表示したいカラム、計算結果等を決定する部分を構築するクエリビルダの集合
}
public function scopeNewsListWhereQuery(QueryBuilder $query){
return // 表示したいデータの条件を決定する部分を構築するクエリビルダの集合
}
public function scopeNewsListOrderQuery(QueryBuilder $query){
return // 表示したいデータの順を決定する部分を構築するクエリビルダの集合
}
/** 次の様にコントローラからクエリの集合体メソッドを呼び出し */
$news_list = query()->scopeNewsListSelectQuery()
->scopeNewsListWhereQuery()
->scopeNewsListOrderQuery()
->get();
これだけでは一見、単にクエリを分解しただけに見えます。実際は大きな変更や追記が必要になった時、次の様に部分的にまとまりを入れ替えることで変更部を減らせます。
例えば、ニュースと同じ仕組みでレビューを表示したい、どのニュースとレビューを表示するかはif文で記述すると複雑になるくらい微細に違う条件で決める、となった場合は次の様にできます。
/** 次の様にページモデル（ニュースモデルを抽象化したモデル）に記述 */
public function scopePageListSelectQuery(QueryBuilder $query){
return // 表示したいカラム、計算結果等を決定する部分を構築するクエリビルダの集合
}
public function scopeNewsListWhereQuery(QueryBuilder $query){
return // 表示したいニュースの条件を決定する部分を構築するクエリビルダの集合
}
public function scopeReviewListWhereQuery(QueryBuilder $query){
return // 表示したいレビューの条件を決定する部分を構築するクエリビルダの集合
}
public function scopePageListOrderQuery(QueryBuilder $query){
return // 表示したいデータの順を決定する部分を構築するクエリビルダの集合
}
/** 次の様にコントローラからクエリの集合体メソッドを呼び出し */
$news_list = query()->scopePageListSelectQuery()
->scopeNewsListWhereQuery()
->scopePageListOrderQuery()
->get();
$review_list = query()->scopePageListSelectQuery()
->scopeReviewListWhereQuery()
->scopePageListOrderQuery()
->get();
すっきりです。さらに突飛な要求が多く来たとしてもページという枠組みに収まっているならば、そうそう困りません。もし工夫なく作ろうとした場合、変更時に漏れのありがちなコピペコードになるか、複雑で読み難いうえにインデックスも張りにくい謎クエリビルディングをすることになります。
もし、SelectもOrderも違うとしたら、それはそれで入力部と表示部は同じだがクエリはごっそり代わるというStrategyパターンにするべきでしょう。
著者:takahashi
EclipseのDBViewerPluginでMySQLに接続する方法
以前の記事でEclipseにDBViewerPluginをインストールする方法についてご説明しました。
今回は実際に接続できるようにするところまでご説明したいと思います。
前回の時点で下記の画像のようにDBViewerPluginが表示されているかと思います。
左上の”DBViewerPlugin”を右クリックし、”登録”をクリックします。
すると、下記のようなウインドウが開きます。
ここで、EclipseがDBを直接扱えるようにするための”ドライバ”を指定する必要があります。
今回はMySQLを例にして説明します。
MySQLのEclipse(Java)用のドライバは下記URLから入手できます。
Windowsの場合はzip形式の方が扱いやすい(専用ソフトなしで解凍できる)と思います。
ダウンロードしたら、Eclipseからいつでも参照できる場所に保存します。
(自分の場合は、Eclipseのフォルダの中に”drivers”フォルダを作成してそこに配置しました。)
ドライバを配置したら、Eclipse上の先ほどの画面で”ファイルの追加”をクリックし、ドライバの場所を指定します。
ドライバを指定したら次へをクリックします。
JDBC Driverに”com.mysql.jdbc.Driver”を選択します。
OKをクリックします。
すると接続文字列にサンプルの文字列が入るので、<host>の部分をMySQLサーバーのホスト名、<database>の部分をDB名にそれぞれ置き換え、さらにMySQLのユーザー名とパスワードを入力します。
ここまで出来たら”テスト接続”をクリックしてエラーが出なければ接続設定はOKです。
次へをクリックします。
最後に接続設定(読み込むテーブルや文字コードの指定など)が出てくるので、必要があれば指定し、完了をクリックします。
これで設定は完了です。
この時点でEclipse上からDBへのアクセスが可能になっているかと思います。
EclipseでMySQLをお使いの方は参考にしてみてください。
著者:takahashi
PHPでMS SQL ServerをCygwinで使うべきではない理由
何故なら、公式のMSSQLドライバがCygwin用に提供されていないからです。
GitHubのMicrosoftの公式リポジトリでも、Cygwinはサポートしないとはっきりと明言されています。
Driver in cygwin enviroment #258 – GitHub
Transact SQL クライアントのオープンソースの実装であるFreeTDSについてはCygwinにもドライバがあるので、これを使えば”とりあえずは”Cygwin上のPHPからSQL Serverへ接続することはできます。
しかしFreeTDSではSQL文実行後一定時間経過しても結果が返ってこない場合、接続を切ってしまううえ、この仕様を修正するにはFreeTDSのソースを修正するしかない、という状況になっているようです。(調べて見つけた記事にそのようなことが書かれていたのですが、その記事のURLを見失ってしまいました…また見つけたら追記します。)
代替えとして、Windowsネイティブでありながら、基本的なLAMP環境を簡単に動作させることができるXAMPPというパッケージがあります
こちらなら、SQL Serverの公式PHPドライバが利用可能ですので、 個人的にはこちらを利用するのがおすすめです。
本当はUNIXライクなOSで動作させるのが一番なのですが、もしWindowsでどうしても…という方は一度検討してみてください。
著者:takahashi
fuelPHP + PDO_sqlsrvでストアドプロシージャ実行時にIMSSPエラーが出てきたときの対処法
FuelPHP上でMSSQLサーバーにあるストアドプロシージャを実行しようとしたところ、こんなエラーが発生。
SQLSTATE[IMSSP]: The active result for the query contains no fields.
エラー
見慣れないエラーコードだったので少し戸惑いましたが、調べてみたところ、解決策が見つかりました。
“The active result contains no fields” using PDO with MS SQL – Stack Overflow
このエラーが出た場合、実行するSQL文の前に
SET NOCOUNT ON;
を実行しておくとエラーを回避できるようです。
このオプションを追加したところ、自分の環境でもエラーが出なくなりました。
同じエラーでお困りの方は、是非試してみてください。
著者:takahashi
FuelPHP + pdo_sqlsrv + SQL Server で「’NAMES’ は SET オプション として認識されません。」となったときの対処法
FuelPHPにSQL Serverを連携させたPHPのWebアプリを作ったときに出た問題。
SQL文自体には問題がなかったのですが、このようなエラーが発生しました。
原因はFuelPHP側の設定ファイルに文字コード設定を入れてしまっていたこと。
// fuel/app/config/db.php
return array(
'default' => array(
'type' => 'pdo',
'connection' => array(
'dsn' => 'sqlsrv:Server=localhost;Database=hoge_db;',
'username' => 'user',
'password' => 'password123',
'persistent' => false,
),
'identifier' => '',
'table_prefix' => NULL,
'charset' => 'utf8', //←これが原因
)
);
charsetを入れると、DB呼び出し時にfuelが自動的に
SET NAMES 文字コード;
のようなSQL文を実行しようとします。
ところが、SQL Serverではこの”SET NAMES”オプションが廃止されており、これが原因でSQL実行時に毎回エラーになってしまいます。
対策としては
'charset' => 'utf8',
を
'charset' => '',
と書き換えれば、SET NAMESが行われなくなり、エラーが解決します。
いろいろ調べまくってやっと判明したぐらい細かい項目で、原因がわかるまで苦労しました…
お困りの方の参考になれば幸いです。