[MySQL]NULL の行を最後にしてソートする

ソート条件を付けない場合、以下のように出力されるテーブルでageがNULLのレコードは常に最下位にしてソートさせたい。
[text]+—-+———+——+
| id | name | age |
+—-+———+——+
| 1 | 名前1 | NULL |
| 2 | 名前2 | 20 |
| 3 | 名前3 | 30 |
| 4 | 名前4 | 18 |
| 5 | 名前5 | NULL |
| 6 | 名前6 | 60 |
+—-+———+——+[/text]
以下のようなSQLの場合、NULLの行が上に表示されてしまいます。
[sql]SELECT
*
FROM
users
ORDER BY
age ASC;[/sql]
[text]+—-+———+——+
| id | name | age |
+—-+———+——+
| 1 | 名前1 | NULL |
| 5 | 名前5 | NULL |
| 4 | 名前4 | 18 |
| 2 | 名前2 | 20 |
| 3 | 名前3 | 30 |
| 6 | 名前6 | 60 |
+—-+———+——+[/text]
このNULLの行を60歳のレコードの下に持って行くようなSQLは以下で実現できます。
[sql]SELECT
  *
FROM
  users
ORDER BY
  age IS NULL ASC, age ASC;[/sql]
age ASCをage DESCに変えても、問題なくNULLが最下位にソートされます。
[text]mysql> SELECT * FROM users ORDER BY age IS NULL ASC, age ASC;
+—-+———+——+
| id | name | age |
+—-+———+——+
| 4 | 名前4 | 18 |
| 2 | 名前2 | 20 |
| 3 | 名前3 | 30 |
| 6 | 名前6 | 60 |
| 1 | 名前1 | NULL |
| 5 | 名前5 | NULL |
+—-+———+——+

mysql> SELECT * FROM users ORDER BY age IS NULL ASC, age DESC;
+—-+———+——+
| id | name | age |
+—-+———+——+
| 6 | 名前6 | 60 |
| 3 | 名前3 | 30 |
| 2 | 名前2 | 20 |
| 4 | 名前4 | 18 |
| 1 | 名前1 | NULL |
| 5 | 名前5 | NULL |
+—-+———+——+[/text]

Solaris 11にMySQLをインストール

Solaris 11 にGUIを入れた環境でMySQLをインストールする手順

パッケージマネージャを起動

mysqlでパッケージを絞り込む

この中から

  • mysql-51
  • mysql-51/library

をチェック。

インストール/更新ボタンをクリック。

これで、パッケージはインストールされる。

mysqlをサービスとして設定する。

mysqlの起動確認

mysqlのプロセスが起動していることを確認。

mysqlクライアントで接続確認

正常に起動できれば、以下のメッセージが表示される。

MySQLのselectで任意の順番にソートする

MySQLで任意の順番にソートをするにはfieldを使う

id name
1 一郎
2 二郎
3 三郎
4 四郎
5 五郎
6 六郎
7 七郎
8 八郎
9 九郎

というテーブルがあるとする。

とすれば、

id
1
2
3
4
5
6
7
8
9

という一覧が返ってくる

とすると

id
1
2
3
7
8
9
4
5
6

という結果が返ってくる。

なぜか、fieldで指定していないIDが最初に表示され、最後にfieldで指定したレコードが順番に並ぶようだ。

希望としては

4,5,6が最初に表示されて、その後に1,2,3,7…と出てほしい。

これを実現するには

とする

Ubuntuに入れたMySQLに外部から接続する

UbuntuはパッケージでMySQLを入れるとすぐに起動しますが

外部からの接続が出来ないように設定されています。

/etc/mysql/my.cnf

このファイルの中に

という行があります。

これは、IPが127.0.0.1からの接続しか受け付けないですよ、という設定で

127.0.0.1は自分自身のことなので、そのマシンの中からしかつなぐことが出来ません。

この設定行をコメントアウトすることで、その制限を外すことが出来ます。

MySQLでデフォルトのキャラセットをUTF-8にする

MySQLのデフォルトのキャラセットをUTF-8にセットする

Linuxの場合

/etcにmy.cnfか、/etc/mysqlにmy.cnfがあるので、それを開く

[client]
の下に

を追記

[mysqld]
の下にも

を追記

2012年09月13日追記
※MySQL5.5からは[mysqld]で使用するキャラセットの設定はdefault-character-setではなく

character-set-serverに変わります。

サーバ再起動

[mysqld]

MySQLで巨大なSQLを送信したときに「Got a packet bigger than ‘max_allowed_packet’ bytes」エラー

MySQLで巨大なSQLを送信したときに

「Got a packet bigger than ‘max_allowed_packet’ bytes」

というエラーが出る。

これは、MySQLサーバの設定で「max_allowed_packet」があり、ここで指定したサイズよりも大きいパケットを送信したときに出るエラーのようだ。

解決方法としてはmy.cnfの[mysqld]のところに

といった感じで大きいサイズを指定することで解決する

MySQL で ビットマスク を扱う

MySQLで、テーブルの項目(type)がビットとして保存されている場合

1 真面目
2 面白い
4 硬派
8 草食系

といったtypeがあるとする

検索条件に指定する

「まじめ」のみを検索したいときは

真面目と硬派のどちらかがチェックされているデータを抽出したい場合

真面目と硬派がどちらもチェックされているデータを抽出する場合

検索結果にテキストで表示する

MySQLのlikeでの「_」の扱い

先ほど、仕事でなぜか違うデータが抽出されるんだけど…という不具合を報告され

よくよく調査したら、

という条件を見つけた。

前任者は、2001_1001_202_100とか、2001_1001_202_の後にコードが付いたデータを抽出したかったのであろうが

この条件だと2001_1001_2023というデータもヒットしてしまう。

これは、likeでは「_」は任意の一文字を意味する記号であるからである。

仕方が無いので、検索条件として使うコードの「_」をエスケープすることで解決した。

Ruby on Railsでの書き方

MySQLでCSVファイルの入出力

MySQLにはmysqldumpというダンプツールがあるが、CSVファイルでデータを出力できると、加工もしやすい。

また、CSV出力時には基本、SQL構文での出力となるため、条件指定も容易になる。

書き方としては

FIELDSオプション

  • TERMINATED BY
    項目の区切り文字を指定する
  • ENCLOSED BY
    項目を囲む文字(たとえばダブルクォーテーション)
  • ESCAPED BY
    エスケープする文字を指定(¥マークが一般的)

LINESオプション

  • TERMINATED BY
    行の末端記号(\r\n or \n)

MySQLでユーザを削除

MySQLでユーザを削除する手順

権限を削除

REVOKEで、削除したいユーザの権限を削除する

REVOKE構文について

参考に、guestユーザの権限を削除する実際の構文は

ユーザを削除