数値をvarchar型で格納している場合、普通にORDER BYでソートすると、以下のような意図しない順序になることがあります。
number
-------------
100
1000
200
2000
...
これを「ORDER BY (number + 0)」でソートしてやると、数値としてソートされます。
number
-------------
100
200
1000
2000
...
[参考文献]
blog.tsukubasi.jp/araki/archives/135
number
-------------
100
1000
200
2000
...
これを「ORDER BY (number + 0)」でソートしてやると、数値としてソートされます。
number
-------------
100
200
1000
2000
...
[参考文献]
blog.tsukubasi.jp/araki/archives/135
PR
MySQLで左に指定の文字を詰める関数があります。
[MySQL]
lpad('123', 5, '0');
ってやると、00123になります。
この関数をどうしてメモるかって、非常に検索しづらいからですな。
「桁合わせ」とか「001」とかで検索して四苦八苦しました(笑)
[MySQL]
lpad('123', 5, '0');
ってやると、00123になります。
この関数をどうしてメモるかって、非常に検索しづらいからですな。
「桁合わせ」とか「001」とかで検索して四苦八苦しました(笑)
MySQLでパスワードを保存したかったので色々調べました。
どうもAES_ENCRYPT関数とAES_DECRYPT関数というのが安全性が高くていいらしい。
MySQL4.0.2以降で利用可能で、AES(Advanced Encryption Standard)アルゴリズムでデータの暗号化/解読を行うそうです。
以下にサンプルを記します。
[SQL]
INSERT INTO table VALUES (AES_ENCRYPT('text', 'key_string'))
textという文字列をkey_stringを使って暗号化してます。
複合化するときは以下のような感じ。
[SQL]
SELECT AES_DECRYPT(column_name, 'key_string') FROM table
column_nameに暗号化された情報が入っているとして、それをkey_stringで複合化してます。
暗号化と複合化に使うkey_stringは同じものを使うように。
それと自分がハマったんですが、暗号化された情報を入れるカラムの型はblob型です。
varchar型に入れようとすると、長さが短くなったよくわからない文字列が入り、複合化するとNULLが返ってきます。
ちなみにblob型の長さは、パスワードの文字数に1を足して16の倍数で切り上げた分だけ必要です。
例をあげると、12文字までのパスワードを受け付ける場合は16、24文字まで受け付ける場合は32にする必要があります。
バイナリで保存したくない場合は、HEX関数でHEXダンプするとvarchar型でも保存できるそうですが、まぁそういうこともできるんだよー、という紹介までに留めておきます。
どうもAES_ENCRYPT関数とAES_DECRYPT関数というのが安全性が高くていいらしい。
MySQL4.0.2以降で利用可能で、AES(Advanced Encryption Standard)アルゴリズムでデータの暗号化/解読を行うそうです。
以下にサンプルを記します。
[SQL]
INSERT INTO table VALUES (AES_ENCRYPT('text', 'key_string'))
textという文字列をkey_stringを使って暗号化してます。
複合化するときは以下のような感じ。
[SQL]
SELECT AES_DECRYPT(column_name, 'key_string') FROM table
column_nameに暗号化された情報が入っているとして、それをkey_stringで複合化してます。
暗号化と複合化に使うkey_stringは同じものを使うように。
それと自分がハマったんですが、暗号化された情報を入れるカラムの型はblob型です。
varchar型に入れようとすると、長さが短くなったよくわからない文字列が入り、複合化するとNULLが返ってきます。
ちなみにblob型の長さは、パスワードの文字数に1を足して16の倍数で切り上げた分だけ必要です。
例をあげると、12文字までのパスワードを受け付ける場合は16、24文字まで受け付ける場合は32にする必要があります。
バイナリで保存したくない場合は、HEX関数でHEXダンプするとvarchar型でも保存できるそうですが、まぁそういうこともできるんだよー、という紹介までに留めておきます。
MySQLで、オートインクリメントを設定した主キーの値を、別のカラムでINSERT時に使用したい、という要件があったのですが、見事にハマりました(汗)
MySQLではINSERTするときに副問合せ(サブクエリ)に同一のテーブルを指定できないという制約があります。(さっき調べたところマニュアルに記述がありました。)
なので、オートインクリメントの値を使用したい場合、別のSQL文にするとかちょっとカッコ悪いことになってしまいます。
また、直接値を取りにいっても値は0になります。(オートインクリメントされる前なので当たり前といえば当たり前なんですけどね…。)
[SQL]
INSERT INTO table_name VALUES ('', 'hoge', auto_increment_column);
ついでに、MAX関数で取得しようとすると、
[SQL]
INSERT INTO table_name VALUES ('', 'hoge', max(auto_increment_column) + 1));
[結果]
「無効なグループ関数の使用」と言われてしまいます。
で、解決方法ですが、灯台下暗し。
『INSERT ~ SELECT』構文なるものがありました…valuesばっか使ってたので存在すら知らなかった。。。
『INSERT ~ SELECT』構文のマニュアル
dev.mysql.com/doc/refman/4.1/ja/insert-select.html
で、解法ですが、下記の通り。
[SQL]
INSERT INTO table_name ( text ) SELECT max(auto_increment_column) + 1 FROM table_name;
彷徨った果に見つけた下記のブログに感謝します。
このブログも誰かの助けになればよいのですが。
[参考文献]
oldblog.xenophy.com/index.php
MySQLではINSERTするときに副問合せ(サブクエリ)に同一のテーブルを指定できないという制約があります。(さっき調べたところマニュアルに記述がありました。)
なので、オートインクリメントの値を使用したい場合、別のSQL文にするとかちょっとカッコ悪いことになってしまいます。
また、直接値を取りにいっても値は0になります。(オートインクリメントされる前なので当たり前といえば当たり前なんですけどね…。)
[SQL]
INSERT INTO table_name VALUES ('', 'hoge', auto_increment_column);
ついでに、MAX関数で取得しようとすると、
[SQL]
INSERT INTO table_name VALUES ('', 'hoge', max(auto_increment_column) + 1));
[結果]
#1111 - Invalid use of group function
「無効なグループ関数の使用」と言われてしまいます。
で、解決方法ですが、灯台下暗し。
『INSERT ~ SELECT』構文なるものがありました…valuesばっか使ってたので存在すら知らなかった。。。
『INSERT ~ SELECT』構文のマニュアル
dev.mysql.com/doc/refman/4.1/ja/insert-select.html
で、解法ですが、下記の通り。
[SQL]
INSERT INTO table_name ( text ) SELECT max(auto_increment_column) + 1 FROM table_name;
彷徨った果に見つけた下記のブログに感謝します。
このブログも誰かの助けになればよいのですが。
[参考文献]
oldblog.xenophy.com/index.php