MySQLのDB管理

IIS de OSS 64bit > MySQL > MySQLのDB管理

• MySQL データベースのダンプ(エクスポート,ファイル出力,exp)

MySQL データベース作成 †

create database データベース名; -- 単純に作成
create database データベース名 default character set utf8 collate utf8_general_ci; -- 作成し、文字コードも指定する

MySQL データベースに権限付与 †

grant all privileges on データベース名.* to ユーザ名@"%" identified by "パスワード"; -- 外部接続用
grant all privileges on データベース名.* to ユーザ名@localhost identified by "パスワード"; -- ローカルホスト接続用(これも必要)
flush privileges; -- 設定を確実に(?)反映させるおまじない

MySQL 権限の確認 †

show grants; -- ログインしているユーザの権限が表示される
show grants for root@localhost; -- このように指定することで柔軟に権限の確認が可能
connect mysql;select * from user; -- ユーザ管理テーブルを見る事が出来る

MySQL インデックス操作 †

インデックス構築時 †

create index インデックス名 on テーブル名(カラム名をカンマ区切り);

• onlineオプションは無いので、大きなテーブルに対して実行するとインデックスの作成が完了するまで他のプロセスからの本テーブルに対するinsert処理などはロックされた状態になるので注意。selectはできる。

インデックス削除時 †

インデックスの削除時にテーブル名を付けなければならないのはOracleと違う。

drop index インデックス名 on テーブル名;

セッションの状態を知る †

mysqladmin processlist -uroot -p

実行中のSQLを見る †

MySQLに接続後、以下のコマンドを実行
show full processlist

プロセスをkillする †

mysqladmin kill プロセスID -uroot -p
プロセスIDはカンマ区切りで複数指定が可能

実行計画を見る †

実行計画は実行計画を表示したいSQL文のはじめに「explain」と付与することで見ることができる
explain select xxxxxx;

• Command
  • Sleep:通常は問題なし。接続はしているけれど何もしていない状態。
  • Query:問い合わせを実行している状態。
• Time

  経過秒を示す

• State
  • Waiting for table:処理待ちの状態。使いたいテーブルが既に使われているため解放されるのを待っている。
  • Sending data:処理中
  • (ブランク):特に何もなし

統計情報を取得する †

analyze table テーブル名;

• テーブルに読み取りロックがかかるため、更新などができなくなる。これを回避するオプションは存在しない。
• サンプリングして統計情報を取得するようなオプションも存在しない。全件走査してるっぽい。

テーブル操作関連 †

テーブル定義の確認 †

desc テーブル名;

テーブルの削除 †

drop table テーブル名;

テーブル名の変更 †

alter table 旧テーブル名 rename 新テーブル名;

統計情報の更新 †

analyze table テーブル名;

主キーの追加(単一の列を主キーとする場合) †

alter table テーブル名 add primary key (列名);

主キー制約の取り消し †

alter table テーブル名 drop primary key;

カラム(列)操作関連 †

カラム(列)の削除 †

alter table テーブル名 drop 列名;

カラム(列)の追加 †

alter table テーブル名 add [create tableと同じ定義]

例

alter table TEST_TABLE add TEST_COL bigint(16) default 0;

→not null制約は該当テーブルに既にデータ存在する場合は使えないとおもわれ。後から設定した方がいいとおもわれ。

列名の変更 †

alter table テーブル名 change 変更前カラム名 新列定義すべて

NOT NULL制約の追加 †

alter table テーブル名 modify カラム名 データ型 デフォルト定義 not null;

外部キー制約の除去 †

alter table テーブル名 drop foreign key 外部キー制約名;

VIEW †

VIEWの作成 †

普通にcreate viewで作成できる。
テーブルAとBを結合したビューを作成する例
create view TEST_VIEW as select A.HOGE, B.FUGO from A join B on B.HOGE = A.HOGE

作成したVIEWの確認 †

show create view ビュー名;

テーブルにコメントを付与する †

alter table テーブル名 comment = 'コメント内容';

コメント †