SQL

DBデータの一括置換

2012年11月02日
データベースに登録しているデータを一気に置換するコマンドはないかと調べてみました。
【replace】関数で実現できるようです。

tableZに
id column1
1 dataA ・・・
2 dataB ・・・
3 dataA ・・・
  ・
  ・
  ・
というデータがあり、【data】を【field】に変更したい場合、
update tableZ set colum1 = replace(colum1, ’data’, ’filed’);
で変更できます。

変更後は
id column1
1 fieldA ・・・
2 fieldB ・・・
3 fieldA ・・・
  ・
  ・
  ・
になります。

使ったことがある気がしますが、忘れてました。

ここまでは何の問題もなかったのですが、updateが処理した件数を取ってみて、驚きました。
ありえない数だったのです。
該当する件数は100件ほどなのに、処理件数は1000件近かったのです。

どうやら処理件数はデータ全件数になるようです。
全ての行に対してreplaceを実行するからでしょう。

ああ、驚いた。

SQL

MySQLエラー取得

2012年10月27日
久々にMySQLを触りました。

SELECTはできたのですが、INSERTがどうしてもできません。
初めは文字コードが違っているので挿入できないのかと思ったのですが、どうやらそうではないらしい・・・
エラー内容を取得できればヒントが得られるのに。

PostgreSQLは【@】でエラーを吐かない様にしていなければ、エラー内容が吐き出されます。
MySQLは【@】を取っても、エラーを吐いてくれません。

どうすれば・・・と考えていると、ふと思い出しました。そう言えば、何かコマンドがあったような気がします。

調べたらすぐ分かりました。
【mysql_error()】
で直前のエラー内容を取得できます。

しばらく使わないと忘れるので、ソース中に残し、コメントアウトしておきます。

SQL

MySQL自動バックアップと最適化

2012年03月12日
昨日で東日本大震災からちょうど一年たちました。あっという間だった気もしますし、やっとという気もします。
私は被災したわけではありませんが、1年前は激しい頭痛と吐き気に襲われ、眠れなかったのを覚えています。
実際に被災された方は、どのようにお感じておられるのでしょうか。一人でも多くの方が、少しでも早く希望を持って生きられるようになることを願ってやみません。
そのために、自分には何ができるのか。忘れないように、ずっと考え続けていかなければと肝に銘じました。
また、TVを通してですが、被災地で力強く立ち上がっておられる方を拝見すると、私も負けてはいられない、と気持ちが奮い立つこともしばしばあります。私の方が励まされてしまいます。


さて、話は変わり、MySQLのまとめです。
一応DB運用ができるようになったので、DBのバックアップと最適化ができるようにしました。

バックアップ
Telnet接続して
mysqldumpのパス -u ユーザーID --password=’パスワード’ --opt データベース名 > BKファイルへのパス
となります。
MySQLの定期的バックアップ
でも、このコマンドにはユーザーIDとパスワードが含まれています。これを隠したいので方法を探してみました。
確かPostgreSQLのときには、パスワードファイルを作った記憶があります。おそらくMySQLでも同じようにできるはずです。
MySQL 5.1 リファレンスマニュアル
どうやら【.my.cnf】に記述すれば自動で読んでくれるようです。
調べてみると、すでに作成されていました。
ユーザーIDとパスワードを抜いて動かしてみます。
mysqldumpへのパス --opt データベース名 > BKファイルへのパス
これでOKです。
参考にしたサイトは、バックアップスクリプトを書いて、それをcronで実行する例ばかりだったのですが、上のコマンドをcronで実行しても動きます。
私の場合、ファイル名を変えて履歴を取っておくというようなことはとりあえず必要ないので、単純にバックアップファイルを上書きしていきます。

最適化
MySQLの最適化を参考にしました。
私の場合、【mysqlcheck -o -u ユーザー名 -p パスワード データベース名】です。
バックアップ時と同様に、ユーザーIDとパスワードを抜いて、
mysqlcheckへのパス -o データベース名
となります。

SQL

MySQLのトランザクション

2012年02月06日
MySQLでトランザクションを切るようにコーディングしていました。
これをテストしようと、エラーを起こして、ロールバックしてみたのですが・・・
なぜかロールバックされません。エラーは起きています。
何度やってもダメです。何が原因なんでしょう。

調べてみると、MySQLには、様々なテーブルがあり、テーブルの種類によってできることとできないこと、特徴が異なるようです。
メジャーなものに、【MyISAM】と【InnoDB】があります。
今回問題になっているトランザクションは、【MyISAM】ではできないことが判明しました。
MySQL、MyISAMとInnoDBを選ぶ方法
(このサイトは、やたらと何かを読み込んでいるようで、滅茶苦茶遅い。ブラウザがかたまる可能性あり)
[MySQLウォッチ]第20回
辺りが参考になります。

しかも、デフォルトだと【MyISAM】が作られるようです。
ううん、トランザクションくらいは普通に使いたいと思うんですが、そうでもないのかなぁ。

パフォーマンスは落ちるようですが、トランザクションは必要なので、テーブルタイプを【InnoDB】に変更してみました。
MySQLの基礎を学ぼう
今回はロックをかけるほどの精度はいらないので、ロックについてはスルーします。

トランザクションを始める前に、オートコミットをオフにする必要がある場合もあるようです。まぁ、オフになっていても、安全のためオフにすればよいと思います。
トランザクションを始める前に、
mysql_query("set autocommit = 0", MySQLへの接続変数);
でオフにできます。

PHPからMySQLへの接続をまとめると、
 mb_language("uni");
 mb_internal_encoding("utf-8");
 mb_http_input("auto");
 mb_http_output("utf-8");
 $objMySQLConID = mysql_connect(MySQLのホスト名, MySQLのID, MySQLのパスワード);
 mysql_query("set names utf8", $objMySQLConID);
 $blnSelDB = mysql_select_db(MySQL名, $objMySQLConID);
 mysql_query("set autocommit = 0", $objMySQLConID);
 mysql_query("start transaction", $objMySQLConID);
 mysql_query("begin", $objMySQLConID);
 mysql_query(SQLなどの実行, $objMySQLConID);
 //コミットする場合
 mysql_query("commit", $objMySQLConID);
 //ロールバックの場合
 mysql_query("rollback", $objMySQLConID);
 mysql_close($objMySQLConID);
となります。

色々な例をみていると、【mysql_query】の2番目の引数【$objMySQLConID】がないものもあります。
PHPのマニュアルをみると、なくてもいいとなってます。
ですが、どうにもないと気持ち悪くて仕方ないので、つけておきます。

SQL

MySQLの文字化け

2012年01月31日
今回の開発はMySQLでやているのですが、どうもDBへの入出力で文字化けが起こります。
今まで、サイトはEUC-JPでコーディングしていたのですが、MySQLはUTF-8ですし、UTF-8だからMySQLで動かしたいので、なんとかUTF-8の文字化けを解消したいです。

調べると、解決方法が見つかりました。
mb_language("uni");
mb_internal_encoding("UTF-8");
mb_http_input("auto");
mb_http_output("UTF-8");
$objMySQLConID = @mysql_connect(ホスト名, ユーザーID, パスワード);
mysql_query("SET NAMES UTF8", $objMySQLConID);
$blnSelDB = @mysql_select_db(サーバー名, $objMySQLConID);
これでOKなようです。

早速変更してみました。化けなくなりました。

SQL

MySQLのテーブル操作

2012年01月27日
今までWebサイトにはPostgreSQLを使っていました。ずいぶん前にDBを作ってもらった関係で、文字コードがEUC-JPになっています。
今作っているシステムではUTF-8を扱いたいので、どうしようかと困っていました。

そして、ふと思い出したのですが、テストのために作ってもらったMySQLがあることを思い出しました。調べてみると、このMySQLはUTF-8で作ってもらっています。
そこで急遽MySQLを利用することにしました。
すでにPostgreSQLで動いているので、DB部分をMySQLに置き換えます。

まずはテーブル作成からです。
テーブル作成は、以前テストしたときに(ボツになったので、途中で止まっているのですが)調べました。

次はテーブルにインデックスを作成して速度を少しでも上げます。
alter table テーブル名 add index インデックス名(フィールド名(サイズ));
 サイズ:フィールドがTEXT又はBLOB型の場合
インデックスについて MySQLメモ
MySQLクイック・リファレンス
などが参考になります。

作成したテーブルの一覧を表示するには、
SHOW TABLES;

テーブルのフィールド情報を表示するには、
DESC テーブル名;

インデックス情報を表示するには、
SHOW INDEX FROM テーブル名;

です。

SQL

MySQLのテーブル作成

2011年01月06日
今日はTelnetで接続して、DBにテーブルを作成しました。

まずは、テーブルを削除します。テーブル削除はPostgreSQLと同じです。
 drop table テーブル名;

次にテーブルを作成します。
テーブル作成もPostgreSQLと同じです。
 create table テーブル名 (
   col1 text,
   col2 int,
   col3 timestamp
 );

デフォルト値やNullの禁止などももちろんできます。
データ型などは
MySQLコマンドリファレンス
が参考になります。

テーブルがちゃんと作成できたかどうかは、
 desc テーブル名;
で確認できます。カラム名、データ型などが一覧表示されます。

DBにあるテーブルの一覧は、
 show tables;
で表示されます。

今日はとりあえずテーブル作成のみです。
項目追加や削除、インデックスなどについてはおそらく後日必要になるでしょうから、そのときに。

SQL

MySQLへの接続

2011年01月05日
こんどのクライアントさんとのお仕事は、おそらくMySQLを使うことになりそうなので、テーブルの作成や、PHPからの接続をあらかじめやっておこうと思います。
使わないことになったとしても、自分のキャリアのために経験しておきたいと思います。
派遣先でWebの開発をやったときは、PostgreSQLだったので、自分のWebサイトはPostgreSQLで組んでいます。
いい機会なので、MySQLも経験しておこうと思います。
といっても、SQLコマンドほとんど同じだそうです。

まずはDBへの接続からです。
私の使っているサーバーでは、テーブルを作成したり、直接DBをみたり触ったりするには、Telnetでサーバーへ接続しなければいけません。

PostgreSQLは、自動でログインできるようにスクリプトを作っています。
それと、レンタルサーバー会社から送られてきた設定情報を参考にスクリプトを編集しました。
【mysql -h ホスト名 -u DBユーザーID -p DB名】
を自動で流すようにスクリプトを作ります。
当然のごとく、パスワードプロンプトが表示されます。パスワードを入力すると無事ログインできました。

これを、毎回パスワードを入力しなくていいようにするのが目標です。

初めに試したのが、スクリプトへパスワードを設定する方法です。
【-p】オプションの後ろへパスワードを設定します。
【mysql -h ホスト名 -u DBユーザーID -pパスワード DB名】
でパスワードを自動で送信し、DBへ接続できます。
【-p】オプションと【パスワード】はくっつけます。半角スペースは入れません。
ここで少々はまってしまいました。パスワードに【&】が含まれていたため、それ以降の文字がパスワードとして認識されず、何度やってもエラーになっていました。
【&】を【\】でエスケープすると、問題なくログインできるようになりました。

スクリプトにパスワードを含めるやり方は、パスワードが盗み見られる可能性があるので、セキュアな方法ではありません。
なので、次はもっとセキュアな方法をさがすことにしました。
PostgreSQLの場合を見てみると、自動スクリプトへはパスワードを設定していません。
そういえば、Webサーバーへパスワードを設定したファイルをアップロードしたような記憶があります。
サーバーを調べると、【.pgpass】へパスワードを設定していました。
おそらくMySQLにもなにかあるはずです。

ネットを調べると、いくつか見つかりました。
MySQLクライアントログイン時にパスワードを省略
MySQL my.cnf オプション設定ファイル
これらが参考になります。

【.my.cnf】へ
-------------------------------
[client]
host="ホスト名"
user="DBユーザー名"
password="DBパスワード"
-------------------------------
を書き込み、WebサーバーのトップディレクトリにアップロードすればOKです。
-------------------------------
[client]
password="DBパスワード"
-------------------------------
だけでもOKです。
【=】の後ろのパスワードは【"】でくくらなければダメなようです。もしかしてサーバーごとに違うのかも。
そして、Telnetへ接続するスクリプトの【-p】オプションをはずします。
【mysql -h ホスト名 -u DBユーザーID DB名】
です。

これでパスワードを自動で読んでくれるようになりました。

SQL

Insertしたデータのシーケンスを取得

2009年07月26日
シーケンスを設定しているテーブルにデータをInsertすると、自動でシーケンス番号が登録されます。
この値を取得したい場合があります。

PostgreSQLでは、【RETURNING】という関数が用意されています。
シーケンスフィールドが、【id】だとすると、

INSERT talbe (field) VALUES ('Insertしたい値') RETURNING id;

で割り当てられたシーケンス番号が取得できます。

SELECT id FROM table;
のSQLを発行したときと同じように値が返ってきます。


ところが【RETURNING】関数は、PostgreSQL 8.2以上でサポートされている関数です。
8.2以下だと別の対処法が必要です。

あまり頻繁にデータが登録されるテーブルではないので、【MAX】関数を代用します。

SELECT MAX(id) FROM table;


PostgreSQLのバージョンチェックは、
SELECT version();

です。

SQL

テーブルの値を操作してupdate

2009年07月02日
すでにテーブルに登録されている値に文字列を追加してupdateしたり、文字の一部を保存しなおしたりするSQLです。

テーブル【table_name】の文字フィールド【field】に文字【_tuika】を追加して、updateするSQLです。
update table_name set field = field || '_tuika';

追加した【_tuika】の除いて、再updateするSQLです。
update table_name set field = substr(field, 0, length(field)-5);

SQL

PostgreSQL バックアップとリストア

2009年02月23日
テーブル単位でDBを移転したくなったので、PostgreSQLのバックアップとリストアに挑戦してみました。
以前にやろうとして挫折してしまいました。

コマンドそのものは簡単です。

バックアップ(テーブル単位)
pg_dump -t テーブル名 DB名 > 出力ファイルパス
バックアップ(DB単位)
pg_dump DB名 > 出力ファイルパス

リストア
psql DB名 < 入力ファイルパス

こう書けば至極簡単なのですが、DBの指定、ファイルのパスの指定方法がわかりません。サーバーについてはほとんど知らないし、どこからコマンドを打っているのかいまいちわからずにやってるので、あてずっぼ色々やってみました。

スクリプトからUNIXへ降りて実行できるのかもしれませんが、telnetから実行しています。

DB名はWebサーバーからDBサーバーへの接続コマンドを参考にしてみました。
ファイルはおそらくWebサーバールートに出力されるはずです。

DBとpg_dumpのバージョンが違うと怒られました。
これは【-i】オプションで無視できます。

pg_dump -i -t テーブル名 DB名 > 出力ファイルパス
ファイルが出力されました!


ここまでくればリストアはすんなりいきます。

めでたくテーブル移動に成功です。

これをcronなどで動かせば自動バックアップができるようになります。

SQL

PostgreSQL テーブルフィールド変更

2008年06月06日
うっかりPostgreSQLで作成したテーブルに、余分なフィールドがあったので、削除しようかと思って、方法を調べてみました。


PostgreSQLテーブルの項目名変更は
alter table テーブル名 rename 旧フィールド名 to 新フィールド名;

テーブル名の変更は
alter table 旧テーブル名 rename to 新テーブル名;


フィールド削除はできないようです。ちょっと不便ですね。

フィールドタイプも変更不可です。


どうしても削除したい場合は、テーブルを作り直し(?)しなければいけません。
drop table テーブル名;


フィールドを追加する場合は、
alter table テーブル名 add フィールド フィールドタイプ;
です。




SQL

PostgreSQL テーブル作成

2008年06月05日
PostgreSQLでテーブルを作成するには、

create table テーブル名 (
  id serial,
  フィールド1 text,
  フィールド2 int,
  フィールド3 date,
  フィールド4 timestamp
);


デフォルト値を設定するときは、

フィールド int default 0

これでデフォルトが0になります。


キーを設定する場合、
create table テーブル名 (
  id serial,
  フィールド1 text,
  フィールド2 int,
  フィールド3 date,
  フィールド4 timestamp,
  primary key (
   フィールド1,
   フィールド2
  )
);


フィールドには
smailint / int2   2バイト整数
nteger / int / int4   4バイト整数
bigint / int8 decimal(a, a) / numeric(a, s)   10進型
real / float4   6桁単精度浮動小数点
double precision / float8   15桁倍精度浮動小数点
serial   4バイト順序
bigserial   8バイト順序
text   無制限テキスト
char(文字数) / character   固定長文字列 (最大 4096 文字)
varchar(文字数) / charcter varying   可変長文字列 (最大 4096 文字) boolean / bool   true / false
date   日付
time   時間
timestamp   日付時間
oid   画像などを保存?
があるようです。
2017年08月
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31
先月
来月
カテゴリ
検索
関連サイト
プロフィール
【ネットネーム】
みゅー

キャラクター by ツカエルサイト


【趣味】
・プログラミング
・写真
・楽器演奏
・手芸
・日曜大工
・家の中の不便を便利に
・考える
・自然科学
・猫と戯れる
・ジグソーパズル

【仕事】
・派遣社員でSE・PGやっていました。次の良い仕事がみつからないので、ビジネスへ注力しようと思っています。
・経験言語:VB6、VB.NET、PHP、Perl、JAVA、Oracle、SQLServer、PostgreSQL



【名前】
アン(足)

2014年4月22日生まれ 女の子
あだ名は くノ一
普段は普通にしゃべりますが、興奮すると『ニャ』を連発します

【趣味】
・狩
・ご飯を食べる
・ママのお手伝い

【仕事】
ママのビジネスの看板招き猫
ママの代わりにPC入力
仕事中のママの右腕を温める
ママに心配をかける
いろんな事をしでかしてママを退屈させない

RSSフィード
コメント
ダイソーのコンニャクパフ
2017-08-13 20:07:29
ダイソーのコンニャクパフ
たかこ さん
2017-08-13 15:35:04