MySQL 逆引き辞典

インストール関連
ユーザ登録
- 複数のＰＣからのアクセスを許可
JDBC関連
文字コード関連
その他

MySQLを利用するにあたり、簡単な環境設定などを「逆引き辞典」的に紹介します。

また文中に「MySQLホーム」が登場しますが、これはMySQLをインストールしたディレクトリのことと読み替えて下さい。文中に MySQL5.1 といった記述があります。こちらはMySQL5のバージョン5.1に対応しているという意になります。また同じようなDriver3.1といった記述があります。こちらはJDBCドライバのバージョンの意です。

インストール関連

noinstall版のMySQLを利用する

インストールするタイプのMySQLでなく、Windows向けnoinstall版MySQLを利用する方法です。

バイナリのダウンロードと設置

インストールするバイナリファイルをダウンロードします。今回は4.1.22のnoinstall版をダウンロードします。

バイナリファイルを解凍後、設置してください。今回は「C:\MySQL\4122」に設置しました。

設定ファイルの作成と設置

以下を参考にmy.iniファイルを作成し、C:\WINDOWS\my.iniになるよう設置します。

my.iniの例

[mysqld]
basedir="C:/Program Files/MySQL/4122"
datadir="C:/Program Files/MySQL/4122/data"
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqldump]
default-character-set=utf8

[client]
default-character-set=utf8

設定ファイルのbasedirはMySQLを展開後、設置したディレクトリ、datadirはデータの保存ディレクトリになります。MySQLの展開直後は「MySQLホーム/data」ディレクトリがデータの保存ディレクトリになります。お好みにあわせ自由に移動させて下さい。

動作確認

動作確認を行います。コマンドプロンプトを２つ用意します。１つ目はＤＢ起動のため、２つ目は起動したＤＢに接続するため、になります。

まずは１つ目のコマンドプロンプトでＤＢを起動します。以下を参考に起動して下さい。起動後はコマンド受け付けない状態になります。

ＤＢ起動

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.

C:\Users\user>cd C:\MySQL\4122\bin

C:\MySQL\4122\bin>mysqld <- ＤＢを起動します。その後コマンドを受け付けません。

次に２つ目のコマンドプロンプトでＤＢに接続します。以下を参考に接続して下さい。

ＤＢ接続

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.

C:\Users\user>cd C:\MySQL\4122\bin

C:\MySQL\4122\bin>mysql <- ＤＢに接続します。
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.22-community

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases; <- 現在起動しているＤＢを確認します。
+--------------+
| Database     |
+--------------+
| mysql        |
| test         |
+--------------+
2 rows in set (0.00 sec)

mysql> exit <- ＤＢとの接続を切断します。
Bye

ＤＢ停止

コマンドプロンプトから上記で起動したＤＢを停止できれば良いのですが、起動したＤＢの停止法を筆者は知りません。筆者はタスクマネージャーから「mysqld.exe」を探し出し停止しています。

mysqladmin -u root shutdown で停止できる？

起動しない

起動するもすぐに停止してしまう。そんなときは以下をお試し下さい。解決の糸口がつかめるかもしれません。

mysqld --console

MySQLをWindowsのサービスに登録

上記の「noinstall版のMySQLを利用する」でインストールはしましたが、毎回ＤＢを起動するのは手間がかかります。Windowsにはサービスというバックグラウンドでソフトを動作させる仕組みがあるので、そちらに登録し、ＰＣ起動と同時にＤＢも起動させます。サービスに登録することをサービス化と言います。

サービスに登録

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.

C:\Users\user>cd C:\MySQL\4122\bin

C:\MySQL\4122\bin>mysqld --install <- サービスに登録します。
Service successfully installed. <- 成功時にこのように表示されます。

C:\MySQL\4122\bin>

サービスへの登録が終わりました。サービス一覧で登録されていることを確認しましょう。

MySQLをWindowsのサービスから削除

「MySQLをWindowsのサービスに登録」で登録したMySQLをサービスから削除するにはどうすればよいでしょう。以下のコマンドを参考に削除下さい。

サービスから削除

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.

C:\Users\user>cd C:\MySQL\4122\bin

C:\MySQL\4122\bin>mysqld --remove <- サービスから削除します。
Service successfully removed. <- 成功時にこのように表示されます。

C:\MySQL\4122\bin>

１台のコンピュータに複数のMySQLをサービス化

開発時に複数のバージョンのＤＢを起動したいことはありませんか。しかもサービス化させたいときにはこちらの方法で対応できます。

異なるMySQLを複数動作させるので、それぞれのMySQLを異なるポート、異なるソケットファイル名で動作させねばなりません。my.iniを編集します。portとsocketに注目して下さい。

複数登録するmy.iniの例

[MySQL41]
default-character-set=utf8
port = 3341
socket = mysql41.sock
basedir="C:/MySQL/4122"
datadir="C:/MySQL/4122/data"

[MySQL50]
default-character-set=utf8
port = 3350
socket = mysql50.sock
basedir="C:/MySQL/5084"
datadir="C:/MySQL/5084/data"

[MySQL51]
innodb_file_per_table
max_connections = 50
port = 3351
socket = mysql51.sock
basedir="C:/MySQL/5173"
datadir="C:/MySQL/5173/data"

[MySQL55]
character-set-server=utf8
innodb_file_per_table
max_connections = 50
port = 3355
socket = mysql55.sock
basedir="C:/MySQL/5540"
datadir="C:/MySQL/5540/data"

[MySQL56]
#collation-server=utf8_bin
character-set-server=utf8
#skip-character-set-client-handshake
collation-server = utf8_general_ci
default-storage-engine=innodb
innodb_file_per_table
max_connections = 50
port = 3306
socket = mysql56.sock
basedir="C:/MySQL/5621"
datadir="C:/MySQL/5621/data"

[mysql]
default-character-set=utf8

[mysqldump]
default-character-set=utf8

[client]
#default-character-set=utf8

では実際にサービスに登録します。

ここで注目すべき点は２点。１点目は「--install」の後ろに、登録したいサービス名を指定する点。このサービス名は先程のmy.iniの[]の内側と統一します。

２点目は登録したいmysqldの存在するディレクトリにcdしてからサービス化する箇所です。こちらは誤り易いので注意して下さい。

複数のMySQLをサービス化

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.

C:\Users\user>cd C:\MySQL\4122\bin

C:\MySQL\4122\bin>mysqld --install MySQL41
Service successfully installed.

C:\MySQL\4122\bin>cd C:\MySQL\5084\bin

C:\MySQL\5084\bin>mysqld --install MySQL50
Service successfully installed.

C:\MySQL\5084\bin>

サービスへの登録が終わりました。サービス一覧で登録されていることを確認しましょう(筆者は５つのMySQLをサービス化しています)。

では実際に接続してみます。接続の際はポートを指定して接続します。

ポートを指定しＤＢに接続

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.

C:\Users\user>cd C:\MySQL\4122\bin

C:\MySQL\4122\bin>mysql --port=3341
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.1.22-community

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> exit
Bye

C:\MySQL\4122\bin>cd C:\MySQL\5084\bin

C:\MySQL\5084\bin>mysql --port=3350
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.84-community

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>exit
Bye

C:\MySQL\5084\bin>

独自の名称をつけたMySQLをWindowsのサービスから削除

独自名称を付けた場合、名称を指定することで削除することができます。以下のコマンドを参考に削除下さい。

サービスから削除

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.

C:\Users\user>cd C:\MySQL\4122\bin

C:\MySQL\4122\bin>mysqld --remove MySQL41
Service successfully removed.

C:\MySQL\4122\bin>

ユーザ登録

複数のＰＣからのアクセスを許可

MySQLでは一般にGRANT文を用いてユーザ登録します。このGRANT文に若干の修正を加えるだけで、IPアドレス指定による接続許可ができます。

以下は「192.168.0.%」(%はワイルドカード)に該当するユーザからの接続を許可するユーザ登録になります。

ユーザ登録

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.

C:\Users\user>cd C:\MySQL\4122\bin

C:\MySQL\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.22-community

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT ALL PRIVILEGES ON *.* TO ユーザ名@localhost IDENTIFIED BY 'パスワード' WITH GRANT OPTION;
Query OK, 0 rows affected (0.13 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO ユーザ名@'192.168.0.%' IDENTIFIED BY 'パスワード' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES; <- 上記で設定したユーザ(正確には権限)を再読込させます。
Query OK, 0 rows affected (0.00 sec)

mysql>

JDBC関連

AS句が使えない

「select id as userid from users」のようにAS句を利用すると期待通りの動作をしない(as句が無視される)。

JDBC接続文字列に「useOldAliasMetadataBehavior=true」を追加する。

JDBC接続文字列の例 jdbc:mysql://localhost:3306/database?useOldAliasMetadataBehavior=true

日付型カラムがnullの時0000/00/00となる

JDBC接続文字列に「zeroDateTimeBehavior=convertToNull」を追加することで解決できます。

JDBC接続文字列の例 jdbc:mysql://localhost:3306/database?zeroDateTimeBehavior=convertToNull

フェッチ件数を指定

ResultSetから1件ずつデータを処理しているのにPreparedStatement#executeQueryでOutOfMemoryErrorになる場合があります。こちらはMySQLドライバ設定を行わないと、全ての行をフェッチすること(たぶん)が原因のようです。

JDBC接続文字列に以下を追加します。Sizeは件数なので要調整です。

JDBC接続文字列の例 jdbc:mysql://localhost:3306/database?useCursorFetch=true&defaultFetchSize=10

JDBCドライバで指定できるパラメータが知りたい

MySQLの公式ページを参考にして下さい。

文字コード関連

利用できる文字コードの種類

mysql> show character set

テーブルの状態を表示

mysql> show table status like 'テーブル名'\G

文字コードを指定してデータベースを作る

mysql> create databaase データベース名 default character set utf8;

その他

MySQLでもrownumを使いたい！

某ＤＢで何行目ってとれますよね？そんなことをMySQLでもしたいです。

rownumの例 SELECT @rownum := @rownum + 1 AS rownum, t.* FROM tablename t, (SELECT @rownum := 0) r

rownumでupdate！

rownumをつかってUPDATE文を利用したい場合はこちら。

rownumのUPDATEの例

UPDATE tablename AS t
JOIN (
    SELECT @rownum:=@rownum+1 rownum, 主キー
    FROM tablename
    CROSS JOIN (select @rownum := 0) r
) AS r ON t.主キー = r.主キー
SET t.アップデートしたい項目 = r.rownum

設定ファイルサンプル

筆者のmy.ini

[MySQL41]
default-character-set=utf8
innodb_file_per_table
max_connections = 50
port = 3341
socket = mysql41.sock
basedir="C:/mysql/4.1.22"
datadir="C:/mysql/4.1.22/data"

[MySQL50]
default-character-set=utf8
innodb_file_per_table
max_connections = 50
port = 3350
socket = mysql50.sock
basedir="C:/mysql/5.0.84"
datadir="C:/mysql/5.0.84/data"

[MySQL51]
innodb_file_per_table
max_connections = 50
port = 3351
socket = mysql51.sock
basedir="C:/mysql/5.1.73"
datadir="C:/mysql/5.1.73/data"

[MySQL55]
character-set-server=utf8
innodb_file_per_table
max_connections = 50
port = 3355
socket = mysql55.sock
basedir="C:/mysql/5.5.40"
datadir="C:/mysql/5.5.40/data"

[MySQL56]
character-set-server=utf8
skip-character-set-client-handshake
collation-server = utf8_general_ci
default-storage-engine=innodb
innodb_file_per_table
max_allowed_packet = 64M
max_connections = 50
port = 3306
socket = mysql56.sock
basedir="C:/mysql/5.6.21"
datadir="C:/mysql/5.6.21/data"

[mysql]
default-character-set=utf8

[mysqldump]
default-character-set=utf8

[client]
#default-character-set=utf8

設定ファイルのサンプルになります。設定のポイントは以下の通り。

複数のバージョンのMySQLをインストールしている。
頻繁に利用するMySQLのバージョンは5.6なので、ポートを3306にしている。
他のバージョンのポートは下２桁をバージョンと合わせている。