カテゴリー「データベース」の27件の記事

MySQLのテーブル情報

MySQLのテーブル情報を詳しく参照したいときのSQLコマンドメモ。


DESC hogehoge

いつも使うやつ、どんなカラムがどんな属性で構成されているかを確認することができる。


SHOW FULL COLUMNS FROM hogehoge

DESCの情報に接続照合順序、権限を追加して表示する。DESCじゃ知りたい情報が出せないときに使っても、こちらも大した情報は出力しない。


SHOW CREATE TABLE hogehoge

読んで字のごとく、テーブルを作成した時のCREATE文を表示するので、知りたい情報はまずこれで確認できる。


SHOW TABLE STATUS

全テーブルの詳細情報を出力する。これを使って必要な情報が含まれないケースというのはあまり考えにくいが、出力内容が多くなってしまうので場合によってCREATE文出力コマンドと使い分けるといいかも。さっきmy.cnfをいじっていて、使っていないはずのInnoDBタイプのストレートエンジン用に6GBもメモリを確保する設定になっていたので、無駄だと思い設定を削除して反映しようとしたところ、エラーが出力された。なのでこのコマンドを使って全テーブルのストレートエンジンタイプを出力して検索したところ、2つだけInnoDBのテーブルが存在した。という使い方で役に立ちました。

MySQLコマンドの\G

MySQLコマンドで\Gというやつがあります。SQLコマンドの終わりにセミコロンの代わりにこれを付けると、出力されるテーブル情報が立てに並ぶので、たくさんカラムがあるテーブルをSELECTするときなんかに使うと視認性が上がって便利です。

localhost-mt> SELECT * FROM mt_ipbanlist;
+--------------+-------------------+----------------------+----------------------+----------------+-----------------------+-----------------------+
| ipbanlist_id | ipbanlist_blog_id | ipbanlist_created_by | ipbanlist_created_on | ipbanlist_ip   | ipbanlist_modified_by | ipbanlist_modified_on |
+--------------+-------------------+----------------------+----------------------+----------------+-----------------------+-----------------------+
|            2 |                30 |                 NULL | 2011-09-02 13:12:04  | 218.45.229.170 |                  NULL | 2011-09-02 13:12:04   |
|            3 |                30 |                 NULL | 2011-09-06 11:28:19  | 202.232.197.10 |                  NULL | 2011-09-06 11:28:19   |
|            4 |                30 |                 NULL | 2011-09-06 11:28:19  | 202.232.197.10 |                  NULL | 2011-09-06 11:28:19   |
+--------------+-------------------+----------------------+----------------------+----------------+-----------------------+-----------------------+
3 rows in set (0.00 sec)

localhost-mt> SELECT * FROM mt_ipbanlist\G
*************************** 1. row ***************************
         ipbanlist_id: 2
    ipbanlist_blog_id: 30
 ipbanlist_created_by: NULL
 ipbanlist_created_on: 2011-09-02 13:12:04
         ipbanlist_ip: 218.45.229.170
ipbanlist_modified_by: NULL
ipbanlist_modified_on: 2011-09-02 13:12:04
*************************** 2. row ***************************
         ipbanlist_id: 3
    ipbanlist_blog_id: 30
 ipbanlist_created_by: NULL
 ipbanlist_created_on: 2011-09-06 11:28:19
         ipbanlist_ip: 202.232.197.10
ipbanlist_modified_by: NULL
ipbanlist_modified_on: 2011-09-06 11:28:19
*************************** 3. row ***************************
         ipbanlist_id: 4
    ipbanlist_blog_id: 30
 ipbanlist_created_by: NULL
 ipbanlist_created_on: 2011-09-06 11:28:19
         ipbanlist_ip: 202.232.197.10
ipbanlist_modified_by: NULL
ipbanlist_modified_on: 2011-09-06 11:28:19
3 rows in set (0.00 sec)

MySQLにログインした状態で\?と打つと、他にも便利なMySQLコマンドの一覧が出力されます。postgresでメタコマンドと呼ばれているやつで、RDBMSではこういった機能は大抵備え付けられているようです。

SQLで最後に挿入されたレコードを取得する

SQLで最後に挿入されたレコード(最新のレコード)を取得する方法を調べ1ました。

SELECT MAX ( カラム名 ) FROM テーブル名

上記のSQLで任意のカラムの最大値を基準にレコードを一件出力します。このとき、オートインクリメントされるIDや、date型のカラムを指定して引っ掛けると、最新のレコードを抽出することになります。

また、同様に最も古いレコードを求めるには以下のようなSQLを利用することができます。

SELECT MIN ( カラム名 ) FROM テーブル名

頻繁に使いそうな構文ですが、WEBアプリケーションを作り始めて5年経つまで使う機会がありませんでした。他の方法で代用できるからかも知れないが、これが一番シンプルなので、これからはこれを使おうと思います。

※MySQL5.2、Oracle10gで動作確認できました。

MYSQLで強い権限を持つユーザの作成

■localhostからのみ接続可能な全ての権限を持つユーザの作成
GRANT ALL ON データベース名.* TO ユーザ名@localhost IDENTIFIED BY 'パスワード';

■どのホストからでも接続可能な全ての権限を持つユーザの作成
DRANT ALL ON データベース名.* TO ユーザ名@"%" IDENTIFIED BY 'パスワード';

MYSQLのプロセスなどを管理するユーザを作るときに必要だが、どのホストにもその権限を与えてしまうのは危険なので、上のSQLでローカルホストからの接続に限定したほうがよいでしょう。

SQL*PLUSで余分な出力を省くコマンド

SQL*PLUSを使っていると、カラム名や-が大量に出力されて邪魔になることがあります。そんなときは以下のコマンドをセットで入れましょう。何もでなくなります。

set pagesize 0
set head off
set feed off

FLUSH構文(MySQL)

FLUSHステートメントは、MySQLに利用された様々な内部キャッシュをクリア、または再ロードします。FLUSHを実行する為には、RELOAD権限を持つ必要があります。

以下にflush_optionの例を挙げます。

LOGS

全てのログファイルを閉じ、再オープンします。もしバイナリログが有効であれば、バイナリファイルのシーケンス番号は前のファイルと比較して1つ増加されます。

TABLE | TABLES

どのテーブル名づけられていないときに、全てのオープンなテーブルを閉じ、利用中の全てのテーブルを強制的に閉じます。これはクエリキャッシュもフラッシュします。複数のテーブル名があると、与えられたテーブルだけをフラッシュします。

1年後の日付を取得する(Oracle)

というか、未来や過去の日付を取得するにはADD_MONTHS関数を使用するのが便利です。以下のように使います。

--------------------------------------------------

1年後  SELECT ADD_MONTHS(sysdate,12) FROM dual;

1年前  SELECT ADD_MONTHS(sysdate,-12) FROM dual;

--------------------------------------------------

表領域の作成と確認(Oracle)

1.表領域「tbl01」を20Mのデータファイル'c:\data\usrdata.dbf'で自動拡張禁で作成
---------------------------------------------------------------
create tablespace tblsp01
                  datafile 'c:\data\usrdata.dbf' size 20m autoextend off

---------------------------------------------------------------

2.作成した表領域を、データディクショナリビューで確認する
---------------------------------------------------------------
select * from dba_tablespaces; データファイルに関する情報の確認
select * from dba_data_files; ※表領域に関する情報の確認
---------------------------------------------------------------

3.作成した表領域を削除する
---------------------------------------------------------------
drop tablespace tblsp01

---------------------------------------------------------------

データディクショナリ・ビュー(Oracle)

ユーザ、権限、制約などの根幹的な情報を保持する読み取り専用の表とビュー。

データディクショナリ表は、DDLなどが実行されスキーマ構造が変更されるなどの行為によって自動更新される。

静的ディクショナリともよばれる⇔動的パフォーマンスビュー
ディクショナリ表へは直接アクセスできない。以下のビューを経由してアクセスを行う。

ALL~
  ユーザがアクセスできる情報(他のスキーマ含む)

USER~
  ユーザのスキーマ情報

DBA~
  データディクショナリ全体に関連する情報(SELECT ANY TABLE、SELECT ANY DICTIONARY権限が必要:DBAロール)

コネクションプーリング

データベースにアクセスするとき、アクセスのたびに接続(コネクション)するのではなく、あらかじめ一定数のコネクションを確立しておき、それを使いまわす手法。

データベースアクセスの負荷を減らすために用いられる。

データベースへのアクセスを行う場合には、データベースへのコネクションを確立する必要がある。コネクションプーリングの手法を用いない場合、コネクションは一つのアクセス(読み出し・書き込み)が終了すると切断される。そして、データベースに対する別の要求が起こったときは、新たにコネクションを確立する。また複数の人が同時にデータベースへのアクセスを要求した場合、人数分のコネクションを確立しなければならないため、データベースを用いたwebアプリケーションなどで一度に大量のアクセスがあると、性能が低下することがある。

コネクションプーリングでは、アクセスのたびに接続を確立するのではなく、あらかじめ一定数のコネクションを確立しておき、それを使いまわす。これにより、アクセスごとにコネクションを確立・切断する手間が省け、大量のアクセスによって性能が低下することもなくなる。しかし、用意したコネクションを超えたアクセスがあった場合には、越えた分のアクセスはコネクションに空きが出るまで待たされることになる。