MySQL

Last Update:2014/02/13

Note

MySQL固有のコマンド等のメモ。

MySQLの基本

  • ユーザ/パスワードがある。
  • 多くのデータ型がある
  • 巨大なデータを格納することもできる。
  • 1テーブルあたり4GB(MyISAM,ただしこれはOSの制限として引っかかるため)
  • 50,000,000(5千万)レコードくらいはいける?

SQLite3よりインストール、セットアップ、バックアップに時間がかかるが、一度それらが終了すれば使いやすいRDB。 特に世の中でよく使われているDBなので、情報も豊富に存在し検索すれば大抵のことは引っかかる。

ログイン

mysql -uユーザ名 -pパスワード

例えばrootでログインする場合。

mysql -uroot -ppassword

データベース作成

CREATE DATABASE データベース名;

ユーザ作成

基本的な作り方。

GRANT ALL ON データベース名.テーブル名 TO ユーザ名@localhost IDENTIFIED BY 'パスワード' WITH GRANT OPTION;

管理者権限付きアカウント

localhostからのみアクセス可能なアカウント。

GRANT ALL ON *.* TO ユーザ名@localhost IDENTIFIED BY 'パスワード' WITH GRANT OPTION;

リモートからもアクセス可能なアカウント。

GRANT ALL ON データベース名.テーブル名 TO ユーザ名@"%" IDNETIFIED BY 'パスワード' WITH GRANT OPTION;

ユーザ削除

DROP USER ユーザ名@ホスト名;

データベースの選択

USE データベース名;

データベース一覧

SHOW DATABASES;

テーブル一覧

SHOW TABLES;

特定のテーブルのデータをsqlでダンプする

sqldump データベース名 テーブル名 > 出力先ファイル名

テーブル構成を調べる

DESC テーブル名;

csvからデータをテーブルにインポート

mysqlにログインする時にenable-local-infileオプションをつけてログインする。

sql -u root -p passwd --enable-local-infile

データを読み込む。なお、コマンドの意味は以下の通り。

  • TERMINATED 区切り文字
  • ENCLOSED データの囲み文字(?)
  • ESCAPED エスケープ文字
  • TERMINATED 行区切り文字(windows rn, Linux n, Mac r)
LOAD DATA LOCAL INFILE "path/to/file.csv"
INTO TABLE tablename
FIELDS
TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES
TERMINATED BY '\r'
;

既存のレコードを上書きしたい場合はINTOの前にREPLACEをつける。

LOAD DATA LOCAL INFILE "path/to/file.csv"
REPLACE INTO TABLE tablename
FIELDS
TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES
TERMINATED BY '\r'
;

テーブルからデータをcsvにエクスポート

SELECT * FROM tablename ORDER BY id
INTO OUTFILE "path/to/file.csv"
FIELDS
TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES
TERMINATED BY '\r'
;

日本語の文字化け対策

ターミナルからサーバに接続してselect文を発行すると 日本語が文字化けすることがある。 設定としては、

  • sql側の文字コード
  • ターミナル側の文字コード

これらを揃えなくてはならない。

MySQL側については/etc/my.cnfに文字コードの設定を行う。 その後、mysqlを再起動する。再起動は

/etc/rc.d/init.d/mysqld restart

[mysqld]
default-character-set=utf8
character-set-server=utf8

[mysql]
default-character-set=utf8

ターミナル側の文字コードは大抵GUIのメニューから変えられるはずなので省略。

autoincrementの値リセット

ALTER TABLE テーブル名 AUTO_INCREMENT = 1;

この時、AUTO_INCREMENTの付いたCOLUMNに1以上の値を持ったレコードが存在すると、その値以下には設定できない。 設定できない値にしようとすると、MyISAMでは最大値+1に、InnoDBでは変更されない。 また、テーブルの作り直しやAUTO_INCREMENTが付いたCOLUMNを作り直したりすることでもリセットされる。

カラムの追加

ALTER TABLE テーブル名 ADD カラム名 型情報;

例)

ALTER TABLE Hoge ADD name varchar(255);

既存のカラムの位置の後ろにカラムを追加する。

ALTER TABLE Hoge ADD name varchar(255) AFTER 既存カラム名;

一番前にカラムを追加する。

ALTER TABLE Hoge ADD name varchar(255) FIRST;

複数カラムの追加。

ALTER TABLE Hoge ADD (name varchar(255), num int unsigned)